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:
- Hub and workers, spreading the write load across servers — this post.
- Consolidation: many databases, different applications and schemas, into one, then re-exported as a change stream for a CDC consumer.
- 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.
What each release changed
Here is the whole story as a table, written from the release notes of Postgres 10 through 19. Read it as “the first release where this stops needing an extension or a workaround”, for the things that matter to an application.
Two rows in that table are empty of a release number, and they decide a lot of what follows.
The scenario: hub and workers
The application here is a small metering system. The hub holds plans,
prices and customers. Each customer is assigned to a worker
(customers.worker_id), and that worker records the customer’s usage events.
The hub needs everything back to compute invoices.
Reference data goes down
The first step is the Postgres 10 one: a publication on the hub, a subscription on each worker.
create publication ref_all for table plans, prices, customers;
create subscription sub_ref_w1
connection 'host=hub dbname=app user=postgres'
publication ref_all;
The subscription name is also the name of the replication slot created on
the hub, so it must be unique per worker. Reuse sub_ref on a second worker
and you get replication slot "sub_ref" already exists. Also notice that the
tables are created by hand on each worker first: DDL is not replicated, in
any version, including the Postgres 19 beta.
With ref_all, every worker sees all nine customers, along with
billing_notes, which is for the finance team and nobody else. That is what
Postgres 15 fixed: a row filter and a column list per worker.
create publication ref_w1 for table plans, prices,
customers (customer_id, name, worker_id, plan_id)
where (worker_id = 1);
Here is the first trap, and it is not in the documentation’s first
paragraph. The filter uses worker_id, which is not in the primary key, and
the primary key is the replica identity. The publication is created without
complaint. The first UPDATE on the table fails, and it does not matter
which column you update:
UPDATE customers SET name = 'renamed' WHERE customer_id = 3;
ERROR: cannot update table "customers"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
The fix is a unique index that includes the filter column, used as the
replica identity, which is cheaper than replica identity full:
create unique index customers_rid on customers (customer_id, worker_id);
alter table customers replica identity using index customers_rid;
I ran the moves too. Reassigning a customer from worker 1 to worker 2
arrives as a DELETE on worker 1 and an INSERT on worker 2, which is what
you would hope. What you might not hope: changing a publication’s filter is
not retroactive. Rows already copied to a worker stay there, so does a
column that used to be published, and cleanup is yours.
Events come up
Each worker writes its own usage_events. The hub subscribes to all of them
into one table partitioned by worker_id, which has worked since Postgres 13:
create table usage_events
(
worker_id int not null,
event_id bigint not null,
customer_id int not null,
meter text not null,
qty int not null,
primary key (worker_id, event_id)
)
partition by list (worker_id);
create table usage_events_w1 partition of usage_events for values in (1);
create table usage_events_w2 partition of usage_events for values in (2);
create table usage_events_w3 partition of usage_events for values in (3);
The rows are routed to their partition, and the invoicing query is a plain join between events that came from three servers and reference data that the hub owns.
The key design matters more than anything else here. Each worker numbers its
events from its own identity column. If the hub’s key is event_id alone,
worker 2’s first event collides with worker 1’s first event. I built that
case on purpose: the hub’s apply worker for that subscription stops, and
retries at every wal_retrieve_retry_interval without ever making progress:
ERROR: conflict detected on relation "public.usage_naive": conflict=insert_exists
DETAIL: Key already exists in unique index "usage_naive_pkey", modified by origin pg_OID in transaction N at TS.
Key (event_id)=(1); existing local row (1, 1, 1); remote row (1, 4, 1).
Only that subscription is stuck. The others keep flowing. The counters in
pg_stat_subscription_stats say so: apply_error_count keeps growing for
that one. That message, with both rows spelled out, is the Postgres 18 way of reporting it.
So the rule for this architecture is to make collisions impossible by
construction: (worker_id, event_id) as the key, or UUIDs.
What about sequences?
Sequences are not replicated by a Postgres 18 publication (FOR ALL SEQUENCES is a syntax error there, and naming a sequence in FOR TABLE
gives “This operation is not supported for sequences”). After five
nextval() calls on the hub, the worker’s copy of the same sequence is
still at 1. If the worker mints an id for a table that also receives rows
from the hub, it collides with a replicated row:
ERROR: duplicate key value violates unique constraint "customers_pkey"
DETAIL: Key (customer_id)=(1) already exists.
In this architecture the workers never mint ids for hub-owned tables, so this is a rule rather than a problem. Postgres 19 changes the situation, see below.
Minting ids on the workers
The events need an id that two workers cannot mint twice. The key
(worker_id, event_id) I used above is the first answer, and it needs no
coordination at all, at the cost of a wider key on every table and every
foreign key that points at it. If you want a single-column id, here are the
options, simplest first.
Modulo and offset. With n workers, give worker k a sequence that
starts at k and steps by a number at least as large as the largest
worker count you will ever have:
-- on worker 1; worker 2 uses start 2, worker 3 start 3
create sequence mod_seq start 1 increment 10;
create table usage_mod
(
event_id bigint primary key default nextval('mod_seq'),
qty int not null
);
That is the classic approach, and it works on every version and needs nothing from the replication layer. After three inserts on each worker, the hub has:
event_id | minted_by_worker
----------+------------------
1 | 1
2 | 2
3 | 3
11 | 1
12 | 2
13 | 3
21 | 1
22 | 2
23 | 3
The worker is event_id % 10, all three subscriptions had
apply_error_count = 0, and no server ever talked to another to get its
ids. The catch is the increment. It is a promise about the largest fleet you
will ever run: a worker number 11 would start at 11, which is exactly the id
worker 1 already handed out, and the hub would stop on insert_exists.
Pick the increment with headroom (bigint has room for a step of a
thousand for a very long time), because changing it later means auditing
every id already issued. The ids are also not time-ordered across workers,
and they have gaps, as any sequence does.
UUIDv7, which is what I would use. Postgres 18 has uuidv7(): a UUID
whose first 48 bits are a millisecond timestamp, followed by random bits.
create table usage_uuid
(
event_id uuid primary key default uuidv7(),
worker int not null,
qty int not null
);
There is no headroom to plan, no worker number to assign, and no registry of
who owns which range. A worker that joins next year, or a system you merge
into the hub, mints ids that cannot collide. And, unlike the random UUIDs
that gave UUID keys their reputation, these are time-ordered, so new rows
land at the right edge of the index like a sequence’s do. I had the workers
insert in turn, a few milliseconds apart, and sorting by event_id on the
hub returned the rows in the order they were written, whichever worker wrote
them:
worker | version
--------+---------
2 | 7
3 | 7
1 | 7
2 | 7
3 | 7
1 | 7
2 | 7
3 | 7
1 | 7
The price is 16 bytes instead of 8, and the creation time is now readable
from the id (uuid_extract_timestamp(event_id)), which matters if the id
is ever shown to users. The ordering across workers is only as good as their
clocks and the millisecond resolution. On a version before Postgres 18 you generate
the value in the application or with an extension.
Reserving ranges, the BDR way. The BDR extension had a sequence access
method that allocated a chunk of values to each node and agreed on new chunks
between nodes when one ran out; its successor, EDB Postgres Distributed, still
has it under the name galloc, next to a snowflakeid kind that is computed
in memory. That is not in Postgres. The sequence access method patch sets
date back to 2015 and 2016, and a new one was under discussion on the
mailing list in late 2025. As far as I can tell from the Postgres 19 source tree it
has not been committed: there is no sequence access method API in it. What
Postgres 19 does contain is groundwork, a refactoring that moves the sequence WAL
code into its own file, described in its commit message as preparation for a
sequence patch. Until an API lands, ranges are something you build in the
application, or get from PGD.
What Postgres 19’s replicated sequences do not do is help here: the values travel from the publisher to the subscribers, and workers minting their own ids need the opposite.
Big batches and many streams
A worker that inserts 300,000 rows in one transaction used to make the hub
wait for the commit before it could apply anything. With Postgres 14’s streaming, and
Postgres 16’s parallel apply, the hub starts working before the commit. On
Postgres 18 the
default for a new subscription is already streaming = parallel
(pg_subscription.substream is p when you leave the option out), so to
compare you have to say streaming = off explicitly. What I measured, on
one laptop, with logical_decoding_work_mem lowered so the batch really
streams: with parallel, a parallel apply worker exists before the commit
and no row is visible on the hub; after the commit, the rows show up in
about 0.08 s. With off, the slot shows spilled transactions and
stream_txns = 0, and about a second passes between commit and visibility.
I make no claim about the total time of the batch, which is not what the
setting is for.
Operating it
Adding a fourth worker is the reason to build it this way, and the
partition is where the care goes. create table … partition of takes an
ACCESS EXCLUSIVE lock on the parent, which blocks everything that touches
usage_events, including the apply workers already running. attach partition only takes SHARE UPDATE EXCLUSIVE. I asked pg_locks from
inside a transaction for both:
attach partition: ShareUpdateExclusiveLock
create table … partition of: AccessExclusiveLock
So the recipe is to create the table standalone first, give it the check
constraint that matches its partition bound (the documentation says this lets
attach partition skip the validation scan), attach it, then subscribe:
create table usage_events_w4 (like usage_events including all);
alter table usage_events_w4
add constraint w4_only check (worker_id = 4);
alter table usage_events
attach partition usage_events_w4 for values in (4);
create subscription sub_usage_w4
connection 'host=worker4 dbname=app user=postgres'
publication pub_usage;
The three existing apply workers keep the same pids through the whole thing.
When something does break, Postgres 15 gave us the tool to get out of it:
logical replication starts skipping transaction at LSN ...
logical replication completed skipping transaction at LSN ...
The catch is the word transaction. alter subscription … skip drops the
whole transaction, not the row that conflicted. In the demo, worker 2’s
three events never reached the hub, and the two sides now disagree until
somebody repairs them by hand.
Reading the conflict counters
Postgres 18 also made conflicts countable. pg_stat_subscription_stats has one
row per subscription. Beyond the two error counters
(apply_error_count, sync_error_count) it now has one column per kind of
conflict. This is what a fresh subscription looks like. I reset the counters
first with pg_stat_reset_subscription_stats(), which is what you want to do
before any experiment, since they are cumulative:
-[ RECORD 1 ]-------------------+------------
subname | sub_conf_w1
apply_error_count | 0
sync_error_count | 0
confl_insert_exists | 0
confl_update_origin_differs | 0
confl_update_exists | 0
confl_update_missing | 0
confl_delete_origin_differs | 0
confl_delete_missing | 0
confl_multiple_unique_conflicts | 0
To learn what each counter means, I provoked all seven on a small table
replicated from a worker to the hub, conf_demo (id int primary key, code text unique, note text), each time by writing the same row on both sides. They fall
in two families:
After the first four, the counters say so and apply_error_count is still
zero, because nothing stopped:
-[ RECORD 1 ]---------------+--
apply_error_count | 0
confl_update_missing | 1
confl_delete_missing | 1
confl_update_origin_differs | 1
confl_delete_origin_differs | 1
Those four are the dangerous ones, because nothing raised an alarm. The data is already different on the two sides: row 1’s update is lost, and row 3 holds the worker’s note while the hub’s edit is gone. A counter that moves here means two servers are writing the same rows, which is a design problem, not an operational one.
The other three stop replication, and the log says exactly what to fix. The
duplicate key from the hub’s insert_exists:
ERROR: conflict detected on relation "public.conf_demo": conflict=insert_exists
DETAIL: Key already exists in unique index "conf_demo_pkey", modified locally in transaction N at TS.
Key (id)=(6); existing local row (6, hub-6, inserted on the hub); remote row (6, worker-6, inserted on the worker).
CONTEXT: processing remote data for replication origin pg_OID during message type "INSERT" for replication target relation "public.conf_demo" in transaction N, finished at X/X
and the update_exists, which also names the row the worker was trying to
change (replica identity (id)=(5)):
ERROR: conflict detected on relation "public.conf_demo": conflict=update_exists
DETAIL: Key already exists in unique index "conf_demo_code_key", modified locally in transaction N at TS.
Key (code)=(code-8); existing local row (8, code-8, inserted on the hub); remote row (5, code-8, from worker); replica identity (id)=(5).
When the incoming row collides on the primary key and on the unique code
together, it is counted under its own name, multiple_unique_conflicts, not
under insert_exists. So a monitor that only watches
confl_insert_exists misses it.
While the conflict stands, the apply worker restarts every
wal_retrieve_retry_interval and fails again, so apply_error_count and the
conflict counter climb together. The fix is on the subscriber: delete or
correct the local row that is in the way, and the next retry succeeds. The
counters stop moving and the row arrives:
delete from conf_demo where id = 6;
If the remote change is the one to give up, alter subscription … skip,
shown above, drops it, with the caveat that it drops the whole transaction.
A query to turn the counters into something a person can act on, that lists only the kinds that happened and says which of them stop replication:
select c.kind, c.stops_apply, c.what_it_means
from pg_stat_subscription_stats s
cross join lateral (values
('insert_exists', s.confl_insert_exists, true,
'a row with this key exists locally: fix or delete one side, or SKIP the transaction'),
('update_exists', s.confl_update_exists, true,
'the new value violates a unique index on the subscriber: fix the local row that holds it'),
('multiple_unique_conflicts', s.confl_multiple_unique_conflicts, true,
'the incoming row violates more than one unique index'),
('update_missing', s.confl_update_missing, false,
'the row to update is not here: the change was dropped, the data diverged'),
('delete_missing', s.confl_delete_missing, false,
'the row to delete is not here: harmless if it was deleted on purpose'),
('update_origin_differs', s.confl_update_origin_differs, false,
'the row was changed locally: the remote change won'),
('delete_origin_differs', s.confl_delete_origin_differs, false,
'the row was changed locally: the delete was applied')
) as c(kind, n, stops_apply, what_it_means)
where s.subname = 'sub_conf_w1' and c.n > 0
order by c.stops_apply desc, c.kind;
kind | stops_apply | what_it_means
---------------------------+-------------+------------------------------------------------------------------------------------------
insert_exists | t | a row with this key exists locally: fix or delete one side, or SKIP the transaction
multiple_unique_conflicts | t | the incoming row violates more than one unique index: fix the local row(s) holding those keys
update_exists | t | the new value violates a unique index on the subscriber: fix the local row that holds it
delete_missing | f | the row to delete is not here: harmless if it was deleted on purpose
delete_origin_differs | f | the row was changed locally: the delete was applied
update_missing | f | the row to update is not here: the change was dropped, the data diverged
update_origin_differs | f | the row was changed locally: the remote change won
Alert on apply_error_count and sync_error_count moving, because that is
replication stopped. Review the other counters on a schedule, because they mean
replication is running on data that no longer matches.
The loop question
In the layout above the reference tables go down and the usage tables go up,
so no change ever comes back to where it was made. What if the same table
has to travel both ways? Two-way replication on one table is exactly where
Postgres 16 helped: the origin option of create subscription. With origin = none
the publisher sends only the changes that were made locally on it, not the ones
that arrived there through replication. See the
origin parameter
of create subscription in the documentation.
The setup is two subscriptions, one in each direction, on the same publication. On the hub, receive the worker’s changes:
create subscription sub_set_from_w1
connection 'host=worker1 dbname=app user=postgres'
publication pub_set
with (origin = none, copy_data = false);
and on the worker, the mirror image:
create subscription sub_set_from_hub
connection 'host=hub dbname=app user=postgres'
publication pub_set
with (origin = none, copy_data = false);
Both tables start empty in this demo, hence copy_data = false; with rows on
both sides, the documentation has a section on initial data that is worth
reading first. I ran three rounds on the same pair of tables, one with a
primary key and one without:
Two things to remember: you have to set the option on both subscriptions, and it only breaks loops. It does not resolve conflicts, and the conflict counters above still apply if both sides write the same row.
The same thing with pglogical
Before 15 and 16, this architecture meant pglogical. To find out what that cost, I built the hub-and-workers again on PostgreSQL 14, with two workers, and here is exactly what I used, since the crash below depends on it.
Installation. The official postgres:14 Docker image (PostgreSQL 14.24,
Debian 13) already has the PGDG apt repository configured, so pglogical is one
package, version 2.4.8:
FROM postgres:14
RUN apt-get update \
&& apt-get install -y --no-install-recommends postgresql-14-pglogical
postgres (PostgreSQL) 14.24 (Debian 14.24-1.pgdg13+2)
postgresql-14-pglogical 2.4.8-1.pgdg13+1
Server settings. Each node runs with:
shared_preload_libraries = 'pglogical'
wal_level = logical
track_commit_timestamp = on
output_plugin_libraries = 'pglogical_output'
The last line is the one to explain, because without it nothing starts:
could not create replication slot on provider: ERROR: library "pglogical_output" may not be used as an output plugin. It is a security
hardening that first shipped in the 14.24 minor release: until then, users with
the REPLICATION privilege were not subject to the restrictions that LOAD
applies to library paths, and could ask for any library as the output plugin of
a logical slot. The new setting,
output_plugin_libraries, lists the plugins the server trusts for that, and
the default is 'pgoutput, test_decoding', the two that ship with Postgres.
Every third-party plugin, and pglogical_output is one, must now be added by the
administrator. It is documented in the
output_plugin_libraries
entry of the replication settings, along with a query on pg_replication_slots
that lists the plugins your existing slots need before you upgrade. It is in the
14 and 19 branches of the source; look for it in the minor release you run.
The hub. It is a node, with a replication set per worker. The row filter and the column list live on the membership of the table in the set, not in a publication:
create extension pglogical;
select pglogical.create_node(node_name := 'hub',
dsn := 'host=pghub dbname=app user=postgres');
select pglogical.create_replication_set('ref_w1');
select pglogical.replication_set_add_table(
set_name := 'ref_w1',
relation := 'customers',
synchronize_data := false,
columns := array['customer_id', 'name', 'worker_id', 'plan_id'],
row_filter := 'worker_id = 1');
A worker. Also a node. It subscribes to its set, and publishes its own events
through a set named usage:
create extension pglogical;
select pglogical.create_node(node_name := 'worker1',
dsn := 'host=pgw1 dbname=app user=postgres');
select pglogical.create_subscription(
subscription_name := 'sub_ref_w1',
provider_dsn := 'host=pghub dbname=app user=postgres',
replication_sets := array['ref_w1'],
synchronize_data := true,
forward_origins := '{}');
select pglogical.create_replication_set('usage');
select pglogical.replication_set_add_table('usage', 'usage_events_w1');
The hub then subscribes to each worker’s usage set the same way. Compare
that with the core version above: a publication and a subscription per
worker, no node to declare, no extension, and Postgres 15’s row filter and column list
are part of create publication. That is the answer to “what did each release
buy”: the same architecture, in less to set up, to learn and to keep running.
What pglogical gave me that core still does not is a conflict policy. With
pglogical.conflict_resolution = 'last_update_wins', which needs
track_commit_timestamp, a deliberate conflict resolves itself, and the log says
how:
LOG: CONFLICT: remote INSERT on relation public.usage_events_w1 (local index usage_events_w1_pkey). Resolution: apply_remote.
It also has three problems, on 2.4.8:
- Replicating into a partitioned parent crashed the apply worker on the
first row after the initial copy. The postmaster restarted every backend,
and the hub crash-looped until I dropped the subscription:
background worker "pglogical apply 16384:SUB" was terminated by signal 11: Segmentation fault. That is why the pglogical version replicates each worker into its own hub partition, named like the worker’s table, instead of into the parent.
- Moving a customer between two workers’ filters was applied on neither
side: worker 1 kept a stale row and worker 2 never got it. The core version
handles it, as a DELETEon one worker and anINSERTon the other.
- A keep_localresolution leaves the two nodes with different values and no error anywhere. That is what the policy says on the tin, and it is worth knowing before you pick it.
Why not just use Citus?
Everything above builds write scaling out of core logical replication and a naming convention. Citus is a purpose-built extension for exactly this problem, so it is worth being honest about what it would have bought, and what it would have cost.
Citus turns a cluster of Postgres servers into one coordinator and several workers. In the topology Citus shipped for most of its history, and still the one its own documentation opens with, the application connects to the coordinator only, never to a worker directly:
A distributed table is sharded across the workers by a distribution
column you pick; a reference table is instead kept whole and copied to
every worker. Our plans and prices are exactly a reference table, and
usage_events is exactly a distributed table, sharded on customer_id or
worker_id. Declaring that is two function calls, create_reference_table()
and create_distributed_table(), not a publication, a row filter and a
column list per worker.
Six things Citus removes that cost real pages above:
- Events land on the right worker on their own. The coordinator hashes
the distribution column and routes the row; nobody writes a publication
per worker, and there is no usage_naiveto build by accident, because there is no second server independently minting the same id.
- Sequences mostly take care of themselves. For a bigintsequence on a distributed table, Citus splits the value range across the worker node groups the same way our “Minting ids on the workers” section did by hand, except it does it for you at distribution time. The one gap: a plainint/serialsequence isn’t split, and a worker is stopped from using it at all, so those still have to go through the coordinator.
- A large transaction has nowhere to lag behind. A batch that keys to one shard is written straight to the worker that owns it, in one transaction, not copied there afterwards. There is no equivalent of our “Big batches and many streams” section, because there is no second copy for a stream to catch up.
- DDL propagates. The documentation says it plainly: changing the schema of a distributed table cascades to every shard across every worker. Our whole “Operating it” section, and the DDL-order rule in the closing one, exist because logical replication does not do this.
- Adding a worker is a supported operation, not a lock-mode reading
exercise. Since Citus 11.0, citus_rebalance_start()moves shards to a newly added node without blocking reads or writes, which is the built-in version of theattach partitiondance above — and, worth noting given everything else in this article, it does the move with logical replication under the hood.
- The invoicing query gets the whole cluster, not just the hub. Citus breaks a query like ours into sub-queries that run on every shard in parallel, on every worker’s own CPU, memory and I/O, and only merges the partial results on the coordinator. Our invoicing query runs entirely on the hub, against data every worker already sent it; Citus’s runs the join where the data already lives.
And under all of that sits one more difference: with Citus, usage_events
exists exactly once, in its shards. There is no second, hub-side copy for
the invoicing query to read, because the query goes to the data instead of
the data coming to the query — one table doing both jobs, not a transactional
copy on the workers and a replicated analytical copy on the hub.
And what it costs. Citus is an extension: installing it, or paying for a managed offering that has it, is a decision the plain hub-and-workers version never asks you to make, because every piece of it is core Postgres from version 10 on, on whatever managed Postgres you already run.
The single-coordinator picture above is also not the whole current story,
and I want to correct myself here rather than leave a stale claim standing.
What used to be a paid-only feature under the name Citus MX — every node’s
metadata kept in sync, so any node can plan and run a distributed query, not
only the coordinator — was open-sourced and turned on by default in Citus
11.0.2, released June 2022 (citus.enable_metadata_sync, true since that
release). The project now calls it Query From Any Node. So “the
coordinator is a SPOF” is the classic diagram, not the current default
behaviour.
It does not make the operational question disappear, it moves it. Citus’s own contributor documentation is direct about the cost: connections stop being one path per node (application to coordinator) and become every node to every other node, so you size connection limits for that.
pg_auto_failover has native support for a Citus formation — pg_autoctl create coordinator and pg_autoctl create worker join a coordinator and
its workers to the same monitor, each with its own failover — and
pg_autoctl show uri already gives the application one connection string
that survives a coordinator failover, by listing every coordinator host with
libpq’s target_session_attrs=read-write, so the client finds whichever one
is currently primary without caring which host that is. What it does not
give you yet is the other half: a router in front that spreads application
connections across every node to make use of Query From Any Node in the
first place, the way a pgbouncer in front of the whole formation would.
That is on the roadmap, not
shipped, under “connection pooling as a managed node type”.
Our hub, either way, has no such requirement to begin with: if it is down, every worker keeps taking its own traffic, and only the invoicing rollup waits.
Trying it. pg_auto_failover’s own test suite ships a complete,
runnable example of exactly this: a coordinator pair and two worker
groups, all under one monitor, with a distributed table, a network
partition, and a failover exercised at every level. It runs on Docker
Compose through the project’s own test runner,
pgaftest:
git clone https://github.com/hapostgres/pg_auto_failover
cd pg_auto_failover
pgaftest tmux tests/tap/specs/citus_basic_operation.pgaf
pgaftest tmux brings the whole stack up under Docker Compose and opens a
three-pane session: live pg_autoctl watch state, container logs, and a
shell to drive the test steps one at a time (pgaftest step). The
cluster it brings up, and how it waits for it to be ready:
cluster {
monitor
formation {
coordinator1a coordinator
coordinator1b coordinator
worker1a worker group 1
worker1b worker group 1
worker2a worker group 2
worker2b worker group 2
}
}
setup {
wait until primary, secondary in group 0 timeout 90s
wait until primary, secondary in group 1 timeout 90s
wait until primary, secondary in group 2 timeout 90s
promote coordinator1a
promote worker1a
promote worker2a
}
teardown {
compose down
}
A coordinator pair and two worker groups, all under one monitor, each with
its own primary and secondary. The rest of the spec is a series of named
steps that create a distributed table, disconnect and reconnect a worker’s
network, wait for Citus’s own metadata sync to catch up
(pg_dist_node.metadatasynced), and fail over a worker group and then the
coordinator pair in turn — the full file, with every step, is on GitHub:
citus_basic_operation.pgaf.
Conclusion
Read the table at the top of this article again with the whole
architecture behind it, and it stops being a list of trivia. Postgres 10
and 11 made the basic pipe. Postgres 13 let a partitioned hub receive from
many workers. Postgres 14 and 16 kept a busy hub from stalling on one
worker’s big transaction. Postgres 15 turned “every worker sees
everything” into a per-worker filter, in one statement instead of a naming
convention. Postgres 16’s origin option is the only reason two-way
replication on a shared table does not loop forever. Postgres 18 turned a
silent conflict into a counter you can alert on. Each release
took one more thing this architecture used to need application code, or
Londiste, or a cron job, for, and folded it into a line of SQL. Postgres
19’s contribution, still in beta as this publishes, is sequences that
travel with the rest of the table — one more manual step this
architecture no longer needs, if it survives to GA the way it is today.
The direction has held for ten releases: the application architecture
gets easier to build, not harder, and that trend is the actual news, more
than any one release’s feature list.
Two tools promise to make it easier still, and it is worth being precise
about what each one actually removes, which is why this article spent a
section on each. pglogical answers a real gap, conflict resolution,
that core still does not have, and it is not the walled-off tool I
expected: RDS’s own extension list carries pglogical on every current
PostgreSQL version, and Cloud SQL and Azure Flexible Server list it too,
so it costs an extra CREATE EXTENSION, not a different database.
Citus is the other kind of tool: not on RDS, not on Cloud SQL as an
extension you enable, and on Azure it is not an extension at all but its
own product line, Cosmos DB for PostgreSQL, formerly Hyperscale (Citus).
Choosing Citus is closer to choosing a database than choosing an
extension, and that is a fair trade for what it buys, covered above — it
is just a different kind of decision than reaching for pglogical.
The deeper difference is what a “worker” is allowed to be. A Citus worker is a shard-storage node that the coordinator owns; the application is not meant to know it exists, and Citus is not designed for you to query it on its own. Our workers are the opposite: full, independent Postgres servers that a region, a large customer, or a compliance boundary already needs to run on its own, and that must keep serving local writes with no hub in sight. If your workers are really just where the rows happen to live, Citus is the more transparent tool. If they are autonomous by design, logical replication keeps that autonomy and Citus does not, because its workers are not meant to run without their coordinator.
That autonomy is bought with a second copy of the data, and Citus’s single copy is bought with workers that cannot stand alone. Both trade-offs are worth having on purpose: know which one you are choosing, and why, before the naming convention above turns into three years of production traffic.
Part 2 of this series consolidates several application databases into one warehouse. Part 3 covers a zero-downtime major upgrade. A fourth post, covering the remaining architectures in less detail, is also planned.
The demo for this post is in the
compose/ directory
of this post’s source. Run it, break it, and tell me what I got wrong.