For years, PostgreSQL logical replication has been the go-to for data distribution and zero-downtime upgrades. But it has always had a “gentleman’s agreement” problem: it works perfectly as long as everyone plays by the rules. The moment two nodes try to edit the same row simultaneously, the replication worker hangs, and administrators are left manually surgical-striking the queue.
With PostgreSQL 18, the engine is finally gaining a “brain” for these collisions. Let’s dive into how the new conflict detection framework changes the game.
1. The Anatomy of a Conflict: Why Do They Happen?
In a standard primary-replica setup (physical streaming), the replica is read-only, so conflicts are impossible. However, in logical replication, the target database is “live.”
Conflicts usually arise in two scenarios:
- Multi-Primary (Active-Active): Two nodes accept writes for the same table and replicate to each other.
- Write-Local/Read-Global: A central hub replicates to a spoke, but a local process on the spoke accidentally modifies the replicated data.
Common Conflict Scenarios
- Duplicate Key: Node A and Node B both insert a row with ID=100.
- Update/Delete Missing: Node A tries to update a row that Node B has already deleted.
- Update/Update (Divergence): Both nodes update the same row at the same time; without detection, the “last one in” wins, but the data state between nodes might never match again.
2. The PostgreSQL 18 Breakthrough: Built-in Detection
Before PG18, the replication worker would simply error out and stop (the “crash and burn” method). You had to check logs, find the LSN, and skip the transaction.
PostgreSQL 18 introduces a formal Conflict Detection framework. Instead of failing blindly, the system now identifies the collision at the moment of application.
Key Innovations:
- Remote vs. Local Comparison: The system now compares the “remote” tuple (coming from the source) with the “local” tuple (already on the disk) before applying the change.
- Conflict Logging: Detailed logging that specifies exactly which row, which table, and what type of conflict occurred, allowing for programmatic responses rather than manual guessing.
LOG: conflict detected on relation "public.users": conflict_type="insert_exists" DETAIL: Remote insert row (id=10, username='remote_user') conflicts with existing local row (id=10, username='local_user').
- Querying the New Conflict Statistics: PostgreSQL 18 introduces views to monitor these events without digging through raw text logs:
SELECT relname, last_conflict_type, conflict_countÂ
FROM pg_stat_subscription_statsÂ
WHERE subname = 'my_sub';
This will return insert_exists as the last_conflict_type.
3. Supported Conflict Types & The Road to PG19
PostgreSQL 18 categorizes conflicts to handle them with more nuance. While PG18 focuses on the detection infrastructure, the scope of what can be identified is expanding rapidly.
| Conflict Type | Status | Description |
| insert_exists | PG18 | Trying to insert a row where a unique key already exists. |
| update_differing | PG18 | Updating a row that has been modified locally since the last sync. |
| update_missing | PG18 | Attempting to update a row that doesn’t exist on the target. |
| update_deleted | PG19 (Planned) | A specific sub-case where an update arrives for a row recently deleted by a local vacuum/process. |
Note: The inclusion of update_deleted in the PG19 roadmap is crucial for high-velocity environments where rows are frequently cycled.
4. The Future: Automated Conflict Resolution
Detection is the “diagnosis”; Resolution is the “cure.” The community roadmap is moving toward a conflict_resolver parameter in the CREATE SUBSCRIPTION command.
Proposed Resolution Strategies:
- error: (Default) Stop the replication and wait for manual intervention.
- apply_remote: The incoming change overwrites the local data (Remote Wins).
- keep_local: The incoming change is discarded (Local Wins).
- last_update_wins: Uses timestamps to determine which version is the “truth.”
Summary: Why This Matters
PostgreSQL 18 isn’t just adding a feature; it’s maturing the database into a truly distributed system. By moving conflict logic into the core engine, we reduce the need for third-party extensions and complex “DIY” triggers.
We are laying the tracks for a future where PostgreSQL can handle global data distribution with the same “set it and forget it” reliability we expect from a single-node instance.