Neues vom PostgreSQL Planet

We continue the series of articles about new patches coming to PostgreSQL 18, this one covering the news of the November CommitFest.

If you missed the previous reviews, you can check them out here: 2024-07, 2024-09.

CloudNativePG 1.26 introduces one of its most anticipated features: declarative in-place major upgrades for PostgreSQL using pg_upgrade. This new approach allows you to upgrade PostgreSQL clusters by simply modifying the imageName in their configuration—just like a minor version update. While it requires brief downtime, it significantly reduces operational overhead, making it ideal for managing large fleets of PostgreSQL databases in Kubernetes.

When it comes to optimizing query performance, indexing is one of the most powerful tools available to database engineers. Both PostgreSQL and Microsoft SQL Server (or Azure SQL) use B-Tree indexes as their default indexing structure, but the way each system implements, maintains, and uses those indexes varies in subtle but important ways. In this blog post, we explore key areas where PostgreSQL and SQL Server diverge: how their B-Tree indexes implementations behave under the hood and how they…

pgvector is a widely adopted extension for PostgreSQL that is often used to handle semantic search. One can find various topics and posts dealing with AI and so on. Recently, we have posted information about semantic search in PostgreSQL (see post).

An important contribution to pgagroal.

pgagroal now has docker files!

Thanks to the contribution of Arshdeep now the pgagroal connection pooler has also docker images available on the repository.

There are two docker files: one based on Alpine Linux and one based on Rocky Linux 9.

Thanks to these docker files it should be simpler to test and do a play of the connection pooler.

It’s a tale as old as time. You want to read data. Your mate wants to write data. You’re stepping on each other’s toes, all the time. When we’re talking about relational data stores, one aspect that makes them what they are is the need to comply with the ACID properties. These are:

Sometimes, we run into a client who has port 5432 exposed to the public Internet, usually as a convenience measure to allow remote applications to access the database without having to go through an intermediate server appllication.

Do not do this.

Dear Postgres Community! In this post, I want to formally introduce Prairie Postgres, a project that has kept me really busy for the past several months.

Introduction

PostgreSQL 18 will normally (as there is always a risk of seeing something reverted until its GA release) include those commits: Add data for WAL in pg_stat_io and backend statistics:

Last Wednesday, March 26, we hosted the second of five virtual Extension Mini-Summits in the lead up to the big one at the Postgres Development Conference (PGConf.dev) on May 13 in Montreal, Canada.

Slides and transcript from my talk, "Postgres on Kubernetes for the Reluctant DBA", at Data on Kubernetes Day Europe in London on 1 April 2025.

Since I sang the praise of the 2024 Swiss PGDay, my people asked me to report from yet another small conference: the Swiss Database Synergy Day 2025, organized by the Swiss Oracle and PostgreSQL user groups.

PostgreSQL Person of the Week Interview with Akshat Jaimini: Hi! I am Akshat Jaimini, a final year undergraduate currently based out of Dehradun, India. I am working as an SDE Intern at a fintech firm called ION Group and will graduate soon in June, 2025.

A lot going on this week (well, last week - this edition of PgPedia Week is unfortunately delayed due to personal reason), as CommitFest 52 (2025-03) - the final CommitFest in the PostgreSQL 18 development cycle - draws to an end. Though usually it will continue right up until code freeze on April 8th .

Yesterday I found significant grow of seq read tuples. After some investigation I found query with strange predicate:

WHERE 1 = case when pb1_0.parent_id is not null then 0 end

It is really strange, and I had to ask, who wrote it.

The reply is - Hibernate. It is a transformation of predicate parent_id = (?) when the list of id is empty.

Unfortunately, PostgreSQL is not able to detect so this predicate is always false, and then the repeated execution ended in repeated full scans.

PostgreSQL is, without a doubt, one of the most popular open source databases in the world. Why? Well, there are many reasons, but if I had to pick just one, I’d say it’s extensibility. PostgreSQL isn’t just a database; it’s an ecosystem of extensions that can transform it to tackle any upcoming challenges. By enabling […]

JSON is one of the most popular ways of disseminating data between systems. It is probably the most common offered by webservices. PostgreSQL is a database perfectly suited for grabbing that data and transforming it into a more structured relational format. All this can be done directly in the database. We'll go over some ways to load and restructure json data.

Continue reading "Converting JSON documents to relational tables"

 
Occasionally, you will need to capture data when working with a database. You can cut-n-paste or use a shell command like script. In typical PostgreSQL fashion, psql has two ways to save your output.
The First Way You must invoke psql with the --log-file- or -L option. The entire session will be recorded.

stoker@ThinkPad:~$ psql --log-file=/tmp/psqllog demo
Password for user stoker:

On March 12, 2025 Elizabeth Christensen organized Postgres Meetup for All and the event theme was Postgres Schema Migrations. The following people contributed with their respective talks:

On March 25, Chicago PUG members gathered one more time at the Training Center of the DRW Holdings office for our monthly meetup. Our speaker was Ryan Booz, an Advocate at Redgate; he presented the talk Intro to database migrations with Flyway.