PostgreSQL logical replication for multi-region reads
Meta description: Master PostgreSQL logical replication slots, WAL configuration, and lag monitoring for multi-region read scaling. Keep your API p99 latency honest before replication lag breaks your SLA.
TL;DR
Logical replication slots give you fine-grained, per-consumer WAL delivery across regions — but mismanage them and you will blow your disk, stall your primary, and watch p99 latency climb. This post covers slot lifecycle, conflict detection, the WAL knobs that matter, and the monitoring queries you should have been running yesterday.
Tags: backend api architecture microservices cloud
Why logical replication, not physical
Physical (streaming) replication ships byte-for-byte page images. It is fast and simple, but it replicates everything — including bloat, temporary tables, and operations your read replicas do not need. More critically, it cannot skip or filter, which makes selective multi-region fan-out impossible.
Logical replication decodes WAL into row-level change streams. Each subscriber receives only the tables it subscribes to, in a format it can apply independently. That selectivity is the entire value proposition for multi-region read scaling.
The slot lifecycle you must understand
A replication slot is a server-side cursor into the WAL stream. The primary guarantees it will retain WAL segments until every slot has consumed them.
-- Create a logical replication slot
SELECT pg_create_logical_replication_slot('region_us_east', 'pgoutput');
-- Inspect active slots
SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots;Three lifecycle states matter in production:
| State | Risk |
|---|---|
| Active, low lag | Healthy — WAL is consumed and released promptly |
| Active, high lag | Disk pressure — WAL accumulates faster than the slot advances |
| Inactive (subscriber down) | Critical — WAL retention grows unbounded until max_slot_wal_keep_size triggers forced invalidation |
What most teams get wrong: an inactive slot is not a monitoring alert — it is a ticking disk bomb. Set max_slot_wal_keep_size (PostgreSQL 13+) to cap retention, but treat slot inactivity as a P1 incident.
The three configuration knobs that govern everything
# postgresql.conf
wal_level = logical # required; 'replica' blocks logical decoding
max_replication_slots = 20 # one per subscriber + internal use; size generously
hot_standby_feedback = on # prevents query cancellations on replicas; has a costhot_standby_feedback deserves more than a comment. Enabling it prevents the primary from vacuuming rows that a long-running replica query still needs — eliminating the dreaded ERROR: canceling statement due to conflict with recovery. But it also pins dead row versions on the primary, inflating table bloat under heavy OLAP workloads on replicas. The right call depends on your read query duration distribution. If p99 replica queries are under two seconds, enable it. If you are running analytics joins that run for minutes, consider a dedicated replica with a relaxed max_standby_streaming_delay instead.
Conflict resolution: where multi-region writes get dangerous
Logical replication is single-primary by default. If you allow writes on a subscriber (bi-directional replication via pglogical or Postgres 16’s native bi-directional support), conflicts are inevitable.
Conflict categories and their defaults:
| Conflict type | Default resolution | Production recommendation |
|---|---|---|
| INSERT on existing key | Error, halt replication | Use SKIP with application-layer reconciliation |
| UPDATE on missing row | Skip silently | Log and alert — data drift indicator |
| DELETE on missing row | Skip silently | Generally safe to ignore |
In my experience building production systems with bi-directional replication, the safest posture is last-write-wins based on an updated_at timestamp column, enforced at the subscriber side via a conflict trigger, not at the replication layer. The replication layer’s job is delivery, not business logic.
Your replication lag budget
Define your lag budget before your SLA is at risk. The query below surfaces per-slot lag in bytes and estimated seconds (using the primary’s current WAL write rate):
SELECT
slot_name,
pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_size,
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int AS lag_seconds
FROM pg_replication_slots
WHERE slot_type = 'logical';Pair this with an alert threshold. For a p99 API budget of 50ms with a read-replica read path, a lag exceeding 500ms on OLTP workloads warrants immediate investigation. For async reporting reads, 30 seconds may be acceptable. Encode the number — do not leave it implicit.
What to do now
Monitor slot lag continuously. Not on-demand, not in a weekly review — continuously. Inactive slots with unbounded WAL retention have caused more surprise outages than misconfigured wal_level. Add confirmed_flush_lsn lag to your primary dashboard today.
Set max_slot_wal_keep_size before you need it. The default is unlimited. Pick a value that gives your subscriber time to recover from a brief outage without exhausting primary disk; a reasonable starting point is 20–40% of your WAL partition.
Treat replication lag as a first-class SLO tied to your API p99 target. If your read path uses replica data, lag is latency. Not approximately — exactly. Your users are waiting on it.