Back to Devoured - September 14, 2026
DEVOURED
PostgreSQL CDC Backfills at Scale: Running a Multi-Day Backfill While Production Keeps Writing

PostgreSQL CDC Backfills at Scale: Running a Multi-Day Backfill While Production Keeps Writing

Data databasedevopspostgresql Estuary
Large-scale PostgreSQL backfills often crash when WAL retention grows faster than the replication slot can acknowledge, leading to slot invalidation.
What: Emily Lucek and Dani Pálma explain how to safely execute CDC backfills by managing chunk sizes and `max_slot_wal_keep_size`. The authors emphasize that backfill queries must be chunked and acknowledged to keep PostgreSQL from recycling logs required for ongoing streaming replication.
Why it matters: Reliable data migration requires balancing the speed of historical data ingestion with the real-time pressure of write-ahead logging to avoid catastrophic replication slot failures.
Takeaway: Before running a multi-day CDC backfill, sample your WAL generation rate with `pg_wal_lsn_diff()` and size `max_slot_wal_keep_size` to accommodate the volume generated during your estimated chunk duration.
Deep dive
  • The Risk: Long-running backfills prevent replication slots from advancing, leading to WAL accumulation and disk pressure.
  • Chunk Sizing: Smaller chunks allow more frequent acknowledgments, reducing the risk of WAL log recycling.
  • Reconciliation: Systems must use watermark-and-fence mechanisms to ensure historical row snapshots do not overwrite newer streaming updates.
  • Heartbeats: Idle databases need regular activity to allow replication slots to advance, requiring a heartbeat table in read-only capture modes.
  • Recovery: If a slot is lost, manual intervention is often required; partial backfills can sometimes be recovered using xmin filters.
Decoder
  • CDC (Change Data Capture): A pattern where database changes (inserts, updates, deletes) are tracked and streamed to other systems.
  • WAL (Write-Ahead Log): A low-level record of all changes made to a database, used for replication and crash recovery.
  • Replication Slot: A mechanism that holds WAL logs for a consumer, ensuring the consumer can catch up without missing data.
  • XID (Transaction ID): A unique identifier assigned to every database transaction in PostgreSQL.
Original article

Full article content is not available for inline reading.

Read the original article →

Explore all 74 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi