Planet PostgreSQL
17h35 06  août
à lvaro Hernández: Beyond jsonb: a generalized, unstructured data type for Postgres
jsonb supports, unsurprisingly, JSON jsonb is, undeniably, king. It is a very flexible data type, that allows for unstructured schema-less storage. It has very powerful indexing mechanisms, and its internal representation is reasonably compact and efficient. It comes with advanced operators and...
20h30 05  août
Andrew Dunstan: NSS on Windows for PostgreSQL development
Daniel Gustafsson has done some terrific work on using NSS as an alternative TLS library to OpenSSL for PostgreSQL. I’ve done some work making that build and run on Windows. Daniel recently asked how to get a working NSS on Windows to use for development, and this blog is about that process....
20h30 05  août
Bruce Momjian: Invalid Times
Have you ever wondered how invalid or ambiguous times are handled by Postgres? For example, during a daylight saving time transition in the USA, time switches either from 2AM to 3AM, or from 2AM back to 1AM. On a fall-forward day (the former), how would 2:30am be represented? Is 1:30AM during a...
09h39 05  août
Pavel Stehule: plpgsql check now supports tracing
The extension plpgsql check can be used like linter, validator for PLpgSQL language. It can be used like profiler with possibility to calculate some basic coverage metrics. And now it can be used for code tracing. In this mode, plpgsql check raises notice when function or statent is starting or is...
07h30 05  août
Kaarel Moppel: A quick pg stat statements troubleshooting hack
An introductory story (with some slight drama added for fun) from those good old days of on-site consulting So…I’m at a client where the database is not behaving nicely among other things…what a crappy DB product indeed I hear, it gets cranky every morning although there should be a...
13h07 04  août
Paul Ramsey: PostGIS and the Geography Type
PostGIS is a geospatial database and the geo in geospatial is an important qualifier: it means that all the coordinates in PostGIS point, lines, and polygons can be located somewhere on the earth.
14h45 03  août
Bruce Momjian: Differences Between Dates
What is the difference between two dates? You would think there was one answer, but there isn’t. You can give an answer in calendar terms (years months days), the number of days, or the number of seconds. Postgres offers all of these options: SELECT age(’2019-12-25’, ’2018-06-01’); ...
14h00 03  août
Andreas ’ads’ Scherbaum: Tatsuo Ishii
PostgreSQL Person of the Week Interview with Tatsuo Ishii: I live in Kanagawa prefecture of Japan, which is adjacent to Tokyo, with my wife. I love to walk beaches, listen to music (classical, rock and Jazz) from my favorite audio set (mainly consisting of TANNOY speakers and Luxman amplifiers),...
16h30 31  juillet
Bruce Momjian: Computing INTERVAL Values
The INTERVAL data type stores time duration as months, days, and seconds. Years are represented as a fixed number of months, and hours and minutes as a fixed number of seconds. Using INTERVAL values makes time computation very simple: -- Daylight saving time started in America New York on...
06h32 31  juillet
Bo Peng: Connection Pooling in Pgpool-II
Pgpool-II is a cluster management tool for PostgreSQL that can cache connections to PostgreSQL servers. This blog introduces Pgpool-II connection pooling feature and shows how to configure connection pooling in Pgpool-II.What is connection pooling? Establishing and maintaining Database connections...
23h57 30  juillet
à lvaro Herrera: PostgreSQL 13: Don’t let slots kill your primary
One of the interesting new features in PostgreSQL for some time now is the ability to control removal of WAL files using replication slots. The dark side is that replication slots can cause disks to fill up with old WAL, killing the main production server. In this article I explain PostgreSQL...
10h01 30  juillet
Amit Khandekar: Backtraces in PostgreSQL
PostgreSQL 13 has introduced a simple but extremely useful capability to log a stack trace into the server logs when an error is reported. Let’s see the details.There is a GUC to enable stacktrace generation : backtrace functions. Set it to a comma-separated function names. SET backtrace functions...
07h30 30  juillet
Hans-Juergen Schoenig: effective cache size: A practical example
A lot has been written about effective cache size in postgresql.conf and about PostgreSQL performance in general. However, few people know what this famous parameter really does. Let me share some more insights. What the PostgreSQL optimizer does The idea behind SQL is actually quite simple: The...
13h15 29  juillet
Bruce Momjian: pgFormatter
In my years with Postgres, I have seen some amazingly complex queries posted to the email lists. I have never understood how people can read complex queries with no formatting, e.g., no keyword capitalization, no indenting, no line breaks for new clauses: select n.nspname as Schema , p.proname...
19h20 28  juillet
Craig Kerstiens: Announcing pgBackRest for Azure - Fast, Reliable Postgres Backups
Backups are a key staple of running any database. Way back in the day, a good friend and colleague wrote one of the most used Postgres backup tools called wal-e. Wal-e was initially written in just a few days, and rolled out to the fleet of databases we managed in the early days at Heroku. We got...
18h26 28  juillet
Ibrar Ahmed: Migrating to PostgreSQL Version 13: Incompatibilities You Should Be Aware Of
The PostgreSQL 13 Beta is out in the testing phase with a rich feature set. It is a very good learning effort to participate in the testing of one of the finest databases in the world. It does not matter how much development, coding, and administration experience you have for the testing of the...
15h01 28  juillet
Jeff Davis: How to securely authenticate with SCRAM in Postgres 13
Making security easy to use is crucial because hard-to-use security is likely to be neglected entirely. SCRAM with channel binding is a variation of password authentication that is almost as easy to use, but much more secure. In basic password authentication, the connecting client simply sends the...
14h30 27  juillet
Bruce Momjian: Writing Style
There seem to be as many methods of writing SQL queries as ways of writing essays. While spacing, capitalization, and naming are all personal preferences, there are also logical arguments for why certain styles are better than others. This web page outlines one set of styles, based on Joe Celko’s...
14h00 27  juillet
Andreas ’ads’ Scherbaum: Gilberto Castillo
PostgreSQL Person of the Week Interview with Gilberto Castillo: My name is Gilberto, I live in Havana, Cuba. I’m married, with three sons. I love sports and I exercise together with my sons. My favorite sports are handball, baseball and basketball. I also enjoy dancing to all kind of music. Havana...
13h32 27  juillet
Hubert ’depesz’ Lubaczewski: You can now add query to your plan on
Quite a lot of people have been bugging me about it, and finally got some time, and worked on it. Long story short – it works. You can add your query below plan (in separate text area), and then it will be displayed as one of tabs For example, check this plan. It also works … Continue...
10h36 27  juillet
Hubert ’depesz’ Lubaczewski: Waiting for PostgreSQL 14 - Rename wal keep segments to wal keep size.
On 20th of July 2020, Fujii Masao committed patch: Rename wal keep segments to wal keep size.   max slot wal keep size that was added in v13 and wal keep segments are the GUC parameters to specify how much WAL files to retain for the standby servers. While max slot wal keep size accepts the...
00h00 27  juillet
Luca Ferrari: PostgreSQL 13 Explain now includes WAL information
The upcoming version of PostgreSQL now includes new information in the EXPLAIN output. PostgreSQL 13 Explain now includes WAL information The upcoming PostgreSQL 13 includes a lot of new features, as a very consolidated habit in every release. One interesting feature among the others is that...
08h31 25  juillet
Tatsuo Ishii: Snapshot Isolation Mode
Pgpool-II 4.2 is under developmentPgpool-II developers have been working hard for upcoming Pgpool-II 4.2 release, which is supposed to be released around this September or October.Snapshot isolation mode is comingFor the new release I have just added new clustering mode called Snapshot isolation...
22h05 24  juillet
cary huang: Approaches to Achieve in-Memory Table Storage with PostgreSQL Pluggable API
1. Introduction Recently, I have had an opportunity to perform some in-depth feasibility study in-memory table using PostgreSQL’s pluggable storage API. The pluggable storage API was introduced Since PostgreSQL v12 and it allowed custom table storage Access Methods (AM for short) to be...
13h30 24  juillet
Bruce Momjian: Encryption at Rest
There are many security guides that require encryption at rest. However, it is unclear exactly what at rest means. Encrypted at rest can potentially mean encrypted when the: Storage is powered off File system is unmounted Database is not running Data is in the kernel’s file system...
07h00 24  juillet
Kaarel Moppel: What is fillfactor and how does it affect PostgreSQL performance?
Recently I was asked if there’s a rule of thumb best practice for setting the fillfactor in Postgres – and the only answer I could give was to decrease it a bit if you’re doing lots and lots of updates on some table. Good advice? Well it could be better of course – this kind of...
01h52 24  juillet
movead li: The SPI feature under PostgreSQL kernel
Server Programming Interface(SPI) is a module in the PostgreSQL kernel which allows kernel developers to execute SQL statements in C functions and have the ability to manage transactions. This module provides great convenience for PostgreSQL plug-in development through its characteristics so that...
22h00 23  juillet
Paolo Melchiorre: Speaking online at EuroPython 2020
An article about my first participation as a speaker at an online conference: EuroPython 2020.
13h26 23  juillet
Peter Eisentraut: Unicode normalization in PostgreSQL 13
Unicode equivalence Unicode is a complicated beast. One of its numerous peculiar features is that different sequences of codepoints can be equal. This is not the case in legacy encodings. In LATIN1, for instance, the only thing that is equal to ‘a’ is ‘a’ and the only thing...
22h00 22  juillet
Paolo Melchiorre: EuroPython 2020
EuroPython is the official European conference for the Python programming language.