cloudparts

Change data capture

Read a database’s own transaction log and turn every committed insert, update and delete into an ordered stream of change events that other systems can follow.

7 technologies on this page ↓
Live model
What you’re watchingThe app writes to the database, which appends every change to its transaction log. Two CDC readers follow the log, each from its own saved position: one updates the search index within seconds, the other loads the warehouse in larger, slower batches. The log keeps entries until the slower reader has passed them.

How it works

Every database writes each change to a log before it applies it, for crash recovery and for its own replicas. Log-based CDC reads that log as if it were one more replica. In PostgreSQL you set wal_level = logical, list the tables in a publication, and create a logical replication slot; an output plugin such as pgoutput decodes the WAL into row changes, sent when each transaction commits, in commit order. In MySQL the reader connects like a replica and reads the row-based binlog, tracking its place as a file and offset or a GTID set. MongoDB exposes change streams over its oplog, with a resume token in every event, and SQL Server runs a capture job that copies changes from its transaction log into change tables that consumers query by log sequence number (LSN). A typical change event carries the row’s primary key, the row before and after the change, the operation, the transaction id and the log position. The log holds only recent changes, not the whole table, so a new reader first takes a snapshot: either one consistent read of every table, taken at the log position where streaming will start, or chunks read by primary key while streaming continues, with markers written into the log to tell which value is newer. Then it streams, saving the position it has finished with as its offset. Two things decide how hard the rest is. Ordering: changes to one row arrive in order if the stream is partitioned by primary key, but nothing orders events across partitions or tables, and after a crash a reader may get the last few changes again, so consumers must apply them idempotently. Schema changes: MySQL’s binlog contains ALTER TABLE statements, so a reader can rebuild each table’s columns at any point, but PostgreSQL’s logical decoding does not decode DDL at all, and SQL Server keeps a change table’s columns fixed until you create a second capture instance.

Think of it as

A bank statement instead of a balance check. Looking at the balance every hour tells you it changed, but not about the deposit and withdrawal that cancelled out, or the account that was closed. The statement lists every transaction in order, and the bank keeps statements only for so long, so you must read them before they are thrown away.

Reach for it when

  • Keeping a search index, cache or read model in step with the database that owns the data, within seconds, without the application writing to both
  • Replicating tables into a warehouse or lake with deletes included and without scanning the source tables
  • Migrating to a new database or another cloud with little downtime: copy everything, then stream changes until you switch over
  • Publishing domain events from a service reliably, through an outbox table written in the same transaction as the business data

Think twice when

  • Hourly or daily copies of small tables are enough; a cursor query on updated_at needs no log access, slots or replica permissions
  • You can’t change server settings or get replication privileges, as on some managed databases or third-party systems you don’t run
  • Nobody will watch the pipeline: a stalled reader makes PostgreSQL keep WAL until the disk fills, unless you cap it
  • Other services would consume your raw table rows as their API; every column rename then breaks them, so publish designed events through an outbox instead

A real example

A shop runs PostgreSQL with Debezium reading two tables, orders and outbox, through one replication slot into Kafka. Order changes, keyed by order_id, feed the search index within a couple of seconds, and a nightly merge loads them into the warehouse with deletes intact. When checkout places an order, it inserts the order row and an OrderPlaced row into outbox in the same transaction, then deletes the outbox row; the insert is already in the WAL, so the event reaches Kafka even though the table stays empty. One Friday the Kafka Connect cluster stops. The slot keeps every WAL segment written since then, and at the shop’s write rate that would fill the 200 GB disk by Sunday night. Because the team set max_slot_wal_keep_size = 100GB, PostgreSQL invalidates the slot on Saturday, when it holds 100 GB, and the database keeps running. The connector cannot resume from a slot that no longer exists, so on Monday they create a new slot and Debezium re-reads the tables in chunks while it streams.

Technologies

Specific products, and what each does differently.

Also used for change data capture

Products of other kinds that are often used this way, grouped by what they are.

ETL & ELT pipelines →

ArtieCommercialA managed change data capture service that copies every insert, update and delete from production databases into a warehouse, aiming for under a minute of delay.EstuaryCommercialA managed data-movement platform that captures changes from databases and SaaS sources into collections kept as JSON files in your own bucket, then keeps warehouses and other systems in sync, in real time or on a schedule.FivetranCommercialManaged connectors that copy data from SaaS apps, databases and files into your warehouse or lake, billed by the rows that change each month.OLakeOpen sourceAn open-source tool in Go that copies databases, Kafka topics and S3 files into Apache Iceberg tables (or plain Parquet), with full loads, incremental syncs and CDC, and no Kafka, Debezium, Spark or Flink in between.StreambedPreviewOpen sourceA small Go program that copies Postgres changes into Iceberg or DuckLake tables on S3 through logical replication, and answers SQL over them through a Postgres-compatible server built on DuckDB.

What each provider calls it

AWS
AWS DMS, with “full load plus CDC” and “CDC only” tasks that read engine logs (logical replication slots on PostgreSQL, the row-based binlog on MySQL, LogMiner or the binary reader on Oracle); DynamoDB Streams for item-level changes, kept for 24 hours
Google Cloud
Datastream: serverless CDC and backfill from MySQL, PostgreSQL and AlloyDB, Oracle, SQL Server, MongoDB and Spanner into BigQuery or Cloud Storage
Azure
Built-in CDC in SQL Server, Azure SQL Database and Azure SQL Managed Instance (change tables filled from the transaction log); Fabric Mirroring, which replicates Azure SQL, Azure Database for PostgreSQL, Cosmos DB, Oracle, Snowflake and others into Delta tables in OneLake; Fabric eventstream CDC sources, which emit Debezium-format change events
Open source & others
Debezium (on Kafka Connect, as Debezium Server or embedded in a Java app); PostgreSQL’s own logical replication and `pg_recvlogical`; OLake for databases into Iceberg. Managed services built on log-based CDC: Estuary, Artie, Fivetran

Three ways to capture changes#

All three end with a list of changed rows. They differ in what they can see and in what they cost the source database.

MethodHow it finds changesSees deletes and every step?Cost on the sourceNeeds
Query-based (polling)Runs SELECT … WHERE updated_at > :last_seen on a scheduleNo. A deleted row is simply gone, and two updates between polls look like oneA query per table per poll; polling often means more loadA reliable timestamp or version column on every table, kept up to date by every writer
Trigger-basedTriggers on each table write the old and new row into an audit or change tableYes, inside the same transaction as the changeAn extra write on every insert, update and deletePermission to create triggers and tables, and a job to read and prune the change table
Log-basedReads the transaction log the database already writes (WAL, binlog, oplog, transaction log)Yes, every committed change in commit order, with before and after values if the database logs themSmall for reads, but the database keeps log files until the reader confirms themServer settings such as wal_level = logical or row-based binlog, and replication privileges
  • SQL Server’s own CDC is log-based but stores its output in change tables, which consumers then poll by LSN; its separate change tracking feature records only which rows changed, not their values.

What goes wrong in production#

Most CDC incidents come from the log’s retention, not from the reader’s code.

WAL bloat from a stalled slot

PostgreSQL

A replication slot keeps every WAL file its consumer has not confirmed, even while nothing is connected, and also stops VACUUM removing catalog rows it may need. A forgotten or stalled slot fills the disk. Set max_slot_wal_keep_size (PostgreSQL 13+) or idle_replication_slot_timeout (PostgreSQL 18+) so the slot is invalidated first, alert on retained WAL, and send heartbeats if the captured tables are quiet.

The log moved on without you

MySQL, MongoDB, DynamoDB

These databases don’t wait for readers: binlog files expire, the oplog is a fixed-size window, and DynamoDB Streams keeps records for 24 hours. A reader that was down longer finds its saved position gone and has to snapshot again.

Schema changes

all

A new column, a renamed column or a changed type has to reach every consumer. PostgreSQL never decodes DDL, so readers infer changes from the rows that follow; MySQL readers keep a history of DDL statements with their binlog positions; SQL Server ignores new columns until you create a second capture instance.

Duplicates after a restart

all

Positions are saved periodically, so after a crash the last changes may arrive again; PostgreSQL itself persists a slot’s position only at checkpoints. Apply changes as upserts keyed on the primary key, or deduplicate by log position.

Missing before values

PostgreSQL

With the default REPLICA IDENTITY, update and delete events carry only the primary key as the old row, and a table without a primary key emits none. Set REPLICA IDENTITY FULL on tables whose consumers need the whole old row.

The outbox pattern#

A service that saves an order and then publishes OrderPlaced to Kafka is doing two writes that can’t share a transaction. If it crashes between them, the order exists and the event doesn’t, or the other way round. The outbox pattern makes it one write: in the same database transaction as the order, the service inserts a row into an outbox table with an id, an aggregate type such as order, the aggregate’s id, an event type and a JSON payload. CDC reads the insert from the log and publishes it.

Debezium’s outbox event router turns each outbox row into a message on a topic named after the aggregate type, such as outbox.event.order, keyed by the aggregate id, so all events for one order land in one partition in order. The event id goes into a header for consumers to deduplicate. Because the log already holds the insert, the service can delete the outbox row straight away and the table stays small; the router ignores those delete events. Consumers get designed events instead of raw table rows, so the service can change its tables without breaking them.

In code · sql

-- PostgreSQL started with wal_level = logical (output from PostgreSQL 15)
CREATE TABLE orders (id int PRIMARY KEY, status text);
SELECT * FROM pg_create_logical_replication_slot('cdc_demo', 'test_decoding');

INSERT INTO orders VALUES (1, 'new');
UPDATE orders SET status = 'paid' WHERE id = 1;
DELETE FROM orders WHERE id = 1;

-- Read the changes without consuming them
SELECT data FROM pg_logical_slot_peek_changes('cdc_demo', NULL, NULL);
-- BEGIN 725
-- table public.orders: INSERT: id[integer]:1 status[text]:'new'
-- COMMIT 725
-- BEGIN 726
-- table public.orders: UPDATE: id[integer]:1 status[text]:'paid'
-- COMMIT 726
-- BEGIN 727
-- table public.orders: DELETE: id[integer]:1    <- only the key (REPLICA IDENTITY DEFAULT)
-- COMMIT 727

-- The slot pins WAL until its consumer confirms; watch this number
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS held_back
FROM pg_replication_slots;

-- Consume them: the same nine rows, and a second call returns nothing
SELECT data FROM pg_logical_slot_get_changes('cdc_demo', NULL, NULL);

-- DDL is not decoded: this ALTER produces no event of its own
ALTER TABLE orders REPLICA IDENTITY FULL;

SELECT pg_drop_replication_slot('cdc_demo');   -- an unused slot holds WAL forever

Often confused with

ETL & ELT pipelinesEvent streamsStream processingCQRSEvent buses
Where it came from2012–2025

Getting changes out of a database used to mean polling tables or adding triggers. Database vendors built log readers for their own replication first; SQL Server’s change data capture, for example, uses the same internal procedure as its transactional replication to read the transaction log. Large web companies then built general systems: LinkedIn’s Databus, described at SoCC 2012, was a source-agnostic CDC system that fed LinkedIn’s search and graph indexes from its databases.

PostgreSQL 9.4, released in December 2014, added replication slots and logical decoding, credited in the release notes mainly to Andres Freund, so any program could read row changes from the WAL. In a 2015 talk Martin Kleppmann called getting data out of databases “a surprisingly tricky problem” and pointed to the log as the way. Debezium, started at Red Hat in late 2015, shipped its first connector in March 2016: a Kafka Connect source that read the MySQL binlog.

The next years went into the hard parts. Debezium’s 2019 post on the outbox pattern showed how to publish events reliably through CDC. Netflix’s DBLog paper (2020) described taking snapshots in chunks while the log keeps streaming, which Debezium adopted as incremental snapshots in 2021. PostgreSQL added max_slot_wal_keep_size in version 13 (2020) and idle_replication_slot_timeout in version 18 (2025) so an abandoned slot can no longer fill a disk. The clouds now sell CDC as a service: AWS DMS, Google’s Datastream and Microsoft Fabric’s mirroring.

Why it’s built this way

Read the log instead of polling tables or adding triggers
Polling misses deletes and changes that happen between polls, and adds load on the source; triggers add a write to every transaction. The log already records every committed change in order, so reading it costs the source little and misses nothing.
Start with a snapshot, then stream from a known position
Logs are trimmed, so they can’t rebuild a table from the beginning. PostgreSQL exports a snapshot when a slot is created that matches exactly where the change stream begins, so a reader can copy the tables and stream from that point with no gap.
Snapshot in chunks while streaming continues
A single initial snapshot can take hours, must restart from scratch if interrupted, and holds back streaming. DBLog’s low and high watermarks in the log let a reader copy one primary-key range at a time, merge it with live changes, and resume after a crash.
Make the database keep the log until the reader confirms
PostgreSQL’s replication slots guarantee a reader never misses a change, at the cost of disk space while the reader is away. Later versions added size and idle-time limits, trading that guarantee for a database that stays up.

Timeline

  1. 2012

    LinkedIn presents Databus, its change data capture system, at SoCC

  2. 2014

    PostgreSQL 9.4 adds logical decoding and replication slots, December 18

  3. 2016

    Debezium 0.1 streams MySQL binlog changes into Kafka

  4. 2019

    Debezium describes the outbox pattern for publishing events through CDC

  5. 2020

    Netflix publishes DBLog; PostgreSQL 13 adds max_slot_wal_keep_size

  6. 2021

    Debezium adds incremental, watermark-based snapshots

  7. 2025

    PostgreSQL 18 can invalidate idle slots with idle_replication_slot_timeout

Sources19

Improve this page on GitHub