Change data capture (CDC) in Postgres, explained

Change data capture (CDC) is the practice of recording every insert, update and delete a database commits, in order, as a stream that other systems can read. In Postgres it is usually built on logical decoding of the write-ahead log, so it sees changes made by triggers and cascades as well as the statement that started them.

Three ways to capture changes

Query-based. A job polls each table for rows whose updated_at moved since the last run. It is easy and needs no database settings, but it only sees the latest state of a row, misses rows deleted in between, and depends on every writer setting updated_at.

Trigger-based. A trigger on each table copies the old and new row into an audit table, inside the same transaction as the change. It sees every row and works on any Postgres. It also adds work to every write, needs a trigger per table, and can be dropped or disabled by a role that owns the table, which may be the role the agent uses.

Log-based. Postgres already writes every change to its write-ahead log before it commits. Logical decoding is “the process of extracting all persistent changes to a database’s tables into a coherent, easy to understand format”.¹ A replication slot keeps the reader’s place, and an output plugin formats the rows. It adds no trigger to your tables, sees changes in commit order, and can’t be skipped by the writer: a row that committed is in the log. It needs wal_level = logical and a slot, which has its own care and feeding; see replication slot.

Why it matters for AI agents

An agent’s tool reports what its statement touched. CDC reports what the database committed. When those two disagree, the database is right: a one-row UPDATE that fires a trigger shows up in the stream as every row the trigger changed, in the same transaction. That makes the change stream the natural thing to check an agent against, and the natural source for undo, because it holds each changed row’s values before and after.

What CDC doesn’t tell you

Schema changes. Logical replication doesn’t carry the database schema or DDL commands,² so a DROP TABLE or ALTER TABLE isn’t in the row stream. Block those with a rule instead.
Sequences and large objects. Sequence data and large objects aren’t replicated either.²
Anything outside the database. An email the agent sent or a file it wrote leaves no trace in Postgres’s log.
Who caused it. The stream says which transaction changed which rows, not which agent or request. Attribution takes something written in the same transaction, such as a row in a ledger table or a transactional message from pg_logical_emit_message, which becomes part of the current transaction.³
Old values, by default. What an update or delete carries about the old row depends on the table’s replica identity; see replica identity.

An example

A logical slot on PostgreSQL 18.6 (test_decoding), after one UPDATE and one DELETEtext

shop=# SELECT slot_name FROM pg_create_logical_replication_slot('cdc_demo', 'test_decoding');
shop=# UPDATE public.orders SET status = 'shipped' WHERE id = 42;
UPDATE 1
shop=# DELETE FROM public.orders WHERE id = 43;
DELETE 1
shop=# SELECT data FROM pg_logical_slot_get_changes('cdc_demo', NULL, NULL);
 BEGIN 755
 table public.orders: UPDATE: id[integer]:42 status[text]:'shipped'
 COMMIT 755
 BEGIN 756
 table public.orders: DELETE: id[integer]:43
 COMMIT 756

How Deedglass uses it

Deedglass's observe mode is change data capture for agent sessions: it reads only Postgres's change log, through the deedglass_witness role and a replication slot, beside any gate you already use.⁴ A watched session gets no Matches or Doesn't match verdict, only what Postgres committed,⁵ and deedglass observe show prints undo SQL for it without running it.⁵

Change data capture

Three ways to capture changes

Why it matters for AI agents

What CDC doesn’t tell you

An example

How Deedglass uses it

Related on this site

Terms

Comparisons

Posts

In the docs

Sources