Initial Snapshot Strategies for Large CDC Source Tables

An initial snapshot of a large CDC source table is a coordinated handoff. Correctness depends on anchoring the read to the change log, processing resumable chunks, and reconciling writes before streaming becomes live. These guarantees must work together, or the pipeline can begin with missing or duplicated data.

Why the initial snapshot is not a bulk copy and what it actually is

The WAL carries only recent changes. Rows that already exist therefore need a separate read tied to a specific log position. The invariant is to capture the current LSN before reading the first row, hold that position throughout the snapshot, and begin streaming from that exact LSN.

PostgreSQL provides the mechanism through logical decoding, with wal_level=logical, a publication, and a replication slot created before the read. Trendyol's go-pq-cdc captures the current LSN, opens a REPEATABLE READ transaction, and calls pg_export_snapshot(). The coordinator holds that transaction open throughout the chunk phase so workers share one consistent point-in-time state.

What the replication slot holds open and why that is both necessary and dangerous

The slot preserves the log history needed to connect that state to subsequent changes. It is a persistent object, and Postgres retains WAL segments until its consumer catches up, with no default ceiling on retention. A stalled snapshot or stopped connector therefore allows WAL to accumulate even when no snapshot work is progressing. The same mechanism that protects completeness can fill the database host's disk.

That cost makes slot lifecycle an operational responsibility from the start. Engineers must monitor pg_replication_slots and drop orphaned slots before retained WAL exhausts available space. A lag alert should trigger at a meaningful threshold, such as tens of gigabytes, while there is still time to act. The response is not to discard the anchor, but to monitor the storage obligation it creates.

How chunking turns a table-sized problem into a resumable sequence of bounded reads

For large tables, a single sequential scan is impractical. Chunking replaces that read with bounded units that share the consistent snapshot transaction opened at the LSN anchor. Parallel workers can process those units, while durable progress records make individual chunks recoverable. How the table is partitioned sets how much work each read needs.

Integer-range chunking is the first choice for sequential integer primary keys because it divides the key space into numeric ranges. CTID-block chunking is the next choice for string or UUID primary keys because it reads physical blocks without an index. Offset-based chunking is the fallback, since ORDER BY pk LIMIT N OFFSET M forces Postgres to scan and skip rows, making successive chunks slower.

Trendyol's go-pq-cdc records chunk states as pending, in_progress, and completed in metadata tables with heartbeats. After a crash, a restarted instance resumes work by claiming only unclaimed or expired chunks. This recovery still needs the original snapshot transaction and anchor to stay available or to be set up again at the same position. Chunk metadata does not replace snapshot consistency.

Why chunking on the wrong key is a silent data-loss bug

Partitioning is a correctness constraint. A non-unique column can leave rows with the same value straddling chunk boundaries, producing overlapping or gapped reads. The queries can succeed while the baseline contains duplicated or missing rows.

Value-based chunking should therefore use the table's true primary key, a column that is stable, unique, and indexed. The shared consistent snapshot prevents concurrent writes from changing values as workers see them, but it does not make an ambiguous boundary unique. When no suitable key exists, CTID-block partitioning avoids that ambiguity by partitioning on physical block address.

Reconciling mid-snapshot changes before streaming starts

Correct chunk boundaries give a consistent baseline, but an active table will change while a snapshot runs for hours. The replication slot preserves those changes from the anchor LSN while workers read the baseline. Snapshot completion therefore marks the end of the initial read. The changes that have built up must still be applied before streaming is treated as live.

The connector begins consuming from the anchor LSN, replaying changes for rows that may already exist at the destination. Those events must be applied as primary-key upserts rather than inserts, so the snapshot row and subsequent updates converge to the correct state. Most CDC pipelines structurally provide at-least-once delivery, making sink-side deduplication a correctness requirement. Trendyol's go-pq-cdc documents this handoff explicitly, starting CDC from the snapshot LSN and applying changes from the snapshot window on top.

Four scenarios where the standard chunked approach breaks and what to do instead

Chunked parallel snapshots are the default, but their assumptions do not hold everywhere. The right alternative depends on whether the constraint is uninterrupted streaming, log retention, baseline scope, or source read capacity. Each constraint changes a different part of the design without removing the need for a consistent handoff.

Scenario 1 is uninterrupted streaming. Use an incremental snapshot when a table must be bootstrapped or re-snapshotted while the connector remains running. Configuring signal.data.collection and inserting an execute-snapshot signal into the signal table lets the snapshot proceed in chunks while live changes continue. This approach avoids stopping the connector or locking tables.

Scenario 2 is insufficient snapshot throughput. Use an externally seeded load when the internal snapshot cannot finish within log retention. Karrot, a Korean hyperlocal secondhand marketplace, encountered this with Flink CDC and MongoDB Change Streams. Adding resources put more load on the database, but the initial snapshot was still too slow for the oplog window. Karrot separated the initial load into Spark and handed ongoing streaming to Flink CDC, with the CDC start position captured before or at the beginning of the load.

For Postgres, the corresponding alternative uses pg_dump --snapshot with a pre-created replication slot and CREATE SUBSCRIPTION WITH (copy_data=false, create_slot=false). The external load can use source partitioning for speed while CDC preserves the older log position. WAL still accumulates on the publisher during the dump, so this changes the loading path while retaining the retention cost.

Scenario 3 is a time-bounded baseline. Use snapshot.select.statement.overrides when only a relevant slice, such as the last 90 days of rows, is needed. An overridden table is processed as a single chunk, so it loses chunked parallelism. The filter also applies only to the snapshot. Streaming can deliver updates and deletes for rows excluded from the baseline, leaving the destination to handle changes for rows it never received.

Scenario 4 is limited source read capacity. Use a read replica when parallel snapshot reads would put too much load on the primary. PeerDB shows how to use the snapshot that CREATE_REPLICATION_SLOT creates on a read replica without changing the code. Where source capacity remains the bottleneck, thread count and chunk size are the main tuning levers. More parallelism is not useful if it degrades application latency.

How log retention windows make the snapshot-duration race non-optional for MongoDB and DynamoDB

Karrot's handoff illustrates a broader constraint for sources with bounded change logs. The baseline must finish while the log position needed to bring it up to date is still available. A faster loading path matters only if it preserves that handoff.

MongoDB's oplog is a capped collection with a variable retention window. A quiet database may keep months of history, while a cluster with many writes may keep only hours. If the oplog rolls past a consumer's resume token, resuming with watch and resumeAfter throws ChangeStreamHistoryLost. To recover, the collection then needs a full re-snapshot and a fresh stream.

DynamoDB Streams have a fixed 24-hour retention window. Delivery is at least once, ordering is scoped per shard, and a shard SequenceNumber permits resumption only while that position remains retained. Snapshot duration plus anticipated consumer downtime must therefore fit within 24 hours. Before choosing a strategy, measure how long the largest table's snapshot takes in the worst case and check that source retention allows that much time plus room for failures and retries.

Schema changes that arrive during or after the snapshot and why they require explicit handling

Retaining every row change does not guarantee that the destination can interpret it. Applications add columns, change types, and change tables as part of their day-to-day work. When ALTER TABLE runs at the source, the pipeline must recognize the changed schema and propagate it downstream. Schema correctness is a separate obligation from data completeness.

AWS DMS supports changes including ADD COLUMN, DROP COLUMN, RENAME COLUMN, and CHANGE COLUMN DATA TYPE during CDC when the task is configured to allow them. Some DDL changes can nevertheless suspend tables from replication and require manual intervention, target-side DDL, or task restarts. The restriction against midstream column reordering further illustrates the gaps in the schema evolution path provided by support for some DDL operations.

Filtered snapshots leave the destination without a baseline for the rows they exclude. If a column is added later, incoming events for those rows can reference a schema state the destination has not seen. A production pipeline therefore needs automatic schema evolution or an explicit DDL propagation path, rather than relying on manual migration scripts after replication breaks.

Separating the full-load task from the CDC task as an operational pattern

These mechanics lead to an operational separation between full loads and steady-state CDC. CDC is predictable and lightweight, while full loads are bursty and expensive. Putting them in one task ties together how they get resources and recover, even though their needs differ. Separating them allows each phase to be operated and scaled independently.

An AWS DMS multi-tenant case illustrates the consequence of that coupling. Teams combined full-load snapshots and CDC for simplicity, then found that reorganizing schemas triggered large reloads. The resolution was to run CDC-only tasks grouped by dozens of schemas, with separate full loads on demand. With this setup, a streaming consumer that is already running can keep going when a snapshot fails. For Postgres, putting snapshot reads on a read replica keeps snapshot I/O separate from the production queries that run on the primary.

What a correctly designed snapshot-then-stream pipeline looks like end to end

Correct by construction means satisfying all of these constraints at once. First, set up the replication slot and LSN anchor before reading data, then export the consistent snapshot and keep it open for all chunk workers. Select integer ranges where the primary key supports them, CTID blocks where appropriate, and offsets only as a last resort. Record chunk progress durably, with recovery that preserves the same snapshot position.

Next, finish the baseline and read the retained changes starting at the anchor LSN, which is before snapshot completion time. Use primary-key upserts and sink-side deduplication to apply the overlap before treating streaming as live. Track slot lag from the start with a firm alert threshold, and check beforehand that log retention leaves enough time for the load and recovery in the worst case. Provide automatic schema handling or an explicit propagation path, and keep full-load operations separate from steady-state CDC.

Assembling this sequence in-house means owning the slot lifecycle, chunk metadata, crash recovery, reconciliation, schema handling, and monitoring. The notes describe that scope as a multi-month engineering project before the first row reaches the warehouse. A managed service is the alternative to assembling those guarantees by hand, but the evaluation standard remains the same. The snapshot is correct only when its baseline and change stream form one continuous, recoverable account of the source.