5. Snapshots

The log only contains changes since the log started. Every row that already existed is invisible to it. Getting those rows out is the snapshot, and it is where most CDC projects spend their difficult days.

The problem

   orders has 400,000,000 rows.
   The WAL contains the last 6 hours.

   Streaming alone gives you:
     the 12,000 rows that changed today

   You need:
     all 400,000,000, then every change
     after that, with nothing missed and
     nothing out of order

The initial snapshot

The traditional approach, and still the default.

   1  acquire a lock / open a repeatable-read
      transaction
   2  record the CURRENT LOG POSITION
      (LSN / GTID / resume token)
   3  read the table schemas
   4  SELECT * FROM every captured table
      emitting each row as op = "r"
   5  release the lock
   6  START STREAMING from the position
      recorded in step 2

Step 2 before step 4 is the entire correctness argument. Any change that happens during the snapshot is also in the log after the recorded position, so it will be replayed in step 6. A row read at its old value in step 4 gets its update again in step 6.

This means duplicates are expected and correct. A row may be emitted as r with old data and then immediately as u with new data. Consumers must be idempotent — chapter 7’s point, arriving early.

Locking

The part that worries DBAs, reasonably.

   MySQL    a global read lock, briefly, to
            read schemas and the binlog
            position — then it is released
            and the SELECTs run in a
            consistent-read transaction.
            `snapshot.locking.mode=minimal`
            (default) or `none`.

   POSTGRES a REPEATABLE READ transaction.
            NO table locks. MVCC does the
            work.
            ⚠ but a long transaction blocks
              VACUUM from cleaning up dead
              tuples for its whole duration.

   MONGO    no locking. the resume token
            gives the position.

PostgreSQL’s version has no locks and a different problem. Its MVCC chapter explains why a long-running transaction prevents vacuum from reclaiming dead tuples anywhere in the database. A six-hour snapshot is a six-hour vacuum hold, and on a busy table that means bloat you will notice.

Why it is painful

   ✗ IT CAN TAKE HOURS OR DAYS
     400M rows through one task,
     serialised, into Kafka.

   ✗ IT DOES NOT RESUME
     the connector dies at 380M of 400M
     → START AGAIN FROM ZERO
     ← the single worst property

   ✗ NO STREAMING WHILE IT RUNS
     changes accumulate in the log behind
     you. on Postgres, that is WAL your
     slot is holding (chapter 2).

   ✗ IT LOADS THE SOURCE
     a full table scan of everything, at
     whatever rate the connector manages.

   ✗ ADDING ONE TABLE MEANT RE-SNAPSHOTTING
     ALL of them
     ← historically the most annoying
       operational fact about Debezium

Those last two combined produced the classic bad afternoon: a new table is needed, the only way to snapshot it is to re-snapshot everything, which takes eleven hours, during which the replication slot holds WAL and the source database’s disk climbs.

Snapshot modes

snapshot.mode=initial

   initial       snapshot if no offsets exist,
                 then stream. THE DEFAULT.

   never         stream only. you get changes
                 from now on, and no history.
                 ← right when something else
                   loaded the existing data

   initial_only  snapshot, then STOP.
                 ← a one-off bulk export

   no_data       schema only, no rows, then
                 stream.
                 ← you have the data already
                   but need Debezium to know
                   the schema
                   (older versions called
                    this `schema_only`)

   when_needed   snapshot if offsets are
                 missing OR unusable.
                 ← self-healing, and it will
                   silently re-snapshot a
                   400M-row table one night

   always        snapshot on EVERY restart.
                 ← testing only

Names have changed across versions — schema_only became no_data, and there are newer additions. Check the documentation for the version you run.

when_needed deserves a warning. It sounds like the sensible resilient choice, and it is, right up until an invalidated slot triggers an unattended full re-snapshot of your largest table at 3 a.m. Prefer initial plus an alert, so a human decides.

Incremental snapshots

The feature that changed how Debezium is operated. Based on Netflix’s DBLog design.

The idea: read the table in chunks, while streaming continues, and resolve conflicts with watermarks.

   FOR EACH CHUNK of the table, ordered by
   primary key:

     1  write a LOW WATERMARK to the signal
        table
     2  SELECT the next chunk (say 1,024 rows)
     3  write a HIGH WATERMARK to the signal
        table
     4  the connector sees both watermarks
        pass by IN THE LOG, in order
     5  any streamed change for a row in
        this chunk that appeared BETWEEN
        the watermarks WINS — that row is
        dropped from the chunk
     6  emit the surviving chunk rows as `r`
     7  next chunk

Steps 1 and 3 are the trick. The watermarks travel through the same log as the real changes, so their position relative to any concurrent change is unambiguous. A row changed mid-chunk is represented by the streamed event, not the stale snapshot read — and neither is lost.

   WHAT THIS BUYS

   ✓ STREAMING NEVER STOPS
     changes flow throughout. no WAL
     accumulation, no lag spike.

   ✓ IT RESUMES
     the connector dies at chunk 380,000
     → it restarts at chunk 380,000

   ✓ ONE TABLE AT A TIME, ON DEMAND
     add a table → snapshot just that table
     → no re-snapshot of anything else

   ✓ RE-SNAPSHOT ANYTHING, ANY TIME
     a consumer lost its state → replay
     one table

   ✓ PAUSABLE
     the source is struggling → pause it,
     resume tonight

   ✓ CHUNKED LOAD
     small SELECTs on an indexed key rather
     than one enormous scan

“Add a table without re-snapshotting everything” is the headline, and it converts CDC from a thing you set up carefully once into a thing you can evolve.

Signals

Incremental snapshots are triggered by signals — rows written to a table Debezium watches.

CREATE TABLE debezium_signal (
  id   VARCHAR(42) PRIMARY KEY,
  type VARCHAR(32) NOT NULL,
  data VARCHAR(2048) NULL
);

signal.data.collection=public.debezium_signal
signal.enabled.channels=source,kafka

-- snapshot one table, now
INSERT INTO debezium_signal (id, type, data) VALUES (
  'ad-hoc-1', 'execute-snapshot',
  '{"data-collections": ["public.order_items"],
    "type": "INCREMENTAL"}');

-- snapshot a SUBSET
INSERT INTO debezium_signal (id, type, data) VALUES (
  'backfill-emea', 'execute-snapshot',
  '{"data-collections": ["public.orders"],
    "type": "INCREMENTAL",
    "additional-conditions": [
      {"data-collection": "public.orders",
       "filter": "region = ''EMEA'' AND created_at > ''2026-01-01''"}]}');

-- the source is struggling
INSERT INTO debezium_signal (id, type, data)
VALUES ('pause-1', 'pause-snapshot', NULL);

INSERT INTO debezium_signal (id, type, data)
VALUES ('resume-1', 'resume-snapshot', NULL);

-- stop entirely
INSERT INTO debezium_signal (id, type, data)
VALUES ('stop-1', 'stop-snapshot', NULL);

additional-conditions is the underrated one. It turns a snapshot into a targeted backfill — re-emit only the rows a downstream consumer got wrong, rather than the whole table.

Signals can also arrive on a Kafka topic rather than a table, which matters when the Debezium role should not have write access to the database. MySQL additionally supports read-only incremental snapshots, using GTID positions instead of watermark writes — no signal table at all.

Blocking snapshots

A middle option worth knowing about.

INSERT INTO debezium_signal (id, type, data) VALUES (
  'blocking-1', 'execute-snapshot',
  '{"data-collections": ["public.orders"], "type": "BLOCKING"}');

Pauses streaming, snapshots the table in one pass, then resumes streaming from where it paused. Faster than incremental for one table, and it stops the stream while it runs.

   INCREMENTAL   streaming continues.
                 slower. resumable.
                 ← the default choice
   BLOCKING      streaming pauses.
                 faster. WAL accumulates
                 while it runs.
                 ← a small table, or a
                   maintenance window

Tuning it

incremental.snapshot.chunk.size=1024
snapshot.fetch.size=10000
snapshot.max.threads=4          # parallel tables, not parallel chunks
snapshot.select.statement.overrides=public.orders
snapshot.select.statement.overrides.public.orders=\
  SELECT * FROM public.orders WHERE created_at > '2024-01-01'

   chunk.size
     larger  → fewer round trips, more
               memory, longer per-chunk
               locks on the source
     smaller → gentler on the source,
               more overhead
     1024 is a reasonable start; raise it
     if the source is idle and the snapshot
     is slow.

   snapshot.select.statement.overrides
     ← the escape hatch for "we do not need
       eight years of history". genuinely
       the fastest way to make a snapshot
       finish.

Not snapshotting data you do not need is the best optimisation available, and it is routinely overlooked. Most consumers of a CDC stream do not need rows from 2017.

The snapshot flood

An operational fact worth planning for.

   400,000,000 `r` events, as fast as one
   task can produce them:

   → Kafka topic grows by hundreds of GB
   → consumers see a burst orders of
     magnitude above steady state
   → a sink that keeps up with 200/sec
     meets 40,000/sec
   → downstream systems fall over, or lag
     for days

Test the snapshot’s downstream impact before running it in production. The steady-state rate tells you nothing about the snapshot rate.

Mitigations: run the snapshot into a topic nothing consumes yet, throttle the connector, use additional-conditions to snapshot in slices, or pause the snapshot when consumers lag.

What to take from this chapter

• The log only has changes; existing rows need a snapshot.
• The initial snapshot records the log position before reading rows, which is why concurrent changes are replayed afterwards and why duplicates are expected and correct. Consumers must be idempotent.
• PostgreSQL snapshots take no locks but hold a long transaction, which blocks vacuum for their whole duration.
• The initial snapshot does not resume. Dying at 380M of 400M means starting again.
• when_needed will silently re-snapshot your largest table at 3 a.m. Prefer initial plus an alert.
• Incremental snapshots read in chunks while streaming continues, using watermarks written through the log to resolve conflicts. They resume, they pause, and they work on one table at a time.
• “Add a table without re-snapshotting everything” is the change that matters most — it makes CDC evolvable.
• Signals trigger them, from a table or a Kafka topic. additional-conditions turns a snapshot into a targeted backfill.
• BLOCKING snapshots are faster and stop the stream. Fine for a small table.
• Not snapshotting data you do not need is the best optimisation, via snapshot.select.statement.overrides.
• Plan for the snapshot flood. Steady-state throughput tells you nothing about snapshot throughput.

Next: a change traced — one UPDATE, from COMMIT through the WAL to a consumer.