Why do UPDATEs on a PostgreSQL table without a primary key fail once it joins a publication, and which REPLICA IDENTITY setting fixes it?
answer
- old row's key in the WAL
- DEFAULT, USING INDEX, FULL, NOTHING
- the source refuses, not the target
- FULL costs WAL and lookups
basics
~20 sPublished UPDATEs and DELETEs need a replica identity; DEFAULT means the primary key, so without one the publisher rejects them with SQLSTATE 55000. Add a primary key, use USING INDEX on a qualifying unique index, or fall back to FULL.
solid answer
~40 sWhen a table belongs to a publication that publishes updates or deletes, PostgreSQL must write enough of the old row into the WAL for the subscriber to find it. `REPLICA IDENTITY DEFAULT` logs the primary-key columns and, with no primary key, behaves like `NOTHING`, so the publisher itself raises `cannot update table ... because it does not have a replica identity and publishes updates` (SQLSTATE `55000`); inserts keep working. Fixes, best first: add a primary key; `ALTER TABLE ... REPLICA IDENTITY USING INDEX` on a unique, non-partial, non-deferrable index over `NOT NULL` columns; or `REPLICA IDENTITY FULL`, which logs every old column and makes the subscriber match on the whole row. FULL means more WAL, and slow apply unless the subscriber has a usable B-tree or hash index.
code
sql · 14 lines-- published tables whose UPDATE/DELETE would be refused
SELECT c.oid::regclass AS table_name, c.relreplident
FROM pg_publication_tables pt
JOIN pg_class c
ON c.oid = format('%I.%I', pt.schemaname, pt.tablename)::regclass
WHERE pt.pubname = 'orders_to_analytics'
AND (c.relreplident = 'n'
OR (c.relreplident = 'd' AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.oid AND i.indisprimary)));
-- fix one with an existing unique index on NOT NULL columns
ALTER TABLE sales.order_events
REPLICA IDENTITY USING INDEX order_events_event_uid_key;go deeper
Remember that a published table needs a primary key, or another replica identity, before its UPDATEs and DELETEs can be replicated.
Explain the four settings, what each writes to the WAL, and why the 55000 error is raised by the publisher while inserts keep working.
Show the production view: identity checks in every ADD TABLE review, fixing a keyless table online, and what FULL costs in WAL volume and subscriber apply lag.
Treat keyless tables as a schema-design debt that replication exposes, and set a policy that every table meant for replication carries a real key.
## What replica identity is When a row is updated or deleted on the publisher, the subscriber has to know **which** of its rows to change. PostgreSQL answers this per table with a **replica identity**: the old-row column values written into the WAL with each `UPDATE` and `DELETE`, so that logical decoding can send them. It is a table property, stored in `pg_class.relreplident` and set with `ALTER TABLE ... REPLICA IDENTITY`. It matters only when logical decoding reads the table. `INSERT` needs no identity at all, because the new row is the whole message. ## The four settings | Setting | `relreplident` | Old values written to the WAL | Typical use | |---|---|---|---| | `DEFAULT` | `d` | primary-key columns; same as `NOTHING` when there is no primary key | the normal case | | `USING INDEX idx` | `i` | the columns of a unique, non-partial, non-deferrable index on `NOT NULL` columns | a natural unique key that is not the PK | | `FULL` | `f` | every column of the old row | last resort for keyless tables | | `NOTHING` | `n` | nothing | the default for system tables | With `DEFAULT` or `USING INDEX`, an `UPDATE` logs an identity column's old value only when it changed or is stored out of line, so the WAL overhead stays small. ## Why the error comes from the publisher The failure is not on the analytics server. Say `sales.order_events` has no primary key: its identity is effectively `NOTHING`. Once it is in a publication that publishes updates or deletes (the default publishes both), PostgreSQL refuses the statement **on the source**: - `ERROR: cannot update table "order_events" because it does not have a replica identity and publishes updates`, or the matching `cannot delete from table` message; - a hint to set `REPLICA IDENTITY` with `ALTER TABLE`; - SQLSTATE `55000` (`object_not_in_prerequisite_state`). So a replication change becomes an application outage on the orders database. That is why every `ALTER PUBLICATION ... ADD TABLE` deserves a check of the table's identity. Inserts keep working, and a publication created with `publish = 'insert'` never triggers the check. The same failure appears if the index behind a `USING INDEX` identity is dropped. ## Fixing a table without a primary key 1. **Add a primary key** if the data has one. On a busy table, build the unique index with `CREATE UNIQUE INDEX CONCURRENTLY` and attach it with `ALTER TABLE ... ADD PRIMARY KEY USING INDEX`; the locking of those steps is schema-change practice. 2. **`REPLICA IDENTITY USING INDEX`** on an existing unique index that meets the rules: unique, not partial, not deferrable, every column `NOT NULL`. A unique index over a nullable column is refused. 3. **`REPLICA IDENTITY FULL`** when no candidate key exists. Every `UPDATE` and `DELETE` then logs the entire old row, which grows WAL volume and decoding work, and the subscriber must find the row by comparing all columns. Each is an `ALTER TABLE` subcommand that briefly takes an `ACCESS EXCLUSIVE` lock on the table. ## The subscriber side - With `DEFAULT` or `USING INDEX`, the subscriber finds the row through its own key index, and it must have a replica identity of the **same or fewer columns** than the publisher's. - With `FULL`, the subscriber can still use a B-tree or hash index that is not partial and whose leftmost column is a plain column, not an expression. Without one, every change scans the table and apply lag grows with table size. - `FULL` cannot be applied for tables holding types with no default B-tree or hash operator class, such as `point` or `box`; a real key avoids that. - A row the subscriber cannot find is a conflict (`update_missing`, `delete_missing`): the change is skipped, not retried. ## Checking before you publish A catalog query that lists published tables with no usable identity (`relreplident = 'n'`, or `'d'` with no primary key) belongs in the review before tables are added, and again after migrations that drop keys. Three details make that review reliable: - `ALTER PUBLICATION ... ADD TABLE` succeeds on a keyless table. Nothing complains until the first `UPDATE` or `DELETE`, which is why the query, not the DDL, is the guard. - In psql, `\d+ sales.order_events` prints a `Replica Identity:` footer when the setting is `FULL` or `NOTHING`, and marks the chosen index with `REPLICA IDENTITY` when `USING INDEX` is in force. - A `FOR ALL TABLES` publication includes every new table automatically, so a keyless table created by any later migration is published, and fails, immediately.
- Does the subscriber need the same replica identity as the publisher?When the publisher uses `DEFAULT` or `USING INDEX`, the subscriber needs a replica identity made of the same or fewer columns, because it looks rows up by those values through its own index. With `FULL` on the publisher, the subscriber matches on the whole row and can use a suitable non-partial B-tree or hash index if one exists.
- Why not simply set REPLICA IDENTITY FULL on every published table?FULL writes every old column into the WAL for each `UPDATE` and `DELETE`, which inflates WAL and decoding work on the publisher. On the subscriber each change must match the whole row, a sequential scan per change when no usable index exists. It also fails for column types without a default B-tree or hash operator class. A real key avoids all three costs.
A replica identity is the address on a work order for a repair crew at a second site. With a primary key the crew drives straight to the house. With FULL it carries a full description and checks house after house until one matches. With no identity the dispatcher at the first site refuses to issue the order at all.
- Logical replication silently skips UPDATEs on tables without a primary key.
- REPLICA IDENTITY FULL is free; it only changes how the subscriber matches rows.
- Any unique index works for USING INDEX, even over nullable columns.
- INSERTs also fail on a published table that has no replica identity.
- Replica identity is a subscription option rather than a table property.