PG18 Hacktober:31 Days of New Features: Logical Replication – Write Conflict Logging

Welcome to the 14th blog post in the “Logical replication” segment of PG18 Hacktober!

PostgreSQL 18 introduced significant enhancements in logical replication, including better handling and tracking of write conflicts. Write conflicts happen when the subscriber has data that clashes with incoming changes from the publisher. These conflicts are now logged and tracked, giving DBAs and developers better visibility into replication issues.

In this blog, we will explore:

1. How write conflicts occur in logical replication
2. How to simulate and monitor them
3. Viewing conflicts via pg_stat_subscription_stats and logs

What does the documentation say?

Logical replication write conflict logging

Write conflicts that occur during logical replication are reported both in logs and in the pg_stat_subscription_stats view.

 What is a Write Conflict?

A write conflict occurs when a replication worker tries to apply a change to a subscriber table, but the row:

• Already exists with different values (UPDATE exists)
• Is missing when expected (UPDATE missing or DELETE missing)
• Has been locally modified in a way that diverges from the publisher

Previously, such conflicts were hard to detect, especially for derived or stored data. PostgreSQL 18 now logs these conflicts and exposes counters via pg_stat_subscription_stats.

Set up for Testing Conflicts

1. Publisher Setup

Publisher table

CREATE TABLE library_books (
    book_id INT PRIMARY KEY,
    title TEXT,
    copies_available INT
);

Insert rows

INSERT INTO library_books VALUES
(1, 'The Great', 5),
(2, '1984', 3);

Publication

CREATE PUBLICATION pub_library_books FOR TABLE library_books;

2. Subscriber Setup

The subscriber table must exist BEFORE subscription creation

CREATE TABLE library_books (
    book_id INT PRIMARY KEY,
    title TEXT,
    copies_available INT
);

Create subscription WITHOUT copying existing data

CREATE SUBSCRIPTION sub_library_books
CONNECTION 'host=172.31.27.61 dbname=kdb user=postgres'
PUBLICATION pub_library_books
WITH (copy_data = false);

Notes:

• copy_data = false will not copy existing rows from the publisher.
• Make sure the subscriber table structure exactly matches the publisher table, or replication will fail.
• If you’re testing conflicts, insert the conflicting rows manually after creating the subscription.

3. Simulating Write Conflicts

A. UPDATE Exists Conflict

Subscriber: manually create a conflicting row
INSERT INTO library_books VALUES (1, 'The Great', 10);

Publisher: update the same row
UPDATE library_books SET copies_available = 4 WHERE book_id = 1;

• When the replication worker tries to apply copies_available = 4 to book_id = 1, it detects a write conflict because the subscriber row differs.

B. INSERT Exists Conflict

Subscriber: insert row first
INSERT INTO library_books VALUES (3, 'Brave New World', 2);

Publisher: insert same row
INSERT INTO library_books VALUES (3, 'Brave New World', 2);

• The replication worker tries to insert book_id = 3, but it already exists → confl_insert_exists increments.

C. UPDATE Origin Differs

The subscriber already has a different value
UPDATE library_books SET copies_available = 5 WHERE book_id = 2;

Publisher issues update
UPDATE library_books SET copies_available = 2 WHERE book_id = 2;

• The subscriber row has been independently modified, confl_update_origin_differs increments.

D. DELETE missing

Subscriber deletes the row first
DELETE FROM library_books WHERE book_id = 2;

Publisher deletes the same row
DELETE FROM library_books WHERE book_id = 2;

• Row does not exist on the subscriber confl_delete_missing increments.
• Both scenarios are tracked by the replication worker in pg_stat_subscription_stats.

New columns added to pg_stat_subscription_stats view:

confl_insert_exists – Number of times a row insertion violated a NOT DEFERRABLE unique constraint during the application of changes. See insert_exists for details about this conflict.

confl_update_origin_differs – Number of times an update was applied to a row that had been previously modified by another source during the application of changes. See update_origin_differs for details about this conflict.

confl_update_exists – Number of times that an updated row value violated a NOT DEFERRABLE unique constraint during the application of changes. See update_exists for details about this conflict.

confl_update_missing – Number of times the tuple to be updated was not found during the application of changes. See update_missing for details about this conflict.

confl_delete_origin_differs – Number of times a delete operation was applied to row that had been previously modified by another source during the application of changes. See delete_origin_differs for details about this conflict.

confl_delete_missing – Number of times the tuple to be deleted was not found during the application of changes. See delete_missing for details about this conflict.

confl_multiple_unique_conflicts – Number of times a row insertion or an updated row values violated multiple NOT DEFERRABLE unique constraints during the application of changes. See multiple_unique_conflicts for details about this conflict.

Viewing Conflicts in Stats

Replication conflicts in PostgreSQL can be tracked using pg_stat_subscription_stats. Example:

SELECT subname,
       apply_error_count,
       sync_error_count,
       confl_update_exists,
       confl_update_missing,
       confl_delete_missing
FROM pg_stat_subscription_stats
WHERE subname = 'sub_library_books';

Output:

• apply_error_count increments for every failed apply
• Row-level conflict counters (confl_update_exists, etc.) show specific types

kdb=# SELECT * FROM pg_stat_subscription_stats WHERE subname = 'sub_library_books';
-[ RECORD 1 ]-------------------+------------------------------
subid                           | 16398
subname                         | sub_library_books
apply_error_count               | 129
sync_error_count                | 0
confl_insert_exists             | 129
confl_update_origin_differs     | 1
confl_update_exists             | 1
confl_update_missing            | 2
confl_delete_origin_differs     | 0
confl_delete_missing            | 1
confl_multiple_unique_conflicts | 0
stats_reset                     | 2025-10-14 06:29:53.404656+00

Enabling Conflict Logging in Logs

To see detailed replication conflict logs, enable:

ALTER SYSTEM SET log_replication_commands = on;
ALTER SYSTEM SET log_min_messages = 'INFO';
SELECT pg_reload_conf();

Log Entries

confl_insert_exists

Best Practices

• Use copy_data = false during testing to avoid duplicate rows.
• Monitor pg_stat_subscription_stats proactively in production.
• Enable logging only when debugging, as it can be verbose.
• Design for conflict resolution — especially in multi-writer topologies.

Conclusion

PostgreSQL 18 brings major observability improvements to logical replication with detailed write conflict tracking:

• Logs conflict types and causes
• Tracks conflict counts by type in pg_stat_subscription_stats
• Helps DBAs and developers debug and fix replication drift efficiently

This feature is especially valuable in multi-writer, read/write hybrid, and derived data replication use cases where data divergence is a real risk.