Keep track of what is changing on Your DB — Part 1

You have an operational Postgres database. Rows are being inserted, updated and deleted all day. Something downstream — a search index, a warehouse, a dashboard, another service — needs to know about those changes in near real time.

The obvious approach is to ask repeatedly:

SELECT * FROM customer;
SELECT * FROM customer;
SELECT * FROM customer;
...

This works, and it is what a lot of systems still do. It is also the wrong shape for the problem. Polling puts constant read load on the database that scales with how fresh you want the data to be, it cannot see anything that happened and was reverted between two polls, and it has no way to tell you that a row was deleted — the row is simply gone, and absence is not an event.

Change data capture inverts it. Instead of asking the database what changed, you read the log it already writes.

The code for this post is at anmolgarg848/postgres-cdc-pipeline — a local stack you can bring up with make up.

The write-ahead log

Postgres already records every change before it touches the data files. That is the write-ahead log, and it exists for durability: if the server dies mid-transaction, the WAL is what lets it recover to a consistent state on restart.

PostgreSQL
    ↓
  INSERT / UPDATE / DELETE
    ↓
   WAL

The important realisation is that this log is a complete, ordered record of everything that happened. It was built for crash recovery, but with logical decoding turned on it can also be read as a change stream. Nothing extra needs to be written. The information is already there.

That is the whole idea. CDC is not an additional system the database has to feed — it is a second reader of a log the database was writing anyway.

The replication slot

If you are going to read the WAL, you need two things: a position to read from, and a guarantee that the segments you have not read yet still exist. Postgres recycles WAL aggressively once it is no longer needed for recovery.

A replication slot provides both. It is a durable bookmark:

PostgreSQL
     ↓
    WAL
     ↓
Replication Slot
     ↓
  Debezium

Concretely, the slot records how far a consumer has confirmed it has read, by LSN — the log sequence number that identifies a position in the WAL:

WAL

LSN 100 → INSERT customer 101
LSN 200 → UPDATE customer 101
LSN 300 → DELETE customer 205
                    ↑
              Debezium has consumed
              through here

Everything after that mark is retained until the consumer confirms it. This is what makes the stream survive a restart: Debezium can go away, come back, and resume exactly where it left off, with nothing lost in between.

It is also the sharpest edge in the whole system. The retention guarantee is unconditional. A slot with no consumer attached does not expire, time out, or give up — it holds WAL indefinitely, waiting. Leave a slot behind after tearing down a connector and Postgres will keep accumulating WAL segments until the disk fills. On a laptop that is a nuisance. On a production database it is an outage, and the cause is not obvious from the symptom.

Worth knowing how to look:

SELECT slot_name,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
  FROM pg_replication_slots;

If active is f and retained_wal is growing, you have found your problem.

Logical decoding

The WAL is a physical record — it describes changes in terms of pages and tuples, which is the right representation for crash recovery and the wrong one for anything downstream. Logical decoding is the step that converts it into logical change events: this row, in this table, changed from this to that.

WAL
 ↓
Logical decoding  (pgoutput)
 ↓
Debezium
 ↓
Kafka

The conversion is done by an output plugin. Postgres ships with pgoutput, which is what its own logical replication uses, and it is the sensible default — no extension to install, maintained as part of the database itself. Older setups often used wal2json or decoderbufs instead.

This is what you are seeing when the connector starts up and logs:

CREATE_REPLICATION_SLOT "debezium" LOGICAL pgoutput

That single line is the connector creating its bookmark and declaring which plugin will translate the log for it.

What Debezium actually publishes

Here is where a common assumption gets in the way. It is tempting to imagine Debezium emitting something like:

city changed from Delhi → Mumbai

It does not. The native event is a row-level change with the full before and after state:

{
  "before": {
    "id": 101,
    "name": "Anmol",
    "city": "Delhi",
    "balance": 5000
  },
  "after": {
    "id": 101,
    "name": "Anmol",
    "city": "Mumbai",
    "balance": 5000
  },
  "op": "u"
}

op is c for create, u for update, d for delete, and r for a row read during the initial snapshot.

Row-level rather than column-level is a deliberate choice, and the right one. The event describes what the row was and what it became; deriving anything narrower from that is easy, while the reverse is not. A column-level event would have thrown away the context that lets a consumer decide what matters to it.

Getting to the column that changed

So if you want this:

changed_columns = ["city"]

you compute it from what you were given:

before.city != after.city
             ↓
        city changed

Which in a consumer is just a comparison across the two maps — the approach tail_changes.py takes in the repo. Debezium also ships an Event Changes SMT that will do this in the pipeline and expose changed and unchanged field lists on the event itself, if you would rather not push that logic into every consumer.

There is a catch, and it is the second thing that bites people. By default Postgres only logs the primary key for updates and deletes, so before arrives almost entirely null and the comparison above finds nothing. The fix is at the table level:

ALTER TABLE customer REPLICA IDENTITY FULL;

That makes Postgres write every column into the WAL for updates and deletes, which is what makes before-images usable. It costs WAL volume, which is a real trade rather than a free win — but without it, column-level change detection quietly does not work.

The whole model

                 PostgreSQL
                     │
              INSERT / UPDATE / DELETE
                     │
                     ▼
                    WAL
                     │
                     ▼
             Replication Slot
                "debezium"
                     │
                     ▼
               Logical decoding
                   pgoutput
                     │
                     ▼
                  Debezium
                     │
              Row-level event
                     │
                     ▼
                    Kafka
                     │
          ┌──────────┼──────────┐
          ▼          ▼          ▼
        Spark      Flink      ksqlDB
          │
          ▼
   Column-level detection
   / Event Changes SMT
          │
          ▼
      "city changed"

Five sentences, if you need it compressed:

The WAL is where Postgres records changes. The replication slot tracks a consumer’s position in it and holds the log until that consumer catches up. Logical decoding turns physical WAL records into logical change events. Debezium publishes those events to Kafka. Column-level changes are derived from the before and after images, or exposed with the Event Changes SMT.

Next

Part 2 gets the stack running — Postgres with wal_level=logical, Debezium registered against a publication, and change events landing on Kafka topics — and covers what goes wrong on the way there.