PostgreSQL logical replication falls behind after ALTER TABLE ADD COLUMN with non-null DEFAULT on large table

Environment

Primary: PostgreSQL 13 on bare-metal (12 cores, 64GB), Logical replication slot streaming to replica on PostgreSQL 13; large table (~2 TB, 500M rows) in production; ALTER TABLE ADD COLUMN ... DEFAULT X NOT NULL executed during low-traffic window.

Symptoms

After running ALTER TABLE ADD COLUMN ... DEFAULT ... NOT NULL, the logical replica's apply lag increased dramatically and eventually stopped applying WAL; on the primary the replication slot retention caused pg_wal to grow; replica showed idle apply worker and no progress for hours; queries that rely on replica read-only reports stale results.

Observations

Primary executed ALTER which, on PostgreSQL 11+, can avoid table rewrite when adding a column with constant default but will write a fast-path column fill on first read; in our case, the ALTER completed quickly on primary, but the logical replication stream shows a huge burst of UPDATE-like change events for rows (visible in pg_logical or decoding plugin logs). Replica's apply process used excessive CPU and paused with errors about out-of-memory for apply worker's work_mem (temporary), then stalled. There are many large transactions in logical decoding stream corresponding to the ALTER column default application. Replica pg_stat_replication and pg_replication_slots show lag; no WAL corruption messages.

Already attempted

Paused application writes and retried alter in a maintenance window (same behavior); increased replica work_mem and max_apply_workers (temporary partial progress); attempted to create a new logical slot and rebase replica with pg_dump/pg_restore (too slow for 2 TB); monitored logical decoding output and confirmed huge change set generated by ALTER. Collected WAL segments and decoding logs; preserved system state for further analysis.

Question

Why did an ALTER TABLE ADD COLUMN with a constant default cause logical replication to emit massive change events and stall the replica, and what safe, reversible strategies can I use to bring replica back in sync without long downtime or full logical reinitialization?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 2d ago• Trouble ID

Most likely causes

Logical decoding plugin or configuration expanded the metadata-only ALTER into per-row logical changes that flooded the subscriber 95%

Replica apply workers or memory settings insufficient to process the large batch of logical change events, leading to failures or pauses 90%

Bug or limitation in logical replication implementation or third-party logical decoding plugin that doesn't compress ALTER metadata into a compact representation 75%

Summary

Although modern PostgreSQL can avoid physical table rewrite when adding a column with a constant default by recording the default in metadata, logical replication operates at logical change level and must convey a consistent state to subscribers. An ADD COLUMN with default may generate a logical change that appears as many updates to existing rows for logical decoding plugins, producing a flood of changes the replica must apply. The apply worker may be overwhelmed (memory, CPU, transaction size), causing stall. Physical replication (streaming base backup + WAL) does not have this problem; logical replication is sensitive to large metadata-only operations that are expanded into per-row events by the logical decoding plugin or subscriber settings.

Suggested Fixes

Diagnostic step 1

Do not drop the replication slot or attempt full resync immediately; preserve WAL to avoid data loss and to allow replay if needed.

Diagnostic step 2

Increase replica resources temporarily: raise max_worker_processes/max_apply_workers and work_mem on replica and monitor pg_stat_subscription and apply throughput; this can allow the backlog to be consumed without reinitializing (reversible).

Diagnostic step 3

Throttle apply by splitting work: if possible, pause logical replication and perform a controlled resync using physical base backup + streaming (pg_basebackup) rather than logical reinitialization; physical restore will bring replica to current WAL position without replaying per-row logical events.