Neues vom PostgreSQL Planet

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"

Check TRAKTOR 2 - less complicated, more stable, cloud ready! Compatible with PostgreSQL 16 and 17.

Stacey Haysler created a document which lists all the available volunteer options for the PostgreSQL project.

Luigi Nardi organized the Malmö PostgreSQL Meetup. Tomas Vondra presented.

A small follow up on my previous post on various Postgres scale-up avenues in my preferred order. The post received quite a bit of interest - meaning people at least still “wish” performant databases :) And - the ones who are willing to put in a bit of effort with...

Working on TRAKTOR again. The good news is, it still works with PostgreSQL 17.x and I have completely replaced psycopg2 with pg8000 to remove all native dependencies. The bad news is, if you don't want to run the arbiter as superuser it's a complicated dance of permissions, some of which can only be GRANTed by a superuser, and I have to find a way to automate this as far as possible.

Today, I would like to discuss additional techniques to speed up query execution. Specifically, I will focus on rearranging conditions in filter expressions, JOINs, HAVING clauses, and similar constructs. The main idea is that if you encounter a negative result in one condition within a series of expressions connected by the AND operator, or a positive result in one of the conditions linked by the OR operator, you can avoid evaluating the remaining conditions. This can save computing resources.