1
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.
FULL AI ANALYSIS
PostgreSQL logical replication falls behind after ALTER TABLE ADD COLUMN with non-null DEFAULT on large table
Generated Oct 13, 2026, 9:45:52 AM
Analysis 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.
Possible 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%Long-running single transaction on primary that overlaps with ALTER causing additional complexity in logical stream ordering
10%Suggested fixes
2
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).
3
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.
4
Diagnostic step 4
For future schema changes: prefer using 'ALTER TABLE ... ADD COLUMN ... DEFAULT NULL' followed by a background UPDATE batched in small transactions, or use CREATE TABLE + INSERT in batches and swap via partition or rename, to avoid generating massive logical replication change sets.
5
Diagnostic step 5
If logical replication is the only option, test ALTER patterns on a staging copy of the DB with logical replication enabled to measure the generated event volume and tune apply parameters before applying to production; consider using pglogical extension behavior notes if used.