Neues vom PostgreSQL Planet

PostgreSQL 18 changes

CommitFest 2025-01 came to an end this week, with a final batch of user-visible changes:

Slides and transcript from my 5 minute lightning talk about the PostgreSQL Europe Diversity Task Force at FOSDEM PGDay 2025.

This was a tough one to fit into 5 minutes, but I'm pleased with how much I managed to share.

Did you know that PGEU now has a Diversity Task Force?

• but Why?
• Who’s working on it?
• What’s being done?
• and How can you get involved?

 

PostgreSQL is already known for its reliability, extensibility, and open-source pedigree and continues to grow and evolve with each release. PostgreSQL 17 introduces several performance improvements and features that make it a powerhouse for OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) workloads.This blog will explore advanced performance tuning techniques for PostgreSQL 17 and highlight key improvements compared to versions 15 and 16.

On 16th of January 2025, Dean Rasheed committed patch: Add OLD/NEW support to RETURNING in DML queries.   This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries to explicitly return old and new values by using the special aliases "old" and "new", which are automatically added to the query (if not already defined) while parsing its … Continue reading "Waiting for PostgreSQL 18 – Add OLD/NEW support to RETURNING in DML queries."

Starting with version 17, PostgreSQL provides a feature many Oracle users have been waiting for: the LOGON trigger. The idea is to make the database launch a function as soon as the user tries to log in.

This post explains how this works and how such a LOGIN trigger (as it is called in the PostgreSQL world) can be implemented and used in real life.

A Legacy of PostgreSQL in GSoC

Google Summer of Code 2025 is here, and PostgreSQL is once again proud to participate in this amazing program! Over the years, many talented contributors have started their PostgreSQL journey through GSoC, with some becoming long-term members of our community. Their commitment has helped improve PostgreSQL and its ecosystem, making this program an essential part of our growth and innovation.

It’s time for the fourth episode of Postgres Café, a podcast from our teams at Data Bene and Xata where we discuss PostgreSQL contribution and extension development.

I delivered my presentation "Databases in the AI Trenches" today as part of a tutorial at Prague PostgreSQL Developer Day, so I have placed my slides online. It focuses on semantic search, generative AI, and RAG, and builds on my discriminative AI talk, "Postgres and the Artificial Intelligence Landscape."

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...