CDC vs Query Based Replication: When to Choose Each

The Core Trade Off: Log based CDC and query based replication both sync downstream systems, but they make fundamentally different trade offs between simplicity and scale. Query Based Replication: The simple approach is polling tables for changes. Every minute, run SELECT * FROM orders WHERE updated_at > last_poll_time. This works beautifully at small scale. If you have 100 writes per second and can tolerate 1 minute lag, query based replication is easier to understand, debug, and deploy. No special database permissions, no log parsing complexity. But it breaks down as writes grow. At 5,000 writes per second, you generate 300,000 changed rows per minute. Scanning that many rows every minute adds 5 to 15 seconds of query time, overloading the OLTP database. You also risk missing deletes (no updated_at column exists after deletion) and updates that happen twice in one polling window (you only see the final state, not intermediate changes). When Query Based Works: Systems with under 1,000 writes per second, where downstream lag of 1 to 5 minutes is acceptable, and where you do not need strict ordering or delete tracking. Think small SaaS products, internal admin dashboards, or batch analytics jobs. Log Based CDC Advantages: At 50,000 writes per second, log based CDC reads sequentially from the commit log without adding load to the query serving path. It captures every change in exact commit order, including deletes. Latency drops from minutes to under 1 second. The operational cost is that you need database level access to read logs and must handle schema evolution carefully. Decision Framework: Choose query based if you have fewer than 1,000 writes per second and can accept 1 to 5 minute lag. Choose log based CDC when you exceed 5,000 writes per second, need sub second freshness, or require strict ordering and delete tracking. The crossover point where CDC operational complexity becomes worthwhile is around 2,000 to 5,000 writes per second. Hybrid Approaches: Some teams use query based replication for low volume tables (configuration, user profiles) and log based CDC for high volume tables (events, orders). This balances simplicity where it works with efficiency where it is needed.