Ten years of Postgres logical replication

Back to Devoured - September 28, 2026

DEVOURED

Ten years of Postgres logical replication

Data databasepostgresql Tapoueh

Postgres logical replication has evolved from complex plumbing (Londiste, PgQ) into a core SQL feature set including row filters, parallel apply, and cross-version upgrades.

What: Stephane Bortzmeyer analyzes ten years of Postgres logical replication, tracking its evolution from Postgres 10 to the Postgres 19 beta. Key additions include row-level filtering, partitioned target support, and conflict reporting, which now allow for complex hub-and-worker architectures using only core SQL.

Why it matters: Understanding how logical replication has moved from extension-dependent to core-native helps developers architect robust data movement and scaling patterns without relying on external tooling.

Takeaway: When setting up hub-and-worker replication, use `(worker_id, event_id)` keys or `uuidv7()` to prevent primary key collisions during data consolidation.

Deep dive

Postgres 10 introduced basic logical replication; subsequent releases added features like partition support (v11), stream-before-commit (v13), and row filtering (v15).
Replication slots and subscriptions can now survive pg_upgrade since v17.
origin = none in create subscription is essential for preventing circular loops in two-way replication.
The article provides a detailed comparison between core logical replication and third-party solutions like pglogical and Citus.
Conflicting writes are now logged with granular counters in pg_stat_subscription_stats, making operational monitoring much easier.

Decoder

Logical Decoding: The process of extracting all persistent changes to a database table into a stream of logical changes (e.g., INSERT, UPDATE, DELETE).
WAL: Write-Ahead Logging, the standard method for ensuring data integrity in relational databases.
Replica Identity: The setting that determines which information is written to the WAL to identify rows for updates/deletes.

Original article

Full article content is not available for inline reading.

Read the original article →

Explore all 67 articles in today's digest