A decade of logical replication in Postgres doesn't eliminate the need for distributed key design

Postgres 10, released in 2017, was the first version with native logical replication. Postgres 19, still in beta, is the tenth release with the feature. Over that span, each version took a piece of what used to require Londiste, Slony, or the pglogical extension and turned it into a single line of core SQL. That's the trajectory Dimitri Fontaine reconstructs in a post published on Planet PostgreSQL, the first in a series about logical replication use cases, approaching the question not as a DBA but as someone who designs application architecture: which topologies can be built with plain Postgres alone today, and where something is still missing.

For those designing distributed systems in Brazil, especially in multi-tenant and billing systems, the timeline matters less than what it leaves out. And it's exactly there, in the two gaps that Fontaine's table marks with no release number (conflict resolution and DDL replication), that the work still left to the data team lives.

What became core, release by release

The historical reconstruction is straightforward: replicating tables between servers is Postgres 10; replicating TRUNCATE is 11; publishing partitioned tables and subscribing to them is 13; streaming a large transaction before commit is 14; filtering rows and columns per publication, plus skipping a problematic transaction with ALTER SUBSCRIPTION ... SKIP, is 15; avoiding loops in bidirectional topologies (origin = none) and applying in parallel is 16; keeping slots and subscriptions surviving pg_upgrade and failover is 17; counting conflicts by type in pg_stat_subscription_stats is 18; replicating sequences (in beta) is 19.

Two rows in that table have no release number at all: conflict resolution (last update wins and variants) and DDL replication. Neither is in core as of Postgres 19. Both remain the territory of pglogical, EDB Postgres Distributed (BDR's successor), or custom scripting. This is the gap that decides whether an architecture can be pure native Postgres or still needs a paid or separately maintained extension.

The use case: hub and workers

Fontaine uses a metering system as an example: a hub stores plans, prices, and customers; each worker is responsible for a subset of customers and records usage events locally. Reference data flows down from the hub to the workers; usage events flow up from the workers to the hub, which consolidates them into a table partitioned by worker_id to close the invoice.

The downward flow of reference data is the simplest case, solved since Postgres 10 with CREATE PUBLICATION and CREATE SUBSCRIPTION. But publishing everything to everyone exposes columns that shouldn't leave the hub, such as a table of financial notes. Postgres 15 solved this with row filtering and column lists per publication:

create publication ref_w1 for table plans, prices,
 customers (customer_id, name, worker_id, plan_id)
 where (worker_id = 1);

Here's the trap that Fontaine's text isolates well: the filter uses worker_id, a column that isn't the primary key, and the primary key is the default replica identity. The publication is created without error. The first UPDATE on the table fails, regardless of which column is changed, with Column used in the publication WHERE expression is not part of the replica identity. The fix is to create a unique index that includes the filter column and declare it as the replica identity:

create unique index customers_rid on customers (customer_id, worker_id);
alter table customers replica identity using index customers_rid;

For those modeling data, this is the central lesson: a publication filter isn't cosmetic, it changes the table's index requirements, and the change isn't retroactive. Changing the filter later doesn't clean up what has already been copied to a worker; the cleanup is manual.

The problem no version solves: distributed IDs

Postgres 13 made it trivial to publish partitioned tables and subscribe to them, which lets the hub receive events from multiple workers into a single table partitioned by worker_id. But if the primary key is just event_id, the first event from worker 2 collides with the first event from worker 1. The apply worker for that subscription stops for good, with conflict=insert_exists, and keeps retrying every wal_retrieve_retry_interval without ever moving forward, while the other subscriptions continue normally.

The structural fix is a composite key, (worker_id, event_id), which requires zero coordination but makes every table and every foreign key that points to it more expensive. Fontaine also describes two alternatives for a unique ID: a sequence with modulo and offset per worker (classic, works on any version, but requires planning ahead how many workers the fleet will have) and uuidv7(), available since Postgres 18, which generates a UUID with the first 48 bits as a millisecond timestamp. The advantage is not needing a registry of who owns which range, and the index grows at the edge like a sequence's, because the UUID is time-sortable. The cost is 16 bytes instead of 8.

It's worth noting what Postgres 19 doesn't solve here: replicated sequences (still in beta) make the value travel from the publisher to the subscribers, which is the opposite of what this scenario needs, namely a worker minting IDs without colliding with another worker. BDR-style range allocation (what EDB Postgres Distributed calls galloc) remains outside core; the sequence access method patch sets date back to 2015 and 2016, and a new proposal was under discussion on the mailing list in late 2025, but according to the text it wasn't in the Postgres 19 tree.

Large batches and the rest of the operation

Streaming large transactions before commit (Postgres 14) and parallel apply (Postgres 16) change the perception of lag when a worker inserts a batch of hundreds of thousands of rows in a single transaction: without streaming, the hub only starts applying after the commit; with parallel apply, which has been the default for new subscriptions since Postgres 18, the apply worker already exists before the commit and the rows show up shortly after it.

In day-to-day operations, the detail that decides between a smooth deploy and an incident is the lock taken when attaching a new partition: create table ... partition of takes ACCESS EXCLUSIVE on the parent table, which blocks apply workers already running; attach partition alone takes only SHARE UPDATE EXCLUSIVE. The safe recipe is to create the table standalone, with a check constraint that matches the partition bound, then attach it, and only then subscribe.

Conflict counters: what screams and what stays quiet

Postgres 18 added a column per conflict type in pg_stat_subscription_stats. The most important point in the text is the split into two families. update_missing, delete_missing, update_origin_differs, and delete_origin_differs don't stop replication and don't increment apply_error_count: the data already diverges silently between the two sides, with no alarm at all. insert_exists, update_exists, and multiple_unique_conflicts, on the other hand, stop the apply worker, which keeps retrying at each retry interval until someone fixes the local row. And ALTER SUBSCRIPTION ... SKIP, a Postgres 15 tool, discards the entire transaction, not just the conflicting row, which can leave the two sides out of sync until manual repair.

What changes for those designing data architecture

Fontaine's analysis supports a conclusion that matters to anyone building multi-tenant systems, event queues, or distributed billing in Brazil: hub-and-workers topologies with reference-data replication and event collection can already be built with plain Postgres, without Londiste, without Slony, without manual triggers. But two decisions still belong to the data team, and no release takes that responsibility away: designing the key that avoids collisions between nodes (composite or UUIDv7, not a plain sequence without headroom) and actively monitoring the silent conflict counters, because pg_stat_subscription_stats doesn't warn you on its own when two servers write the same row. Where the use case requires replicating DDL or resolving conflicts automatically (last update wins), core still doesn't get there, and the honest choice remains pglogical or EDB Postgres Distributed, not a playbook made only of standard SQL.