Trigger-Based Change Data Capture (CDC): Deep Dive

Introduction

Trigger-Based Change Data Capture (CDC) leverages database triggers to intercept and record data mutations (INSERT, UPDATE, DELETE) into a side or "audit" table. It provides deterministic, schema-level control over captured changes.

Trigger CDC is useful when:

Transaction logs are inaccessible.
Fine-grained control or custom transformation is required.
Legacy systems prevent log-based access.

However, it introduces operational overhead and potential performance degradation if not carefully implemented.

Architecture Overview

At a high level, the architecture of trigger-based CDC looks like this:

flowchart TD
  A[Primary Table] -->|Insert/Update/Delete| B[Trigger]
  B --> C[Audit Table]
  C --> D[CDC Processor / Streamer]
  D --> E[Downstream Systems]

Trigger: Executes immediately after DML.
Audit Table: Stores captured mutations with metadata.
CDC Processor: Reads audit logs and propagates changes.

Trigger Implementation Patterns

Triggers must be defined AFTER DML operations to ensure they capture committed data. Typical patterns include:

Insert Capture

CREATE TRIGGER trg_customers_insert
AFTER INSERT ON customers
FOR EACH ROW
INSERT INTO customers_cdc (operation, primary_key, data, changed_at)
VALUES ('INSERT', NEW.id, row_to_json(NEW), now());

Update Capture

CREATE TRIGGER trg_customers_update
AFTER UPDATE ON customers
FOR EACH ROW
INSERT INTO customers_cdc (operation, primary_key, data, changed_at)
VALUES ('UPDATE', NEW.id, row_to_json(NEW), now());

Delete Capture

CREATE TRIGGER trg_customers_delete
AFTER DELETE ON customers
FOR EACH ROW
INSERT INTO customers_cdc (operation, primary_key, data, changed_at)
VALUES ('DELETE', OLD.id, row_to_json(OLD), now());

Note: It is crucial to capture OLD row data on DELETE operations.

Audit Table Schema

Example schema for the audit (CDC) table:

CREATE TABLE customers_cdc (
    id BIGSERIAL PRIMARY KEY,
    operation VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    primary_key UUID NOT NULL,
    data JSONB NOT NULL,
    changed_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

operation: Change type.
primary_key: Identifier for the affected row.
data: Full snapshot of the row after change.
changed_at: Capture timestamp.

Transaction Handling

Because triggers execute inside the transaction that mutates the original table, audit entries are automatically rolled back if the parent transaction fails.

sequenceDiagram
  participant Client
  participant Database
  participant Trigger
  participant AuditTable

  Client->>Database: Begin Transaction
  Database->>Database: Execute INSERT/UPDATE/DELETE
  Database->>Trigger: Fire Trigger
  Trigger->>AuditTable: Insert audit log
  Database->>Client: Commit/Rollback

On COMMIT: Both primary and audit tables persist changes.
On ROLLBACK: No changes remain.

Performance Implications

Advantages:

Exact capture at point-of-change.
Schema and business-logic aware.
Simple to implement for low-throughput workloads.

Disadvantages:

Additional I/O per write operation.
Increased transaction times.
Potential deadlock scenarios if audit table locking isn't managed.

Optimization Tips:

Keep trigger logic minimal.
Use asynchronous CDC processing downstream.
Partition audit tables by time (e.g., monthly) for performance.

Failure Scenarios

Scenario	Description	Mitigation
Trigger Logic Failure	Misconfigured trigger causes failures	Rigorous testing and version control of DDL
Audit Table Growth	Unbounded audit table size	TTL cleanup jobs or archival pipelines
Deadlocks	Concurrent writes to audit table causing deadlocks	Use `NOWAIT` insert modes or sharded audit tables
Backpressure	Downstream systems cannot consume fast enough	Implement retry and backoff mechanisms at the CDC processor level

Advanced Pattern: Multi-Table Trigger Consolidation

Instead of one audit table per source table, use a unified audit log:

CREATE TABLE cdc_unified_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation VARCHAR(10) NOT NULL,
    primary_key UUID NOT NULL,
    data JSONB NOT NULL,
    changed_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

Each trigger includes the table_name, allowing a single CDC processor to handle multiple sources.

flowchart LR
  A[Table A Trigger] --> C[Unified Audit Log]
  B[Table B Trigger] --> C
  C --> D[Single CDC Processor]

Conclusion

Trigger-based CDC is powerful but must be treated as production-grade critical infrastructure. It offers complete control over change capture at the database layer, but carries risks if poorly implemented. Production systems must:

Minimize trigger complexity.
Monitor audit table growth.
Ensure transactional correctness.

In scenarios where access to transaction logs is unavailable, or fine-grained change semantics are required, trigger-based CDC remains a robust alternative.