Checkpointing and Crash Recovery in CDC
Checkpointing and Crash Recovery in CDC
Your CDC pipeline has been running for three days. It has delivered 40 million events from PostgreSQL to Kafka. Then it crashes.
When it restarts, where does it resume? If it has no record of its progress, it has two options: replay all 40 million events from the beginning of the WAL (slow, produces massive duplication), or start from the current WAL position (fast, but events between the crash and the current position are lost).
Neither is acceptable. Checkpointing is how a pipeline avoids both.
What Is a Checkpoint?
A checkpoint is a saved position marking "I have processed up to here." It is the pipeline's equivalent of a bookmark. On restart, the pipeline loads its last checkpoint and asks the source database to resume streaming from that position.
In PostgreSQL terms, the checkpoint stores an LSN (Log Sequence Number): the byte offset in the WAL that the pipeline has processed through. On restart, the pipeline tells PostgreSQL "send me everything after this LSN." PostgreSQL obliges.
Without a checkpoint, the pipeline has no memory of what it has already processed. With a checkpoint, recovery is a matter of resuming from the last saved position.
The Duplicate Window
A checkpoint is not saved after every event. Saving after every single event would add a disk write (or network round trip) per event, which at high throughput becomes the bottleneck.
Instead, checkpoints are saved periodically: every N seconds, or every N events, or after each batch is delivered. Between checkpoint saves, the pipeline processes events but has not yet recorded its position.
If the pipeline crashes between checkpoints, the last saved position is behind the actual progress. On restart, events between the checkpoint and the crash point are replayed.
These replayed events are duplicates: the sink already received them before the crash, and now receives them again. The number of duplicates is bounded by the checkpoint interval. With a 10-second checkpoint interval, at most 10 seconds of events are duplicated on recovery.
This is why CDC pipelines use at-least-once delivery: checkpointing makes data loss impossible (the source retains unacknowledged WAL), and the duplicate window is small enough that sinks can handle it via deduplication or idempotent writes.
Checkpointing Strategies
Four approaches exist for when to save a checkpoint. Each trades off overhead against duplicate window size.
Periodic
Save a checkpoint every N seconds, regardless of how many events have been processed. Between saves, the pipeline tracks its latest position in memory.
This is the most common approach. It is simple, predictable, and decouples checkpoint frequency from event throughput. A 10-second interval means at most 10 seconds of duplicates on recovery, whether the pipeline processes 100 events or 100,000 events per second.
The overhead is one write to the checkpoint store every N seconds. At 10 seconds, that is 6 writes per minute.
Per-Batch
Save a checkpoint after each batch is successfully delivered to sinks. The batch delivery confirms the events reached the destination, and the checkpoint records that fact.
This ties the duplicate window to the batch size rather than wall-clock time. If batches deliver quickly, checkpoints are frequent. If batches are large and slow, the duplicate window grows.
The overhead is one checkpoint write per batch delivery. At 100 events per batch and 10,000 events per second, that is 100 checkpoint writes per second. This is acceptable with fast local storage but can strain a network-based checkpoint store.
Per-Event
Save a checkpoint after every event. The duplicate window is zero: if the pipeline crashes, it resumes from exactly the next unprocessed event.
The overhead is one checkpoint write per event. At 10,000 events per second, this is 10,000 writes per second to the checkpoint store. For most storage backends, this is not practical. It is viable only with extremely fast local storage and low throughput.
Transactional
Store the checkpoint inside the same transaction that writes events to the sink. The sink write and the checkpoint advance atomically: both succeed or both roll back.
This achieves exactly-once delivery without any duplicate window. If the pipeline crashes before the transaction commits, neither the events nor the checkpoint are persisted. On restart, the pipeline replays and delivers them for the first time.
The constraint: this only works when the checkpoint can live in the same transactional system as the sink. If the sink is a PostgreSQL database, the checkpoint can be a row in that same database. If the sink is Kafka or S3, no such transaction spans both the sink and the checkpoint store.
| Strategy | Duplicate window | Overhead | When viable |
|---|---|---|---|
| Periodic | Checkpoint interval | Low (fixed rate) | Most production workloads |
| Per-batch | Batch delivery time | Medium | Fast local checkpoint store |
| Per-event | Zero | High | Low throughput only |
| Transactional | Zero | Medium | Sink supports transactions and stores checkpoint |
What Goes in a Checkpoint
A checkpoint is more than a position. A production checkpoint contains the metadata needed to resume correctly, not just the metadata needed to resume at all.
Position. The WAL LSN (or equivalent) marking the last processed event. This is the primary data: where to resume from.
Timestamp. When the checkpoint was saved. Used for monitoring and diagnostics: "the pipeline was last checkpointed 45 seconds ago" tells an operator whether things are healthy.
Transaction ID. The ID of the last completed transaction. On restart, this helps the pipeline verify it is resuming at the correct point, especially when multiple transactions share similar LSN ranges.
Deduplication state. A snapshot of recently-seen event identifiers. On restart, the pipeline loads this snapshot and uses it to detect some duplicates in the replay window. Events that were processed (added to the dedup map) before the checkpoint was saved will be in the snapshot even if they postdate the checkpoint position. Events processed after the checkpoint save are not in the snapshot and will not be caught by pipeline-level dedup.
Processing statistics. Optional metadata (processed event count, error count, active transactions) that helps operators understand the pipeline's state at the time of the checkpoint.
The deduplication snapshot provides partial protection against replay-window duplicates. Because events are added to the dedup map when processed (before sink delivery), the snapshot can contain entries ahead of the checkpoint position. But it cannot contain events processed after the snapshot was taken. For complete duplicate protection, sinks must handle duplicates independently via idempotent writes or position-based deduplication. The pipeline-level dedup reduces duplicate volume; it does not eliminate it.
Where to Store Checkpoints
The checkpoint store must be durable and available. If the pipeline crashes and the checkpoint store has also lost data, recovery is not possible.
File System
Write checkpoints as JSON files to a local directory. Each save writes to a temporary file and atomically renames it to prevent corruption if the process is killed mid-write.
Suitable for single-node deployments where the pipeline and its checkpoint live on the same machine. Fast (no network round trip), simple (no dependencies), but not suitable for distributed deployments where the pipeline might restart on a different node.
Distributed Key-Value Store
Write checkpoints to etcd, Consul, or a similar distributed store. The checkpoint is accessible from any node, which supports failover: if the pipeline restarts on a different machine, it can still load its last checkpoint.
The trade-off is latency and availability. A network round trip per checkpoint save is slower than local disk. If the key-value store is unavailable, checkpoints cannot be saved, and the pipeline must decide what to do (pause, continue without checkpointing, or shut down).
Same Database as Sink
Store the checkpoint in the same database the pipeline writes to. When the sink is PostgreSQL, the checkpoint can be a row in a dedicated _checkpoints table, updated in the same transaction that writes events. This enables the transactional checkpointing strategy described above.
The constraint: this couples the pipeline to one specific sink. If you add a second sink later, the checkpoint is still stored in the first sink's database, which may not reflect delivery to the second sink.
Crash Recovery: The Resume Flow
When a pipeline starts, it follows this sequence:
- Load the last checkpoint. Read the stored position, timestamp, and metadata from the checkpoint store.
- Restore deduplication state. Load the saved dedup map so the pipeline can detect some duplicates from the replay window.
- Connect to the source. Open a replication connection to PostgreSQL and request streaming from the checkpoint position.
- Process the replay window. Events between the checkpoint and the crash point arrive again. Events that were in the dedup snapshot (processed before the checkpoint save) are recognized as duplicates and skipped. Events processed after the snapshot was taken are not in the map and may be delivered to sinks again. Sinks absorb these via idempotent writes.
- Resume normal processing. Once past the replay window, all events are new. The pipeline delivers them normally and saves checkpoints on its regular schedule.
If no checkpoint exists (first run), the pipeline starts from the current WAL position (or from a configured starting point) and begins processing. No replay occurs.
When Checkpoint Storage Fails
A checkpoint store can become unavailable. The network partition between the pipeline and etcd might last minutes. The local disk might be full.
If the pipeline continues processing without saving checkpoints, the potential replay window grows without bound. A crash after 10 minutes without a successful checkpoint save means 10 minutes of events might be re-delivered on restart.
A production pipeline should detect this condition and respond. The options:
Pause consumption. Stop reading from the source. The replication connection stays open (PostgreSQL sends keepalives), the replication slot retains WAL, but the pipeline does not process new events. When the checkpoint store recovers, the pipeline saves a checkpoint and resumes. This caps the replay window at whatever it was when the store became unavailable.
Continue with monitoring. Keep processing but alert operators that checkpoints are failing. The pipeline accumulates risk: the longer it runs without a checkpoint, the larger the duplicate window on crash. Acceptable if crashes are rare and the checkpoint store recovers quickly.
Self-terminate. After a configurable duration without a successful checkpoint, shut down cleanly. Let the process supervisor restart the pipeline, at which point the checkpoint store may be available again. This is a last resort that prevents unbounded replay windows at the cost of availability.
Where to Go from Here
This article covers how checkpointing works and how CDC pipelines recover from crashes. The related articles go deeper into connected topics:
- CDC Delivery Guarantees: why at-least-once is the industry standard and how checkpointing enables it
- Deduplication Strategies: how sinks handle the duplicate events produced by checkpoint-based recovery
- Backpressure in Data Pipelines: what happens when checkpoint storage is unavailable and the pipeline pauses
- Transaction Boundaries: how checkpoints interact with in-flight transactions
For the broader picture of how checkpointing fits into transaction log processing, see What is Transaction Log Processing.
We built Remac with periodic checkpointing at a configurable interval (default: 10 seconds). Checkpoints are stored as atomic JSON writes to either a local file system or etcd, and include the WAL position, last transaction ID, and a bounded snapshot of the deduplication map for cross-restart duplicate detection. Three acknowledgment modes control when the source position is confirmed back to PostgreSQL: immediately on receive, after sink delivery, or after checkpoint save (each trading latency for safety). If the checkpoint store is unavailable beyond a configurable threshold, the pipeline pauses consumption automatically to cap the replay window, and self-terminates if the stall persists. Remac currently supports PostgreSQL, with MySQL, MongoDB, SQL Server, and Oracle on the roadmap.
Take a look at remac.io.
Further Reading:
- PostgreSQL: pg_replication_slots. Official documentation on replication slots, which retain WAL for pipeline recovery.
- Kafka: Message Delivery Semantics. How Kafka uses consumer offsets as checkpoints, and the relationship between offset commits and delivery guarantees.
- Flink: Checkpointing. Apache Flink's approach to distributed checkpointing with barriers and aligned/unaligned modes.
- Designing Data-Intensive Applications, Chapter 11. Martin Kleppmann's analysis of exactly-once semantics, idempotency, and checkpoint-based recovery in stream processing.