Neues vom PostgreSQL Planet

On 5th of April 2025, Álvaro Herrera committed patch: Add modern SHA-2 based password hashes to pgcrypto.   This adapts the publicly available reference implementation on https://www.akkadia.org/drepper/SHA-crypt.txt and adds the new hash algorithms sha256crypt and sha512crypt to crypt() and gen_salt() respectively.

Just a quick note to users of pgenv and anyone else who compiles Postgres on macOS. In macOS 15.4, Apple introduced a new API, strchrnul, which is common from other platforms. As a result attempting to compile Postgres on 15.4 and later will lead to this error:

I wrote User, User, Who Is the User? in January of 2024, but didn't publish the slides at that time. I was planning to publish them once I delivered the presentation, but it has been a year and I have still not deivered the talk, so I am now publishing the slides.

On 4th of April 2025, Andrew Dunstan committed patch: Non text modes for pg_dumpall, correspondingly change pg_restore   pg_dumpall acquires a new -F/--format option, with the same meanings as pg_dump. The default is p, meaning plain text. For any other value, a directory is created containing two files, globals.data and map.dat.

Performance optimization in a production database is important, but trying to over-optimize can make things more complicated without real improvements.

In this post, I’ll share two very basic EXPLAIN ANALYZE outputs from a production system. A user asked us to help optimize these queries. I've changed the table and column names for privacy. We will look at how to spot slow parts of a query, improve performance the right way, and avoid unnecessary tuning.

Plan A: Identifying and Resolving a Bottleneck

In the previous post we have imported a fairly large data set containing Wikipedia data, which we downloaded using pgai. However, importing all this data is not enough because we also need to keep an eye on efficiency. Therefore, it is important to understand that indexing is the key to success.

In general, pgvector provides us with two types of indexes that we can use:

- hnsw
- ivfflat

Last week Christoph Berg, who maintains PostgreSQL’s APT packaging system, gave a very nice talk on that system at the third PostgreSQL Extension Mini-Summit.

 Way back in PostgreSQL 9.5, there was a simple entry in the release notes:

Improve PL/pgSQL array performance (Tom Lane)

We are proud of what we have done in Bolzano.

PgTraining OpenDay is over!

Last Friday was PgTraining OpenDay in Bolzano, a free of charge day entirely dedicated to PostgreSQL, that we at PgTraning organized.

Well, the code freeze has come and gone, so it's all over for PostgreSQL 18 development, bar the testing. RC1 is likely due at the start of September, so fire up those test environments sooner rather than later!

Any new features from here on out will be for the future PostgreSQL 19 , whose first CommitFest will start in July ( 2025-07 ).

Shortly before the code freeze for PostgreSQL 18, Robert Haas added a feature that allows external modules to provide additional information to the EXPLAIN command.

This was a long-awaited feature for me. For an extension that influences the query planning process, providing users with notes on how the extension has affected the plan makes perfect sense. Instead of merely writing to a log file - access to which is often restricted by security policies - this information may be made available through the EXPLAIN command.

I was watching Heikki Linnakangas’s talk on the Postgres protocol. I wanted to follow along with and see the protocol in action for myself. I ran into a problem here: my ostgres server rejected all non secure connections. And with TLS enabled, I could not get wireshark to decode postgres protocol messages because of encryption.

CommitFest 52 (2025-03) - the final CommitFest in the PostgreSQL 18 development cycle - is approaching its conclusion, running as always beyond the end of March right up until code freeze on April 8th .

There have been a lot of interesting changes during this end spurt, and combined with personal reasons mean it's been challenging keeping track of them. As always, if there's something missing, please let me know via the feedback form.

After meeting with another database fellow recently - the evergreen topic of database scaling popped up again. That won’t go out of fashion anytime soon I guess, despite the AI advancements and the rise of Postgres-aware chatbots a la Xata Agent. Namely - when to grab for which designs, technical...

A few weeks ago I wrote about the introduction of pgNow, a free, cross-platform desktop application from Redgate that is designed to assist users who might lack extensive Postgres experience or a robust monitoring solution. It provides real-time data on queries, indexes, high-value configuration settings, and vacuuming setup and efficiency to help you pinpoint and ... Read more

Postgres is a data storage tool, and as a tool it is neutral in how it is used. Tools can be used for good, for harm, and because Postgres is open source, the community doesn't even know how it is used.

When dealing with AI, one of the key things is: Where can I find data to experiment with? While there are many places out there on the internet to satisfy the need for test data, Wikipedia is certainly one of the most interesting candidates to get you started. So: How can we import Wikipedia into PostgreSQL and make use of it? Here is how it works.

On February 25, 2025 Christoph Berg committed the patch:

This Wednesday, April 9 at noon America/New_York (16:00 UTC) for Extension Mini Summit #3, where Christoph Berg will take us on a tour of the PostgreSQL Global Development Group’s APT repository with a focus on packaging ex

PostgreSQL Person of the Week Interview with Frédéric Delacourt: I live in Normandy, France. I’m sure you know the place since it is where „D-Day - 6th june 1944 The battle of Normandy in France“ took place.