Online Schema Change Management

Online schema change management is the discipline within Replication Topologies & Failover Operations of altering tables that logical replication is actively capturing without breaking the stream: because PostgreSQL never replicates DDL, every change is a coordinated pair of migrations — one on the publisher, one on the subscriber — that must be applied in an order that never lets one side reference a column the other does not yet have. Get the order wrong and the apply worker does not degrade gracefully; it stops on the first offending row, apply_error_count climbs, and the slot behind it pins WAL until you either fix the schema or lose the primary’s disk.

The failure is structural, not exotic. A publisher-side ALTER TABLE ... ADD COLUMN starts emitting a wider tuple in the next pgoutput relation message; if the subscriber table does not already carry that column, the apply worker raises logical replication target relation "public.orders" is missing replicated column "region" and halts the whole subscription — not just that table. The subscription stops advancing confirmed_flush_lsn, the replication slot freezes restart_lsn, and every downstream Change Data Capture (CDC) consumer stalls behind a schema mismatch that a single mis-ordered statement introduced. The safe path is always additive-first and side-aware: widen the target before the source, narrow the source before the target.

Prerequisites & Configuration Objects #

Two facts about how logical replication matches rows drive every ordering rule on this page. First, the apply worker maps columns by name, not by ordinal position, so column order on the two sides is irrelevant but names must match exactly. Second, the mapping is asymmetric: a subscriber table may have more columns than the publisher exposes — the extras take their local default or NULL on every applied row — but it may never have fewer, because a replicated column with no target is a hard apply error. That asymmetry is why a widening change is safe to land on the subscriber early and a narrowing change is safe to land on the publisher early.

Before you run any migration against a captured table, confirm the objects and privileges are in place on both nodes:

-- Publisher: the replica identity determines what UPDATE/DELETE carry, and a
-- type or identity change here reshapes every subsequent change event.
SELECT relname, relreplident            -- d=default(PK) f=full n=nothing i=index
FROM pg_class WHERE relname = 'orders';

-- Publisher: exactly which columns the publication currently emits.
-- PG 15+ column lists mean the published shape can differ from the table.
SELECT pubname, attnames, rowfilter
FROM pg_publication_tables WHERE tablename = 'orders';

-- Subscriber: the role running apply needs DDL rights on the target schema
-- if you let migration tooling alter the target as the same principal.

The role that owns the migration needs ALTER on the affected tables on each node, and — if the change adds a table to a publication that must then be picked up — the subscription owner needs to be able to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION. Keep max_slot_wal_keep_size finite (for example 20GB) throughout any schema migration: a change that accidentally stalls apply will pin WAL, and the finite cap is the difference between an invalidated slot you can re-seed and a full-disk outage that takes the primary offline. On PG 15+, prefer explicit publication column lists over FOR ALL TABLES so that adding a physical column does not silently change the replicated shape before the subscriber is ready for it.

Step-by-Step Implementation #

The safe procedure is the expand/contract pattern: introduce the new shape additively, move traffic onto it while both shapes coexist, then remove the old shape — never rewriting a captured table in place. Each numbered step names the node it runs on.

1. Add the column on the subscriber first (expand). The subscriber now carries a column the publisher does not emit; apply continues to fill it with the local default. This is always safe.

-- SUBSCRIBER
ALTER TABLE public.orders ADD COLUMN region text;   -- nullable, no rewrite

2. Add the column on the publisher and extend what is published. In PG 11+ an ADD COLUMN with a non-volatile default is a metadata-only change — no table rewrite, no ACCESS EXCLUSIVE lock held while the whole table is scanned. If the publication uses an explicit column list, you must also add the column there, or it will never enter the WAL decoding stream.

-- PUBLISHER
ALTER TABLE public.orders ADD COLUMN region text DEFAULT 'unknown';
-- PG 15+: only needed when the publication enumerates columns explicitly.
ALTER PUBLICATION pub_orders SET TABLE public.orders (id, status, total, region);

3. Refresh the subscription only when the published table set changed. Adding a column to an already-replicated table needs no refresh — the next pgoutput relation message carries the new shape automatically. Adding a new table to the publication does require a refresh, and by default it copies that table’s existing contents:

-- SUBSCRIBER: pick up newly added tables. copy_data=true (default) initial-copies
-- them; copy_data=false streams only changes from now, leaving history unsynced.
ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION WITH (copy_data = true);

4. Dual-write and backfill during the transition. Point application writes at the new column, backfill historical rows in bounded batches on the publisher, and let the changes flow to the subscriber through the existing stream rather than backfilling both sides independently. Keep batches small (a few thousand rows) so a backfill UPDATE does not spill a giant reorder buffer through logical_decoding_work_mem and stall the slot.

5. Contract in reverse order. When nothing reads the old column, drop it on the publisher first so it stops appearing in change events, then on the subscriber. Between the two statements the subscriber harmlessly carries a column the publisher no longer sends — the same tolerated asymmetry, now working in your favor.

-- PUBLISHER first
ALTER TABLE public.orders DROP COLUMN legacy_zone;
-- SUBSCRIBER last, once no replayed event references it
ALTER TABLE public.orders DROP COLUMN legacy_zone;

Type changes follow the same shape but never mutate the column in place on a captured table. ALTER COLUMN ... TYPE that requires a rewrite takes ACCESS EXCLUSIVE and rewrites every heap tuple, generating a WAL storm the decoder must process and blocking reads for the duration. Instead add a new column of the target type, dual-write, backfill, and drop the old one — expand/contract applied to a retype. The concrete zero-downtime version of this sequence against a live slot and a streaming connector is worked end to end in rolling schema migrations with active CDC slots.

Parameter Reference Table #

Each row is a class of change, the side that must go first, and the object touched. “Order” is the rule that keeps the two schemas mutually valid at every intermediate moment.

Diagnostic Queries #

The signal that a schema change has broken apply is durable and specific: the subscription’s error counter increments and the worker stops. Poll these on the subscriber during and after any migration.

-- SUBSCRIBER: durable apply error counters (PG 15+). A rising apply_error_count
-- right after a DDL change is the schema-drift signature.
-- ALERT: apply_error_count increases while a migration is in flight.
SELECT subname, apply_error_count, sync_error_count, stats_reset
FROM pg_stat_subscription_stats;

-- SUBSCRIBER: per-relation sync state. 'r' = ready (streaming), 'd' = data copy,
-- 'i' = init. A table stuck in 'd'/'i' after REFRESH never finished its copy.
-- ALERT: srsubstate <> 'r' for longer than the expected copy window.
SELECT sr.srrelid::regclass AS relation, sr.srsubstate, sr.srsublsn
FROM pg_subscription_rel sr
JOIN pg_subscription s ON s.oid = sr.srsubid
WHERE s.subname = 'sub_orders';

-- PUBLISHER: confirm the published column set matches what you intend to emit
-- BEFORE running the subscriber-side DDL. Divergence here is the root of most
-- "missing replicated column" halts.
SELECT pubname, schemaname, tablename, attnames
FROM pg_publication_tables
WHERE pubname = 'pub_orders' AND tablename = 'orders';

-- PUBLISHER: a captured table stalling apply pins its slot. Watch retained WAL
-- climb if a bad migration halted the subscriber.
-- ALERT: retained_wal rising with active=false → schema halt is now a WAL leak.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots WHERE slot_type = 'logical';

When the subscription has halted, the exact failing statement and relation are in the subscriber’s server log (LOG: logical replication apply worker for subscription "sub_orders" has started followed by the ERROR). PG 15+ also lets you skip a single poison transaction with ALTER SUBSCRIPTION ... SKIP (lsn = '0/...') once you have reconciled the schema — a scalpel, not a fix, since it discards that change.

Failure Modes & Gotchas #

Publisher-first column add halts the subscription. Signature: apply_error_count jumps, the worker logs is missing replicated column, and every table on that subscription stops advancing. Root cause: the publisher emitted a wider relation message than the subscriber schema supports. Remediation: add the column on the subscriber, and apply resumes from confirmed_flush_lsn automatically — no data is lost because the WAL is still retained. Prevention is the ordering rule: widen the target first.

In-place type change floods the slot. Signature: an ALTER COLUMN ... TYPE on a large captured table takes an ACCESS EXCLUSIVE lock, pg_wal swells, and the decoder falls minutes behind. Root cause: the rewrite touches every tuple and every touched tuple is WAL that must be decoded and shipped. Remediation: cancel it and switch to expand/contract with a new column; never rewrite a captured table in place under an active slot.

New table added but never refreshed. Signature: rows written to a table you just added to the publication never appear on the subscriber, with no error anywhere. Root cause: ALTER PUBLICATION ... ADD TABLE changes only the publisher; the subscription does not know the table exists until REFRESH PUBLICATION. Remediation: run the refresh with copy_data = true to backfill, or false if you only want changes from now on. This silent-drop is more dangerous than a loud halt because monitoring on apply_error_count will not catch it — verify with pg_subscription_rel.

Rename diverges the name-based mapping. Signature: after renaming a column on one side, that column applies as NULL on every row or the worker errors, depending on nullability. Root cause: apply maps by name, so a name present on only one side no longer lines up. Remediation: rename on both sides as close to atomically as your change window allows, then republish the column list; treat a rename as a paired drop-and-add if you cannot coordinate the two statements tightly.

Downstream deserializers break on schema drift. Signature: a Debezium connector keeps streaming but its consumers throw on an unknown field or a type mismatch after your DDL. Root cause: Debezium updates its own view of the table from the pgoutput relation message automatically, so the connector does not halt — but the schema-registry contract and any Avro-typed consumer downstream may reject the new shape. Remediation: register the evolved schema under BACKWARD_TRANSITIVE compatibility in CI before the DDL ships, and additive-first ordering that PostgreSQL requires happens to be exactly what backward-compatible schema evolution also wants. When you must propagate the DDL itself rather than just tolerate it, replicating DDL changes with event triggers covers capturing and re-applying it on the subscriber.

Integration Touchpoints #

Schema change management sits between the objects defined during setup and the topology concerns of the wider failover operations section. The publication and subscription you alter here are the same ones created under publication and subscription models; the REFRESH PUBLICATION step is the schema-change-time counterpart to the initial load documented in subscription sync procedures. A migration that stalls apply becomes a slot-recovery problem, which is why the finite max_slot_wal_keep_size and the resume-from-confirmed_flush_lsn behavior tie directly into failover and slot recovery.

Two children go deeper than this overview. Rolling schema migrations with active CDC slots is the concrete zero-downtime expand/contract runbook while a slot is live and a connector is streaming, including the revert path. Replicating DDL changes with event triggers addresses the harder problem of automatically propagating DDL that core PostgreSQL will not replicate. For the alerting that must be wired before any of this runs, see monitoring and alerting, and for the underlying decoding of the relation messages that carry schema, the architecture fundamentals reference.

Frequently Asked Questions #

Related guides #

• Rolling Schema Migrations with Active CDC Slots — the concrete zero-downtime expand/contract sequence with a live slot and a streaming connector, including the revert.
• Replicating DDL Changes with Event Triggers — capturing DDL with event triggers and propagating it to subscribers that core PostgreSQL leaves unsynchronized.
• Failover & Slot Recovery — recovering the slot and subscription when a mis-ordered migration halts apply and pins WAL.
• Subscription Sync Procedures — the initial-copy mechanics that REFRESH PUBLICATION reuses when a schema change adds a table.
• Replication Topologies & Failover Operations — the parent guide this schema-change discipline sits within.