Neues vom PostgreSQL Planet

Relational databases are at the heart of countless applications around the world, from high-traffic e-commerce websites to enterprise resource planning (ERP) systems and financial services. Concurrency management—where multiple database transactions operate on the same data simultaneously—is critical to getting good performance and avoiding problems like deadlocks or data inconsistencies.

Django and its admin interface are a big part of why Caktus uses Django, but the admin's ability to log database changes is limited. For example, it shows only changes made via the Django admin, not via other parts of the site.

Nowadays it’s not uncommon to deal with machines with hundreds of GB of RAM.

Abundant memory can give PostgreSQL a massive performance boost. However things work slightly different than you may expect.

Let’s find out!

Back in November, I met with Claire Giordano, host of the Talking Postgres podcast, who asked a ton of great questions about my experience writing a Postgres book aimed at Ruby on Rails web developers.

Some questions

Claire had a lot of thoughtful questions. Here’s a few:

• Why write a technical book? Was there some moment or spark?
• Why write a book about Postgres?
• Why Ruby on Rails?

Fun topics

Claire also brought up a lot of fun points and reactions. Here’s a sample:

To honor the name of the site (boringSQL) let's deep dive into a topic which might sound obvious, but it might be never ending source of surprises and misunderstanding.

Simple things

We can start with the simple statement like

SELECT '2025-03-30 00:30' as t;

Result:

 I have started a repository of handy SQL Queries for Database Administration, which I have collected over the years. Finding unused or duplicate indexes or queries without indexes and other information can speed up response times. Knowing statistics like cache hit rates can aid in planning upgrades. 

Histograms were first used in a lecture in 1892 by Karl Pearson — the godfather of mathematical statistics. With how many data presentation tools we have today, it’s hard to think that representing data as a graphic was classified as “innovation”, but it was. They are a graphic presentation of the distribution and frequency of data. If you haven’t seen one recently, or don’t know the word histogram off the top of your head - it is a bar chart, each bar represents the count of data with a defined range of values. When Pearson built the first histogram, he calculated it by hand.

As you may have heard, there are reportedly over 1,500 PostgreSQL servers that have been exploited to mine Bitcoin. And your server could be next if you haven't taken precautions. Firstly, you need to update to the latest minor release, just so no known exploitable bugs exist on your system. But regardless of whether you update, your PostgreSQL instance could still be misconfigured in a way that would allow unwelcome visitors access, so you need to make sure you shore up your defenses.

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.