PostgreSQL logical replication: zero-downtime migrations

SEO Meta Description: Master PostgreSQL logical replication for zero-downtime migrations. Learn slot management, WAL pressure traps, DDL gaps, and safe cutover choreography at scale.

TL;DR

Logical replication in PostgreSQL enables zero-downtime table migrations through a dual-write → logical replication → atomic cutover pattern. The failure modes are WAL sender memory pressure, DDL replication gaps, and unmonitored subscriber lag drift. This post walks through the choreography that keeps your pager silent.

The problem most teams discover too late

Moving large multi-tenant tables in production — hundreds of millions of rows across thousands of tenants — is where conventional pg_dump or ALTER TABLE approaches collapse entirely. A naive ALTER TABLE ADD COLUMN on a 500GB table can hold a lock for hours. At scale, that’s a P0 incident waiting for a date on the calendar.

In my experience building production systems with PostgreSQL at scale, the teams that sleep through their migrations share one characteristic: they use logical replication correctly. The teams that get paged at 3am don’t.

Understanding pg_logical replication slots

The foundation is the replication slot. A logical replication slot tracks the WAL position that a subscriber has consumed.

-- Create a logical replication slot
SELECT pg_create_logical_replication_slot('migration_slot', 'pgoutput');

-- Monitor slot lag — this is the metric that matters
SELECT
  slot_name,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS lag_size,
  active
FROM pg_replication_slots;

The part that catches everyone off guard: an inactive slot holds WAL indefinitely. If your subscriber drops during migration and the slot persists, your primary disk fills with retained WAL segments. It’s the most common production disaster I’ve seen with this pattern.

The WAL sender memory pressure trap

At scale, WAL senders consume memory proportional to in-flight change volume. The key parameter is logical_decoding_work_mem, introduced in PostgreSQL 13.

SHOW logical_decoding_work_mem;

-- Recommended for large multi-tenant migrations
ALTER SYSTEM SET logical_decoding_work_mem = '256MB';
SELECT pg_reload_conf();

Parameter	Default	Migration recommendation
`logical_decoding_work_mem`	64MB	256MB–1GB
`max_wal_senders`	10	20–40
`wal_keep_size`	0	2GB+
`wal_sender_timeout`	60s	0 (disable during cutover)

Under-provisioning logical_decoding_work_mem forces logical decoding to spill to disk, degrading replication throughput by 10–20x under write-heavy workloads. That’s not a minor performance hit — it’s the difference between a clean cutover and a missed window.

DDL replication: the silent gap

PostgreSQL logical replication does not replicate DDL. Schema changes must be applied to the subscriber first — before data replication begins — and they must remain backward-compatible with the publisher’s current schema.

The correct sequence for adding a column:

Add nullable column to the subscriber first
Start logical replication
Add nullable column to the publisher after replication stabilizes
Backfill defaults independently on each side

-- Step 1: Subscriber (target) FIRST
ALTER TABLE tenants ADD COLUMN plan_tier TEXT;

-- Step 3: Publisher (source), only after replication is running
ALTER TABLE tenants ADD COLUMN plan_tier TEXT;

Reversing this order causes silent replication breakage on any INSERT referencing the new column.

The migration choreography

Phase 1: Dual-write

Route writes to both old and new tables via your application layer. This validates write correctness before introducing any replication dependency.

Phase 2: Logical replication

-- Publisher (source)
CREATE PUBLICATION tenant_migration FOR TABLE tenants;

-- Subscriber (target)
CREATE SUBSCRIPTION tenant_migration_sub
  CONNECTION 'host=source-db dbname=prod'
  PUBLICATION tenant_migration;

Monitor lag continuously. Don’t advance to cutover until lag is consistently under 100ms for at least 10 minutes under full production load.

Phase 3: Atomic cutover

BEGIN;
LOCK TABLE tenants IN SHARE MODE; -- blocks writes, allows reads
-- Confirm subscriber lag = 0 via pg_stat_replication
-- Signal application to switch connection string
DROP PUBLICATION tenant_migration;
COMMIT;

The lock window is typically 200–800ms for final synchronization — not hours.

Subscriber lag monitoring

Don’t fly blind. Ship this query to your observability stack:

SELECT
  pg_wal_lsn_diff(pg_current_wal_lsn(), s.sent_lsn)   AS network_lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), s.replay_lsn)  AS apply_lag_bytes
FROM pg_stat_replication s;

Alert at 50MB lag. Pause migration at 500MB. If lag is growing under steady-state load, the system isn’t ready for cutover.

What to take from this

Monitor replication slot lag as a first-class production metric. An unconsumed slot is a disk exhaustion incident in slow motion. Alert hard at 10GB retained WAL.
Always apply DDL to the subscriber before the publisher. This sequencing is non-negotiable. Encode it as a hard gate in your migration runbook, not a guideline.
Right-size logical_decoding_work_mem before migration day. Spill-to-disk degradation under load is the leading reason cutover windows miss their targets. Benchmark under production write volume first.

Tags: backend, architecture, microservices, devops, cloud