skip to content

Why do UPDATEs on a PostgreSQL table without a primary key fail once it joins a publication, and which REPLICA IDENTITY setting fixes it?

level: middleimportance: must knowfreq 62%

answer

  1. old row's key in the WAL
  2. DEFAULT, USING INDEX, FULL, NOTHING
  3. the source refuses, not the target
  4. FULL costs WAL and lookups

basics

~20 s

Published 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 s

When 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
sql
-- 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

for a junior

Remember that a published table needs a primary key, or another replica identity, before its UPDATEs and DELETEs can be replicated.

for a middle

Explain the four settings, what each writes to the WAL, and why the 55000 error is raised by the publisher while inserts keep working.

for a senior

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.

for a principal

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.