Neues vom PostgreSQL Planet

PostgreSQL version 12 introduced a new option on the VACUUM command, INDEX_CLEANUP. You should (almost) never use it.

First, a quick review of how vacuuming works on PostgreSQL. The primary task of vacuuming is to find dead tuples (tuples that still exist on disk but can’t ever be visible to any transaction anymore), and reclaim them as free space. At a high level, vacuuming proceeds as:

© Laurenz Albe 2024

During training sessions, I tell my students how they can tune transactional workloads by disabling the parameter synchronous_commit. It is easy to demonstrate the striking effects of that measure, but the possibility to lose committed transactions during an operating system crash makes it a non-starter for many applications.

It is rare that a Postgres table keeps the exact same structure year after year. New columns get added. Old columns get dropped. Column data types need to change. Those are all done with the ALTER TABLE command. One big drawback to these changes is that they may force a complete table rewrite. A rewrite means a completely new copy of the table is created, and then the old one is dropped. This can take a very long time for large tables. Worse, everything else is blocked/locked from using the table, so your application may need downtime.

In this blog (the third in my series), I'd like to present yet another new feature in the PostgreSQL 17 release: enhancement to logical replication functionality in PostgreSQL. The blog will also provide a small script that demonstrates how to use this feature when upgrading from Postgres 17 to a future version.  In my prior blogs, (also published on Planet PostgreSQL, and DZone) I have written about other PG-17 features which you can read about:

In " an awkward gesture in a moment of enthusiasm ", we have decided it is no longer appropriate to post on the website formerly known as "Twitter". Let that sink in. Please follow us on BlueSky (or even via RSS ).

And one more tale from the frontline!

When I encounter cases like this, I start wondering whether I am the first person who ever tried to do “this” for real, whatever “this” is.

On 6th of January 2025, Nathan Bossart committed patch: Allow changing autovacuum_max_workers without restarting.   This commit introduces a new parameter named autovacuum_worker_slots that controls how many autovacuum worker slots to reserve during server startup.

Autovacuum is one of PostgreSQL's most powerful features, designed to maintain database health and optimize performance by automating routine maintenance tasks. However, improper configuration can lead to performance bottlenecks, excessive costs due to resource inefficiency, or uncontrolled table bloat. This blog explores what autovacuum is, its role in performance optimization and cost reduction, and best practices for configuring its parameters.

People continue to tell me on a semi-regular basis how much they appreciate these approximately annual posts, the first of which came out in April of 2017. I think this might be more because the project doesn't have enough official ways to recognize people than it is an endorsement of the particular thing that I've done here, the limitations of which I am always careful to mention.

And as promised, the recording is here! Enjoy

As promised – January meetup recording

PGDay CERN 2025 was held on January 17, 2025 at CERN, Genève, Switzerland

List of contributions:

Recently there was a nice article on the Planet PostgreSQL feed on Postgres’ parallel query capabilities and the pertinent tuning parameters. All good and logical, the main settings and considerations were highlighted…but then I suddenly remembered that there’s one more parallelism relevant parameter that for some reason is mostly always...

I have been working recently on making various scanners and parsers in PostgreSQL thread-safe, and this article is a bit of a brain dump to record what I did, what the different steps were, because all of that was pretty difficult to piece together, and it might be worth recording somewhere what was found and learned.

If you are using PostgreSQL for analytics or large-scale aggregations, you might occasionally notice the planner making false assumptions regarding the number of rows. While this isn't a problem for small aggregates, it is indeed an issue for large-scale aggregations featuring many different dimensions.

In short: The more columns your GROUP BY statement contains, the more likely it is that optimizer overestimates the row count.

This blog explains how this can be handled in PostgreSQL.

One of my favorite uses of JSONB functionality is to unpivot data.

Continue reading "Unpivoting data using JSONB"

The schedule of the free event is available!

Open Day 2025 in Bolzano (Italy): schedule available

The schedule of the next free event organized by PgTraining is available.

The event, that will be held in the great NOI Techpark in Bolzano (Italy) will be organized in two parts:

Not all operations require the same level of locking, and PostgreSQL offers tools and techniques to minimize locking impact.

The PostgreSQL Project is present with a booth at FOSDEM ever since 2007. And ever since 2008 we organize a Devroom, starting 2013 we also have our own PGDay on the Friday before FOSDEM. This year it’s the 11th FOSDEM PGDay, skipping 2021 and 2022 for obvious reasons. This blog post provides useful information for visitors of the PGDay, the booth and the PostgreSQL Devroom at FOSDEM. Welcome to FOSDEM PGDay 2025 When does the PGDay take place, and can I still attend?

That happened one long and warm Thai evening when I read another paper about the re-optimisation technique in which the authors used Postgres as a base for implementation. Since I had nearly finished with the WIP patch aiming to do the same stuff in the Postgres fork, I immediately began comparing our algorithms using the paper's experimental data as a reference. However, I quickly realised that neither my code nor even the standard Postgres instance bore any resemblance to the paper's figures.

PostgreSQL 18 changes pg_stat_io count IOs as bytes instead of blocks for some operations postgres_fdw : SCRAM authentication can be passed through using new option use_scram_passthrough psql x can be appended to various list commands (e.g.