DEV Community

SoftwareDevs mvpfactory.io
SoftwareDevs mvpfactory.io

Posted on • Originally published at mvpfactory.io

PostgreSQL Logical Replication for Zero-Downtime Multi-Tenant Migrations

#webdev #programming 
---
title: "PostgreSQL Logical Replication: Zero-Downtime Migrations Without the 3am Page"
published: true
description: "Master PostgreSQL logical replication for zero-downtime migrations. Learn slot management, WAL pressure traps, DDL gaps, and safe cutover choreography at scale."
tags: postgresql, architecture, devops, cloud
canonical_url: https://blog.mvpfactory.co/postgresql-logical-replication-zero-downtime-migrations
---
Enter fullscreen mode Exit fullscreen mode

What We Are Building

By the end of this workshop, you will have a working migration choreography for moving large multi-tenant PostgreSQL tables — hundreds of millions of rows — without downtime, without locks lasting more than ~800ms, and without filling your primary disk with retained WAL segments.

The pattern is: dual-write → logical replication → atomic cutover. Let me show you the exact choreography I use in production, including the failure modes that will page you at 3am if you skip them.

Prerequisites

  • PostgreSQL 13+ (for logical_decoding_work_mem)
  • Superuser access on both publisher and subscriber
  • Basic familiarity with WAL and replication concepts
  • A monitoring stack you can ship custom queries to

Step 1: Understand the Replication Slot

The foundation is the replication slot. A logical replication slot tracks the WAL position 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;
Enter fullscreen mode Exit fullscreen mode

Here is the gotcha that will save you hours: 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 is the most common production disaster I have seen with this pattern. Monitor that slot like a first-class production metric — alert hard at 10GB retained WAL.

Step 2: Right-Size WAL Sender Memory Before Migration Day

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();
Enter fullscreen mode Exit fullscreen mode
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 is not a minor performance hit — it is the difference between a clean cutover and a missed window. Benchmark under production write volume before migration day.

Step 3: Apply DDL to the Subscriber First

The docs do not emphasize this enough, but PostgreSQL logical replication does not replicate DDL. Schema changes must land on the subscriber before data replication begins, and they must remain backward-compatible with the publisher's current schema.

Here is the minimal setup to get DDL sequencing right when adding a column:

-- 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;
Enter fullscreen mode Exit fullscreen mode

Reversing this order causes silent replication breakage on any INSERT referencing the new column. Encode this as a hard gate in your runbook, not a guideline.

The correct sequence:

  1. Add nullable column to the subscriber first
  2. Start logical replication
  3. Add nullable column to the publisher after replication stabilizes
  4. Backfill defaults independently on each side

Step 4: The Three-Phase Migration Choreography

Phase 1 — Dual-Write

Route writes to both old and new tables at the 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;
Enter fullscreen mode Exit fullscreen mode

Monitor lag continuously. Do not 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;
Enter fullscreen mode Exit fullscreen mode

The lock window is typically 200–800ms for final synchronization — not hours. That is the entire point of this choreography.

Step 5: Ship Lag Monitoring to Your Observability Stack

Do not fly blind. Wire this query to your alerting system before migration day:

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;
Enter fullscreen mode Exit fullscreen mode

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

Gotchas

Inactive slots are a disk exhaustion incident in slow motion. Drop any slot that is no longer actively consumed. Never leave orphaned slots on a production primary.

DDL sequencing is non-negotiable. Subscriber first, always. If your migration runbook does not encode this as a hard gate with a human checkpoint, it will eventually be skipped.

wal_sender_timeout during cutover. Set it to 0 in the cutover window. A timed-out WAL sender mid-cutover forces a full restart of the subscription — and your lag counter resets to zero while rows are still in-flight.

Spill-to-disk degradation is not obvious until it is. Under write-heavy workloads, insufficient logical_decoding_work_mem will silently tank replication throughput. You will not see it in error logs; you will see it in lag graphs that creep upward and never stabilize.

Conclusion

Let me show you a pattern I use in every project: treat replication slot lag as a first-class production metric from day one, not something you wire up on migration day. The teams that sleep through large migrations have this instrumentation running weeks before they need it.

Three things to take from this workshop:

  1. Monitor replication slot lag in production, always. Alert at 10GB retained WAL.
  2. Apply DDL to the subscriber before the publisher. No exceptions.
  3. Right-size logical_decoding_work_mem and benchmark under production write volume before you commit to a cutover window.

Further reading:

Top comments (0)