Latest Blog Posts

A long time ago I ran a write-heavy system on a hub and a handful of workers. Each worker took a share of the application traffic and wrote events locally. The hub owned the reference data (customers, plans, prices), pushed it down to the workers, and pulled every worker’s events back up to compute the invoices. The plumbing was Londiste and PgQ: triggers on every table, a queue per node, a ticker, and a Python daemon per hop. It worked, and it was a lot of moving parts to explain to anyone new.

Postgres 10 shipped logical replication in 2017, and 19 is the tenth release that has it. Every release since Postgres 10 has taken a piece of that plumbing and made it a line of SQL.

This is the first article in a series about Postgres logical replication use-cases, and about how the feature set has evolved over the past ten years and ten releases. The question is the application developer’s one, not the DBA’s: which architectures can I deploy with Postgres core alone today, what does each release change about that, and where do I still need something else? I built three architectures for real, across three posts:

1. Hub and workers, spreading the write load across servers — this post.
2. Consolidation: many databases, different applications and schemas, into one, then re-exported as a change stream for a CDC consumer.
3. Zero-downtime major upgrade, with a way back.

A fourth post, covering what is left out of this series in less detail — geo-replication, BDR-style multi-active setups, plain CDC and triggers — is also planned.

If I could give one piece of advice to the past me, starting to contribute to open source projects, it’d be to value other developers' time more. It took me a while to appreciate the “economy” behind this, and adjust how I work to increase my chance of getting patches done. Hopefully some new contributors could learn from my mistakes.

On the server this article was written against, pg_stat_statements_info.dealloc reads 20, and that counter is all the database has left to say about forty rows an agent deleted. The forty entries were there while the DELETE statements ran, one per table, counted and timed like anything else. Then the same agent read the schema twice over, 12 800 statements that changed not one row, and the reading needed room.

The eviction is documented behaviour rather than a defect. The view is sized once, at 5 000 entries by default, and once more distinct statements arrive than that the documentation says "information about the least-executed statements is discarded". Run once, a statement is the least-executed thing in the database.

Ordinary application traffic sits at the far end of the axis that policy implies, since an application issues a small set of statements a great many times each. An agent writing its SQL fresh on every turn does the reverse, and the rewriting adds a second cost on top, because a question asked in two shapes occupies two entries and each of those two has been called once.

Key Takeaways

• Twelve ways of asking one question produced eight entries. Lowercase keywords, reflowed whitespace, a leading -- comment and a public. prefix all merged into the baseline, while count(1), a table alias, a swapped predicate order, a subquery and a CTE each took an entry of their own.
• Two tenant schemas whose same-named tables hold 100 and 100 000 rows share one queryid on PostgreSQL 18, where 17 kept them apart, and the surviving entry carries the name of whichever schema was queried first.
• Nothing in the view names the caller beyond its role. Two connections under one role, one with application_name set to payments-api and one to claude-agent, landed in the same entry, so an agent borrowing the application's login cannot be separated from it after the fact.
• With track at its default top, a statement inside a PL/pgSQL function or a DO block leaves no entry of its own, and the sam

Relational databases like Postgres provide many unique features, specifically atomicity, consistency, isolation, and durability (ACID), but providing durability has always been a challenge. Though computers originally used non-volatile magnetic-core memory, the past five decades have been dominated by computer architectures where CPU-accessible memory is volatile, and OS-accessible storage is non-volatile/durable. Postgres uses the write-ahead log (WAL), which is stored on OS-accessible durable storage, to provide durability. (I recently wrote a presentation about WAL, and I have a presentation explaining durability.)

However, using OS-accessible storage for durability adds complexity. What if CPU-accessible memory, where most of the database processing happens, could be made durable in a high-performance and cost-effective way? This has been a goal of memory manufacturers for over twenty years, and it might finally be ending in failure.

Variously called phase change memory (PCM), 3D XPoint, Optane, and non-volatile Compute Express Link (CXL), the technology allows durable CPU-accessible memory to be mixed with volatile DRAM in the same system. This 30-minute video covers the fits and starts of the effort, and its eventual abandonment by Micron and Intel. This 2012 article summarizes frustration with the industry, "Long-derided as a Techno-Ponzi scheme — useful for raising a development budget but never delivering a return — PCM may now finally start earning its way in the world."

Continue Reading »

On 16 September 2026, the Postgres Meetup for All user group met, organized by Elizabeth Christensen and Ryan Booz. Ryan Booz and Greg Potter delivered a talk.

On 18 September 2026, Claire Giordano and Aaron Wislang hosted and published a new podcast episode “25 years of contributing to Postgres with Peter Eisentraut” from the Talking Postgres series.

On September 19 2026, the Postgres Bangalore (PGBLR) user group met, organized by:

• Amit Kapila
• Dilip Kumar
• Kuntal Ghosh
• Sivji Kumar Jha
• Vigneshwaran C

Speakers:

• Pavan Krishna Rampalli
• Srinivasarao Kaithepalli
• Chandan Kumar

Community Blog Posts:

• Jimmy Angelakos about “PostgreSQL Commitfest Metrics: PGConf.dev 2026 Part One”
• Andreas Scherbaum about Prague PostgreSQL Meetup - April 2026 Edition
• Gülçin Yıldırım Jelinek about Postgres week in the Netherlands: PGDay Lowlands & Percona Live 2026

I did it! The most important thing I anticipated after leaving DRW was having more time for important work, and one of the top items on my to-do list was the pg_acm update.

I already knew about two dozen bugs I needed to fix; several new functions people asked about. In addition, I had several conceptual changes in mind. Do you know how disturbing it is when you know you need to do something, and you can’t focus on that “something” because other things, less important but more urgent, keep popping up?

Finally, a rainy Saturday came! I promised myself not to look for a break in the rain, and not to even think about going biking, until I am done. Full disclosure: I am not done with the most boring and most important part: documentation! However, I figured I should at least publish the code and let people criticize it! And bug me about documentation!

My goal is to finish the documentation update before PG Conf.EU, where I am going to give a talk about pg_acm. Can you imagine how excited I am about this opportunity?! That’s why I want to be ready beforehand. I hope that some non-artificial intelligence will discover some bugs and ask some intelligent questions.

Please check it out: pg_acm

I used AI tools to size an OLTP workload on an EC2 system, with DBT-5, a TPC-E-like fair-use implementation. I provided a systematic and mechanical plan for running a series of tests to determine what the appropriate scale factor is on a system.

I pre-configured the database with some settings that are known to be needed to be changed, such as shared_buffers and max_wal_size but more on this at a later time when we characterize the system behavior further to be able to tune some of these settings better.  Remember, this is an iterative process when trying to figure it out for any workload.

I decided to use Claude Fable 5.1 for this exercise and fed in the following instructions:

1. Start with the minimum valid database size with 5000 customers.
2. Start testing with 1 user and increase by 1 user up to 2 times the number of logical processors.
3. Run all tests with a 1 hour steady state as that is expected to be long enough to establish a stable result.
4. Observe the TPC-E specification for valid transaction rates to determine what the appropriate database size is for the maximum observed transaction rate.
5. It is expected that we will have to continue bisecting the database size to find the highest legal transaction rate allowed for this system.
6. Continue to validate that the number of users that produced the most throughput continues to produce the most throughput at each database size.

The following chart illustrates the sum of all the testing from starting at a 5000 customer database, up to a 97,000 customer database:

The future of Postgres is bright. So bright in fact, that I spent almost a dozen posts expounding on the upcoming features it would bring, with veritable stars in my eyes. Unfortunately, while I was out counting my chickens, it would seem some of these exciting new features failed to hatch.How, and perhaps more importantly why that happened, deserves some investigation.

A Call to Arms

1. RI fast-path FK checks / batching
2. REPACK / REPACK CONCURRENTLY
3. Online data checksums
4. UPDATE/DELETE FOR PORTION OF
5. SQL/PGQ property graphs
6. postgres_fdw statistics import

• Non-text output formats for
• , reverted by Andrew Dunstan in commit 
• on June 15th.

• , reverted by Tom Lane in commit
• on July 17th. I wrote about this patch in
• .

• , reverted by Alexander Korotkov in commit
• on August 27th. This feature was the entire point of
• .

• SQL/PGQ property graphs, reverted by Peter Eisentraut in commit
• on September 7th. Particularly notable because it unwound
• from both
• and
• .

• The batching layer of th

postgres.scot is live

Postgres events in Scotland now have a permanent address: postgres.scot

It is a deliberately small page. Right now it points at the PostgreSQL Edinburgh User Group (PostgresEDI) on cloomba, where you will find RSVPs, a calendar subscription and an RSS feed, and it will carry other Scottish Postgres events as they come along. The point is to have one address worth bookmarking and sharing, rather than whichever platform we happen to be on this year.

If you are running something Postgres-related in Scotland and want it listed, email info (at) postgres.scot.

postgres.scot is a volunteer website, not affiliated with or endorsed by the PostgreSQL project or the PostgreSQL Community Association.

The August meetup

Thursday, August 13th, Paterson's Land at the University of Edinburgh, two talks, pizza and refreshments sponsored by pgEdge, and the rest of the evening at the Tolbooth Tavern, one of the few pubs nearby that was not hosting an Edinburgh Fringe show that night.

Logical Replication: Escape the Cloud, Own Your Data

Torsten Förtsch

Torsten Förtsch on replaying the stream of changes into the target database

Torsten started from a move to Aurora and the question of what "your data" actually means once the database is somebody else's service. His answer was to rebuild point-in-time recovery at the logical level: a pg_dump for the base copy, a stream of changes captured with wal2json in place of archived WAL segments, and replay into a database you control, on whatever operating system and Postgres version you like.

He took us through both halves of that. Capture and replay turned out to be 35 lines of jq translating the JSON change stream into SQL statements, with some care over how those statements are written so they find the right row quickly. The harder half is the initial copy: working out which position in the stream a dump corresponds to, so that replay starts in exactly the right place. He finished with where he wants to take it, incl

Two client connections, opened one after the other through PgBouncer transaction mode, asked the same database whose rows they were allowed to see, and the second one got the first one's answer. It had set nothing, it had never met the first caller, and the rows it read belonged to that caller's tenant. Any runbook that keeps a tenant key in a session variable sits one pooler away from this, and since 28 July the MCP protocol carries no session of its own, so a tool call from an agent arrives in this shape by default.

The setup is the one most multi-tenant guides teach. A row level security policy reads current_setting('app.tenant', true), each request or tool call opens with SET app.tenant, and on a connection nobody else is using the rows that come back belong to whoever asked for them. Through the pooler, the first call still behaves as written.

SET app.tenant = 'a';
SET
SELECT current_setting('app.tenant') AS tenant;
 tenant 
--------
 a
(1 row)

SELECT tenant, body FROM docs;
 tenant |      body      
--------+----------------
 a      | alpha invoice
 a      | alpha contract
(2 rows)

A second client connection, opened after the first one had closed and setting nothing of its own, then asks the database who it is working for and what it may read.

SELECT current_setting('app.tenant', true) AS inherited_tenant;
 inherited_tenant 
------------------
 a                <-- set by the caller before it
(1 row)

SELECT tenant, body FROM docs;
 tenant |      body      
--------+----------------
 a      | alpha invoice
 a      | alpha contract
(2 rows)

Both callers ran on one backend, as did the two after them that switched the tenant to b and inherited it. When caller A's implicit transaction ended, PgBouncer released that backend and handed it over without sending anything in between that would have cleared app.tenant. Two changes made that shape the ordinary one. On 28 July 2026 the MCP specification took the session out of the protocol, its announcement stating that "Each request now travels on its

Ask a database vendor about on-prem and you'll usually get a one-line answer: 'we're open source, you can just self-host it.' For a hobby project, fine. For an enterprise, that line rarely survives contact with production. And enterprises want on-prem more than ever right now, largely because of AI. Running open models on hardware you own is far cheaper than renting inference by the token, and in a regulated industry, keeping data where you control it isn't optional. When the models move in-house, the database moves with them, because that's where the data lives and, increasingly, where the AI feeds on it.So a lot of teams are asking a question they thought they had retired: can we run this in our own environment, on our own terms? Plenty of vendors have a ready answer. “Sure, we are open source. Just self-host.” For a solo developer or a small team, that answer holds up fine. For an enterprise, it usually falls apart, because open source, self-hostable, and enterprise-ready are three different promises, and most vendors only deliver the first.

Three Words, Three Different Promises

Self-Hosting Should Be a Product, Not a Cloud in a Box

Last week, I spent three days in the Netherlands and gave two talks at two conferences: a lightning talk at PGDay Lowlands in Utrecht on Thursday, September 10, and a session at Percona Live in Amsterdam on Friday, September 11\. In this blog post, I’m going to share my notes from both.

As often happens with conferences (or any big events, really), there was a minor hurdle to overcome before we could get there. On Wednesday, September 9, just one day before PGDay Lowlands, a nationwide 24-hour public transport strike stopped trains, buses, trams and metros across the whole country. Not the ideal warm-up for a conference that draws people from all over the world, but by Thursday morning everything was moving again and the day went ahead as planned. Yay!

PGDay Lowlands, Utrecht {#pgday_lowlands_utrecht}

PGDay Lowlands is a one-day Dutch PostgreSQL conference (although all the talks are in English), organized by PostgreSQL Europe. This was its third edition, and the event moves around: last year, it was held at Blijdorp Zoo in Rotterdam; this year, it took place at TivoliVredenburg, a music venue in the center of Utrecht, with the main track in a hall called Cloud Nine.

As a SQL Server developer and DBA learning Postgres, it’s easy to expect that the information you need for meaningful query and performance tuning will be readily available. For years (decades, maybe) you’ve learned the DMVs, set up Extended Events sessions, relied on Query Store, and regularly run Ola Hallengren’s maintenance scripts and Brent Ozar’s First Responder Kit. Nearly everything you do to find and tune poorly performing queries happens through SQL or through a GUI in SSMS.

Rarely, if ever, do you think about combing through logs to find query performance issues. The error log is where you go when something broke: a failed startup, a corruption message, a login from an IP that shouldn’t exist, a backup that didn’t. It’s an incident destination, not a daily instrument.

Most SQL Server DBAs I talk to have also never had to think hard about log configuration, because there was never a decision to make. Logging is built into the Windows server ecosystem. It just exists, and you get it for free.

It’s no wonder, then, that SQL Server DBAs who are new to Postgres have real confusion about where to find the information they need when there’s a problem. And it’s no wonder so many are shocked when they discover the information isn’t there at all, because Postgres was never configured to record it.

This doesn’t mean Postgres monitoring is worse. In some cases it’s markedly better. Postgres actually gives you significantly more configuration options around what gets tracked and logged. They’re just set conser

In PostgreSQL, every tuple starts with 23-byte header, and the first eight bytes are two transaction IDs. t_xmin for the transaction that created the row and t_xmax for the one that deleted or updated it. That is the visibility story covered in PostgreSQL MVCC, Byte by Byte. For now we have discussed t_xmax acting as the delete marker.

t_xmax has a second job. When you run SELECT ... FOR UPDATE or an insert checks a foreign key, PostgreSQL has nowhere else to record the row lock. The shared memory lock table is limited by max_locks_per_transaction. Locking a million rows would exceed its capacity. PostgreSQL works around this by storing the locking transaction ID in t_xmax and marking the row as locked with flags in t_infomask, while readers can still see it, so every row lock in PostgreSQL ends up as a write to the page.

Setup

The setup is one parent table in the usual shape, plus a child table with a foreign key, since foreign key checks lock parent rows. Everything below was captured on a single PostgreSQL 18.6 cluster using the postgres:18 image. Transaction IDs will be different on your cluster; compare the bits instead.

CREATE EXTENSION IF NOT EXISTS pageinspect;

CREATE TABLE lock_demo (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner text NOT NULL,
    balance numeric(12,2)
);

CREATE TABLE lock_demo_tx (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id integer NOT NULL REFERENCES lock_demo (id),
    amount numeric(12,2)
);

INSERT INTO lock_demo (owner, balance)
VALUES ('alice', 100.00), ('bob', 200.00), ('carol', 300.00);

SELECT count(*) FROM lock_demo;

SELECT lp, t_xmin, t_xmax, t_ctid,
       (heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags
FROM heap_page_items(get_raw_page('lock_demo', 0));

 lp | t_xmin | t_xmax | t_ctid |                        raw_flags
----+--------+--------+--------+-

Postgres 19 won't make the expected release date. PostgreSQL has shipped its major version every fall for the last several years. But this year, the code is in a heavy review cycle, major features have been reverted during beta, and many others are under heavy revision. Beta 4 is scheduled for Sept. 24, 2026. A release of Postgres 19 is certainly delayed by weeks and maybe even months.

Postgres 19 was an ambitious release already, with a lot of large features. With any project, you have to choose priorities. For Postgres, the priorities were quality, followed by shipping within the time window. The team is reducing the scope of the release to get closer to meeting its timeline with the quality it requires.

I'll break down some of the major reversions in Postgres 19. While this is a long list, I want to make it abundantly clear that the PostgreSQL code development process is working beautifully. Code is getting tested at a wide scale and things that aren't ready are getting pulled.

How PostgreSQL gets made

A typical PostgreSQL code development process works like this:

• New patches are added by the community in a series of open commitfests and discussed on the Hackers mailing list
• Patches selected for approval are added to a future version by one of the ~30 project committers
• A beta version is cut, the community starts testing for ~6 months, updates and bugs are patched and some features are reverted
• A final production version is released, followed by quarterly maintenance versions for bug patches and security updates

Notable PG19 Feature Reverts

There have been quite a few reverts for version 19 of Postgres: 53 since the beta began in June 2025. Below are some of the more major user-facing features you may be familiar with:

SQL Property Graph Queries (SQL/PGQ)

Property graphs would add graph query views on top of tables and a light approach to graph queries in Postgres. The hackers discussion suggests broader design and readiness concerns.

ALTER TABLE MER

In the final part of this special Postgres in Production deep dive series, Ryan Booz does something the first six episodes rarely did: he queries pg_stat_statements itself. This episode covers why your first stop during an incident should actually be pg_stat_activity, two ways to get a usable window out of cumulative metrics (diffing snapshots, or resetting and re-querying), which columns to order by and why the slowest query is not always your problem, and what to look for when you pick a monitoring tool to keep this history for you.

Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel.

Transcript

A quick recap

Believe it or not, through these last six episodes we’ve rarely queried the data itself. We’ve talked about what pg_stat_statements is and isn’t (Part 1), how query texts get normalized (Part 2), where the texts themselves are stored and how that can get contentious (Part 3), and we’ve looked at the source code to see exactly what happens once your query finishes executing (Part 4). We covered configuration (Part 5) and, in the la

Over the past few years, I have been working on assessing the strengths and weaknesses of PostgreSQL migration tools. Several articles published by yours truly led to the ambitious project I have been pursuing since 2024 with my colleagues Étienne Bersac and Pierre-Louis Gonon.

… And the fisrt stable version of PostgreSQL Migrator was released on September 4th. This is an opportunity to showcase features I use daily and what advantages they offer over other tools. In this article, I want to focus on one of them, particularly valuable when preparing a migration: the offline catalog.

Inspecting the Oracle catalog

The catalog of a relational database contains the structure of the data model, table column names and data types, constraint definitions, the definition of a view or a function, and so on. Everything declared by the user with DDL (Data Definition Language) is stored in the catalog as the single source of truth.

In systems like PostgreSQL, MySQL or MSSQL Server, the standard provides a universal catalog, the information_schema schema. For example, table names can be retrieved there with the same query:

SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'scott'
 ORDER BY table_name;

However, this is not the ideal solution to reconstruct a data model. Each of these systems conforms to the SQL standard as best it can but very often enriches it with language extensions or takes liberties with the implementation of a feature. As a result, the information_schema catalog is not the universal source, little more than a set of views on top of each system’s proprietary system catalog.

Turning to Oracle and Ora2Pg. If we want to recreate the structure of a table in the Oracle ecosystem, several methods exist and they all rely on the catalog views which I cover last.

The DESCRIBE command

By far the least informative of the solutions but the fastest for a first inspection. It is analogous to the \d meta-command in psql or the pragma table_info in SQLite.

D