Change Data Capture: Stream Database Changes

Many businesses need data to move from one system to another as it changes: from an application database to a reporting warehouse, from an order system to a CRM, from a legacy database to a new platform during migration. The simplest approach is to query the source on a schedule and look for rows that changed. That works for small volumes, but it misses deletes, depends on reliable timestamp columns and adds load to the source database. Change data capture takes a different route. Instead of repeatedly asking the database what changed, it reads the database's own record of changes and turns each insert, update and delete into an event that other systems can consume. This guide explains how it works, when to use it and what to plan for.

What change data capture is

Change data capture, often shortened to CDC, is a set of techniques for identifying and delivering changes made to a database. The most robust form is log-based: databases already write every change to a transaction log for durability and replication, and CDC tools read that log.

In PostgreSQL, the built-in mechanism behind this is logical decoding, which the documentation describes as the process of extracting all persistent changes to a database's tables into a coherent, easy-to-understand format that can be interpreted without detailed knowledge of the database's internal state (PostgreSQL: Logical decoding concepts). Other databases offer their own change logs.

Log-based CDC versus polling

Debezium, an open-source CDC platform, contrasts log-based capture with approaches such as polling or dual writes. Its documentation says log-based CDC ensures that all data changes are captured, produces change events with very low delay while avoiding the extra CPU usage of frequent polling, requires no changes to the data model such as a "last updated" column, can capture deletes, and can capture old record state and additional metadata depending on the database (Debezium: features).

Polling still has a place. It is simpler to set up, works with almost any database and may be perfectly adequate for small tables that change slowly. Our guide to database synchronization compares sync approaches more broadly.

When change data capture fits

Consider CDC when:

• Changes must reach other systems soon after they happen
• Deletes must be reflected downstream
• Source tables lack reliable timestamp columns
• Polling would put too much load on a busy production database
• Several systems need the same stream of changes
• You are migrating between databases and need to keep them in step during the transition

How a CDC pipeline is put together

A typical pipeline includes:

1. Source database configured to expose its change log
2. A CDC connector that reads the log and produces change events
3. A transport, such as a message broker or event stream, that holds events
4. Consumers that apply changes to targets, such as a warehouse, search index, cache or CRM
5. Monitoring for lag, errors and connector health

Each change event usually includes the operation type, the table, the key, the new values and sometimes the old values and transaction details.

Start with a snapshot

When a CDC pipeline starts, the target usually needs the existing data, not only future changes. Debezium's documentation notes that connectors can optionally take an initial snapshot of a database's current state, typically when logs no longer contain the full history. Plan how the snapshot and the ongoing stream join without gaps or duplicates, and how long the snapshot will take on large tables.

Watch replication slots and log retention

Log-based CDC depends on the database keeping log data until the connector has read it. In PostgreSQL, replication slots track this. The documentation cautions that replication slots persist across crashes, know nothing about the state of their consumers and prevent removal of required resources even when no connection is using them, which consumes storage. If a connector stops for a long time, retained log data can grow and fill disks. Monitor slot lag and disk usage, alert when they rise and remove slots that are no longer used.

Handle ordering and duplicates

Consumers should apply changes in the right order for each record and cope with duplicates, which can occur after restarts or failures. Common techniques:

• Process changes for the same key in order, for example by partitioning on the primary key
• Make target updates idempotent, such as upserts keyed on the source primary key
• Use log positions or version numbers to ignore outdated changes

Plan for schema changes

Source tables change: columns are added, renamed or removed. Decide how the pipeline will handle this. Adding nullable columns is usually straightforward; renames and type changes need coordination between source owners and consumers. Agree a process so schema changes in the source do not silently break downstream systems.

Worked example: a CDC rollout plan

The table below is a hypothetical rollout plan for streaming changes from an order database into a reporting warehouse and a CRM. It shows the sequence, not a specific product setup.

StepTaskCheck before moving on1Enable change log access on a replica or primary as supportedDatabase settings reviewed with the database owner2Configure connector for orders and customers tables onlyEvents appear with expected fields3Run initial snapshot into the warehouseRow counts and totals match source4Switch to streaming changesLag stays within agreed limit during busy hours5Add CRM consumer with upsert logicDuplicate and out-of-order tests pass6Add monitoring and alertsAlerts fire on lag, connector failure and slot growth7Document schema change processOwners of source and consumers agree it

Protect sensitive data

Change streams contain every change, including personal and sensitive fields. Capture only the tables and columns consumers need, mask or exclude sensitive fields where possible, secure the transport and restrict access to the stream. Consider data protection obligations for every system that receives the data.

Keep CRM syncs sensible

When CDC feeds a CRM, avoid pushing every minor database change as an API call. Batch or filter changes, respect API limits and map only the fields the CRM needs. Our guide to Zoho CRM and PostgreSQL sync covers the practical side of syncing a CRM with a relational database.

Monitor end to end

Track connector status, event lag, consumer errors, dead-lettered events and target freshness. Reconcile source and target counts regularly to catch silent gaps.

Frequently Asked Questions

What is change data capture?

It is a set of techniques for identifying changes made to a database, such as inserts, updates and deletes, and delivering them to other systems.

Why use log-based CDC instead of polling?

Log-based CDC reads the database's change log, so it can capture all changes including deletes with low delay and without frequent polling queries.

What risks come with log-based CDC in PostgreSQL?

Replication slots retain log data until consumers read it, so a stopped connector can cause storage to grow; slots need monitoring and clean-up.

How do CDC consumers handle duplicates?

By applying idempotent upserts keyed on the source primary key and using log positions or versions to ignore outdated changes.

Move data as it changes

We design and run change data capture pipelines between databases, warehouses and CRMs. See our real-time CRM and database synchronization service, or talk to us about change data capture.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.