PostgreSQL logical replication: slot lag & WAL tradeoffs
Meta description: Deep dive into PostgreSQL logical replication slot lag under bursty mobile traffic, replication identity WAL tradeoffs, conflict resolution, and disk-safe monitoring thresholds.
TL;DR: Logical replication is the right tool for zero-downtime multi-region read scaling — until slot lag eats your primary’s disk or a misconfigured replication identity doubles your WAL output. Most teams don’t figure this out until their mobile backend is already in trouble.
The promise and the hidden cost
In my experience building production systems, logical replication gets adopted for one reason: read scaling without downtime. Spin up a subscriber in us-east, replicate from eu-west, point your mobile read traffic there. Clean, surgical, zero-schema-lock migrations as a bonus.
Logical replication latency on low-traffic tables hovers at 10–50ms. Under bursty mobile traffic patterns (push notification waves, morning retention spikes), that number can balloon to seconds. The culprit is almost always slot lag.
Slot lag: how bursty mobile traffic breaks your primary’s disk
Replication slots are PostgreSQL’s mechanism for ensuring a subscriber never misses a WAL segment. The primary holds onto WAL files until every slot has consumed them.
The danger: if a subscriber falls behind — network partition, replica maintenance window, a slow query on the subscriber eating I/O — the primary accumulates WAL on disk indefinitely.
Mobile backends produce a specific failure pattern. A push campaign fires at 09:00. Your write primary absorbs 40,000 INSERTs in 90 seconds. The subscriber, already processing a batch, falls 200MB behind. That is recoverable. But if your replica is also handling a schema migration or a long-running analytics query, you can accumulate gigabytes of WAL in under 10 minutes.
-- Check current slot lag in bytes and WAL files retained
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_size,
wal_status,
safe_wal_size
FROM pg_replication_slots
WHERE slot_type = 'logical';A wal_status of 'lost' means you have already blown past max_slot_wal_keep_size. At that point the slot is invalidated and your subscriber must be re-seeded from scratch.
Replication identity: the WAL amplification nobody benchmarks
Every UPDATE and DELETE on a published table writes a before-image to WAL. The replication identity mode controls what that before-image contains.
| Mode | Before-image contents | WAL amplification | When to use |
|---|---|---|---|
DEFAULT | Primary key columns only | Low (1x baseline) | Tables with a PK — the default, correct choice |
FULL | All columns | High (2–5x for wide rows) | Tables without a PK, or when you need full before-image for conflict detection |
NOTHING | No before-image | Minimal | INSERT-only tables; breaks UPDATE/DELETE replication silently |
INDEX | Specific unique index columns | Medium | Composite-key tables without a serial PK |
The production trap I see repeatedly: a team migrates a legacy table without a primary key to the subscriber. Logical replication requires FULL mode for that table. A 20-column user-events table that previously wrote 200 bytes per WAL record now writes 1.8KB. At 5,000 events/second, that is a 9x increase in WAL generation rate — enough to saturate I/O on an underpowered primary.
Audit every published table with \d+ <table> before enabling replication. Add primary keys. Use DEFAULT mode everywhere you can.
Conflict resolution when replica writes slip through
PostgreSQL logical replication does not handle write conflicts automatically. If your application writes directly to a replica — even by accident, through a misconfigured connection pool or a read/write split bug — you will get silent divergence or subscription errors.
Common conflict types:
- Duplicate key: A row inserted on the replica that the primary then tries to replicate
- Update on missing row: A replica delete happens before the primary’s UPDATE arrives
The subscriber logs these as ERROR and stops. The slot lag then begins accumulating again while an engineer investigates.
The correct architecture: replicas are read-only at the PostgreSQL role level. Enforce this with pg_hba.conf and connection pool routing rules, not application discipline.
-- Lock down the replica subscriber user
ALTER USER replication_user CONNECTION LIMIT 0; -- block writes by convention
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user;Monitoring thresholds for mobile backend read scaling
On production backends at 50K–500K DAU, I alert on:
| Metric | Warning | Critical | Action |
|---|---|---|---|
| Slot lag (bytes) | 500MB | 2GB | Investigate subscriber I/O, check max_slot_wal_keep_size |
| Replication delay (seconds) | 5s | 30s | Check subscriber load, network latency |
safe_wal_size | < 1GB | < 200MB | Increase max_slot_wal_keep_size or drop idle slots |
| Inactive slots | Any | — | Drop immediately; they retain WAL forever |
-- Alert: any slot older than 30 minutes with no activity
SELECT slot_name, now() - pg_last_xact_replay_timestamp() AS idle_time
FROM pg_replication_slots
WHERE active = false;Before you ship
Set max_slot_wal_keep_size before anything else. The default is unlimited. Pick a value your primary’s disk can tolerate — 10GB is a reasonable starting point. Yes, slots may be invalidated under extreme lag. That is recoverable. Disk exhaustion is not.
Audit replication identity before publishing tables. Every table without a primary key in your publication is a WAL amplification bomb. Add PKs, or benchmark FULL mode under your actual write load before you go live.
Make replicas structurally read-only, not just conventionally. Revoke write permissions at the database role level. Logical replication conflict errors are silent and accumulate lag. Enforce the boundary at the infrastructure layer.
Tags: backend api architecture microservices mobile