PostgreSQL Logical Replication for Zero-Downtime Multi-Region Reads

---
title: "PostgreSQL Logical Replication for Mobile Backends: Slot Lag, WAL Tradeoffs, and Conflict Resolution"
published: true
description: "Deep dive into PostgreSQL logical replication slot lag under bursty mobile traffic, replication identity WAL tradeoffs, conflict resolution, and disk-safe monitoring thresholds."
tags: postgresql, architecture, mobile, performance
canonical_url: https://blog.mvpfactory.co/postgresql-logical-replication-slot-lag-mobile-backend
---

What We Are Building

By the end of this article you will know how to safely run PostgreSQL logical replication for multi-region read scaling on a mobile backend — without blowing up your primary's disk or silently diverging your replica. We will cover slot lag mechanics, replication identity modes, conflict resolution, and the monitoring thresholds I use in production at 50K–500K DAU.

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. Zero-schema-lock migrations as a bonus. Let me show you a pattern I use in every project — and the traps most teams only find after they are already in trouble.

---

Prerequisites

• PostgreSQL 14+ (logical replication slots and safe_wal_size available)
• A primary and at least one subscriber instance
• pg_hba.conf access on both nodes
• Basic familiarity with WAL concepts

---

Step 1: Understand Slot Lag Before You Ship

Replication slots ensure a subscriber never misses a WAL segment. The primary holds WAL files until every slot has consumed them. That retention has no upper limit by default.

Here is the minimal setup to get this working and visible:

-- 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. The slot is invalidated and your subscriber must be re-seeded from scratch.

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 falls 200MB behind — recoverable. But if the replica is also handling a schema migration or a long-running analytics query, you can accumulate gigabytes of WAL in under 10 minutes.

Set max_slot_wal_keep_size before anything else. The default is unlimited. 10GB is a reasonable starting point. Yes, slots may be invalidated under extreme lag. That is recoverable. Disk exhaustion is not.

---

Step 2: Audit Replication Identity on Every Published Table

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	WAL amplification	When to use
DEFAULT	Primary key only	Low (1x)	Tables with a PK — use this everywhere you can
FULL	All columns	High (2–5x)	Tables without a PK, or full conflict detection
NOTHING	None	Minimal	INSERT-only tables; breaks UPDATE/DELETE silently
INDEX	Specific unique index	Medium	Composite-key tables without a serial PK

The docs do not mention this, but a 20-column user-events table without a primary key forced into FULL mode 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 — enough to saturate I/O on an underpowered primary.

Audit every published table before enabling replication:

\d+ your_table_name

Add primary keys. Use DEFAULT mode everywhere you can.

---

Step 3: Make Replicas Structurally Read-Only

PostgreSQL logical replication does not handle write conflicts automatically. If your application writes directly to a replica — even accidentally, through a misconfigured connection pool or a read/write split bug — you will get silent divergence or subscription errors that stop the slot and begin accumulating lag again.

Enforce the boundary at the infrastructure layer, not application discipline:

-- Lock down the replica subscriber user
ALTER USER replication_user CONNECTION LIMIT 0;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user;

Replicas must be read-only at the PostgreSQL role level, enforced in pg_hba.conf and connection pool routing rules.

---

Gotchas

Inactive slots retain WAL forever. Any slot with active = false is silently accumulating WAL. Drop them immediately.

-- Alert: any slot idle for more than 30 minutes
SELECT slot_name, now() - pg_last_xact_replay_timestamp() AS idle_time
FROM pg_replication_slots
WHERE active = false;

Logical replication latency is not flat. It hovers at 10–50ms on low-traffic tables. Under bursty mobile traffic — push notification waves, morning retention spikes — it can balloon to seconds. The culprit is almost always slot lag, not network.

Conflict errors are silent accumulators. Duplicate key or update-on-missing-row errors stop the subscriber and log an ERROR. Lag builds while an engineer investigates. The correct fix is structural read-only enforcement, not error handling.

---

Monitoring Thresholds

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 limit or drop idle slots
Inactive slots	Any	—	Drop immediately

---

Conclusion

Logical replication is the right tool for zero-downtime multi-region read scaling on mobile backends. Here is the checklist that will save you hours: set max_slot_wal_keep_size before you go live, audit replication identity on every published table and eliminate FULL mode where a primary key will do, and enforce read-only replicas at the role level — not the application level. Get these three right and your slot lag stays predictable under the bursty traffic patterns that break most teams.

Further reading: PostgreSQL Logical Replication docs, pg_replication_slots view