Change Data Capture

You’re in a system design interview and you need to keep two data stores in sync. Maybe your primary database needs to feed a search index. Or perhaps you’re moving data into a warehouse to run offline analytics.

The answer here is usually change data capture (CDC), which works by reading changes from the database’s replication log and propagating them to downstream services. Often, CDC is exactly the right tool. But candidates also tend to reach for it in situations where CDC isn’t the best fit and an alternative would serve them better.

CDC works best when consumers just need a copy of your data. But it can start to fall apart when they need to know why that data changed. In this article, we’ll build a simple test you can use in an interview to decide when CDC is the right choice, when it creates subtle correctness problems, and what to use instead.

Suppose your product catalog lives in Postgres, but you use Elasticsearch to power product search across your app. Postgres is the source of truth, but every time a product is created, updated, or deleted, you need that change reflected in Elasticsearch too.

One option is to have the application write to both systems so that when a product changes, the application updates Postgres and Elasticsearch at the same time. This seems simple enough, but if one write succeeds while the other fails, the two systems are now out of sync. You can retry the failed write, but now you need retry logic, idempotency, and some way to recover if the application crashes between the two operations.