The safest practical model
The safest way to capture Postgres changes without silently losing events is to retain an acknowledged source position, deliver at least once, make every downstream operation idempotent and continuously reconcile the destination with PostgreSQL. A production design should prefer a detectable duplicate over an undetectable gap.
No connector setting can replace an end-to-end recovery model. PostgreSQL owns WAL retention and logical slots. The connector owns reading, conversion and offsets. A broker may own durable replay. The destination owns idempotency and state modeling. Operators need one runbook that crosses all four boundaries.
The reliability target should state a recovery point objective, maximum acceptable freshness lag and maximum time a consumer can be unavailable before source storage becomes unsafe. Without those numbers, alerts are arbitrary.
Design for at-least-once delivery
PostgreSQL's logical-decoding documentation notes that a slot's current position is persisted at checkpoints. After a crash, the slot can return to an earlier LSN and emit recent changes again. Brokers and destinations can also retry when acknowledgements are ambiguous. The correct default assumption is therefore that duplicates will happen.
Give each change a stable identity. Depending on the connector, this may combine source server, transaction and LSN information with table and row key. Preserve source metadata until after deduplication. Do not generate a new event ID on every retry, because that makes the same source change appear unique.
Commit destination data and its consumed offset atomically where the technology supports it, or make reprocessing safe where it does not. A process that writes a row and crashes before acknowledging will receive the event again. Idempotency converts that uncertainty from corruption into harmless repeat work.
Protect the production database from a stalled slot
A logical slot prevents PostgreSQL from removing WAL the consumer may still need. That is a recovery feature with an operational limit: a stopped connector can fill pg_wal and take the primary database down. Monitor the difference between current WAL and each slot's restart/confirmed position, plus free disk space and slot activity.
PostgreSQL provides max_slot_wal_keep_size to limit WAL retained by slots. A limit protects storage, but crossing it can invalidate a slot and force resynchronization. Set the threshold from write rate and response time, alert well before it is reached, and maintain a tested snapshot path.
Heartbeats matter in quiet or uneven workloads. They demonstrate liveness and can advance acknowledged positions when the captured database produces few eligible changes. Alert separately on no source activity, no heartbeat and growing byte lag.
Handle Postgres failover as a data event
Failover is not complete merely because applications reconnect. The promoted primary must expose a logical slot whose position is safe for the CDC client. PostgreSQL 17 and later support failover-enabled logical slots that can be synchronized to a standby. PostgreSQL's current failover documentation says synchronization is asynchronous and operators should confirm the standby is ready before a planned promotion.
For older versions or providers without compatible slot synchronization, document the manual recovery path. It may require establishing a new slot from a known consistent point and reconciling or resnapshotting affected tables. Never guess an LSN after an incident.
Test both planned and unplanned failover. During the test, continue writing identifiable rows, promote the standby, reconnect the connector, and compare every expected key and version. Measure duplicates and the time until the analytical destination becomes current. Keep the evidence with the runbook.
Retries, poison events and backpressure
Retries should be bounded and observable. Transient network errors justify exponential backoff with jitter. Authentication errors, incompatible schemas and oversized records normally need operator action rather than an endless tight loop. Route irrecoverable records to a quarantine or dead-letter path that preserves payload, source position and error reason.
Backpressure must travel upstream as metrics, not necessarily as blocked database writes. A broker absorbs temporary destination outages, but consumer lag and retention still have limits. Define warning and critical thresholds from time-to-catch-up under proven throughput.
Large transactions deserve special tests. Logical changes become visible around commit, so one long transaction can produce a sudden burst and delay apparent progress. Load-test the largest realistic transaction and confirm connector memory, broker message limits and destination batch sizes.
Deduplicate without hiding valid updates
Deduplication by row primary key alone is wrong for a change-history table because it erases legitimate versions. For current state, keep the row with the highest trustworthy source version and apply delete state explicitly. For history, deduplicate the same source event ID while retaining distinct changes to the same row.
Tinybird offers query-time, engine-based, snapshot and lambda-style deduplication strategies. Its ReplacingMergeTree documentation is explicit that background merges are eventual and do not guarantee the absence of duplicates at every query instant. Queries that require correct current state need a compatible query-time or architectural strategy.
Test out-of-order arrival. The destination should not let a delayed older version overwrite a newer state merely because it arrived later. Version from the source position or another monotonic field, not from destination ingestion time.
Treat schema evolution as a deployment
Classify changes as compatible, conditionally compatible or breaking. Adding a nullable column is usually easier than renaming a field, changing its type or changing the primary key. Test connector serialization, broker schema compatibility, destination parsing and serving queries together.
Use expand-and-contract migrations for important fields: add the new representation, publish both during a transition, update consumers, backfill if required and remove the old field only after usage is gone. Pause or quarantine incompatible events rather than silently coercing them.
Tables added to a publication need a snapshot plan. DDL capture varies by tool; do not assume a row-change connector will reproduce every index, constraint or database-side function at the destination.
Reconciliation is the proof of completeness
Compare PostgreSQL and destination current state on a schedule. Use row counts by stable partitions, min/max keys, sums of important numeric fields and hashes over normalized values. Sample individual records, including recently updated and deleted keys. Reconciliation should generate an actionable mismatch report, not only a green dashboard.
Keep a replay window and raw layer long enough to repair common incidents. A modeled table can be rebuilt from raw CDC events only if the raw stream includes the necessary starting state and deletes. Otherwise the runbook must combine a new snapshot with changes after a coordinated position.
Observe freshness end to end: source commit time to connector, broker, modeled table and served API. A low broker lag does not prove that the API is current if materialization failed.
Minimum production runbook
- Identify the failing stage and preserve its offsets and logs.
- Check source disk, retained WAL and slot validity before restarting repeatedly.
- Stop destructive automated cleanup while the recovery point is assessed.
- Restore the connector from its durable offset or execute the resnapshot plan.
- Let consumers catch up under controlled capacity and monitor lag.
- Run reconciliation and investigate every unexplained gap.
- Record the incident, cause, affected LSN/time range and prevention change.
FAQ
How should a CDC pipeline handle duplicates?
Preserve a stable source event identity and make writes idempotent. Use a source version for current-state models and distinguish replayed events from legitimate later updates.
What happens to CDC during PostgreSQL failover?
The client must reconnect to the promoted primary and resume from a safe logical slot. Newer PostgreSQL versions support synchronized failover slots, but readiness must be verified and provider support checked.
Is a replication slot a backup?
No. It retains WAL for a consumer position. It does not replace database backups, broker retention, raw event storage or reconciliation.
Sources checked September 5, 2026: PostgreSQL on logical replication failover and WAL retention; Debezium PostgreSQL connector operations; and Tinybird's deduplication and ReplacingMergeTree reference.