Trade-Offs: Log-Based CDC vs Alternative Approaches

The Three CDC Approaches: There are three main ways to capture database changes, each with distinct trade offs. Understanding when to choose each is critical in system design interviews. When Log Based CDC Wins: Log based CDC is the right choice when you need high throughput, low latency, and completeness at scale. If your database handles over 10,000 writes per second and you have multiple downstream consumers (search, analytics, caching), log based CDC scales far better than alternatives. Query based polling becomes impractical because you would need to query hot tables every few seconds, adding massive read load. At 20,000 writes per second, polling every 5 seconds means scanning potentially 100,000 changed rows each time, creating lock contention and slowing down primary writes. Additionally, query based approaches cannot reliably capture deletes unless you use soft deletes or a separate deletion log table. Trigger based CDC adds synchronous work to every INSERT, UPDATE, and DELETE. On a table with 10,000 writes per second, triggers that write to shadow tables can increase write latency from 5 milliseconds to 7 or 8 milliseconds, a 40 to 60 percent increase. This latency directly impacts user facing requests. Triggers are also schema dependent: adding triggers to 200 tables across many microservices requires coordination and ongoing maintenance. When NOT to Use Log Based CDC: If your write volume is low (under 1,000 transactions per second) and you have only one or two consumers, log based CDC may be overkill. The operational complexity of setting up binlog access, running connector infrastructure, and managing Kafka or equivalent messaging is significant. In this scenario, query based CDC with a last_modified timestamp column is simpler. Poll the table every minute, select rows where last_modified is greater than your last processed timestamp, and handle deletes with a separate soft delete flag. Latency is 1 to 2 minutes instead of sub second, but if that is acceptable for your use case, you avoid the entire CDC infrastructure. Another case where log based CDC is challenging is when you do not have control over the database. If you are consuming from a third party managed database or a legacy system where enabling binlog replication is not permitted, you must fall back to query or trigger based approaches. Cross System Trade-Offs: Compared to native database replication, log based CDC gives you events with metadata (before/after values, transaction IDs, timestamps) rather than just a schema copy. Replicas are great for read scaling but not for building independent materialized views or integrating with search and analytics systems. Compared to application level event publishing (where your application emits events directly when writing to the database), log based CDC guarantees completeness. Application events can be missed if code paths forget to publish or if the publish fails. Log based CDC captures everything the database commits, even changes made by batch jobs, admin tools, or direct SQL scripts.