Change Data Capture Tools

A log-based tool decodes the transaction log. A query-based tool polls each table for rows that changed since the last run, using an updated-at column or a system column. The split decides what the destination can know. A poll cannot see a row that was deleted between 2 runs, and it only sees the last state of a row that changed twice.

Fivetran documents this for Postgres: its logical replication mode sets _fivetran_deleted on a deleted row, while its query-based mode, which replaced the older XMIN method, does not recognise deletes unless delete capture is turned on. Airbyte describes CDC as the only one of its Postgres sync methods that records deletes. If the destination must mirror deletes or keep every intermediate change, choose a log-based tool. For the mechanics in general, see what change data capture is and how log-based CDC works.

Postgres decodes its log through an output plugin. pgoutput is the standard plugin since PostgreSQL 10 and needs no extra install. Debezium also supports decoderbufs, a Protobuf plugin the Debezium community maintains. AWS DMS uses pglogical when it is installed on the source and test_decoding otherwise. Fivetran and Airbyte both use pgoutput, and Airbyte requires PostgreSQL 14 or later for CDC.

The server also ships a complete logical replication feature: a publication on one database and a subscription on another. It takes a snapshot of each table, then streams changes in publisher order, which keeps a single subscription transactionally consistent. For copying a Postgres database to another Postgres database, it's the tool with the fewest parts. Its restrictions matter for anything else: DDL isn't replicated, sequence data is not replicated and large objects aren't replicated, so schema changes have to be applied to the subscriber by hand.

Debezium is Apache 2.0 licensed and runs in 3 ways. As a Kafka Connect connector, the most common form, it writes each table's changes to Kafka topics. As Debezium Server it streams to messaging systems without Kafka. As the Debezium Engine it is a Java library embedded in your own application. Airbyte's Postgres CDC runs on Debezium too, so a Debezium question is often an Airbyte question.

Its strength is fan-out and control: one stream of row changes that many consumers read at their own pace, with snapshot modes you choose. The cost is that you operate Kafka Connect and the slot, and you own the schema handling. Delivery is exactly once in normal operation and at least once after a crash, so sinks should deduplicate. The comparison of Kafka Connect and Debezium covers how the pieces fit.

AWS DMS runs tasks on a replication instance, with full load plus CDC or CDC only. Each task uses its own slot, and a slot stays open after the task stops, so unused slots must be deleted by hand. DMS can also fail a task when a lagging slot falls past a set max_slot_wal_keep_size. It fits teams already in AWS that want a managed path from RDS or Aurora.

Google Datastream is a serverless CDC and replication service. Its sources include PostgreSQL, MySQL, Oracle, SQL Server and MongoDB, and it writes to BigQuery and Cloud Storage. It fits a Google Cloud team that wants changes in BigQuery without running a connector.

Fivetran and Airbyte are warehouse-loading platforms with a Postgres CDC mode. Fivetran is fully managed and supports Postgres 10 through 18. Airbyte runs self-hosted or in its Cloud, and its CDC needs a dedicated slot and a publication. Both are built to load tables into a warehouse and don't fan out to many consumers. For the wider tool set, see open-source ETL tools compared.

Flink CDC is an Apache project, described in its documentation as a distributed data integration tool for real-time and batch data, with pipelines written in YAML. Its source connectors cover Postgres, MySQL, Oracle, SQL Server and MongoDB, among others. Pipeline sinks include Kafka, Doris, StarRocks and Iceberg. It fits teams that already run Flink and want to transform changes in flight.

PeerDB is a Postgres-first data movement platform with WAL-based CDC and a query-based mode. Postgres is its only named source, which makes it a narrow tool by design, and its documentation says a caching mechanism removes the need for REPLICA IDENTITY FULL on TOAST columns. Its published speed claims come from the vendor, so test it on your own tables.

Interviewers rarely ask for a tool ranking. They ask you to design a pipeline that mirrors an orders database into a warehouse, then probe the failure modes of CDC: what happens to deletes, what a stalled slot does to the primary, how a restart avoids duplicates and how a schema change flows through. Naming the slot and its WAL retention shows you have run one.

Practice that reasoning with the CDC pipeline interview questions, the data pipeline interview questions and the data pipeline practice problems. The system design round guide shows where a CDC design fits in the loop.