Zero-downtime schema migrations with Postgres replication
Meta description: Learn how PostgreSQL logical replication slots let you run dual schema versions, drain in-flight writes, and execute a clean atomic cutover.
Tags: backend api microservices architecture devops
TL;DR
Logical replication lets you run old and new schema versions simultaneously. The cutover becomes a traffic switch, not a database lock. The traps: slot lag causing WAL bloat, replication identity mismatches silently dropping row updates, and a cutover window you didn’t actually drain. This post walks through the full playbook.
Why most teams get schema migrations wrong
Most teams treat schema migration as a database problem. It’s actually a traffic coordination problem. You lock the table, run ALTER TABLE, unlock — and somewhere in those seconds, a queue backs up, a timeout fires, and a customer sees an error. At scale, “seconds” is unacceptable.
The correct mental model is blue/green deployment, applied to your schema layer. Logical replication makes this possible.
Dual schema with a logical slot
PostgreSQL logical replication decodes the WAL stream into row-level change events. Unlike physical replication, it’s schema-aware and filterable. You can replicate a single table, transform column names mid-stream, and keep two schema versions live at once.
-- On the source (primary), create a replication slot
SELECT pg_create_logical_replication_slot(
'migration_slot',
'pgoutput'
);
-- Create a publication for the target table
CREATE PUBLICATION migration_pub FOR TABLE orders;On the target (which can be the same cluster, a different schema, or a separate instance):
-- Subscribe and begin streaming
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=primary dbname=prod user=replicator'
PUBLICATION migration_pub
WITH (slot_name = 'migration_slot', create_slot = false);Your application writes to the old schema. The replication stream propagates every insert, update, and delete to the new schema in near-real-time. When lag is zero, you flip the connection string.
The three traps that will wake you up at 3am
1. WAL bloat from inactive slots
A logical replication slot holds WAL segments until the subscriber confirms consumption. If your subscriber falls behind — network hiccup, slow transform logic — WAL accumulates on disk.
| Slot lag | WAL retained | Risk |
|---|---|---|
| 0s | Minimal | Safe |
| 30s | ~500MB at 100 TPS | Monitor |
| 5min | Multi-GB | Disk pressure |
| Stuck slot | Unbounded | Disk full, primary crash |
(Estimates assume ~5KB avg row size; actual figures vary by schema and wal_level setting.)
Monitor with:
SELECT slot_name, pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';Set a hard limit: max_slot_wal_keep_size = 10GB. If a slot exceeds this, Postgres will drop it rather than crash. That’s the acceptable tradeoff.
2. Replication identity silently dropping updates
This one is subtle and will corrupt your migration without a single error message. For logical replication to propagate UPDATE and DELETE, Postgres needs a way to identify the row on the subscriber — that’s the replication identity.
-- Check current identity
SELECT relreplident FROM pg_class WHERE relname = 'orders';
-- 'd' = default (primary key), 'f' = full row, 'i' = index, 'n' = nothing
-- If no primary key exists, updates silently drop
ALTER TABLE orders REPLICA IDENTITY FULL;Tables without a primary key have no row identifier under DEFAULT identity, so updates and deletes are silently dropped on the subscriber. You’ll only discover this when row counts diverge at cutover.
3. The cutover window you didn’t actually drain
Zero-lag on the slot doesn’t mean zero in-flight writes. Application connections have uncommitted transactions. The sequence:
-- 1. Force long-running transactions out
SET statement_timeout = '5s';
-- 2. Poll until active write connections reach zero
-- (do NOT use a fixed sleep — it does not guarantee drain)
SELECT count(*) FROM pg_stat_activity
WHERE state = 'active'
AND query NOT ILIKE 'select%'
AND backend_type = 'client backend';
-- Re-run until this returns 0 before proceeding
-- 3. Flip application config to new schema endpoint
-- 4. DROP SUBSCRIPTION migration_sub;
-- 5. SELECT pg_drop_replication_slot('migration_slot');Steps 1-2 are the part teams skip. Skip them and you race uncommitted writes at cutover.
The cutover playbook
# 1. Monitor lag until it hits zero
watch -n1 "psql -c \"SELECT confirmed_flush_lsn, pg_current_wal_lsn() FROM pg_replication_slots WHERE slot_name='migration_slot'\""
# 2. Quiesce writes (feature flag or maintenance mode)
# 3. Poll pg_stat_activity until active write connections = 0
# 4. Rotate DNS / connection string to new schema
# 5. Smoke test new schema
# 6. Clean up slot (do NOT leave it dangling)
psql -c "SELECT pg_drop_replication_slot('migration_slot');"The entire window between step 2 and step 4 should be under 10 seconds. If it’s not, your drain logic is incomplete.
Three things to do before your next migration
Set max_slot_wal_keep_size before you create any logical replication slot in production. An unmonitored stuck slot will fill your disk and crash your primary. This is not a hypothetical.
Audit replication identity before you start. Run SELECT relname, relreplident FROM pg_class WHERE relkind = 'r' on every table in scope. Any value other than 'd' backed by a real primary key needs explicit remediation before you touch anything else.
Treat the cutover as a traffic coordination exercise. Quiesce writes at the application layer, poll for zero active write connections, then switch. A controlled 10-second quiesce removes all the rollback risk that a live ALTER TABLE lock carries with it.
In my experience, the replication slot approach is the only migration strategy that reliably keeps your SLO intact — but only if you respect the WAL retention semantics and replication identity contract. Get the traffic layer right first, and the database cutover becomes the easy part.