SQL 123 🛢️ Change Data Capture (CDC) with Debezium and Database Triggers
Change Data Capture is the pattern of detecting row-level changes—inserts, updates, and deletes—as they happen, and streaming them to downstream systems. The two dominant approaches in relational databases are log-based CDC, which reads the database’s transaction log, and trigger-based CDC, which installs database triggers that write to an audit table. Debezium is the de-facto open-source implementation of log-based CDC, running as a set of Kafka Connect source connectors that read the WAL in PostgreSQL, the binlog in MySQL, the change tables in SQL Server, and equivalent mechanisms in other databases . The trigger approach is older, simpler to understand, and still useful when log access is unavailable or when only a specific set of tables needs to be captured.
This chapter covers both approaches: the log-based architecture that Debezium implements, the PostgreSQL logical decoding plugins that make it work, the trigger-based alternative and its trade-offs, and the criteria that determine which approach belongs in a given environment.
Key point: Log-based CDC reads the transaction log without querying the source tables, which means it captures every committed change with millisecond latency and adds no write overhead to the application . Trigger-based CDC adds a write to an audit table on every change, which means it adds latency to every transaction and contends for locks on the audit table . The choice is between operational overhead on the database and operational overhead in the CDC pipeline.
Why the two approaches exist
The log-based advantage. PostgreSQL writes every committed change to its Write-Ahead Log before the data files are updated. Logical decoding reads those WAL records, resolves the internal identifiers against the system catalogs, and produces a structured event with the table name, operation type, and row values . The source tables are never queried, so the CDC process does not compete with the application for table locks or buffer cache. The replication slot tracks how far the consumer has read and prevents WAL segments from being reclaimed until they are acknowledged, which is what makes the stream resumable after a restart .
The trigger-based problem. A database trigger fires on every insert, update, or delete, and writes a record to a shadow table. This works on any database that supports triggers, including managed databases where logical replication is disabled or restricted. But the trigger runs inside the transaction that made the change, which means the write to the shadow table must commit before the application’s transaction can commit. The latency of every write is increased by the shadow table write, and the shadow table becomes a point of lock contention under concurrent load .
The Debezium architecture. Debezium connectors run inside a Kafka Connect cluster. Each connector establishes a connection to its source database, reads the transaction log through the database’s native replication protocol, and writes one Kafka message per row-level change . The Kafka topic name defaults to the table name, and the message contains the before-image, the after-image, the operation type, the transaction ID, and other metadata. Downstream consumers read from Kafka, which decouples the CDC pipeline from the source database’s availability and provides the durability and replay that a direct connection would not.
a. Log-Based CDC with Debezium and PostgreSQL
The PostgreSQL connector reads from a logical replication slot using a logical decoding plugin. The recommended plugin is pgoutput, which ships with PostgreSQL 10 and later and requires no extension installation . The older wal2json plugin produces JSON output and is still encountered in managed services that predate pgoutput, but new pipelines should use pgoutput .
The connector configuration requires a slot name, a plugin name, and a publication name. The publication defines which tables are captured. Creating it explicitly gives control over scope; letting the connector create it automatically is convenient but less precise .
-- Create a publication for the tables to capture
CREATE PUBLICATION cdc_pub FOR TABLE orders, customers, products;{
"name": "inventory-connector",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "postgres",
"database.port": "5432",
"database.user": "debezium",
"database.password": "secret",
"database.dbname": "inventory",
"topic.prefix": "prod",
"plugin.name": "pgoutput",
"slot.name": "debezium_inventory",
"publication.name": "cdc_pub",
"snapshot.mode": "initial"
}
}The snapshot.mode determines what happens at startup. initial takes a full snapshot of the included tables and then switches to streaming, which is the correct choice for a new pipeline against an existing database. never skips the snapshot and starts from the current WAL position, which is appropriate when the downstream system already has the data or when the snapshot would be too expensive .
REPLICA IDENTITY FULL is a table-level setting that controls the before-image in UPDATE and DELETE events. By default, PostgreSQL includes only the primary key in the before-image. With FULL, the complete row is included, which is necessary for audit trails or for destinations that need the before-image to compute deltas. The cost is increased WAL volume on tables with wide rows or high update rates .
-- Enable full before-image for a table that needs audit trails
ALTER TABLE orders REPLICA IDENTITY FULL;Debezium also handles schema changes through the replication stream, though PostgreSQL does not send explicit DDL events. The connector detects the change when it processes the first event from a table that includes the new column. With a schema registry, this produces a new schema version; without one, the schema is embedded in each message .
b. Trigger-Based CDC
The trigger approach installs a function that fires on INSERT, UPDATE, and DELETE, and writes the changed row to a shadow table. The shadow table has columns for the operation type, the table name, the old values, the new values, and a timestamp. A separate process polls the shadow table and forwards the changes.
-- Audit table for captured changes
CREATE TABLE orders_audit (
audit_id bigserial PRIMARY KEY,
operation text NOT NULL,
row_id bigint NOT NULL,
old_data jsonb,
new_data jsonb,
changed_at timestamptz DEFAULT now()
);
-- Trigger function
CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO orders_audit (operation, row_id, old_data)
VALUES ('DELETE', OLD.id, to_jsonb(OLD));
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO orders_audit (operation, row_id, old_data, new_data)
VALUES ('UPDATE', NEW.id, to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSE
INSERT INTO orders_audit (operation, row_id, new_data)
VALUES ('INSERT', NEW.id, to_jsonb(NEW));
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Attach the trigger
CREATE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();The trigger runs inside the application’s transaction. If the application inserts an order, the trigger inserts a row into orders_audit, and both must commit for the transaction to succeed. This is the mechanism that guarantees no changes are missed, but it is also the source of the overhead. Every write to orders becomes two writes, and the audit table becomes a contention point under concurrent load .
The polling process reads new rows from the audit table and forwards them. The polling interval determines the latency, and the process must track its position to avoid re-processing or missing rows. This is the same problem that replication slots solve for log-based CDC, but the trigger approach must solve it manually.
The trigger approach is appropriate when logical replication is unavailable—managed databases that disable it, legacy databases without logical decoding support, or environments where the database user cannot be granted replication privileges . It is also appropriate when only a small number of tables need to be captured, because the overhead is localized to those tables.
c. Choosing Between Log-Based and Trigger-Based CDC
The decision comes down to four factors: whether log access is available, how much write overhead the source database can tolerate, whether deletes must be captured, and how much operational complexity the team can absorb.
Log access is the first gate. PostgreSQL requires wal_level = logical and a user with replication privileges. Managed services like AWS RDS support this with a parameter change and a reboot, but some environments disable it entirely . If logical replication is not available, the trigger approach is the only option.
Write overhead is the second gate. Log-based CDC adds no overhead to the application’s writes because it reads the WAL after the fact. Trigger-based CDC adds a write per change and contends for locks on the audit table. For high-throughput tables, the trigger overhead can be significant .
Delete capture is the third gate. Log-based CDC captures deletes natively through the transaction log. Trigger-based CDC captures them only if the trigger is configured for DELETE operations, and the before-image must be written to the audit table. Both can capture deletes, but the trigger approach requires explicit handling.
Operational complexity is the fourth gate. Log-based CDC with Debezium requires Kafka Connect, a Kafka cluster, and the operational knowledge to run both. The trigger approach requires only the database, but the polling process and the audit table become additional components to monitor and maintain. The trade-off is between infrastructure complexity and database overhead.
Complete Example Session
# ============================================
# PART 1: POSTGRESQL LOGICAL REPLICATION SETUP
# ============================================
# postgresql.conf
wal_level = logical
# Restart PostgreSQL
sudo systemctl restart postgresql
# ============================================
# PART 2: CREATE THE DEBEZIUM USER
# ============================================
CREATE USER debezium WITH REPLICATION LOGIN PASSWORD 'secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium;
# ============================================
# PART 3: CREATE THE PUBLICATION
# ============================================
CREATE PUBLICATION cdc_pub FOR TABLE orders, customers, products;
# ============================================
# PART 4: CONFIGURE THE DEBEZIUM CONNECTOR
# ============================================
# Kafka Connect connector configuration
{
"name": "inventory-connector",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "postgres",
"database.port": "5432",
"database.user": "debezium",
"database.password": "secret",
"database.dbname": "inventory",
"topic.prefix": "prod",
"plugin.name": "pgoutput",
"slot.name": "debezium_inventory",
"publication.name": "cdc_pub",
"snapshot.mode": "initial"
}
}
# ============================================
# PART 5: START THE CONNECTOR
# ============================================
curl -X POST http://localhost:8083/connectors \
-H "Content-Type: application/json" \
-d @connector.json
# ============================================
# PART 6: VERIFY THE STREAM
# ============================================
# Consume from the Kafka topic
kafka-console-consumer --bootstrap-server localhost:9092 \
--topic prod.public.orders --from-beginning
# ============================================
# PART 7: TRIGGER-BASED CDC SETUP
# ============================================
# Create the audit table and trigger function
CREATE TABLE orders_audit (...);
CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger AS $$ ... $$;
CREATE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();
# ============================================
# PART 8: POLL THE AUDIT TABLE
# ============================================
SELECT * FROM orders_audit
WHERE audit_id > :last_processed_id
ORDER BY audit_id;
# ============================================
# PART 9: ENABLE FULL BEFORE-IMAGE
# ============================================
ALTER TABLE orders REPLICA IDENTITY FULL;
# ============================================
# PART 10: MONITOR THE REPLICATION SLOT
# ============================================
SELECT slot_name, plugin, active, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name = 'debezium_inventory';The ten parts trace the log-based setup from the PostgreSQL configuration through the connector start and verification, then show the trigger-based alternative and the replication slot monitoring query.
Quick Reference
Log-Based vs. Trigger-Based CDC
| Aspect | Log-Based (Debezium) | Trigger-Based |
|---|---|---|
| Source load | None (reads WAL) | Write per change |
| Latency | Milliseconds | Poll interval |
| Captures deletes | Yes | Yes, if configured |
| Requires log access | Yes | No |
| Operational complexity | Kafka Connect + Kafka | Polling process + audit table |
| Lock contention | None on source tables | Audit table contention |
PostgreSQL Logical Decoding Plugins
| Plugin | Output | Availability |
|---|---|---|
pgoutput | Binary protocol | Built-in, PostgreSQL 10+ |
wal2json | JSON | Extension, often available on managed services |
test_decoding | Text | Built-in, for debugging only |
Debezium Connector Settings
| Setting | Purpose |
|---|---|
plugin.name | pgoutput for PostgreSQL 10+ |
slot.name | Replication slot identifier |
publication.name | Tables to capture |
snapshot.mode | initial, never, or initial_only |
topic.prefix | Namespace for Kafka topics |
Best Practices
✅ Do This:
-- Use pgoutput for PostgreSQL 10+ pipelines
"plugin.name": "pgoutput" -- ✅-- Create the publication explicitly to control scope
CREATE PUBLICATION cdc_pub FOR TABLE orders, customers; -- ✅-- Enable FULL replica identity only where before-image is needed
ALTER TABLE orders REPLICA IDENTITY FULL; -- ✅# Monitor replication slot lag
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
FROM pg_replication_slots; -- ✅❌ Don’t Do This:
-- Use wal2json for new pipelines
"plugin.name": "wal2json" # pgoutput is the standard -- ❌-- Enable REPLICA IDENTITY FULL on every table
# Increases WAL volume for tables that do not need it -- ❌-- Let the connector create the publication automatically
# Less control over captured tables -- ❌-- Use triggers on high-write tables without measuring overhead
# Every write becomes two writes -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Connector fails to start | wal_level not set to logical | Change config, restart PostgreSQL |
| No deletes in stream | REPLICA IDENTITY not set | Set FULL or use primary key |
| Replication slot fills disk | Consumer stopped, WAL retained | Monitor slot lag, drop unused slots |
| Trigger adds latency | Audit write inside transaction | Accept or use log-based CDC |
| Schema change not detected | No schema registry | Use Confluent Schema Registry or handle per-message |
Real-World Examples
1. Enable Logical Replication
ALTER SYSTEM SET wal_level = 'logical';
-- Restart required2. Create Publication
CREATE PUBLICATION cdc_pub FOR TABLE orders, customers;3. Debezium Connector Config
{
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"plugin.name": "pgoutput",
"slot.name": "debezium_slot",
"publication.name": "cdc_pub"
}4. Trigger Function
CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
INSERT INTO orders_audit (operation, row_id, new_data)
VALUES (TG_OP, NEW.id, to_jsonb(NEW));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;5. Attach Trigger
CREATE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();6. Poll Audit Table
SELECT * FROM orders_audit WHERE audit_id > :last_id;7. Monitor Slot Lag
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
FROM pg_replication_slots;8. Snapshot Mode
"snapshot.mode": "initial"9. Full Replica Identity
ALTER TABLE orders REPLICA IDENTITY FULL;10. Drop Unused Slot
SELECT pg_drop_replication_slot('old_slot');Visual
Log-Based CDC Architecture
┌──────────────────────────────────────────────────────────────┐
│ POSTGRESQL │
│ │
│ ┌─────────────────┐ │
│ │ Application │ INSERT/UPDATE/DELETE │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ WAL │ Write-Ahead Log │
│ │ (transaction │ Every committed change │
│ │ log) │ │
│ └────────┬────────┘ │
│ │ logical decoding │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Replication │ Tracks consumer position │
│ │ Slot │ Prevents WAL reclamation │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Debezium │ Reads WAL, produces events │
│ │ Connector │ │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Kafka Topic │ One message per row change │
│ └─────────────────┘ │
│ │
│ No queries against source tables. │
│ No write overhead. Millisecond latency. │
│ │
└──────────────────────────────────────────────────────────────┘Trigger-Based CDC Architecture
┌──────────────────────────────────────────────────────────────┐
│ POSTGRESQL │
│ │
│ ┌─────────────────┐ │
│ │ Application │ INSERT/UPDATE/DELETE │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Source Table │ │
│ └────────┬────────┘ │
│ │ AFTER trigger │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Audit Table │ Write per change │
│ │ (shadow table) │ Lock contention under load │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Polling Process │ Reads new audit rows │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Downstream │ │
│ └─────────────────┘ │
│ │
│ Every write becomes two writes. │
│ Trigger runs inside the application transaction. │
│ │
└──────────────────────────────────────────────────────────────┘The Replication Slot Mechanism
┌──────────────────────────────────────────────────────────────┐
│ WAL SEGMENTS │
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ LSN 100 │ │ LSN 200 │ │ LSN 300 │ │ LSN 400 │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ ▲ ▲ │
│ │ │ │
│ confirmed_flush_lsn current_lsn │
│ (consumer position) (latest write) │
│ │
│ Slot prevents reclamation of segments │
│ between confirmed_flush_lsn and current_lsn. │
│ │
│ If the consumer stops, WAL accumulates. │
│ Monitor slot lag to prevent disk exhaustion. │
│ │
└──────────────────────────────────────────────────────────────┘Decision Matrix
┌──────────────────────────────────────────────────────────────┐
│ LOG ACCESS AVAILABLE? │
│ │
│ ┌─────────────────┐ │
│ │ YES │────► Log-based (Debezium) │
│ │ │ No write overhead │
│ │ │ Captures deletes natively │
│ └─────────────────┘ │
│ │
│ ┌─────────────────┐ │
│ │ NO │────► Trigger-based │
│ │ │ Works on managed databases │
│ │ │ Write overhead per change │
│ └─────────────────┘ │
│ │
│ If log access is available, log-based is almost always │
│ the better choice. Triggers are the fallback. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Log-based CDC | Reads transaction log, no table queries |
| Debezium | Kafka Connect source connectors for log-based CDC |
| PostgreSQL plugin | pgoutput for PostgreSQL 10+ |
| Replication slot | Tracks consumer position, retains WAL |
| Publication | Defines tables to capture |
| Snapshot mode | initial for new pipelines |
| Trigger-based CDC | Audit table, write per change |
| REPLICA IDENTITY FULL | Includes full before-image |
| Slot lag monitoring | pg_wal_lsn_diff |
Key takeaways:
- Log-based CDC is the standard because it reads the WAL, not the tables. It captures every committed change with millisecond latency, adds no write overhead to the application, and captures deletes natively . Debezium is the open-source implementation that reads the WAL through PostgreSQL’s logical replication protocol and writes one Kafka message per row change.
pgoutputis the plugin to use on PostgreSQL 10+. It ships with PostgreSQL and requires no extension installation.wal2jsonis still encountered on managed services and older setups, but new pipelines should usepgoutput.- The replication slot is the bookkeeping mechanism. It tracks how far the consumer has read and prevents WAL segments from being reclaimed until they are acknowledged. This is what makes the stream resumable after a restart, and it is what causes disk to fill if the consumer stops .
- Trigger-based CDC adds a write to every transaction. The trigger runs inside the application’s transaction and writes to an audit table. This works on any database that supports triggers, but it adds latency and lock contention. It is the fallback when log access is unavailable .
REPLICA IDENTITY FULLcontrols the before-image. Without it, UPDATE and DELETE events carry only the primary key. With it, they carry the complete row. The cost is increased WAL volume, so it should be enabled selectively on tables where the before-image matters .
Remember: Change Data Capture is not a single mechanism; it is a choice between reading the log and writing to a shadow table. The log-based approach reads what the database already writes for crash recovery, which means it gets every change for free in terms of application overhead. The trigger-based approach asks the database to write the change twice, which means every application write pays for the CDC. The log-based approach requires access to the replication stream and infrastructure to consume it; the trigger approach requires only the database. For environments where logical replication is available, Debezium and pgoutput are the correct choice. For environments where it is not, triggers are the only option, and the overhead is the price of observability.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!