In Postgres logical replication, what does REPLICA IDENTITY control in a CDC stream's change events?

## Why the setting exists at all A log-based capture stream is a sequence of "this row changed" records. For an `INSERT`, the new row is enough. For an `UPDATE` or a `DELETE`, the record must also say *which* row — and, if anyone downstream cares, *what it used to look like*. Postgres does not write the whole previous tuple into the write-ahead log unconditionally; that would be expensive on every write in the database. Instead, each table carries a `REPLICA IDENTITY` setting that decides how much of the old row goes into the log. ```sql ALTER TABLE orders REPLICA IDENTITY FULL; ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_uniq_ext_id; ``` ## The four settings and what a consumer receives - **`DEFAULT`** — the old image contains the primary-key columns. A delete event is, in effect, a key. An update event carries the full new row plus a key-only before-image. - **`USING INDEX <name>`** — the old image contains the columns of a named unique index, which must be unique, not partial, and over `NOT NULL` columns. Useful when the natural downstream key is not the primary key. - **`FULL`** — the old image is the complete previous row, every column. - **`NOTHING`** — no old image is emitted at all. Updates and deletes become unidentifiable to a consumer. ## What breaks downstream at `DEFAULT` Most pipelines run happily on `DEFAULT`, because most sinks apply changes by key: an upsert on the new image, a delete on the key. The gap opens when a consumer needs the *old values*: - **Filtering deletes.** If the pipeline routes rows by tenant or region and a delete event carries only the primary key, there is nothing to route on. The consumer must look the row up in its own copy, which works only if it has one and it is current. - **Change detection and history.** "Which columns changed in this update?" and "what was the price before?" require the before-image. At `DEFAULT`, the answer is not in the stream. - **Transform predicates.** Any routing or masking rule evaluated on old values silently degrades to "key only" for deletes. The fix is `FULL` on that table specifically. Turning it on everywhere because one table needed it is a classic over-correction. ## What `FULL` costs `FULL` means every `UPDATE` and every `DELETE` writes the entire previous row into the WAL, in addition to the new row. On a wide table with a hot update path — a status column ticking over on a million-row table — this can be a large multiple of the WAL that table produced before, with knock-on cost in WAL write I/O, archiving, replication bandwidth to standbys, and the size of the backlog a stalled capture consumer retains. Measure the table's update rate and row width before enabling it, and prefer narrowing the table or having the consumer keep its own current copy when the volume is unacceptable. ## The table with no primary key This is the failure candidates most often meet in production. A table with `REPLICA IDENTITY DEFAULT` and no primary key has no way to identify an old row. Postgres does not silently emit a useless record: once the table is part of a publication that publishes updates or deletes, an `UPDATE` or `DELETE` against it fails outright with an error saying the table has no replica identity. Inserts still work. So the symptom is bizarre from the application's side — writes to a table that worked yesterday now fail, and the change that broke it was adding the table to the capture setup. The remedies, in order of preference: add a real primary key; add a unique, not-null index and point `REPLICA IDENTITY USING INDEX` at it; or, as a last resort on a low-write table, set `FULL`. ## Where the boundary sits This setting is a property of the *source database*, and the capture tool does not control it — no connector configuration can conjure a before-image that was never written to the log. Interviewers use it precisely because it separates candidates who have run log-based capture from those who have only read about it: the person who has operated it knows that fixing a missing before-image means a DDL change on the source, agreed with whoever owns that database, and paid for in WAL volume. The MySQL analogue is the binary-log row-image setting, which likewise decides whether the log records full before and after images or only the minimal columns; the shape of the tradeoff is the same even though the syntax is not.