Change data capture (CDC) is the practice of identifying and capturing every insert, update, and delete made to a database so downstream systems — warehouses, caches, search indexes, event streams, or application inboxes — can react to those changes without polling the source. When teams evaluate CDC tooling, two architectural approaches dominate the conversation: log-based CDC, which reads changes from the database's native transaction log (the redo log in Oracle, the write-ahead log in PostgreSQL, the binary log in MySQL), and trigger-based CDC, which installs database triggers on each monitored table that write a row into an audit or shadow table whenever data changes. The short answer for most modern deployments is that log-based CDC wins on performance, scalability, and source-system safety, while trigger-based CDC survives as a pragmatic option for legacy databases, managed platforms that block log access, or small workloads where operational simplicity matters more than throughput.
What Log-Based CDC Actually Does
Also worth reading: Streaming vs batch ingestion: which data pipeline approach should your team choose in 2026? · customer feedback inbox vs survey tool: which approach actually captures actionable product signals? · What is customer signal retention in SaaS and how does userhero.io help product teams capture it?
Every serious relational database already records every committed change in a sequential transaction log before it touches the actual data files. This log exists for crash recovery and replication; log-based CDC simply attaches a reader to it. Tools such as Debezium, AWS DMS, Oracle GoldenGate, and Fivetran parse these logs, decode row images, and emit change events in near real time — typically within one to five seconds of commit, depending on log flush settings and reader lag.
The defining property of this approach is that the capture process runs outside the transaction path of your application. The database engine writes its log exactly as it always would; the CDC connector tails it asynchronously. A busy OLTP database doing 50,000 transactions per second sees essentially zero additional write latency from a healthy log reader, because the log was being written anyway. Readers can also replay history: because logs are retained (PostgreSQL WAL segments, MySQL binlogs with a configured expiry, typically 1–7 days by default), a new consumer can start from a saved checkpoint or snapshot position and reconstruct changes it missed. AWS documents this pattern explicitly in its incremental load guidance using DMS checkpoints against database logs.
The trade-offs are real. You need privileged access to the log — often requiring superuser roles, enabling logical decoding (in PostgreSQL, setting wal_level=logical, which forces full-page writes and increases WAL volume by roughly 20–40% under heavy update churn), or licensing vendor-specific log access. Log retention becomes an operational contract: if a consumer falls behind past retention, you lose changes and must resync from a fresh snapshot. And parsing proprietary log formats means connectors are version-sensitive; a major database upgrade can break a decoder until the CDC tool ships support.
What Trigger-Based CDC Actually Does
Trigger-based CDC takes a different route: it modifies the schema behavior itself. For every table you want to monitor, you create AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers. Each fired trigger writes the changed row — sometimes the whole image, sometimes just keys plus operation type — into a shadow or audit table within the same transaction. A separate poller or streamer then reads that audit table and publishes events downstream.
This approach has genuine advantages. It works anywhere triggers work, including managed databases and legacy systems where you cannot touch server configuration or read logs. It captures changes synchronously and transactionally: if the trigger write fails, the business transaction rolls back, so you never get phantom events. It is also dead simple to reason about — the audit table is just another table you can query with plain SQL, join against business data, and inspect during debugging. Many ERP and CRM integrations built in the 2000s and 2010s still run this way, and SAP-centric shops frequently pair trigger-fed staging tables with external pipelines when they lack BTP middleware.
The costs show up at scale. Triggers execute inside the application's transaction, adding synchronous latency to every write. Benchmarks across common engines generally show 15–60% write-throughput degradation on heavily updated tables with three triggers per table, and worse on high-concurrency hot rows due to contention on the audit table. Audit tables grow unboundedly — a table taking 10 million updates per day generates 10 million audit rows per day unless you prune aggressively. Trigger logic is also easy to break silently: a bulk load that disables triggers, a migration that recreates a table without re-adding them, or an ORM bulk-update path that bypasses them will cause silent data loss downstream, often discovered weeks later during reconciliation.
Head-to-Head Comparison
| Feature | Log-Based CDC | Trigger-Based CDC |
|---|---|---|
| Write overhead on source | Near zero (~1–3%); log written anyway | 15–60% write slowdown; runs in-transaction |
| Latency to downstream | Sub-second to ~5 seconds | Poll interval dependent (often 30s–5min) |
| Schema setup | Enable logging/decoding; no table changes | Triggers + audit table per monitored table |
| Captures TRUNCATE/bulk ops | Usually yes (engine dependent) | Often missed; triggers skipped by many bulk paths |
| Historical replay | Yes, via log position checkpoints | Only as far back as audit table retention |
| Access requirements | Superuser/log privileges; config changes | Ordinary DDL rights |
| Managed DB compatibility | Varies (RDS supports logical replication on most engines) | Works almost everywhere |
| Failure mode | Lag past retention = lost changes, needs resnapshot | Silent loss if triggers disabled/dropped |
| Operational complexity | Moderate–high (connector tuning, monitoring lag) | Low initially, grows with table count |
Performance and Scale Thresholds That Matter
Concrete numbers help make the decision less abstract. Below roughly 100 writes per second on any given table, the absolute cost of trigger overhead is trivial — a few milliseconds per transaction that users will never notice. Between 100 and 1,000 writes per second, trigger overhead starts appearing in p99 write latency, and audit-table bloat becomes a weekly maintenance task. Above roughly 1,000–2,000 writes per second sustained, trigger-based designs routinely fall over or require sharded audit tables, and log-based CDC should be treated as the default choice.
On the log side, watch two numbers. First, WAL/binlog generation rate: enabling logical decoding on PostgreSQL roughly doubles WAL volume for update-heavy workloads because logical slots force more aggressive retention; budget disk accordingly. Second, replication slot lag: an idle or crashed consumer holding a slot prevents log truncation entirely, which on a busy database can fill a disk within hours. Teams running Debezium in production commonly alert when slot lag exceeds 1 GB or consumer lag exceeds 60 seconds. Trigger-based setups have analogous thresholds — audit tables exceeding 50–100 million rows usually signal that pruning jobs are failing and query plans on the poller will degrade soon.
Practical Implementation Steps
For log-based CDC, the sequence looks like this. Inventory the tables you actually need — teams routinely over-scope and monitor 500 tables when 40 drive real decisions. Verify log prerequisites: on PostgreSQL set wal_level=logical and max_replication_slots appropriately; on MySQL enable row-based binlog format (binlog_format=ROW) since statement-based logs cannot be decoded into row images; on SQL Server enable CDC or use change tracking depending on edition. Take a consistent snapshot as your baseline — most tools handle this automatically by starting a transaction, exporting a snapshot position, then streaming from that exact point, guaranteeing no gap and no duplication between initial load and tailing. Deploy the connector with named checkpoints persisted durably, monitor lag continuously, and test failure recovery by killing the consumer and verifying it resumes from its last committed offset.
For trigger-based CDC, generate triggers programmatically from information_schema rather than hand-writing them, include a sequence number and captured timestamp in every audit row, add a composite index on (sequence_id) for the poller, and build the pruning job before launch, not after the first capacity incident. Critically, add a reconciliation job that compares row counts and checksums between source and destination daily — this is the only reliable detector for the silent-loss failure modes inherent to triggers.
Where Each Approach Fits Real Architectures
The right framing is not 'which is best' but 'which fits this system.' Log-based CDC is the standard for feeding cloud warehouses and lakehouses — Snowflake's expanded Oracle Database integration announced in 2024, Databricks' ingestion patterns, and DataStax adding CDC to Astra DB all reflect the industry consolidating around log reading as the default ingestion mechanism. It is also the backbone of real-time operational use cases: keeping a search index current, invalidating caches, powering event-driven microservices, and driving customer-signal pipelines where a support ticket, a plan upgrade, or a usage spike in an operational database must appear in a product or success team's inbox within seconds rather than overnight batch windows.
Trigger-based CDC remains defensible in four situations: databases where log access is contractually or technically blocked; legacy versions whose log formats lack stable decoders; low-volume tables inside otherwise batch-oriented estates where a nightly or hourly poll of an audit table is genuinely sufficient; and environments where transactional guarantees on capture matter more than throughput — financial audit trails, for example, where you want the event recorded atomically with the business change even at the cost of write latency.
A hybrid pattern is also common and worth naming: log-based CDC for the high-volume core tables, triggers on a handful of exotic legacy tables that no connector supports, both converging into the same event bus. This is pragmatism, not indecision.
Common Mistakes and How to Avoid Them
The most frequent log-based mistakes: treating log retention as infinite and discovering lost changes after a weekend outage; forgetting that schema changes (ALTER TABLE) need handling — most frameworks emit schema-change events, but consumers that ignore them deserialize garbage until fixed; and ignoring idempotency, because log readers can redeliver events after a crash, so downstream sinks must deduplicate on primary key plus version rather than assuming exactly-once delivery.
The most frequent trigger-based mistakes: shipping triggers without pruning jobs (audit tables hitting hundreds of millions of rows within months); relying on triggers to catch bulk ETL loads, which many engines skip or which developers disable for speed ('DISABLE TRIGGER ALL' during migrations is a classic silent killer); creating triggers that raise exceptions on edge cases and thereby breaking production transactions; and never testing what happens when the audit table's own storage fills up, which fails the business transaction too.
A mistake spanning both approaches: building CDC without a defined consumer contract. Decide up front whether consumers need full row images, before-and-after images, or keys-only, and whether deletes must be distinguishable from updates. Retrofitting this later forces a full resync.
Cost Considerations
Open-source log-based CDC (Debezium, or PGLogical/Bucardo variants) carries infrastructure cost — typically one to three small connector instances, $100–$400/month on cloud VMs — plus engineering time for setup and monitoring, realistically two to six engineer-weeks for a first production deployment. Managed offerings price differently: Fivetran and similar ELT vendors charge by monthly active rows, commonly $300–$5,000+/month at mid-market volumes; AWS DMS charges for replication instances ($0.05–$3+/hour depending on size) plus transfer; GoldenGate carries significant licensing. Trigger-based CDC has near-zero software cost — it is just SQL — but pays in engineering maintenance and in the hidden tax of degraded write performance on production hardware. For a B2B SaaS product whose value depends on surfacing customer signals quickly, the sub-second freshness of log-based CDC usually justifies its higher setup cost within the first quarter of operation.
Decision Checklist: When to Act
Choose log-based CDC when write volume exceeds a few hundred operations per second, when downstream consumers need latency under a minute, when you need historical replay or reliable recovery from outages, when the database version has mature log-decoding support, and when you have (or can obtain) the privileges to enable logical logging. Choose trigger-based CDC when the source is legacy or locked down, when change volume is modest (under ~100 writes/second per table), when transactional capture guarantees outweigh performance concerns, or when you need something working this week with nothing but SQL. If you are currently polling source tables on a schedule — comparing timestamps or updated_at columns — either approach is an upgrade, but note that timestamp polling misses hard deletes entirely, a gap both CDC methods close. Act before your warehouse batch windows start slipping or before stakeholders ask why yesterday's customer activity shows up today; retrofitting CDC into an architecture that assumed nightly batches takes longer than designing it in from the start.