PostgreSQL 18
Replication conflicts became countable
For anyone running logical replication who has been told the subscriber is fine because it is running.
Reference page, revised in place. Last updated .
Two kinds of conflict, one of them invisible
When a logical replication subscriber applies a change and the row it expects is not in the state it expects, that is a conflict. There are several ways it can happen, and they divide cleanly into two groups that behave completely differently.
The first group stops replication. An insert arrives for a key that already exists on the subscriber, the unique index refuses it, the apply worker raises an error and dies, and the subscription is stuck until somebody intervenes. That is unpleasant and it is loud: the error count goes up, replication lag grows without bound, and every alert anybody has configured about logical replication fires.
The second group does not stop anything. An update arrives for a row the subscriber does not have, or a delete arrives for a row that is already gone. There is nothing to apply, so the apply worker writes a line to the log and carries on. Replication does not lag. No counter moves. The subscription is healthy by every measure the server offers, and the two databases are now different in a way nobody has recorded.
Until 18 that second group left no trace outside the log file. Version 18 gives each kind of conflict its own counter on the subscription statistics view, including the kinds that never interrupt anything.
SELECT subname, confl_insert_exists, confl_update_missing, confl_delete_missing
FROM pg_stat_subscription_stats;ERROR: column "confl_insert_exists" does not exist
LINE 1: SELECT subname, confl_insert_exists, confl_update_missing, c...
^A subscription with a divergence built into it
The fixture is a publisher and a subscriber in the same cluster, which is not how anyone runs this in production and is exactly what is wanted for a demonstration: two databases, one publication, one subscription, and no network in between to complicate the timing.
DROP DATABASE IF EXISTS upstream;
CREATE DATABASE upstream;
\c upstream
CREATE TABLE invoices (invoice_id integer PRIMARY KEY, total numeric);
CREATE PUBLICATION invoices_pub FOR TABLE invoices;
SELECT slot_name FROM pg_create_logical_replication_slot('invoices_slot', 'pgoutput');DROP DATABASE
CREATE DATABASE
You are now connected to database "upstream" as user "postgres".
CREATE TABLE
CREATE PUBLICATION
slot_name
---------------
invoices_slot
(1 row)The subscriber starts with two rows that the publisher does not know about, one of which will matter later. The initial copy is skipped so that the two sides begin deliberately out of step, which is the state any long-lived subscription drifts into eventually.
CREATE TABLE invoices (invoice_id integer PRIMARY KEY, total numeric);
INSERT INTO invoices VALUES (1, 10.00), (7, 70.00);
CREATE SUBSCRIPTION invoices_sub
CONNECTION 'dbname=upstream' PUBLICATION invoices_pub
WITH (create_slot = false, slot_name = 'invoices_slot', copy_data = false);
SELECT pg_sleep(3);
SELECT subname, apply_error_count FROM pg_stat_subscription_stats;CREATE TABLE
INSERT 0 2
CREATE SUBSCRIPTION
pg_sleep
----------
(1 row)
subname | apply_error_count
--------------+-------------------
invoices_sub | 0
(1 row)Zero errors, which is correct and is the baseline everything below should be compared against.
Two conflicts that nothing notices
Now a row is inserted upstream and replicated normally, then deleted on the subscriber behind the publisher’s back, and then updated and deleted upstream. The update and the delete both arrive at a subscriber that no longer has the row.
CREATE EXTENSION dblink;
SELECT dblink_exec('dbname=upstream', 'INSERT INTO invoices VALUES (2, 20.00)');
SELECT pg_sleep(3);
SELECT invoice_id, total FROM invoices ORDER BY invoice_id;
DELETE FROM invoices WHERE invoice_id = 2;
SELECT dblink_exec('dbname=upstream', 'UPDATE invoices SET total = 21.00 WHERE invoice_id = 2');
SELECT dblink_exec('dbname=upstream', 'DELETE FROM invoices WHERE invoice_id = 2');
SELECT pg_sleep(4);
SELECT subname, apply_error_count FROM pg_stat_subscription_stats;The result is byte for byte the same on both versions, which is the point of showing it.
CREATE EXTENSION
dblink_exec
-------------
INSERT 0 1
(1 row)
pg_sleep
----------
(1 row)
invoice_id | total
------------+-------
1 | 10.00
2 | 20.00
7 | 70.00
(3 rows)
DELETE 1
dblink_exec
-------------
UPDATE 1
(1 row)
dblink_exec
-------------
DELETE 1
(1 row)
pg_sleep
----------
(1 row)
subname | apply_error_count
--------------+-------------------
invoices_sub | 0
(1 row)Two conflicts happened. The error count is zero. On 17 that is the end of the available information: the subscription is running, the lag is nil, the error count is nil, and the only evidence that the two databases diverged is a pair of lines in a log file that nothing is alerting on.
On 18 the same subscription has more to say.
SELECT subname, confl_update_missing, confl_delete_missing,
confl_insert_exists, confl_multiple_unique_conflicts
FROM pg_stat_subscription_stats; subname | confl_update_missing | confl_delete_missing | confl_insert_exists | confl_multiple_unique_conflicts
--------------+----------------------+----------------------+---------------------+---------------------------------
invoices_sub | 1 | 1 | 0 | 0
(1 row)One update against a missing row, one delete against a missing row. Two counters, each naming what happened, on a subscription that every other measure calls healthy.
That is a genuinely new capability rather than a convenience. The failure mode it exposes is the one that matters most for logical replication used as a migration or a fan-out to reporting replicas: the subscriber drifts, nothing breaks, and the divergence is discovered months later by someone reconciling totals. A counter that goes up when the divergence happens turns a forensic exercise into an alert.
The loud kind, for contrast
The subscriber has had a row with key seven since before the subscription existed. Inserting that key upstream produces the conflict that stops everything.
SELECT dblink_exec('dbname=upstream', 'INSERT INTO invoices VALUES (7, 77.00)');
SELECT pg_sleep(6);
SELECT subname, apply_error_count FROM pg_stat_subscription_stats; dblink_exec
-------------
INSERT 0 1
(1 row)
pg_sleep
----------
(1 row)
subname | apply_error_count
--------------+-------------------
invoices_sub | 3
(1 row)Three errors from one conflicting row, on both versions. The apply worker hit the unique violation, died, was restarted, hit it again, and will go on doing that until somebody removes the obstruction. That retry loop is why an error count on a stuck subscription is a measure of how long it has been stuck rather than of how many things went wrong, and it is a number people routinely over-read.
On 18 the same moment is described more precisely.
SELECT subname, apply_error_count, confl_insert_exists,
confl_update_missing, confl_delete_missing
FROM pg_stat_subscription_stats; subname | apply_error_count | confl_insert_exists | confl_update_missing | confl_delete_missing
--------------+-------------------+---------------------+----------------------+----------------------
invoices_sub | 3 | 3 | 1 | 1
(1 row)The error count and the insert-conflict count move together, because in this case every error was that conflict. The value of having both is that they do not always move together: an apply error caused by a missing table, a permission problem or a type mismatch raises the error count and none of the conflict counters, so the difference between the two is a rough classifier for what kind of intervention is needed before anyone opens the log.
The two counters from the quiet conflicts are still sitting there, unchanged, which is the last thing worth noticing. They are cumulative and they persist across the incident, so the record of the silent divergence is not lost when a noisy one happens on top of it.
What the subscriber has to know to detect anything
A conflict is only countable if the apply worker can tell that the row it is looking for is missing or different, and that depends on something configured on the publisher rather than on the subscriber.
A published table carries a replica identity, which is the set of columns used to identify a row in the change stream. By default it is the primary key. A table without a primary key needs one set explicitly, either to a unique index or to the whole row, and a table published without any usable identity cannot have updates or deletes replicated at all.
That is the prerequisite behind every counter on this page. Where the identity is the primary key, a missing row is detected exactly and counted exactly. Where the identity is the full row, detection still works and is more fragile, because any difference in any column makes the row unrecognisable and produces a conflict that is really a divergence somewhere else.
The other thing worth knowing is that the four counters shown above are not the whole set. There are counters for conflicts specific to bidirectional and multi-origin setups, where a row arrives carrying an origin different from the one that last modified it locally. Those are meaningless on a plain one-way subscription and they are the most important counters in the view on anything doing active-active, because that is precisely where two sides disagree about the same row.
And one counter covers a case the others cannot: an incoming row conflicting with more than one unique constraint at once. That is worth having separately because resolving it requires a human decision about which constraint wins, which is not a decision replication can make.
The alerting shape this changes
The counters change what a logical replication alert can be, and the change is larger than the feature looks.
The alert everybody has is on lag: the subscriber is behind by more than some number of bytes or seconds. It is necessary and it detects exactly one failure mode, which is the loud one. A subscriber that is stuck lags, so the alert fires; a subscriber that is quietly diverging does not lag at all, so the alert stays green while the two databases drift apart. Lag has been the only available proxy for correctness and it is a proxy for availability instead.
The second alert most fleets have is on the error count, which catches the same loud failure a little earlier and adds nothing about the quiet one.
What the conflict counters add is the first signal that is about correctness rather than about progress. A subscriber can be perfectly current, perfectly error-free, and wrong, and until this release the server had no number that went up when that happened. Alerting on any movement in the quiet counters closes a gap that most logical replication deployments do not know they have.
The reason to care is that the quiet divergence compounds. A missing update leaves one row stale. The next update to the same row also finds nothing and is also skipped. Nothing converges, nothing repairs itself, and the difference between the two databases only grows, until somebody reconciles a report and finds a number that does not match.
What to collect, and what to alert on
- Collect every conflict counter on the view, not only the ones you expect. There are more kinds than the four shown here, including one that counts a row conflicting with several unique constraints at once, and a collector built around a fixed list will silently drop whichever kind you did not think of.
- Alert on any increase in the quiet counters, at any rate. These are not a capacity signal where a threshold makes sense; a single update against a missing row means the two databases have diverged and somebody should know.
- Keep alerting on the error count, but read it as duration rather than volume. A stuck subscription inflates it steadily, and treating a high number as many distinct problems sends people looking for a complexity that is not there.
- Reset deliberately or not at all. The view has one reset stamp per subscription, so clearing it to make a dashboard tidy destroys the only record that a divergence ever occurred.
What it costs
Nothing needs enabling. The counters are maintained by the apply worker as part of handling a conflict it was handling anyway, and the view has one row per subscription, so collecting it is negligible on any cluster with a countable number of subscriptions.
The prerequisite is the same as for logical replication generally: the publisher has to be configured for it, which is a restart on a cluster that is not already, and the subscriber needs the identity information to detect a conflict in the first place. A table published without a replica identity gives the apply worker nothing to match on, and conflicts it cannot detect are conflicts it cannot count.