A Debezium PostgreSQL connector (`pgoutput`) streams 8,500 writes/sec from an `orders` database to Kafka, feeding Elasticsearch and Snowflake. After a Kafka broker disk failure and an accidental `DROP_REPLICATION_SLOT` during disk-full remediation, the connector restarts with missing offsets in `connect-offsets`. Because `snapshot.mode=when_needed` was set without `heartbeat.interval.ms` on a low-write-table slot, 4 hours of `orders` updates (1.2M rows) are silently skipped, and Elasticsearch returns stale order statuses to customers. Design a fault-tolerant Debezium CDC pipeline that prevents offset/LSN loss, detects gaps automatically, and repairs missed rows without full-table locks.
From patterns to decisions.
A Debezium PostgreSQL connector (`pgoutput`) streams 8,500 writes/sec from an `orders` database to Kafka, feeding Elasticsearch and Snowflake. After a Kafka broker disk failure and an accidental `DROP_REPLICATION_SLOT` during disk-full remediation, the connector restarts with missing offsets in `connect-offsets`. Because `snapshot.mode=when_needed` was set without `heartbeat.interval.ms` on a low-write-table slot, 4 hours of `orders` updates (1.2M rows) are silently skipped, and Elasticsearch returns stale order statuses to customers. Design a fault-tolerant Debezium CDC pipeline that prevents offset/LSN loss, detects gaps automatically, and repairs missed rows without full-table locks.
Implementation & Guardrails (1).
Losing connect-offsets (RF=1) plus dropping the Postgres slot caused when_needed to create a fresh slot at the current WAL head, bypassing the 4-hour gap.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Dropping a slot immediately frees WAL disk but permanently severs LSN continuity. |
Step 1
Trace how RF=1 on connect-offsets caused offset loss on a single broker disk failure.
Step 2
Explain how dropping the replication slot erased Postgres's memory of the old LSN.
Step 3
Show why when_needed with no offset and a new slot only streams from the new LSN forward unless an initial snapshot is triggered.
Alternative 1
Setting max_slot_wal_keep_size = 100GB so Postgres manages disk bounds predictably.
Failure Mode 1
Silent data drift in Elasticsearch and Snowflake.
-- Inspecting slot health and WAL lag before ever touching DROP_REPLICATION_SLOT
SELECT slot_name, active, confirmed_flush_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS wal_lag
FROM pg_replication_slots;
Use RF=3 compacted connect-offsets, 10s.
Use RF=3 compacted connect-offsets, 10s heartbeats, and public.debezium_signal with additional-conditions to backfill the 4-hour window in ~30 seconds.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Incremental snapshots take 30s for 1.2M rows with zero table locks, vs. 14 hours for a full snapshot. |
Step 1
Configure connect-offsets with cleanup.policy=compact, RF=3, min.insync.replicas=2.
Step 2
Enable heartbeat.interval.ms=10000 and heartbeat.action.query.
Step 3
Insert an execute-snapshot row scoped to the 4-hour updated_at range.
Alternative 1
Running a separate Kafka producer script reading from a PostgreSQL read replica.
Failure Mode 1
Missing index on updated_at causing sequential scan during chunked additional-conditions.
INSERT INTO public.debezium_signal (id, type, data) VALUES (
'repair-4h-gap', 'execute-snapshot',
'{"data-collections":["public.orders"],"type":"incremental","additional-conditions":[{"data-collection":"public.orders","filter":"updated_at >= ''2026-11-12 10:00:00+00''"}]}'
);
Implementation & Guardrails (3).
Debezium uses watermarked window deduplication in-flight, and Elasticsearch enforces version_type=external keyed by monotonic row_version as an end-to-end invariant.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Requires a monotonic version column or LSN-derived integer on every indexed document. |
Step 1
Include a monotonically increasing version column on orders.
Step 2
Configure the Elasticsearch sink connector to pass version with version_type=external.
Step 3
Elasticsearch automatically rejects any update with incoming_version <= stored_version.
Alternative 1
Using PostgreSQL xmin + epoch or Kafka Streams stateful deduplication.
Failure Mode 1
Using updated_at epoch seconds where two updates in the same second collide.
{
"version.type": "external",
"document.id.strategy": "record.key",
"behavior.on.version.conflict": "ignore"
}
The primary DB stays online.
The primary DB stays online safely; on recovery, recreate the slot and trigger a targeted incremental snapshot from the last debezium_heartbeat timestamp.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Bounds primary disk risk to 100 GB while making recovery deterministic. |
Step 1
Read the last committed timestamp from connect-offsets or sink watermark.
Step 2
Recreate the Debezium connector with a fresh slot.
Step 3
Emit an execute-snapshot signal covering [last_committed_ts - 5m, now].
Alternative 1
Archiving WAL segments to S3 and restoring via pgBackRest (much higher complexity).
Failure Mode 1
No alert on pg_replication_slots.wal_status = 'lost', delaying recovery.
# PostgreSQL postgresql.conf WAL protection
max_slot_wal_keep_size: 102400MB # 100 GB hard cap
wal_keep_size: 4096MB
Run a lightweight Python cron.
Run a lightweight Python cron every 5 minutes comparing row counts and updated_at bucket checksums between a Postgres read replica and Elasticsearch.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Detects silent gaps within 5 minutes with negligible read-replica load. |
Step 1
Query [now - 10m, now - 5m] (allowing 5m replication lag) on both stores.
Step 2
Compare COUNT(*) and BIT_XOR(id_hash).
Step 3
On mismatch, auto-insert a Debezium execute-snapshot signal for that 5-minute window.
Alternative 1
Debezium Server + Prometheus lag metrics alone (detects lag, not silent sink drops).
Failure Mode 1
Comparing [now - 1m, now] and false-alarming on normal 200ms replication lag.
if pg_count != es_count or pg_checksum != es_checksum:
emit_debezium_signal(window_start, window_end)
metrics.increment("cdc_drift_auto_repaired")
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Pair compacted, RF=3
connect-offsetstopics and Debezium heartbeat tables with PostgreSQLmax_slot_wal_keep_sizemonitoring and DBLog-style incremental snapshots to heal gaps without downtime. - 02Trade-off tip: Requires a monotonic version column or LSN-derived integer on every indexed document.
- 03Watch out for: No alert on
pg_replication_slots.wal_status = 'lost', delaying recovery.
Reliable Cdc Debezium Pipelines — Normal → Load / Fault → Recovery
Pair compacted, RF=3 connect-offsets topics and Debezium heartbeat tables with PostgreSQL maxslotwalkeepsize monitoring and DBLog-style incremental snapshots to heal gaps without downtime.
Putting Building Reliable Change Data Capture (CDC) Pipelines Together
Pair compacted, RF=3 `connect-offsets` topics and Debezium heartbeat tables with PostgreSQL `max_slot_wal_keep_size` monitoring and DBLog-style incremental snapshots to heal gaps without downtime.
Putting Building Reliable Change Data Capture (CDC) Pipelines Together
- Configure connect-offsets with cleanup.policy=compact, RF=3, min.insync.replicas=2.
- Enable heartbeat.interval.ms=10000 and heartbeat.action.query.
- Insert an execute-snapshot row scoped to the 4-hour updated_at range.
Interview Readiness Check
The primary DB stays online safely; on recovery, recreate the slot and trigger a targeted incremental snapshot from the last debezium_heartbeat timestamp.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is Change Data Capture (CDC) the right default for a production team?
Building Reliable Change Data Capture (CDC) Pipelines
Pair compacted, RF=3 connect-offsets topics and Debezium heartbeat tables with PostgreSQL max_slot_wal_keep_size monitoring and DBLog-style incremental snapshots to heal gaps without downtime.
