Skip to content
All articles

Change Data Capture for PostgreSQL & Cassandra: 4 Approaches Ranked

Dean Jain

Dean Jain

Senior Staff Software Engineer · Enterprise AI, Data & Cloud Architect

· 6 min read

CDCPostgreSQLCassandraData Engineering
---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    DB[("🗄️ Source DB")]:::server -->|"INSERT · UPDATE · DELETE"| CAP["📡 Capture changes"]:::gate
    CAP --> DS["⬇️ Downstream<br/>cache · warehouse · search · services"]:::good
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A

Figure 1: CDC captures every row change and streams it downstream in near real time to caches, warehouses, search indexes, and other services.

Change Data Capture (CDC) keeps the rest of your stack in sync with a database without hammering it. Spot every INSERT, UPDATE and DELETE, then stream those changes downstream in real time. It powers replication, real-time analytics, cache invalidation, search indexing, auditing, and zero-downtime migrations. There are four ways to do it, and they are not equal they trade database overhead against reliability and latency. The big idea up front: read the log the database already writes; don’t add triggers or poll.

TL;DR

  • CDC = capture every change, stream it downstream the backbone of replication, real-time analytics, and zero-downtime migration.
  • A full setup is two phases: an initial snapshot of existing data, then continuous streaming of new changes stitched together with no gaps.
  • Log-based CDC wins. Reading the DB’s own write-ahead/commit log is low-overhead, transactionally consistent, and captures everything including deletes.
  • Triggers and polling are fallbacks. Triggers add write overhead inside every transaction; timestamp polling is inefficient, laggy, and can’t see deletes.
  • In practice, use a tool (Debezium) on top of log-based CDC it handles snapshots, offsets, schema changes, and delivery to Kafka.

1. What CDC is and the two phases

CDC spots data changes and delivers them to other systems in real time (or close to it). That’s what keeps a search index, a cache, a warehouse or a sibling microservice consistent with your source of truth. No polling the database to death.

A complete setup almost always has two phases:

  1. Initial load / snapshot efficiently copy all the current data (the history) from the tables.
  2. Continuous CDC capture and stream every change that happens after that snapshot.

The seam between them is what matters, because doing it naively means you either miss the changes that occur while the snapshot is running or double-apply them afterwards. The best solutions (see §3) blend the two seamlessly snapshot first, record the exact log position, then stream from that position onward. Hold onto that “two phases, one seam” idea; it’s what separates a toy CDC pipeline from one you’d trust in production.

2. The four approaches, ranked

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart TD
    A["🥇 Log-based (logical decoding /<br/>native CDC logs)"]:::good
    B["🥈 Third-party tools<br/>(Debezium on top of log-based)"]:::server
    C["🥉 Triggers<br/>(overhead inside every txn)"]:::warn
    D["🚫 Timestamp polling<br/>(inefficient, laggy, misses deletes)"]:::danger
    A --> B --> C --> D
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef warn fill:#FFE6A8,stroke:#E0A106,stroke-width:2px,color:#0F172A
    classDef danger fill:#FFB3B3,stroke:#D14545,stroke-width:2px,color:#0F172A

Figure 2: The four approaches, best to worst. Read the log, ideally via a tool; triggers and polling are last resorts.

1. Log-based CDC, the winner. Every database already writes an ordered log of changes for durability PostgreSQL’s Write-Ahead Log (WAL), Cassandra’s commit log. Log-based CDC reads that log instead of touching your tables:

  • Low overhead it reads logs the DB writes anyway, far lighter than triggers.
  • Reliable & consistent captures changes transactionally, in the right order.
  • Captures everything inserts, updates, deletes, often with old and new values.
  • Decoupled consumers work independently without burdening the database.

The costs are operational, not architectural. PostgreSQL needs wal_level = logical, which is a restart, plus careful replication-slot management. A stuck consumer means WAL files pile up and can fill your disk. Monitor slot lag closely.

2. Third-party tools, which is how you actually run log-based CDC. Debezium with Kafka Connect, Striim or Fivetran sit on top of logical decoding. They handle the hard parts: parsing, schema changes, offsets, failover, output formatting, and the initial snapshot. For most production needs this is the answer; you rarely hand-roll log readers.

3. Triggers, a fallback. Database triggers write change rows into an audit table that a separate process polls. They give you full control and customization, but the cost is steep. Triggers run inside the original transaction, so they add overhead to every write and slow your app down. Reserve them for low-traffic auditing, or when you genuinely can’t change wal_level.

4. Timestamp polling, which you should avoid. Periodically query WHERE last_updated > :last_poll. Simple, and it needs no database config. But it’s inefficient, because of constant table scans. It’s high-latency, only as fresh as the interval. It misses intermediate updates between polls. And it can’t capture deletes without soft-delete flags. Use only for tiny, low-write, non-critical tables.

3. PostgreSQL vs Cassandra: same idea, different logs

The ranking holds for both databases, but the log mechanics differ:

  • PostgreSQL log-based CDC means logical decoding of the WAL (since v9.4). Pick an output plugin (pgoutput is the native default), create a replication slot, point Debezium at it. Mature, well-supported, the clear default.
  • Cassandra. Log-based CDC means native CDC commit logs, from v3.8 onward. Enable cdc_enabled, set a cdc_raw directory and a cdc_total_space_in_mb cap, which is critical. Then run a consumer, Debezium’s Cassandra connector, that reads and deletes processed segments on every node. The sharp edge: if consumption lags, cdc_raw fills and writes block, so monitor it on all nodes. Cassandra’s CDC log carries no history either, since it only captures changes from the moment you enable it. Debezium’s connector covers that by taking an initial snapshot of the cluster by default, and dsbulk or COPY is the alternative if you disable it. Cassandra triggers still exist, but they run inside the write path and are a poor fit for CDC. Timestamp polling is even worse here, because non-primary-key queries need ALLOW FILTERING and scan the whole cluster.

4. The gold standard: snapshot, then stream from the exact log position

The seam from §1 is solved cleanly by letting a tool own both phases:

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    SNAP["📸 Initial snapshot<br/>(all current data)"]:::obs --> POS["📍 Record exact<br/>log position"]:::gate
    POS --> STREAM["▶️ Stream changes<br/>from that position onward"]:::good
    classDef obs fill:#AED6F1,stroke:#2E86C1,stroke-width:2px,color:#0F172A
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A

Figure 3: The snapshot-to-stream handoff. Capture all current data, mark the exact log offset, then stream from there. No gaps, no duplicates.

Debezium’s snapshot.mode = initial does exactly this. It reads all current data, remembers the precise log position, then switches to streaming from that position. Full history plus real-time changes, stitched together with no gaps and no overlaps.

Recommendations: for PostgreSQL, use logical decoding via Debezium + Kafka Connect, with the tool’s initial snapshot (or native COPY) for bulk load. For Cassandra, use native CDC logs consumed by Debezium, and watch cdc_raw like a hawk. Use dsbulk or the tool’s own snapshot for the initial load.

The instinct when you need to sync systems is to add triggers or a polling job. Both quietly tax or lag your database. The better mental model is that your database is already writing an ordered, durable log of every change; CDC just reads it. Lean on log-based capture through a managed tool, handle the snapshot seam deliberately, and monitor lag. You get a real-time, low-overhead pipeline that the source database barely notices.

Further reading