Neues vom PostgreSQL Planet

I have just completed the first draft of the Postgres 18 release notes. It includes a little developer community feedback but still needs more XML markup and links.

Slides and transcript from my talk, "Anatomy of a Database Operation", at DjangoCon Europe in Dublin on 25 April 2025.

I'll share the recording as soon as it's available.

On April 23, we hosted the fourth of five (5) virtual Mini-Summits that lead up to the big one at the Postgres Development Conference (PGConf.dev), taking place May 13-16, in Montreál, Canada.

On 7th of April 2025, Álvaro Herrera committed patch: Allow NOT NULL constraints to be added as NOT VALID   This allows them to be added without scanning the table, and validating them afterwards without holding access exclusive lock on the table after any violating rows have been deleted or fixed.   Doing ALTER TABLE … Continue reading "Waiting for PostgreSQL 18 – Allow NOT NULL constraints to be added as NOT VALID"

 I am (slowly) adding handy PostgreSQL queries to my GitHub, and Vacuum is the newest category.  The end goal is to have a compilation of queries for those of us who need to keep an instance healthy.

Over the years, I have collected hundreds of various queries and hate hunting them down in my code snippet library. Finally, they will be in one place and easy to search. 

Please contribute if you have similar or better queries (hint, hint!).

Introduction

Recently, we had a long discussion in our internal chat about the concept of Trusted Extensions in PostgreSQL. It became clear that while the feature is very useful, it’s often misunderstood — especially by beginners. Let's fix that!

This post explains what trusted extensions are, why they exist, how they work, and provides some important hints and warnings for everyday use.

I recently discovered an unexpected behavior in PostgreSQL involving a pattern of using a Common Table Expression (CTE) with DELETE ... RETURNING and LIMIT to process a batch of items from a queue-like table. What seemed straightforward turned out to have a surprising interaction with the query planner. The scenario Let’s say you have a task_queue table and want to pull exactly one task for a specific queue_group_id. A common approach is using a CTE:

In the previous blog post we have seen how plenty of ram doesn’t necessary results in a faster instance.

This time we’ll quickly see how PostgreSQL and Linux relate to each other in particular if we want to run our RDBMS at scale.

Then we’ll have a look to the virtual memory in Linux and how to make it more friendly to PostgreSQL.

Let’s dig in.

We did it!

Thank you so much to everyone who made this event a success! Starting with the great talks selection (thank you, CfP committee!), to our amazing sponsors (thank you, Pat Wright!), to volunteers, to attendees!

My highlights were having multiple new speakers, new topics, and seeing a lot of new attendees for many of whom this was their first Postgres conference! I hope you all enjoyed it, and I hope to see you again!

© Laurenz Albe 2025

PostgreSQL is famous for its stability, and rightly so. But that does not mean that it can never crash. And while PostgreSQL usually cleans up after itself, it has no good way to do so after a crash (after all, it has lost its memory). As a consequence, you can end up with orphaned files in your data directory. If these files are small, they probably won't worry you. But sometimes you can end up with a lot of junk in your data directory. It is notoriously difficult to deal with that problem, so I decided to write about it.

Back in 2022, we covered how to log database query generation information from a web app using pg_stat_statements for Postgres. https://andyatkinson.com/blog/2022/10/07/pgsqlphriday-2-truths-lie

The application context annotations can look like this. They’ve been re-formatted for printing:

We continue to follow the news about PostgreSQL 18. The January CommitFest brings in some notable improvements to monitoring, as well as other new features.

You can find previous reviews of PostgreSQL 18 CommitFests here: 2024-07, 2024-09, 2024-11.

Reviews of the extension search path patch, now committed and slated for PostgreSQL 18, revealed a few issues with extension configuration. Based on the ensuing discussion, and even though PostgreSQL 18 will include workarounds, it’s best to make adjustments to the extensions you maintain, the better to serve existing PostgreSQL versions and to hew closer to best practices.

Thus, a couple of recommendations for extension maintainers.

PostgreSQL Person of the Week Interview with Adam Hendel: My name is Adam Hendel and I live in Minnesota (USA). I grew up playing ice hockey and it was a very important part of my life up until age 21. Then I went on to get bachelor degrees in finance and physics and amasters in data science. I served in the U.S. Army military intelligence corps after college. I’ve been working in data science, machine learning, and software engineering ever since.

PostgreSQL databases tend to grow silently. One day, you notice queries slowing down, backups taking forever, and no one remembering what is in that 500 GB log table. If this sounds familiar, you’re not alone.

Partitioning makes things manageable

If your database has one massive table that keeps growing, partitioning can be a lifesaver. PostgreSQL has supported native declarative partitioning since version 10, and recent versions (13 and up) have made it much easier to work with.

Last week I visited the Malmö PUG to talk about performance cliffs. It’s a really nice meetup - cozy environment, curious audience asking insightful questions. I highly recommend attending or even giving a talk there.

A very minimal edition this week, due to lack of time, and it's been relatively quiet anyway.

more...

Postgres is a powerful and feature-rich database, but like any system, it has certain limits that are good to be aware of. In this post, we'll take a look at a few interesting limits in Postgres.

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use.
This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies.

When I have set returning functions and even non-set returning functions, I love putting them in the FROM clause instead of the SELECT part. I often take it for granted that the results are usually the same, but in some important situations, they are different. It's not guaranteed to be the same when your function is not immutable.

Continue reading "FROM function or SELECT function"