Rolling Schema Migrations with Active CDC Slots

A rolling schema migration reshapes a table while its replication slot stays live and a connector keeps streaming, using an expand/contract sequence so no single step ever invalidates the slot or halts apply — a discipline covered more broadly in online schema change management. It matters because the naive alternative — stop the connector, migrate, restart — drops or stalls the slot, and a slot that stops advancing restart_lsn pins WAL on the primary until the disk fills.

The whole point of the rolling approach is that the slot never leaves the reserved/active state and the connector never needs a restart. Every phase is designed so that the publisher and subscriber schemas remain mutually valid at that instant, and so that apply can always resume from the last confirmed_flush_lsn without a resnapshot. The revert path is equally deliberate: until the old column is physically dropped, you can back out with a single statement and lose nothing.

Phase-by-Phase Slot Behavior #

Each phase has a defined effect on the slot and a defined revert cost. The table is the contract: as long as you stay in a revert-safe phase, a bad deploy costs a rollback, not a resnapshot.

The one phase that can hurt the slot is backfill, and only through volume: a single UPDATE public.orders SET region = ... over ten million rows produces one enormous transaction whose reorder buffer spills past logical_decoding_work_mem and stalls decoding for every consumer on that slot. Batching keeps each transaction small enough to decode inline, which is why the deployment sequence below never issues an unbounded backfill.

Diagnostic Patterns #

Watch three things throughout: the slot stays active, the apply worker is not erroring, and the backfill is not outrunning the decoder. Run these on the publisher and subscriber during the migration window.

-- PUBLISHER: the slot must stay active with retained WAL bounded during backfill.
-- ALERT: retained_wal climbing fast, or active=false, during the migration.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))        AS retained_wal,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS apply_lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- SUBSCRIBER: a rising apply_error_count means a phase landed out of order.
-- ALERT: any increase in apply_error_count while the migration is in flight.
SELECT subname, apply_error_count, sync_error_count, stats_reset
FROM pg_stat_subscription_stats;

-- PUBLISHER: confirm the largest in-flight transaction is not a giant backfill
-- that will spill the reorder buffer. ALERT: a single backend writing for minutes.
SELECT pid, state, now() - xact_start AS txn_age, query
FROM pg_stat_activity
WHERE state = 'active' AND query ILIKE 'update public.orders%'
ORDER BY xact_start;

If apply_lag grows during backfill but drains between batches, the decoder is keeping pace and no action is needed. If it grows monotonically, the batch size is too large — shrink it before the slot approaches max_slot_wal_keep_size.

Safe Deployment Sequence #

This is the zero-downtime sequence with the revert kept one statement away at every reversible step. It assumes a live slot named dbz_orders and a Debezium connector streaming from a publication named pub_orders.

1. Expand — subscriber first, then publisher. Add the target column where apply lands before the source can emit it.

-- SUBSCRIBER
ALTER TABLE public.orders ADD COLUMN region text;
-- PUBLISHER (metadata-only in PG 11+ with a non-volatile default)
ALTER TABLE public.orders ADD COLUMN region text DEFAULT 'unknown';
-- PG 15+ only if the publication enumerates columns:
ALTER PUBLICATION pub_orders SET TABLE public.orders (id, status, total, region);

Debezium picks up the widened shape from the next pgoutput relation message without a restart; no ALTER SUBSCRIPTION ... REFRESH is needed because the table set did not change.

2. Backfill in bounded batches on the publisher. Let the changes flow to the subscriber through the existing slot rather than backfilling both sides. Keep each transaction under a few thousand rows.

-- PUBLISHER: loop this until no rows remain; each batch is one small transaction.
UPDATE public.orders
SET region = derive_region(shipping_zip)
WHERE region = 'unknown'
  AND id IN (SELECT id FROM public.orders WHERE region = 'unknown' LIMIT 2000);

3. Dual-write and cut over reads. Deploy the application build that writes both columns, verify the new column matches, then move reads to it. Nothing here touches the slot.

4. Contract — publisher first, then subscriber. Only once nothing reads the old column, and only when you accept losing the revert path:

-- PUBLISHER first: the relation message narrows on the next change.
ALTER TABLE public.orders DROP COLUMN legacy_zone;
-- SUBSCRIBER last: safe because the source no longer emits it.
ALTER TABLE public.orders DROP COLUMN legacy_zone;

Revert. At any point before the contract phase, back out by dropping the new column — publisher first so it stops appearing in change events, then subscriber. Apply resumes from confirmed_flush_lsn with no resnapshot because the slot never dropped:

-- PUBLISHER first, then SUBSCRIBER
ALTER PUBLICATION pub_orders SET TABLE public.orders (id, status, total);  -- PG 15+
ALTER TABLE public.orders DROP COLUMN region;   -- publisher
ALTER TABLE public.orders DROP COLUMN region;   -- subscriber

Pipeline Integration #

The consumer must tolerate the transition window, when a change event may arrive with or without the new column depending on which side of the migration produced it. Make the apply idempotent and default-tolerant so a replay across the phase boundary converges rather than throwing.

# Idempotent upsert that tolerates the new column being absent mid-migration.
def apply_change(cur, evt: dict) -> None:
    row = evt["after"]
    region = row.get("region")            # None during the pre-expand window
    cur.execute(
        """
        INSERT INTO orders (id, status, total, region)
        VALUES (%(id)s, %(status)s, %(total)s, %(region)s)
        ON CONFLICT (id) DO UPDATE SET
            status = EXCLUDED.status,
            total  = EXCLUDED.total,
            region = COALESCE(EXCLUDED.region, orders.region)  -- never overwrite with NULL
        WHERE orders.source_lsn < %(lsn)s;                     -- LSN high-water guard
        """,
        {**row, "region": region, "lsn": evt["lsn"]},
    )

Two rules keep this safe under an at-least-once stream. COALESCE(EXCLUDED.region, orders.region) prevents a pre-expand replay from clobbering a value written after the column existed, and the source_lsn guard drops any event not newer than what is already stored, so a connector restart that replays from confirmed_flush_lsn produces no regressions. On failover, the same guard is what lets a promoted primary resume safely: if the new node’s write head sits behind an LSN the consumer already applied, the high-water comparison discards the re-sent rows instead of double-applying them. Wrap the fetch loop with exponential backoff and jitter so a connector blip during the migration does not become a reconnect storm, and never let backfill lag translate into unbounded slot growth — throttle the backfill, not the connector.

The connector itself needs no special handling if it is configured correctly going in. A Debezium PostgreSQL connector re-reads column metadata from every pgoutput relation message, so the expand step is transparent to it; there is no schema-history replay and no restart. The one setting that matters during a long backfill is heartbeat.interval.ms: if the captured table is quiet while you migrate a different table, set a heartbeat (for example 10000) so the slot’s confirmed_flush_lsn keeps advancing on unrelated WAL rather than pinning it for the whole window. Leave slot.drop.on.stop = false throughout — an accidental connector bounce must never drop the slot mid-migration, because that is precisely the resnapshot the rolling approach exists to avoid. If a downstream schema registry fronts the topic, register the widened schema under backward-compatible rules before the expand step so consumers accept the new field the moment it appears; contract only after every consumer has been redeployed to stop requiring the old one.

Authoritative references #

• PostgreSQL: ALTER SUBSCRIPTION — REFRESH PUBLICATION, copy_data, and SKIP semantics used during and after a migration.
• PostgreSQL: ALTER PUBLICATION — adding, removing, and setting the published column list (PG 15+).
• PostgreSQL: ALTER TABLE — which column changes are metadata-only versus a full rewrite.

Related #

• Online Schema Change Management — the parent guide: additive-first ordering, REFRESH PUBLICATION, and cross-node coordination.
• Replicating DDL Changes with Event Triggers — automating the propagation of the DDL this sequence applies by hand.
• Debezium Connector Configuration — how the connector reacts to the relation-message shape change without a restart.