What is CDC (Change Data Capture)

• Log-based: read binlog/WAL (low overhead, zero SQL load)
• Trigger-based: trigger on INSERT/UPDATE → write to outbox table (higher overhead)
• Timestamp-based: polling column updated_at (misses deletes)
• Debezium: Java-based, supports Postgres/MySQL/Mongo/Oracle/SQL Server
• Typical output: Kafka topic per table with JSON events

# Debezium Kafka Connect config for Postgres
{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "database.hostname": "pg.internal",
  "database.dbname": "mydb",
  "slot.name": "debezium_slot",
  "publication.name": "debezium_pub",
  "topic.prefix": "mydb"
}
# Output: Kafka topics mydb.public.users, mydb.public.orders, ...

• Event Sourcing
• Materialized View

Change Data Capture (CDC) is a set of software design patterns used to identify and capture changes made to data in a database, enabling real-time data integration and processing. CDC tools can track changes at a granular level, ensuring that any modifications—such as inserts, updates, or deletes—are captured and transferred to target systems, improving data synchronization and analytics capabilities.

Change Data Capture (CDC) is a vital component in modern data management, primarily used in environments where real-time data processing is crucial. CDC allows organizations to track changes in their databases without the overhead of traditional batch processing methods. This capability is essential for maintaining data consistency across distributed systems and for enabling timely analytics.

The core principle of CDC is to monitor and record changes made to data in a database. This can be accomplished using various methods, including:

• Database Triggers: These are procedural codes that are automatically executed in response to certain events on a table or view. For example, a trigger can be set to log every insertion into a table.
• Log-Based CDC: This method involves reading the database transaction logs to capture changes. It is less intrusive than triggers and can handle high-throughput environments efficiently.
• Timestamp-Based CDC: In this approach, records are modified to include a timestamp indicating the last update. Queries can then be run to retrieve changes based on this timestamp.

Each method has its advantages and trade-offs, and the choice often depends on the specific use case and database architecture. For example, a large retail company may prefer log-based CDC due to the high volume of transactions, while a smaller application might leverage triggers for simplicity.

To illustrate how CDC can be implemented, let’s consider a practical example using PostgreSQL, a popular relational database. We will create a simple CDC mechanism using logical replication, which is a form of log-based CDC.

1. Enable Logical Replication: First, ensure that your PostgreSQL instance is set up to allow logical replication. Modify the postgresql.conf file:

wal_level = logical

2. Create a Publication: Next, you need to create a publication that defines which tables should be replicated. Execute the following SQL command:

CREATE PUBLICATION my_publication FOR TABLE my_table;

3. Create a Subscription: On the target database where you want to capture changes, create a subscription to the publication:

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=source_host dbname=mydb user=myuser password=mypass' PUBLICATION my_publication;

4. Monitor Changes: Changes to my_table in the source database will now be replicated to the target database. To monitor these changes, you can query the target table or create a logging mechanism to capture changes in real time.

This example demonstrates how CDC can be effectively utilized for real-time data synchronization. By implementing such mechanisms, organizations can ensure their data remains consistent across multiple systems, enhancing decision-making and operational efficiency.