Latest Blog Posts

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:

A whole lot of effort is then made to build databases that both allow you to meet the necessary ACID properties while simultaneously letting lots of people into your database. PostgreSQL does this through the Multi-version Concurrency Control (MVCC). In this article we’ll discuss what MVCC is and how PostgreSQL deals with concurrency in order to both meet ACID properties and provide a snappy performance profile. Along the way we’ll also be talking once more about the VACUUM process in PostgreSQL (you can read my introduction to the VACUUM here).

Let me start by giving you the short version of what MVCC is, and then the rest of the article explains more details. Basically, PostgreSQL is focused on ensuring, as much as possible, that reads don’t block writes and writes don’t block reads. This is done by always, only, inserting rows (tuples). No updates to an existing row. No actual deletes or updates. Instead, it uses a logical delete mechanism, which we’ll get into. This means that data in motion doesn’t interfere with data at rest, meaning a write doesn’t interfere with a read, therefore, less contention & blocking. There’s a lot to how all that works, so let’s get into it.

Concurrency Modes in PostgreSQL

The world can be a messy place. If everything in a database were ordered, completely in series, including exactly who could access what and when they could access it, we’d nev

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.

This report of a “security issue” in PostgreSQL is alarmist, because it’s a basic brute-force attack on PostgreSQL, attempting to get supueruser credentials. Once it does so, it uses the superuser’s access to the underlying filesystem to drop malware payloads.

There’s nothing special about this. You could do this with password-auth ssh.

But it’s one more reason not to expose PostgreSQL’s port to the public. There are others:

• You open yourself up to a DDOS attack on the database itself. PostgreSQL is not hard to do a DOS attack on, since each incoming connection forks a new process.
• There have been, in the past, bugs in PostgreSQL that could cause data corruption even if the incoming connection was not authenticated.

As good policy:

• Always have PostgreSQL behind a firewall. Ideally, it should have a non-routable private IP address, and only applications that are within your networking infrastructure can get at it.
• Never allow remote logins by superusers.
• Make sure your access controls (pg_hba.conf, AWS security groups, etc.) are locked down to the minimum level of access required.

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.

Recently, you’ve seen a lot of me online, talking about things I want to do and things I am already doing. Thinking about all these activities holistically, I can group them all around three common themes: Postgres “elementary education,” connecting with application developer communities, and connecting with educational institutions. The overall goal is broader promotion of knowledge about Postgres and its best practices.

Why do I think this is important? Postgres is undoubtedly the most beloved and most well-known open-source database, but too many people jump into the ocean of possibilities without being equipped with even basic knowledge. This creates a barrier for adoption and results in disappointments and not being able to get the most out of PostgreSQL.

The most important mission of Prairie Postgres is creating Postgres educational opportunities in the United States Midwest States. We want to focus on Midwest because other parts of the US have PostgreSQL User Groups and conferences, and the Midwest has a lot of Postgres but not enough community activities. We know that there are many people in the region working with Postgres, and we want to reach out to them. I often hear the sentiment that we see “all familiar faces” at Postgres events, and this includes not only the speakers, but the attendees as well, and we want to change that.

We are making our very first steps, but even these first steps wouldn’t be possible without tremendous help from many people. Firstly, I want to thank Pat Wright who gave me the idea to create an NFP, and then helped navigate the legal field – I can’t even imagine how long it would have taken us without his help.

Next I want to thank Dian Fay and Anna Bailliekova, Prairie Postgres co-founders, who jumped into this adventure and took upon themselves the most important task of never letting me make any dec

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:

commit a051e71e28a12342a4fb39a3c149a197159f9c46
Author: Michael Paquier 
Date:   Tue Feb 4 16:50:00 2025 +0900

Add data for WAL in pg_stat_io and backend statistics

This commit adds WAL IO stats to both pg_stat_io view and per-backend IO
statistics (pg_stat_get_backend_io()).
.
.

and Add WAL data to backend statistics:

commit 76def4cdd7c2b32d19e950a160f834392ea51744
Author: Michael Paquier 
Date:   Tue Mar 11 09:04:11 2025 +0900

Add WAL data to backend statistics

This commit adds per-backend WAL statistics, providing the same
information as pg_stat_wal, except that it is now possible to know how
much WAL activity is happening in each backend rather than an overall
aggregate of all the activity.  Like pg_stat_wal, the implementation
relies on pgWalUsage, tracking the difference of activity between two
reports to pgstats.

This data can be retrieved with a new system function called
pg_stat_get_backend_wal(), that returns one tuple based on the PID
provided in input.  Like pg_stat_get_backend_io(), this is useful when
joined with pg_stat_activity to get a live picture of the WAL generated
for each running backend, showing how the activity is [un]balanced.
.
.

It means that:

• WAL IO statistics are available per backend through the pg_stat_get_backend_io() function (already introduced in Postgres backend statistics (Part 1))
• WAL statistics are available per backend through the pg_stat_get_backend_wal() function

So that we can see the WAL activity in each backend.

Let’s look at some examples

Thanks to the pg_stat_get_backend_io() function, we can:

Retrieve the WAL IO statistics for my backend

db1=# SELECT backend_type, object, context, reads, read_bytes, read_time, writes, write_bytes, write_time, fsyncs, fsync_time  FROM pg

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.

Introduction

This is me!
As you can see from the diagram representing my career so far (and as you already know if you've read my posts or watched my talks before), I have a database background.
I was a DBA for 20 years before I moved into database consultancy, and I’m now a senior solutions architect at Crunchy Data, working with customers to design, implement and manage their database environments, almost exclusively on Kubernetes.

Over the past few years, I’ve given a lot of talks about running Postgres on Kubernetes, and I work with a lot of customers who are at various different points on their databases on Kubernetes journey.
The questions from the audience, and hallway conversations at conferences are always interesting, and tend to come from one of 2 groups of people:

1. People who are deep into running databases on kubernetes and are looking for answers to some tricky technical issue or architectural question.
2. Self-proclaimed “old-school DBAs” who still aren’t convinced that running databases in containers is a good idea.

I prepared this talk especially for that 2nd group of people, so I hope some of you are in the audience today!
And don’t forget, as you saw on the previous slide, I come from an old-school DBAs background, so I’ve gone through that process.

To get an idea of who was in the audience, I asked the question
What’s your main area of responsibility in your current role? There was a reasonably even split betweeen:

• Databases
• System or Platform Administration
• Devops or Automation
• Development

The plan for the session was:

1. Some Databases on Kubernetes background.
2. Some audience participation.*
3. A look at some of the main concerns that DBAs have about running DBs on Kubernetes.
4. Some of the challenges you might encounter, and how you can overc

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.

A joint event with Oracle and PostgreSQL? You must be kidding!

It may indeed be surprising to hear of such an event. But then, PostgreSQL people have held talks at the yearly conference of the German Oracle User Group. The Austrian Oracle User Group held an event dedicated to the interplay of Oracle and PostgreSQL. Furthermore, previous editions of the Austrian PGDay were co-sponsored by an Oracle support company. And this list only covers events in my part of the world.

In addition, there is this nice reminder that the Polish Oracle User Group has sent my boss after he had talked there a while ago:

Still, there is a difference between PostgreSQL people talking at an Oracle conference and a joint event. I can understand why PostgreSQL people are interested in Oracle conferences: after all, a lot of our business comes from Oracle users who have fallen out of love with their database. But what motivates the Oracle users? Are they just curious and open-minded? Do they think the future belongs to PostgreSQL? Or is it just their way of showing dissatisfaction with Oracle?

Then there is the ambivalent word “synergy” in “Database Synergy Day”. Fundamentally, “synergy” is something positive, but in today's business speak it usually refers to fusioning two companies or departments. Rather than increasing productivity, that means that the two entities spend a year fighting for dominance until one of them ends up on top. All the while, the productivity is near zero. I sincerely hope that I am not about to witness that kind of synergy...

Well, I'm going to find out.

The speakers' dinner

It is customary to invite the speakers to a dinner as a “thank you” for their (unpaid) effort. This conference is no exception. As I said in my article on the Swiss PGDay 2024, a PostgreSQL feels a

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.

Fortunately, Postgres has simple workaround - conditional index

CREATE INDEX ON TABLE pb(id)
  WHERE 1 = case when pb1_0.parent_id is not null then 0 end

This index is always empty, and then index scan is fast.

This issue should be fixed in more recent versions of Hibernate where predicate 1=0 is generated instead.

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.

The First Way

The Second Way

The other choice is to use -o or --output= when you start psql. The differences are that the SQL command is not recorded

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:

• Ryan Booz talked about Flyway product;
• David Wheeler talked about open source project Sqitch;
• Gülçin Yıldırım Jelínek talked about open source tool pgroll.

PGConf India 2025 took place on March 05 - 07, 2025 in Bangalore, India. The Organizing Team of PGConf India 2025 consists of the following people:

• Pavan Deolasee
• Ashish Mehra
• Nikhil Sontakke
• Aditya Raje
• Hari Kiran

The Program Committee entrusted with selecting talks and presentations for PGConf India, 2025 consists of the following people:

• Amit Kapila
• Jobin Augustine
• Melanie Plageman
• Pavlo Golub
• Shaun Thomas

PostgreSQL Person of the week: Doug Ortiz. Interview conducted by: Andreas Scherbaum.

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.

Fun fact: Ryan was one of the first presenters at Chicago PUG at our new location at DRW in January 2023. It was only the second meetup at that location, and Ryan braved the Chicago winter to come and present here, which shows a real dedication from somebody who is not local to the Midwest

At that time, our Training Center was undergoing reconstruction, so we met in one of the conference rooms. Interestingly, Ryan’s talk in January 2023 was the earlier version of the same talk, and the topic is still more than relevant.

I would say that the March meetup was perfect in each of the aspects: the speaker, the presentation, the audience participation, the makeup of the audience, and the discussions we had after the presentation. My only oversight was that we ran out of pizza! That happen to me for only a two or three of times in all eight years I have been running this meetup. In my defence, that meetup was unusual in a positive way: the majority of those who RSVPed, actually showed up :).

Many thanks to Ryan and all Chicago PUG members! You are the best!

PostgreSQL offers advanced capabilities through extensions like pgvector, which enable semantic search at a level and quality never achieved before. Unlike traditional text search, which mostly relies on trivial string comparison, semantic search in PostgreSQL goes beyond keywords by understanding context and
meaning, enhancing relevance.

With extensions such as pgvector and pgai it is possible to combine the power of a relational database with cutting- edge features of modern AI models.

Benefits of Semantic Search in PostgreSQL

The benefits of semantic search are plentiful:

• Improved search accuracy and user experience
• Handling ambiguity
• Generate content recommendations
• Create personalized content

pgvector: Create a table for text data

After our brief introduction, it is time to take a look at the technical aspects
and see how this can be done for real. For the purpose of this demo we have used the excellent data set available here. It contains 3.6 million Amazon reviews, which are in an easy to use CSV format.

The first thing we have to do is to load the extension into our database and create a table that we can use to store the data we want to query:

cybertec=# CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION

cybertec=# CREATE TABLE t_document 
(
        id              serial          PRIMARY KEY, 
        polarity        float, 
        title           text, 
        body            text, 
        embedding       vector(384) 
);
CREATE TABLE

There are two important things to note here. The first observation is that the extension has been loaded into the database. Note that the name we have to use here is "vector", and not "pgvector" as one might expect. The second noteworthy thing is the fact that a new data type called "vector" is used here. However, the extension has more data types:

• vector: up to 2,000 dimensions
• halfvec: up to 4,000 dimensions
• sparsevec: up to 1,000 non-zero elements

However, for our use case we need

We are currently setting up a staging server (called beta) for a project consisting of two sub-projects and a bunch of services in each project. Each service uses a distinct role (postgres-speech for user), so I needed to set up a lot of roles, where each role has a distinct username and password. We use gopass to manage our passwords (and related info like usernames). When deploying (via Ansible) we can extract the passwords from gopass and inject them into the environment of the containers running the services. Instead of manually creating a password, storing it in gopass and creating a role in our Postgres cluster, I wrote a short script to automate this:

The script

#!/usr/bin/perl

use v5.36;

open(my $fh, ">", "create_roles.sql") || die $!;

my @stages = qw(beta production);
my @services = qw(accounts-admin accounts-mailer accounts-sso winxle-admin winxle-ga-logger winxle-merchant winxle-newsletter winxle-quickdatacollectionform winxle-scripts winxle-user winxle-winner);

for my $stage (@stages) {
    for my $service (@services) {
        $service =~ /^(\w+)-/;
        my $project = $1;
        my $gopasspath = join('/','validad', $project, $stage, $service, 'database');
        my $snake_service = $service;
        $snake_service=~s/-/_/g;

        my $rv = `gopass generate -f -p $gopasspath 32`;
        my @lines = split(/\n/, $rv);
        my $pw = $lines[3];

        my $role = sprintf('connect_%s_%s', $stage, $snake_service);

        my $create_role = sprintf("CREATE ROLE %s IN ROLE %s LOGIN PASSWORD '%s';", $role, $project, $pw);
        say $fh $create_role;

        my $add_username_cmd = qq{echo "\nuser: $role" | gopass insert -a $gopasspath};
        system($add_username_cmd);
    }
}
close $fh;

Walk through

I open a file to store the SQL statements (create_roles.sql) and define my stages and my services and walk through all of them in a double for loop.

I extract the $project name from the $service name (i.e. accounts or winxle) and use that to create the path to the secret in

It’s been a couple weeks since P2D2 (Prague PostgreSQL Developer Day) 2025. We’ve been busy with the various tiny tasks that need to happen after the conference - processing feedback, paying invoices, and so on. But it’s also a good opportunity to look back - I realized this was the 15th year of the event I’ve helped to organize, so let me share some of that experience.

The Center for Internet Security (CIS) releases security benchmarks to cover a wide variety of infrastructure used in modern applications, including databases, operating systems, cloud services, containerized services, and even networking. Since 2016 Crunchy Data has collaborated with CIS to provide this security resource for those deploying Postgres. The output of this collaboration is a checklist for folks to follow and improve the security posture of Postgres deployments.

The PostgreSQL CIS Benchmark™ for PostgreSQL 17 was just recently released.

The Center for Internet Security

The Center for Internet Security (CIS) is a nonprofit organization that collaborates with government and commercial entities to develop best practices for securing IT systems and data. CIS Benchmarks are community driven and help provide configuration recommendations in the form of security checklists. CIS allows public contributions, reviews, and an open discussion forum on the benchmarks to make sure they meet broader community standards.

The CIS Benchmark for Postgres is a free, community supported, security checklist for Postgres.

Getting started with the Postgres benchmark

The CIS Benchmark for Postgres is a freely available pdf for non-commercial use with recommendations alongside Postgres configurations. The pdf is 200+ pages of descriptions, rational, and sample code to verify Postgres configurations.

In addition to manual verification, to standardize on this benchmark, teams incorporate these settings into their infrastructure deployment tools. Using infrastructure-as-code tools with the benchmarks ensure deployments across an organization meet these security specifications.

For commercial use of CIS Benchmarks, CIS has membership and tools to automatically run the benchmarks.

What is in the CIS Postgres benchmark security checklist?

The benchmark covers a variety of topics for Postgres deployment and configurations, including:

• Postgres install and file permiss

For a long time now, PostgreSQL has had an extensible Index Access Method API (called AM), which has stood the test of time and enabled numerous robust extensions to provide their own index types. For example: rum, pgvector, bloom, zombodb and others. PostgreSQL 12 introduced the Table AM API, promising equivalent flexibility for table access methods.

Despite PostgreSQL's Table AM API being available since version 12 and ongoing criticisms of its built-in storage engine — particularly the MVCC model ([1], [2], [3]) — it remains surprising that no fully featured transactional storage engine has yet emerged purely as an extension.

Since the table AM and index AM APIs are tightly coupled, this is an issue for both implementations.

The features most in demand for alternative PostgreSQL table engines are:

1. Alternative MVCC implementations, e.g.., UNDO-log-based storages. The motivation to provide this is well-discussed in Uber blog post, Andy Pavlo blog post, and many other sources.
2. Non-heap-like storages. For example, in index-organized tables, the index is not an optional addition to the table that speeds up requests but a necessary layer the table storage uses internally. Consequently, the table tuple is a part of a complex data structure and can’t be addressed by a fixed-length address like page number and offset number. It requires to be addressed by variable length identifier like index key.

The API extension to provide #2 is more or less understandable. This could be done by replacing ctid with an arbitrary sequence of bytes in all the APIs. However, #1 seems rather complex and requires much clarification.

As an example that illustrates the motivation for the changes to the table and index AM API comes OrioleDB. It is an extension providing a table access method we developed to address many well-known shortcomings of the built-in storage engine. However, OrioleDB is not yet a drop-in extension; it requires several patches to the PostgreSQL Core.

Besides these two things, wh

With CommitFest 52 (2025-03) - the final CommitFest in the PostgreSQL 18 development cycle - entering its final stages, there has been a burst of activity, and the PostgreSQL 18 code freeze has been announced for April 8th .

more...

The 22nd edition of the Southern California Linux Expo, or SCaLE 22x, took place from March 6-9, 2025, in Pasadena, California. It was a fantastic event packed with insightful talks, community spirit, and legendary figures from the computing world.

One of the highlights of SCaLE 22x was getting the chance to attend talks by two icons: Leslie Lamport and Jon "maddog" Hall. Leslie Lamport, whom many know for Paxos, TLA+, and generally being the "father of distributed computing", delivered the closing keynote titled "Coding isn't Programming". Meant to shake up developers, it was thought-provoking and engaging arguments. It was a rare privilege to hear directly from someone whose work has influenced so much of modern computing.

Earlier, Jon "maddog" Hall's talk "The JOY of Free Software: It should be fun to fish" was a great reminder of why we do what we do in the open-source world and how we should not lose track of how fun this journey of creation was. maddog's passion for free software and its ethical implications is always inspiring, and I've mentioned before on this blog how his example has guided my career choices.

PostgreSQL @ SCaLE 22x

Postgres again had a strong presence at SCaLE with a dedicated track of talks (thanks PgUS for the booth in the Expo hall), and I had the pleasure of being part of several related sessions:

• On Thursday, March 6th, I participated in the PostgreSQL Ask Me Anything session alongside some incredible PostgreSQL experts. The discussion touched on internals, performance, best practices, and the future of Postgres. It's always great to interact with the community and share insights from our experiences.

• On Friday, March 7th, I delivered my talk titled "Row-Level Security Sucks. Can We Make It Usable?". It was a deep dive into PostgreSQL's Row-Level Security (RLS), highlighting its strengths and shortcomings, and exploring ways to make it more accessible to empower developers in real-world applications.

Someone recently asked on the Postgres mailing lists about how to remove unwanted duplicate rows from their table. They are “unwanted” in that sense that the same value appears more than once in a column designated as a primary key. We’ve seen an uptick in this problem since glibc was kind enough to change the way they sorted things. This can lead to invalid indexes when one upgrades their OS and modifies the underlying glibc library.

One of the main effects of a corrupted unique index is allowing rows to get added which should be caught by the primary key. In other words, for a table with a primary key on a column named “id”, you might observe things like this:

-- Can you spot the problem?
SELECT id FROM mytable ORDER BY id LIMIT 5;

 id
----
  1
  2
  3
  3
  4
(5 rows)

Without knowing anything else about the problem, what’s the first step to solving it? If you said take a backup, you are correct! Make a fresh backup anytime you think something is wrong with your database, or before any attempt to fix such a problem.

Aside: can we simply reindex? No - a unique index cannot be created (or recreated) as long as there are duplicate rows. Postgres will simply refuse to do so, as it violates the uniqueness we are trying to enforce. So we must delete the rows from the table.

Here is one careful recipe for fixing the problem of duplicated primary key entries in your Postgres table. Obviously you need to adjust this as needed for your situation, but just walk through it slowly and make sure you understand each step. Especially if this happens in production (spoiler: it almost always happens in production).

1. Debugging aids

The first thing we want to do may look a little strange:

-- Encourage not using indexes:
set enable_indexscan = 0;
set enable_bitmapscan = 0;
set enable_indexonlyscan = 0;

Because bad indexes are the primary way bad rows like this get into our database, we cannot trust them. These low-level debug aids are a way of telling the Postgres planner to prioritize other ways

More?