TL;DR - RegreSQL 1.0 tested that your queries return the right rows. 2.0 tests that they return them the right way, and it does the checking against production's real statistics instead of your empty dev database, which lies.

A migration cleanup dropped an index nobody thought was load-bearing. Every test passed: same rows, same order, green. Three days later the API started timing out on a query that hadn't changed a character, because the planner had quietly switched it from an index scan to a sequential scan over a table that had kept growing.

The first version of RegreSQL would have passed that change too. It tests what your queries return: run them, diff the rows against a committed expected file, go red when the output changes. That catches the query that now returns the wrong rows. It says nothing about the query that returns the right rows the wrong way, which is most of what takes a database down.

Version 2.0 tests that.

Test the plan, not just the rows

Here is that failure on a laptop. An orders table, an index on customer_id, and a query that reads one customer's orders:

-- orders-by-customer.sql
select id, total
  from orders
 where customer_id = :cid
order by id;

Baseline it and run the tests. Green:

$ regresql test
  ✓ 2 passing

Now drop the index the way that migration did, and run the same tests again:

$ regresql test
  ✓ 1 passing
  ✗ 1 failing

FAILING:
  orders-by-customer.1.buffers (3898 > 109 * 102%, +3476.1%)
  Expected buffers: 109
  Actual buffers:   3898 (+3476.1%)

  Cost (info):      7962.41 (baseline: 421.03)

  ⚠️ Table 'orders': Bitmap Heap Scan → Seq Scan

Here is the whole loop, start to failure, as it actually runs:

The output check still passes; the rows didn't change. The plan check catches what the output check can't: the same query, returning the same result, now runs a sequential scan instead of the bitmap index scan it used before, reading thirty-five times the buffers. That failure blocks the merge. And the diff names the table and

I've been going to conferences and meetups of all kinds since 2004. And today — much like in the era when a Nokia brick was giving people their first, still-primitive taste of mobility — these events follow the same format: you give a talk, you answer questions from the room, and the slides get posted somewhere. These days a video lands on YouTube too. Sometimes a chat survives the event, filled mostly with logistics. And that's about it.

I get it: the way humans interact and consume information doesn't really change, our analog bandwidth is limited, and the format — refined over centuries — is probably close to optimal. But why do we limit ourselves artificially and use almost none of what information technology has to offer?

Concretely, here's what I want to discuss.

Delays

For some mysterious reason, we still wait days, weeks, sometimes months for the slides and the video of a talk that matters to us. What stops organisers from publishing the slides before the talk starts, and the audio/video right after it ends? Editing isn't what makes a technical talk valuable — I can rewind to the right moment myself.

Access

Sure, people pay for tickets — but mostly for the chance to meet in person, argue, and soak up the atmosphere. The commercial upside of events like PGConf.dev or PGConf.EU is hardly the main point: they lean heavily on sponsors. And sponsors care about reach, not box office, don't they? So what stops us from streaming at least an audio feed from the nearest smartphone in real time and attaching the recording to the talk page the moment it ends? The community is international, and travel costs and visa formalities keep plenty of professionals away.

Interactivity

An on-site attendee can ask a question after the talk — or in the hallway track, over coffee, at an afterparty jogging. Spontaneous voice conversation is familiar, convenient, and important. But there are also people who don't think that fast on their feet, aren't that strong in face-to-face conversation, don't

 Ever have a query 'tossed over the fence' that you find incomprehensible but still have to support it? A few years ago, you would have needed to triage the query. Obfuscated queries can be tough to decipher.  Sometimes, the query is due to someone or an ORM being clever. Many times the query is touch to read because the 

DBeaver recently added its AI Chat to the free, open-source DBeaver Community Edition. And you will find it very at determining what a query does. Let's start with a simple query.

Open AI Assistant

Where to find the AI Assistant

Input Prompt

Prompt with a query

Explanation

The Query Explained

A few months ago, I spent time with multiple teams inside the same large financial services organization.

I spoke with the data engineering team. The AI and model team. The platform team. The governance and compliance team.

Each conversation sounded right.

That was the problem.

Each team understood its domain. Each had a clear mandate. Each had a reasonable explanation for the choices it was making. Nothing sounded careless. Nothing sounded irresponsible.

But the real issue did not appear inside any one conversation. It became visible only when I looked across all four of them together.

The data engineering team had pulled customer data into a separate environment to support AI use cases. Faster to build. Safer to experiment. They did not want to touch core systems.

The AI team was building agents on top of that data — assuming it had already been governed, validated, and approved before it reached them.

The platform team was focused on infrastructure stability. AI-specific data governance was outside its current scope.

The compliance team was building a model governance framework. Access controls. Audit requirements. Approval workflows. Human review points.

Solid work. Built on one assumption nobody had verified: that the data underneath the models was already governed correctly.

Nobody had lied. Nobody had been careless. Each team was doing exactly what it believed it was supposed to do. But when I looked across all four conversations, the real risk became obvious.

The customer data grounding the AI agents existed in at least three separate copies across the organization. Each copy had been created independently, with slightly different field definitions, access controls, lineage, and freshness. None of them was the governed system of record.

The control said: “Only show this customer’s data to an authorized user.” The data layer replied: “Which copy? Governed by which access model? Created by which team? Refreshed when?”

That is where AI governanc

One of the most valuable things about partitioned tables is pruning - the database's ability to eliminate entire partitions based on a query predicate. Under conventional wisdom, pruning can only be achieved when querying by the partition key - this makes choosing the right key extremely difficult. However, if your data follows certain patterns, using some clever tricks you can achieve pruning even when filtering by non-partition key columns.

In this article, I demonstrate how to achieve partition pruning when filtering by non-partition key columns.

Table Partition

Imagine you run a popular website with many users. Your product team wants to gain some insight into how the system is used, so you start logging events. To give events context, you group them into sessions and keep the time, the type, and some data in a database table:

db=# CREATE TABLE event (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  timestamp  TIMESTAMPTZ NOT NULL,
  session_id BIGINT NOT NULL,
  type       TEXT NOT NULL,
  data       JSONB
) PARTITION BY RANGE (timestamp);

CREATE TABLE;

You have many users so you expect many events. Most queries use only a subset of the data, usually a specific date range, so you create a partition for each year based on the timestamp:

db=

Replication Deadlock Bug in Current Postgres Releases 14-16

The current minor releases of Postgres versions 14-16 (14.23, 15.18 and 16.14, released on May 14th) introduced a regression that can lead to a MultiXactOffsetSLRU deadlock during transaction log (WAL) replay in certain circumstances.

The bug was (to our knowledge) first reported in Bug#19490 on May 20th by Radim Marek from BoringSQL. Further reports were done on the pgsql-bugs and pgsql-admin mailing lists and we also got customer support requests through our Open-Source Support Center.

What is Currently Known About This Bug

The bug can be hit in two ways. First, during streaming replication where the standby eventually hangs due to the deadlock. The other possibility is hanging point-in-time-recovery (PITR). The following is currently known:

1. The bug is only live on Postgres versions 14-16. Version 17 and 18 (and earlier versions) are not affected.

2. The WAL needs to be generated by a leader running the Q4/2025 (November 13th) back-branch releases or earlier (14.20/15.15/16.11).

3. The standby or the instance running PITR needs to be updated to the latest minor release (14.23/15.18/16.14).

4. The startup process hangs with wait event LWLock/MultiXactOffsetSLRU in pg_stat_activity during WAL replay.

So to summarize, the leader needs to be at least a few minor versions behind and the standby needs to be updated to the latest minor version. Due to the recommended procedure of updating streaming standbys first before updating the leader, this bug is likely to be hit relatively often in the field, especially by organizations that only patch every few minor releases.

Circumventing the Issue

If one has not yet hit the issue, then not upgrading the standby or a PITR machine to the latest minor releases will avoid the problem. In the case that one is affected by the regression, there are three (well, currently two) ways to address the problem:

CloudNativePG 1.30 introduces the DatabaseRole CRD and built-in TLS client certificate issuance, letting application teams own their PostgreSQL credentials declaratively and connect without ever handling a password.

A practical guide to what .ready and .done mean, and why WAL sticks around

Introduction

It is 9:12 a.m. on a Monday. Someone on your team opens pg_wal/archive_status/ during a storage scare and sees a long list of files ending in .ready. They ask the question many of us have asked at least once: “Is replication broken?” Streaming replicas still look mostly fine, but .ready files keep piling up, disk usage keeps climbing, and nobody is fully sure what .ready and .done are actually telling you.

What is .ready and what (if any) action do I need to take? Let’s talk about that today.

Hint: It’s About WAL Delivery

Think of WAL delivery as three independent steps:

1. Generate WAL
2. Transport WAL
3. Replay or consume WAL

archive_command is one way to do transport. Streaming replication is another. Note, logical replication also has a transport channel, but what it transports is decoded logical change data rather than raw WAL segment files.

What archive_command Actually Does

When archive_mode=on, Postgres tries to copy each completed WAL segment to long-term storage by running archive_command.

Typical example:

archive_mode = on
archive_command = 'rsync -a %p backup@walbox:/archives/%f'

• %p is the local path to the WAL segment in pg_wal
• %f is just the filename

Postgres runs this command from the archiver process. If the command exits with status 0, Postgres treats it as success. Any non-zero exit code means failure, and it retries later.

Info: Archiving usually happens when a WAL segment is complete (typically 16 MB), not every transaction. So pure archive shipping can have more lag unless segment switches happen frequently.

What .ready and .done Are For

Inside pg_wal/archive_status/, Postgres tracks each WAL segment’s archiving state with tiny marker files.

For a segment named:

000000010000000A000000FE

you may see:

• 000000010000000A000000FE.ready
• 000000010000000A000000FE.done

.ready

Almost every conversation about pg_hardstorage's repository format ends up at the same question: "where's the incremental chain?"

Short answer: there isn't one. By design.

The chain footgun

In a chained-incremental format, pgBackRest's default, Barman's incremental mode, every incremental references the previous backup directly:

full A   ←   incr B   ←   incr C   ←   incr D   ←   diff E

This is fine when everything works. But every chain has a single property that bites you at the worst time: any single corrupted backup invalidates everything downstream.

Real failure modes we've all seen:

• An S3 lifecycle policy quietly deletes incr B; incr C..D and diff E become useless.
• A bit flip on the storage backend corrupts diff E; the chain restores up to incr D only.
• An operator runs backup expire with too-aggressive retention; full A goes; the entire chain is now floating.
• The base full's manifest schema bumped between releases and the migration script had a rounding bug.

Each of these is recoverable in isolation. The pattern that bites is: you don't find out until the restore. The 3am restore.

Content addressing kills the chain

pg_hardstorage's manifest references chunks by SHA-256, not by reference to a previous manifest:

{
  "id": "prod-2026-05-02-0334",
  "chunks": ["7e1f2a…ab", "9c4d3b…12", "2faabc…77", /* … */]
}

Each chunk's filename is its hash. Two backups that share data also share chunks — but at the storage level, not via a "parent" pointer. Delete the older backup; the newer one's chunks stay until garbage collection finds zero references.

This is what restic, kopia, and (importantly) borgbackup all do. Not novel — but worth saying out loud, because in the PostgreSQL ecosystem the chained-incremental model is so dominant it feels like the only way. See the how it works page for a full walkthrough of the manifest format.

"But what about storage cost?"

The wins of chained incrementals are:

1. Don't write the same byte twice when nothing

In HOT Updates in Postgres we covered page pruning clean up HOT chains, an elegant shortcut where PostgreSQL reclaims dead tuple space during ordinary reads. All that without waiting for any background process. But pruning is exactly that: a shortcut. It only works within a single page, and only for HOT-updated tuples. For everything else (cold updates that touch indexed columns, plain DELETEs, index entry cleanup, free space map registration, visibility map maintenance) we need VACUUM.

This article won't repeat what VACUUM does operationally. The DELETEs are difficult article covers autovacuum tuning, worker allocation, and the operational side of dead tuple cleanup. Here we are going to watch VACUUM work byte by byte. We'll snapshot a page before and after each phase, tracking exactly what changes in the page header, line pointers, tuple headers, free space map, and visibility map. Same tools as always: pageinspect, pg_visibility, and pg_freespacemap.

Setup

We need a table with enough rows to make the before-and-after comparison meaningful, plus indexes to demonstrate the full VACUUM cycle.

CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION IF NOT EXISTS pg_visibility;
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

CREATE TABLE vacuum_demo (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category text NOT NULL,
    payload text
);

INSERT INTO vacuum_demo (category, payload)
SELECT
    'cat_' || (i % 5),
    repeat('x', 100)
FROM generate_series(1, 50) AS i;

Fifty rows with a 100-byte payload each. The primary key gives us an index, which matters: VACUUM's behavior changes when indexes are involved. Run VACUUM once upfront so we start from a clean baseline:

VACUUM vacuum_demo;

Snapshot before any deletes

Record the baseline state of page 0. First the page header:

SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('vacuum_demo', 0));

 lower | upper | special | pagesize
-------+-------+---------+----------
   224 |  1392 |    8192 |     8192

The PostGIS Team is pleased to release PostGIS 3.7.0alpha1! Best Served with PostgreSQL 19 Beta1 and GEOS 3.15 which will be released soon.

This version requires PostgreSQL 14 - 19beta1, GEOS 3.10 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.15+ is needed. To take advantage of all SFCGAL features SFCGAL 2.3.0+ is needed.

• source download md5

• NEWS

• HTML Online en ja zh_Hans fr

• PDF docs: en ja, zh_Hans, fr

• Cheat Sheets:

  • postgis: en ja zh_Hans fr
  • postgis_raster: en ja zh_Hans fr
  • postgis_topology: en ja zh_Hans fr
  • postgis_sfcgal: en ja zh_Hans fr

This release is an alpha of a major release, it includes bug fixes since PostGIS 3.6.4 and new features.

It has been a month since PG DATA 2026, the first full-scale event organized by Prairie Postgres. Looking at the feedback we received from the seekers, sponsors, and participants (and regrets of those who were unable to come :)), I couldn’t be happier with how it went.

I know everyone says this, but let me repeat: this event wouldn’t be possible without everyone who contributed in so many different ways! One more time, I want to thank all organizational committee members, all CfP members. volunteers, speakers, and every single person who attended. You all helped us to build an open and inclusive event where everyone felt welcome.

The organization team and volunteers were so efficient that I was able to attend several talks (which is a huge improvement in comparison with all three PG Days I organized in previous years :)). My only regret is that I didn’t have time for longer conversations with speakers and attendees, especially those who visited Chicago for the first time, but I hope it wasn’t their last time in our city!

And guess what – we are already working on PG DATA 2027! The website is not up yet, but mark your calendar for June 11-12, 2027, and plan to join us in Chicago!

Things I hope will stay the same

• We will have a similarly amazing CfP committee, and will have a great program featuring both new and experienced speakers
• We will keep the ticket prices low, making the conference affordable for anyone
• We will have multiple community sponsors

Things I hope we will do more

• More people are using DEI – focused grants
• More training sessions
• More students participation
• We hope that at least some universities will participate in our Academic Partnership program

What will be better

• The conference will be held on Friday and Saturday, which we hope will allow more people to participate
• Better venue with more space and better floor plan (you won’t need to take the elevator to get from the Red Line to Green Line :))
• More flexible sponsori