Optimizing CDC Snapshots for Large Databases

This is an operational guide for making the initial and ongoing snapshot phase of a CDC pipeline as fast as it can safely be on a large source database. It assumes you already understand the snapshot types and modifiers. For those definitions — Full (Initial), Ad-hoc, Incremental, Parallel, Partial — see Snapshot Management. This article does not repeat them; it focuses on how to choose and tune a strategy for volume.

Who this is for:

• Engineers preparing a large initial CDC load.
• Operators troubleshooting a slow snapshot.
• Teams loading CDC data into cloud data warehouses.
• Anyone deciding between a full, partial, blocking ad-hoc, incremental, or externally seeded snapshot.

Availability

Snapshotting a large database is not tied to any particular Etlworks version. Almost everything in this guide — sizing snapshot threads and JDBC fetch size, partial and incremental snapshots, resumability, queue and file tuning, and the external-load pattern — works on current Etlworks releases generally.

Only one feature has a version floor: the chunked parallel snapshot option (dividing a single large keyed table across snapshot threads) requires Etlworks 9.7.1 or newer. For Flows running on customer-managed Integration Agent, the agent must also be on the 9.7.1 build. Where a scenario below relies on chunking, that is called out; the rest applies regardless of version.

The end-to-end performance model

Total snapshot throughput is a pipeline, and a pipeline is only as fast as its slowest stage:

Source database read
      → snapshot workers
            → CDC queue
                  → staged files
                        → destination bulk load

Every tuning knob in this article acts on exactly one of those stages. Tuning a stage that is not the bottleneck does not improve total throughput — and can make things worse. Adding snapshot threads when staged files are already accumulating faster than the destination can load them just burns more source capacity and memory while the real backlog is downstream. Before you change anything, find the constrained stage (see Diagnosing bottlenecks), change one class of settings, and re-measure.

The two ends of the pipeline pull in opposite directions: source-extraction settings decide how fast rows come out, and queue, file, and destination settings decide how fast they go in. Balancing them — not maximizing either — is the goal.

Choosing a snapshot strategy

Start from the shape of your data and your destination. The table summarizes the common cases; the scenario playbook below covers each in detail.

Scenario playbook

1. Many small or medium tables

Use Snapshot Max Threads for table-level parallelism so several tables snapshot concurrently. Start conservatively — typically 1–4 threads — after confirming the source database and the Etlworks instance have the capacity. Chunking usually adds little here: when each table already finishes quickly, the per-table boundary queries and scheduling overhead of chunking outweigh the benefit.

2. One very large keyed table

When a single large table with a database primary key or valid custom message key columns configured with message.key.columns dominates the snapshot, enable chunked parallelism so more than one thread reads that one table:

• Snapshot Max Threads greater than 1.
• Chunk large tables across snapshot threads enabled.
• Snapshot Thread Multiplier initially set to 1.

Increase the multiplier only when you observe that some chunks finish much earlier than others and snapshot threads sit idle waiting for the slow chunks. Multiplier 1 is the right starting point.

3. A few large tables with uneven sizes

Use table-level and chunked parallelism together. Table-level parallelism keeps several tables moving at once; chunking splits the large ones so a single big table does not become a long tail. Raising Snapshot Thread Multiplier creates smaller, more numerous chunks, which improves worker utilization when sizes are uneven — but each additional chunk adds a key-boundary query and scheduling overhead, so raise it only when threads are demonstrably idle.

4. Mixed small and very large tables

Use Snapshot Max Threads for concurrency across the many small tables and Chunk large tables across snapshot threads for the large keyed ones. Small tables remain normal single-chunk work items and run alongside the chunks of the big tables in the same shared worker pool.

5. Keyless tables

Chunked parallel processing divides a table by ordering it on a key and calculating non-overlapping ranges. If a table has no database primary key, it can still be chunked when message.key.columns defines a unique, stable, non-null row key for it. Without either, the table cannot be chunked safely — there is no stable column to split on. Your options for a table with no usable key:

• Process the table sequentially (it runs as a single work item alongside other tables).
• Configure custom message key columns through message.key.columns after verifying that the columns form a unique, stable, non-null key and that changing the Debezium event key will not break downstream consumers.
• Load it separately with a partitioned ETL flow, outside the CDC snapshot.
• Reconsider whether the table belongs in the CDC pipeline at all.

For example, to give a keyless order_lines table a composite key for chunking, set it in the CDC connection's Other Parameters: message.key.columns=inventory.order_lines:order_id,line_number. See CDC settings reference → Custom message key columns.

Do not chunk on a non-unique or unstable key. A column that is not unique produces overlapping or gapped ranges; a column whose values change under load produces rows that move between ranges. Either one silently loses or duplicates rows in the baseline. If you cannot guarantee a unique, stable key, do not chunk the table.

6. Partial or filtered initial load

When only a subset of a table is needed at the destination — for example, the last 90 days — use a snapshot SELECT override (snapshot.select.statement.overrides) as described in Snapshot Management → Partial Snapshot. Points to keep in mind:

• A table with a snapshot SELECT override is processed as one snapshot chunk — Etlworks does not rewrite an arbitrary custom SELECT into key ranges.
• Other tables on the same connection can still use chunked parallelism; chunking is a connection-wide setting and only the override tables fall back to a single chunk.
• The filter applies only to the snapshot. Once streaming starts, every change on the table is captured regardless of the WHERE clause.
• Updates and deletes can arrive for rows that were excluded from the baseline. The destination then sees a change for a row it never received.
• INSERT-only destinations are especially vulnerable — a missing baseline row plus a later update can produce a duplicate or an orphaned change. Design the destination flow (MERGE, or an explicit boundary) before using a partial load in production.

7. Add a new table to a running pipeline

Two mechanisms, different trade-offs:

• Blocking ad-hoc snapshot — briefly pauses streaming, snapshots the new table, then resumes. It runs as a blocking ad-hoc snapshot, so it can use chunked parallelism for a large keyed table. Best when a short streaming pause is acceptable.
• Incremental snapshot — continues streaming while it snapshots, using its own Incremental Snapshot Chunk Size. It does not use Snapshot Max Threads, the multiplier, or partial snapshot overrides. Best when streaming must not pause.

Choose blocking ad-hoc when the table is large but a pause is tolerable and you want chunked parallelism to finish it quickly; choose incremental when uninterrupted streaming is a hard requirement. See Snapshot Management → Ad-hoc Snapshot and Incremental Snapshot.

8. Reload an existing table or repair destination drift

For a moderate table, use a blocking ad-hoc snapshot and optionally chunk it. For a very large table where streaming cannot pause, use an incremental snapshot instead.

Watch for duplicates on re-snapshot. Re-snapshotting into an INSERT-only destination re-inserts every baseline row and can create duplicates. Before reloading, plan one of: truncate/replace the affected destination data, load through a MERGE, or apply an explicit deduplication strategy. This applies to any re-snapshot — ad-hoc, incremental, or a resumed full snapshot that repeats a table.

9. Interrupted large snapshot

Keep Resume interrupted full snapshot enabled (the default in 9.6.9+). On restart the snapshot skips tables already completed and continues with the rest. Understand its limits:

• Progress is persisted by completed table, not by completed chunk. A large table interrupted mid-way is read again from the beginning on restart.
• Source log retention must cover the original snapshot start position plus all retries — streaming resumes from the position recorded when the first snapshot began, not the current head.
• Monitor binlog (MySQL), WAL (PostgreSQL), archive log (Oracle), transaction log (SQL Server), or journal (AS400/IBM i) retention throughout the snapshot, with a margin that comfortably exceeds the expected total duration including retries.

See Snapshot Management → Resumable full snapshot.

10. Source database cannot tolerate parallel reads

If the source cannot absorb concurrent full-table reads, keep Snapshot Max Threads at 1 and chunking disabled. To still make the single-threaded snapshot workable, consider:

• Running the snapshot during a maintenance window.
• Reducing JDBC Fetch Size to lower per-read memory and buffer-cache pressure.
• Using an incremental snapshot, which reads in bounded chunks between change events.
• Loading the baseline with a separate ETL flow (see scenario 11).
• Splitting tables among separately scheduled flows — but only after accounting for the total source load, since several flows can add up to more pressure than one parallel snapshot.

11. Externally seeded or partitioned initial load

This is an advanced pattern: load the very large tables with regular ETL flows that use source partitioning for speed, while CDC establishes and preserves an older log position so no changes are lost between the baseline and streaming. It is powerful but easy to get wrong, and it requires a carefully documented sequence:

1. Capture the CDC start position before or at the very beginning of the baseline load.
2. Retain source logs until both the baseline load and the CDC catch-up have completed.
3. Partition the ETL load by stable, non-overlapping ranges.
4. Ensure there is no overlap or gap between the external baseline and the CDC events.
5. Use MERGE or deduplication during catch-up wherever possible.
6. Do not treat this as safe for INSERT-only destinations without an explicit boundary and duplicate-handling design.
7. Test recovery and restart behavior before using it in production.

If any of these cannot be guaranteed, prefer a standard full or incremental snapshot.

12. Schema-only or no-snapshot startup

Set Snapshot Mode to schema_only (capture structure, no rows) or never (start streaming from the current position) when:

• The destination was seeded separately.
• Only future changes are required.
• Historical rows are intentionally excluded.

These modes do not create a complete baseline. The destination will be missing every row that existed before the connector started; only changes from that point forward are captured. Use them deliberately, not as a way to skip a slow snapshot.

13. Incremental snapshots

An incremental snapshot is the right tool for a very large table when streaming must continue. Key points:

• Best for very large tables where a blocking snapshot's pause is unacceptable.
• Incremental Snapshot Chunk Size controls how many rows are buffered per incremental chunk. Larger chunks reduce query overhead but consume more memory.
• Incremental snapshots require signaling support and write access to the source signal table.
• On PostgreSQL, WAL and replication-slot retention must be monitored closely — the slot holds back WAL for the duration of the snapshot.
• They are separate from chunked parallel full/ad-hoc snapshots and do not use Snapshot Max Threads, the multiplier, or partial overrides.

See Snapshot Management → Incremental Snapshot for setup and production risks.

Tuning settings reference

Snapshot performance is set by two groups of knobs: source-extraction settings that control how fast rows are read, and queue/serialization settings that control how they are buffered and written. Per-setting detail is in the CDC settings reference; this table is the tuning-oriented view.

How these interact:

• Maximum Queue Size must remain greater than Maximum Batch Size, or the writer cannot assemble a full batch.
• Larger queues absorb temporary downstream slowdown, but they consume memory and do not increase source throughput by themselves.
• Larger batches reduce per-batch overhead but increase latency and memory usage.
• JDBC Fetch Size is applied per snapshot worker, so total buffering grows with thread count — four threads at fetch size 10,000 buffer up to 40,000 rows at once.
• Max Rows in File and Max File Size in Bytes work together: the file closes when either limit is reached.
• Very small files increase destination COPY overhead (many COPY operations over few rows); very large files delay data availability and make retries more expensive. Aim for the middle.

Destination-specific bulk-load tuning. Cloud data warehouses expose their own bulk-load controls — load interval, file combining, and direct-insert paths — that materially affect how fast staged files become table rows. Those settings live in each destination connector's documentation; tune them alongside the source-side settings above so neither end starves the other.

Supported connectors for chunked parallel snapshots

Unsupported connectors ignore the chunked path and continue using their existing snapshot behavior; table-level parallelism (except on MongoDB) is unaffected.

Diagnosing bottlenecks

Match the symptom to the constrained stage before changing settings.

A safe tuning procedure

Tune methodically. Chasing several settings at once hides which one helped and makes regressions hard to unwind.

1. Measure the baseline with default settings.
2. Identify the constrained stage from the bottleneck table.
3. Change only one class of settings at a time.
4. Start Snapshot Max Threads at 2 before trying 4 or higher.
5. Enable chunking with Snapshot Thread Multiplier = 1.
6. Observe source load, Integration Agent heap/GC, staged-file backlog, destination COPY duration, and CDC lag.
7. Increase concurrency or chunk count only while throughput keeps improving without destabilizing the source.
8. Confirm restart, retry, and duplicate-handling behavior before the production rollout.

No universal numbers. There is no thread count, fetch size, file size, or load interval that is correct everywhere. Every value in this article is a starting point to be measured against your actual source, network, Integration Agent, and destination.

See also

• Snapshot Management — snapshot types and modifiers, including the full Chunked Parallel Snapshot reference.
• CDC settings reference: tradeoffs and dependencies — per-setting detail.
• Database-Specific CDC Cases — source-specific snapshot behavior.