Skip to content 
Replicating data from a critical operational PostgreSQL database into Kafka at scale is not a trivial challenge. When datasets reach billions of rows, performing a naive full snapshot can overwhelm and degrade the performance of production databases.
At Infinite Lambda, we faced this challenge when replicating close to 300 tables from a Postgres database into individual Confluent Kafka topics, with some tables exceeding 1.5 billion rows.
An initial full sync for the largest table would have taken 8 to 10 hours, imposing an unacceptable load on our production systems.
Additionally, relying solely on logical decoding via Write-Ahead Log (WAL) replication slots would come with operational risks: if a connector failed and replication slots were not properly cleaned up, dead tuples would accumulate, threatening to destabilise the database itself.
To address these challenges of scaling Postgres to Kafka, we adopted incremental snapshotting using the Confluent Kafka Postgres connector.
In this article, we will compare the two solutions, explain our approach, and explore the trade-offs.
The Challenge: Full Snapshots and WAL Pitfalls
When you replicate data from a relational database into Kafka, you usually do this in two phases:
- Snapshotting: capturing the existing state of the tables
- Change Data Capture (CDC): streaming ongoing changes via WAL logs
Both phases are essential, but each comes with scaling issues when dealing with billions of rows and hundreds of tables.
Full Snapshot Load using replication slots
A full snapshot sounds simple enough: scan the table, dump the rows, publish them into Kafka. In practice, this is brutal for large tables because:
- The snapshot would take 8–10 hours for just one of our tables with 1.5 billion records.
- During this window, Postgres’s CPU, I/O, and buffer cache were under heavy strain.
- Queries from our application experienced increased latency and, in the worst cases, lock contention.
This kind of load may be tolerable in a lower environment, but it is unacceptable in production, where even a few minutes of performance degradation can cascade into user-facing downtime.
Now multiply that by 284 tables, each with its own size and access pattern. Clearly, “just snapshot everything at once” is not a viable approach at this scale.
Once snapshotting is complete, we can switch to WAL-based streaming replication. This works well for capturing ongoing changes in near-real time, because the connector streams row-level changes, including inserts, updates, and deletes, while they are being committed to the PostgreSQL database.
Understanding the challenge a bit better
First, let’s see what dead tuples are.
Codecademy explains it well: “in PostgreSQL, when a row is deleted or updated, PostgreSQL creates so-called Dead tuples. Dead tuples are no longer visible to any active transaction but still occupy space on disk.”
And what is a replication slot? According to Dev.to’s definition, “a replication slot is a feature that ensures WAL (Write-Ahead Log) files are retained until a replica (standby server) or a logical consumer has processed them. It prevents WAL files from being removed before they are received by the subscriber, ensuring smooth replication.”
To reclaim space from dead tuples, you can use VACUUM operation. PostgreSQL has a feature called auto vacuum, which automatically runs VACUUM and ANALYZE commands. When enabled, auto vacuum checks for tables that have had a large number of inserted, updated or deleted tuples.
Replication slots track values such as catalog_xmin, which represent the oldest transaction ID that must be retained to ensure logical decoding consistency.
When a logical replication slot lags behind or becomes inactive, it can hold back PostgreSQL’s global transaction ID horizons. As a result, VACUUM cannot safely remove dead tuples that may still be visible to the replication slot. Over time, these dead tuples accumulate, leading to table bloat, until the replication slot catches up or is removed.
The Confluent Postgres CDC Connector connector creates a logical replication slot when it begins the snapshot process. While the snapshot runs, the slot ensures that new changes are retained, and once the snapshot is complete, the connector uses the slot to stream ongoing changes.
However, replication slots introduce their own set of risks:
- If the Kafka connector stops or crashes, the replication slot is not automatically released.
- While the slot is active, Postgres is forced to retain old WAL segments until the connector catches up.
- If the slot lags too much, the database accumulates dead tuples that block vacuuming and can bloat tables.
- In the worst of cases, this can bring the entire database cluster into a degraded state, forcing manual emergency interventions.
This is not hypothetical. We have seen scenarios where a misconfigured connector left a slot dangling and database performance degraded within hours. With production data, this is not a risk we can afford to take lightly.
Enter incremental snapshots
Incremental snapshots provided exactly the middle ground we were looking for. Instead of treating the entire table as a single unit of work, the connector breaks the snapshot into discrete, manageable chunks, defined by primary key ranges. Each chunk is queried and published to Kafka independently, significantly reducing the pressure on the source database.
This chunk-based strategy has several advantages:
- Reduced load spikes: instead of one massive scan, the database processes thousands of smaller range queries, allowing normal workloads to run more smoothly alongside them.
- Fault-tolerant: if an incremental snapshot is paused, it can be resumed without any data loss. The process will pick up exactly where it left off, rather than starting over from the beginning.
- Streaming in parallel: WAL streaming can continue while snapshots are running, ensuring no data is lost between the start and completion of the snapshot.
For example, our largest table (1.5 billion rows) was divided into ~1.46 million chunks, each containing 1024 records, which is the default chunk size. The connector steadily processed these batches while simultaneously consuming WAL events.
Incremental snapshots also reduced the operational risk associated with long-lived replication slots. Even if a connector failed, we were no longer holding the system hostage with an unbounded accumulation of WAL logs; snapshot chunks could be retried independently without rolling back the entire process.
Implementation
First, create a Debezium signaling table on the source database. Later on, you will will be sending a signal request to this table to initiate an incremental snapshot.
Connector configuration
Here is how to configure your Postgres CDC connector to avoid immediate full snapshots and enable safe incremental snapshotting:
Important bits:
publication.autocreate.mode → The connector automatically adds the signal table to the publication only if the publication.autocreate.mode is set to filtered or all_tables.
signal.data.collection → Fully-qualified name of the data collection that you need to use on order to send signals to the connector.
snapshot.mode → Skip initial snapshot, stream from WAL slot only with setting snapshot.mode to no_data or never. Be sure to not use ‘initial’ to avoid a full sync using the replication slot.
table.include.list → A comma-separated list of regular expressions that match fully-qualified table identifiers. Include both the signal table and all tables to be synced.
incremental.snapshot.chunk.size → Default chunk size is 1024 and can be tuned if required.
Signal table commands
You specify the tables to capture with incremental snapshotting by sending an execute-snapshot record to the previously created signaling table.
Set the type of the execute-snapshot signal to ‘incremental’ while providing the tables in a list in the data-collections. You also need to provide an id, which has to be unique for signal table entries.
You can also use an optional ‘additional-conditions’ array to specify a set of additional conditions that the connector evaluates to determine the subset of records to include in a snapshot. You can only add filters that you could pass to a WHERE statement (e.g. no windowing).
To pause/resume/stop a snapshotting process, send a record with the corresponding signal type to the signal table. The table you want this process to affect would need to be included in the ‘data-collections’ part.
Limitations and caveats of incremental snapshotting
While Debezium’s incremental snapshotting provides a practical way to backfill data with minimal impact on production systems, it comes with several important limitations that we should understand before adoption.
Primary key is mandatory
Tables without a primary key cannot be snapshotted incrementally. Debezium relies on the primary key to chunk the table and to correlate snapshot reads with change events.
Event ordering must be handled by consumers
Consumers may observe a READ event followed by an UPDATE, or only an UPDATE event for the same row, depending on timing and WAL interleaving.
Strong dependence on correct signaling
Incremental snapshots are controlled via Debezium signaling. Misconfigured, missing, or incorrectly scoped signals can result in stalled, partially completed, or entirely missing snapshots.
Generated columns are not supported
Tables containing generated (computed) columns cannot be snapshotted incrementally. Workarounds include excluding columns via column.exclude.list=public.table.generated_col, redesigning schemas to materialise computed values as regular columns, or excluding tables from the incremental snapshot workflow.
Incremental snapshotting in a nutshell
Incremental snapshotting gave us the missing piece to scale Postgres-to-Kafka replication without breaking production. By combining controlled snapshots with WAL streaming, we could strike a balance between efficiency, safety, and real-time replication.
If your team is struggling with seeding Kafka topics from large Postgres datasets, incremental snapshots are worth considering.
The Infinite Lambda team shares practical insights gleaned from real-world implementations. Visit our tech blog to see how we apply cutting-edge modern data and AI technology to solve business problems.
