Introduction

Every time you save a row to a database, that database writes the change to a special append-only file before it touches the actual data pages. In PostgreSQL that file is called the write-ahead log (WAL); in MySQL it is the binary log. The log was invented for crash recovery, but it carries something valuable: an ordered, complete record of every INSERT, UPDATE and DELETE that ever happened.

Change Data Capture (CDC) is the technique of reading that log and turning each entry into an event that downstream systems can consume. Instead of polling a table every few seconds and guessing what changed, a CDC pipeline reads the log in real time — giving you exactly what changed, in what order, with no missed updates.

This sounds simple, but the details are where engineering lives: how do you start reading mid-stream without losing history? What happens when a schema changes? How do you handle transactions that span multiple tables? These questions push CDC from a tailing trick into a discipline of its own.

Try It

The demo below simulates a small database table. Click INSERT, UPDATE or DELETE to write a row, then watch the WAL stream on the right capture each change as an ordered event — exactly as a real CDC connector like Debezium would emit it.

<!-- Simulated DB table (left) + WAL event stream (right) -->
<div class="layout">
  <div class="panel left-panel">
    <h3>Database Table</h3>
    <table id="db-table">
      <thead><tr><th>id</th><th>name</th><th>email</th></tr></thead>
      <tbody id="table-body"></tbody>
    </table>
    <div class="form-row">
      <input id="inp-name" type="text" placeholder="Name" />
      <input id="inp-email" type="text" placeholder="Email" />
    </div>
    <div class="form-row">
      <button id="btn-insert" type="button">INSERT</button>
      <button id="btn-update" type="button" class="secondary" disabled>UPDATE</button>
      <button id="btn-delete" type="button" class="danger" disabled>DELETE</button>
      <button id="btn-reset" type="button" class="ghost">Reset</button>
    </div>
    <p class="hint">Click a row to select it, then UPDATE or DELETE it.</p>
  </div>
  <div class="panel right-panel">
    <h3>WAL Event Stream</h3>
    <div id="wal-stream" class="wal-stream"></div>
  </div>
</div>

/* Layout for CDC demo — two-panel, table left, event stream right */
* { box-sizing: border-box; margin: 0; padding: 0; }
body { font-family: system-ui, sans-serif; font-size: 14px; color: #1a1a2e; }
.layout { display: flex; gap: 12px; padding: 8px; min-height: 420px; }
.panel { flex: 1; display: flex; flex-direction: column; gap: 8px; }
h3 { font-size: .85rem; font-weight: 700; text-transform: uppercase;
     letter-spacing: .06em; color: #5a7088; }
table { width: 100%; border-collapse: collapse; }
th, td { padding: 5px 8px; border: 1px solid #dde3ea; font-size: .82rem; text-align: left; }
th { background: #eef2f7; font-weight: 600; }
tr.selected td { background: #dbeafe; }
tr { cursor: pointer; }
.form-row { display: flex; gap: 6px; flex-wrap: wrap; }
input { flex: 1; min-width: 90px; padding: 5px 8px; border: 1px solid #b0bec8; border-radius: 6px;
        font-size: .85rem; outline: none; }
input:focus { border-color: #3b82f6; }
button { font: 600 .8rem system-ui, sans-serif; padding: 5px 12px; border-radius: 6px;
         border: none; cursor: pointer; color: #fff; background: #1d7a4f; }
button.secondary { background: #2563eb; }
button.danger { background: #dc2626; }
button.ghost { background: transparent; border: 1px solid #b0bec8; color: #444; }
button:disabled { opacity: .4; cursor: default; }
.hint { font-size: .78rem; color: #888; }
.wal-stream { flex: 1; overflow-y: auto; display: flex; flex-direction: column; gap: 6px;
              max-height: 340px; }
.event { border-radius: 6px; padding: 6px 8px; font-size: .77rem; font-family: ui-monospace, monospace;
         line-height: 1.5; border-left: 3px solid; }
.event.INSERT { background: #dcfce7; border-color: #16a34a; }
.event.UPDATE { background: #dbeafe; border-color: #2563eb; }
.event.DELETE { background: #fee2e2; border-color: #dc2626; }
.event .op { font-weight: 700; font-size: .8rem; }
.event .ts { float: right; color: #999; font-size: .72rem; }

const i18nFmt = (s, v) => s.replace(/%(\w+)%/g, (_, k) => String(v[k] ?? ''));
// CDC demo — simulates a DB table and emits WAL-style change events

let rows = []; // in-memory table rows
let nextId = 1; // auto-increment primary key
let selectedId = null; // currently selected row id

// format wall-clock time as HH:MM:SS.mmm
function nowTs() {
  const d = new Date();
  return d.toTimeString().slice(0, 8) + '.' + String(d.getMilliseconds()).padStart(3, '0');
}

// Log Sequence Number — increments with every emitted event
let lsn = 0;
function nextLsn() { lsn++; return '0/' + String(lsn).padStart(8, '0'); }

// Re-render the table rows; highlight the selected row
function renderTable() {
  const tbody = document.getElementById('table-body');
  tbody.innerHTML = '';
  rows.forEach(r => {
    const tr = document.createElement('tr');
    if (r.id === selectedId) tr.className = 'selected';
    tr.innerHTML = `<td>${r.id}</td><td>${r.name}</td><td>${r.email}</td>`;
    tr.addEventListener('click', () => {
      selectedId = r.id;
      document.getElementById('inp-name').value = r.name;
      document.getElementById('inp-email').value = r.email;
      document.getElementById('btn-update').disabled = false;
      document.getElementById('btn-delete').disabled = false;
      renderTable();
    });
    tbody.appendChild(tr);
  });
}

// Append a WAL-style event card to the stream panel
function emitEvent(op, before, after) {
  const stream = document.getElementById('wal-stream');
  const div = document.createElement('div');
  div.className = 'event ' + op;
  const lsnVal = nextLsn();
  const ts = nowTs();
  let body = '';
  if (op === 'INSERT') {
    body = `<b>after:</b> {id:${after.id}, name:"${after.name}", email:"${after.email}"}`;
  } else if (op === 'UPDATE') {
    body = `<b>before:</b> {id:${before.id}, name:"${before.name}", email:"${before.email}"}<br>`
         + `<b>after:</b>  {id:${after.id}, name:"${after.name}", email:"${after.email}"}`;
  } else {
    body = `<b>before:</b> {id:${before.id}, name:"${before.name}", email:"${before.email}"}`;
  }
  div.innerHTML = `<span class="op">${op}</span><span class="ts">${lsnVal} @ ${ts}</span><br>${body}`;
  stream.appendChild(div);
  stream.scrollTop = stream.scrollHeight;
}

// INSERT handler — add a new row and emit an INSERT event
document.getElementById('btn-insert').addEventListener('click', () => {
  const name = document.getElementById('inp-name').value.trim();
  const email = document.getElementById('inp-email').value.trim();
  if (!name || !email) { alert('Please fill in both name and email.'); return; }
  const row = { id: nextId++, name, email };
  rows.push(row);
  emitEvent('INSERT', null, row);
  renderTable();
  document.getElementById('inp-name').value = '';
  document.getElementById('inp-email').value = '';
});

// UPDATE handler — modify the selected row and emit an UPDATE event
document.getElementById('btn-update').addEventListener('click', () => {
  const idx = rows.findIndex(r => r.id === selectedId);
  if (idx < 0) return;
  const name = document.getElementById('inp-name').value.trim();
  const email = document.getElementById('inp-email').value.trim();
  if (!name || !email) { alert('Please fill in both name and email.'); return; }
  const before = { ...rows[idx] };
  rows[idx] = { id: selectedId, name, email };
  emitEvent('UPDATE', before, rows[idx]);
  renderTable();
});

// DELETE handler — remove the selected row and emit a DELETE event
document.getElementById('btn-delete').addEventListener('click', () => {
  const idx = rows.findIndex(r => r.id === selectedId);
  if (idx < 0) return;
  const before = { ...rows[idx] };
  rows.splice(idx, 1);
  selectedId = null;
  document.getElementById('btn-update').disabled = true;
  document.getElementById('btn-delete').disabled = true;
  emitEvent('DELETE', before, null);
  renderTable();
});

// Reset — clear table and event stream
document.getElementById('btn-reset').addEventListener('click', () => {
  rows = []; nextId = 1; selectedId = null; lsn = 0;
  document.getElementById('btn-update').disabled = true;
  document.getElementById('btn-delete').disabled = true;
  document.getElementById('table-body').innerHTML = '';
  document.getElementById('wal-stream').innerHTML = '';
});

// Seed two initial rows so the demo is not empty on load
(function seed() {
  const demo = [
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' }
  ];
  demo.forEach(d => {
    const row = { id: nextId++, name: d.name, email: d.email };
    rows.push(row);
    emitEvent('INSERT', null, row);
  });
  renderTable();
})();
Result
Console

Notice that the stream records not just the new value but also the operation type and the before/after state of the row. This is what makes CDC useful: a downstream search index, cache, or analytics pipeline can apply each event precisely — no full-table scans needed.

The Real Complexity

Tailing a log sounds easy. In practice, several hard problems lurk inside:

  • Ordering and consistency. The WAL records commits in the order the database serialized them, not in wall-clock order. Multi-row transactions appear as a single atomic event only if the connector buffers until the COMMIT record arrives — adding latency and memory pressure.
  • Initial snapshot. Before you can tail the log you must take a consistent snapshot of the current table state. Doing this without locking the table while new writes arrive is a non-trivial problem that tools like Debezium solve with careful transaction-isolation tricks.
  • Schema evolution. If a column is renamed or a table is dropped, events already in flight reference the old schema. CDC connectors must carry schema metadata alongside each event — a technique popularized by the Kafka ecosystem's Schema Registry.
  • Exactly-once delivery. The log itself guarantees at-least-once: a connector crash and restart will re-read some events. Idempotent consumers or transactional sinks are required to avoid double-applying updates.
  • Log retention. The WAL is not infinite; databases prune it. A consumer that falls too far behind may find its position gone — a failure mode with no graceful fallback except a fresh snapshot.

Each of these problems has known solutions, but combining them correctly in production is where most CDC implementations get tripped up.

Where It Matters

Any system that needs to react to database changes without coupling tightly to the writer is a candidate for CDC:

  • Cache invalidation: instead of setting short TTLs or writing dual-write code that often misses edge cases, a CDC pipeline invalidates or refreshes cache entries the moment the row changes.
  • Search indexing: Elasticsearch and similar systems stay current by consuming CDC events rather than re-indexing entire tables on a schedule.
  • Microservice event buses: a service can publish the fact "an order was updated" by writing to its own database and letting CDC emit the event — no dual-write, no distributed transaction needed. This pattern is called the outbox pattern.
  • Analytics and data warehouses: streaming CDC events into a Kafka topic and on to a data warehouse gives near-real-time analytics without ETL batch jobs.
  • Audit logs: because the WAL records every change with a timestamp and transaction ID, CDC produces a tamper-evident audit trail for free.

Tools like Debezium (open source, JVM), AWS DMS, and Google Datastream have made CDC accessible enough that it is now standard infrastructure in event-driven architectures.

Conclusion

Every modern relational database has been silently keeping a perfect ordered log of every change since the day it was installed. Change Data Capture simply reads that log and lets the rest of your system listen in.

The idea is elegant: instead of building complex polling logic, dual-write patterns, or distributed transactions, you let the database do what it already does — write a log — and tap into that stream. The real engineering work lies in ordering, schema evolution, and delivery guarantees, but those are solved problems with mature tooling.

If your architecture involves syncing data between systems, maintaining a cache, or reacting to database events, CDC is almost always simpler and more reliable than the alternatives.

Share this article

Pick a channel — or use your device's native share sheet.

Comments

Loading comments...

https://www.kipuhub.com/en/article/change-data-capture/Content licensed under CC BY-NC 4.0.