In row-level logical replication, how does the receiving database find the row to change when it receives an UPDATE or DELETE event, and what goes wrong when the source table has no primary key?
answer
- Event = identity (which row) + new values
- Identity modes: default PK / designated unique index / full / nothing
- NOTHING → source errors on UPDATE and DELETE
- FULL → whole old row logged + scan per event on subscriber
- Subscriber needs the matching index too, not just the source
basics
~20 sThe change event carries an identifying value set — the row's replica identity, by default its primary key — and the receiver looks the row up by it. With no primary key, either the operation is rejected outright, or the full old row must be logged and matched column-by-column, forcing a scan per event and collapsing throughput.
solid answer
~60 sA logical UPDATE/DELETE event has two parts: **identity** (which row) and **new values** (what it becomes). The identity is defined by the table's **replica identity**: - **default** — the primary key columns. The receiver does one index lookup per event. This is the intended path. - **a designated unique index** — a not-null unique index used when there is no primary key. - **full** — the entire old row is written into the log and shipped; the receiver matches on every column. - **nothing** — no identity is logged; UPDATE and DELETE cannot be replicated at all. With no primary key you get one of two bad outcomes. Either the source refuses the operation — PostgreSQL raises *"cannot update table ... because it does not have a replica identity"* — or you set identity to full, and then: the log grows (every old row value shipped), and the receiver has no key to probe with, so it **scans the table once per row event**. A 100k-row batch becomes 100k scans and replication lag goes from milliseconds to hours. The receiver also needs a matching index; identity on the source alone is not enough.
code
text · 9 linesWith PRIMARY KEY (id) -- replica identity = default
event: DELETE public.orders identity{ id: 42 }
subscriber: 1 index probe -> microseconds
No key, REPLICA IDENTITY FULL
event: DELETE public.orders identity{ id:42, customer:..., status:...,
created_at:..., total:..., notes:... }
subscriber: sequential scan matching all columns -> O(table) per row event
100,000 deleted rows = 100,000 table scansgo deeper
Know that the change event carries the row's key so the receiver can find the row, and that tables without a primary key cause problems.
Name the identity modes and explain why FULL means scanning per event and larger log volume.
Add the diagnosis path and the subscriber-side index requirement, and connect keyless tables to runaway apply lag in production.
Turn it into policy: primary keys mandatory on any replicated table, index parity enforced between publisher and subscriber, and FULL identity permitted only by exception on small tables.
## Why identity is needed at all Physical replication never asks "which row?" — it replays a page-level edit at a known file offset. Logical replication throws that addressing away deliberately, because page addresses are meaningless on a target with different storage, a different version, or different indexes. What it sends instead is a *description of the change in data terms*, and that description must contain enough information for the receiver to locate the affected row in its own copy. An INSERT is easy — it carries the whole new row and nothing needs finding. **UPDATE and DELETE are the hard cases**, because they must say *which* existing row. That identifying information is the table's **replica identity**. ## The identity settings Conceptually there are four modes, and PostgreSQL names them directly: **DEFAULT — primary key.** The log records the primary-key columns of the old row. The subscriber does an index lookup on its own primary key and applies the change. Cost: one probe per event. This is what a healthy setup looks like, and it is why "every replicated table needs a primary key" is a rule of thumb. **USING INDEX — a designated unique index.** For tables with a natural unique, not-null key but no declared primary key. Same cost profile as default, provided the subscriber has the same index. **FULL — the entire old row.** Every column of the pre-image is written into the log. The subscriber, having no key, matches rows by comparing all columns. Two costs stack here, and both are severe: - **Write amplification on the source.** Every UPDATE and DELETE now logs a complete old row in addition to the new values. On a wide table this can multiply log volume several times over, which in turn inflates replication bandwidth, log retention and backup size. - **Sequential scan per event on the subscriber.** With no index to probe, locating the matching row means scanning. One event is a table scan; ten thousand events are ten thousand table scans. On a large table this is a throughput collapse, not a slowdown — apply rates drop by orders of magnitude and lag grows without bound. **NOTHING — no identity logged.** UPDATE and DELETE simply cannot be replicated. PostgreSQL surfaces this as an error on the *source* at write time: `ERROR: cannot update table "t" because it does not have a replica identity and publishes updates`. That is deliberate — it fails loudly at the moment the un-replicatable change is attempted, rather than silently diverging the target. ## Duplicates: the deeper problem FULL identity has a correctness wrinkle beyond performance. If two rows are byte-identical — legal in a table with no key — an UPDATE or DELETE of "one of them" is ambiguous. Implementations typically apply the change to a single matching row, which keeps counts consistent but means the target's notion of *which* duplicate changed is arbitrary. In practice this is tolerable only because duplicate-row tables are themselves a modelling defect. ## The subscriber side is half the story A point candidates routinely miss: **replica identity is configured on the source, but the lookup happens on the subscriber.** The subscriber must have an index that supports the identity columns. Two failure modes follow: - The source has a primary key; the subscriber's copy of the table was created without one (a hand-built reporting target, say). The subscriber falls back to scanning even though identity is set correctly — the source's configuration looks fine while apply crawls. - Someone "optimises" a reporting subscriber by dropping indexes it does not query. If the dropped index is the identity index, apply throughput collapses immediately. So the operational rule is: **identity on the source, matching index on the subscriber, verified together.** ## MySQL's version of the same problem The vocabulary differs but the physics do not. With row-based binary logging, a row event carries a before-image and an after-image. The applier searches for the matching row using, in order of preference, the primary key, then any usable unique or secondary index, then a full scan. Keyless tables under row-based replication are one of the most notorious causes of runaway replica lag, for exactly the reason above: per-row scanning. Some deployments also historically shrank the logged before-image to reduce log volume, which trades bandwidth for a weaker ability to locate rows. ## Diagnosing it in the field Symptom: apply lag climbs steeply during batch updates or deletes on one particular table, while the network and the receive side are idle. The applier process is pegged on CPU or I/O against a single table, and the applied position barely advances. Check, in order: does the table have a primary key on the source; what is its replica identity; does the subscriber's copy of the table carry the matching index; is the identity set to FULL. The fix is almost always "give the table a primary key on both sides", which converts scans back into probes. Where a real key genuinely does not exist, adding a surrogate key column is the standard remedy — it is cheaper than living with FULL identity on a hot table. ## The takeaway Row identity is the single most consequential logical-replication configuration detail. Physical replication needs no such concept; logical replication depends on it for both correctness (can this operation replicate at all?) and performance (probe versus scan). "Every logically replicated table has a primary key, and the subscriber has it too" prevents nearly all of it.
- A table cannot be given a natural primary key. What is the practical remedy for logical replication?Add a surrogate key — an identity/auto-increment or UUID column — declare it the primary key, and make sure the subscriber's copy has the same key and index. It costs a column and an index but restores single-probe apply. Setting replica identity to FULL is the alternative, and it should be treated as a last resort limited to small, rarely-updated tables, because it inflates log volume on the source and forces per-event scans on the subscriber.
- Replication of one table is slow, but the source's replica identity is correctly set to its primary key. What else would you check?The subscriber's copy of the table. Identity is declared on the source, but the row lookup executes on the subscriber, so if that table was created without the primary key, or the index was dropped as an 'unused' index on a reporting target, the applier scans regardless of the source's setting. Verify index parity between the two sides for every replicated table, and treat it as a standing invariant rather than a one-time setup step.
saying these in an interview costs you the question
- Believing logical replication locates rows by physical address or row id
- Thinking REPLICA IDENTITY FULL is a harmless fallback for keyless tables
- Configuring identity on the source and never checking the subscriber's indexes
- Assuming a keyless table simply fails to replicate rather than silently scanning per row
- Dropping 'unused' indexes on a subscriber without checking which one serves apply