Choosing Replica Identity for Updates and Deletes

REPLICA IDENTITY decides exactly which old-row columns PostgreSQL writes into the WAL for every UPDATE and DELETE, and therefore what key a downstream…

REPLICA IDENTITY decides exactly which old-row columns PostgreSQL writes into the WAL for every UPDATE and DELETE, and therefore what key a downstream consumer receives to locate the row it must change — a choice that sits underneath every publication/subscription model and silently governs both correctness and WAL volume. Pick NOTHING on a table that publishes deletes and the DELETE fails outright; pick FULL on a wide, high-churn table and you multiply WAL generation for change-data-capture (CDC) with no correctness benefit.

This page fixes what the four identity settings — DEFAULT, USING INDEX, FULL, and NOTHING — actually emit as the old-tuple key, the index each one requires, the unchanged-TOAST pitfall that nulls columns downstream, and the WAL trade-off for a CDC stream. It assumes you have already modelled your publications and are deciding how each published table identifies a changed row. All behavior is validated against PostgreSQL 14 through 17.

What each REPLICA IDENTITY emits as the old-tuple key An UPDATE on a five-column customers table with columns id, email, name, balance and updated_at. Four rows show what the pgoutput change event carries as the old-tuple key under each REPLICA IDENTITY. DEFAULT fills only the id cell, using the primary key, at minimal WAL cost. USING INDEX fills only the email cell, using the columns of a named unique non-partial NOT NULL index, at minimal WAL cost. FULL fills all five cells with the entire prior row, at high WAL cost and requiring a full-row match downstream. NOTHING fills no cells and is marked rejected, because an UPDATE or DELETE on a table that publishes those operations without a replica identity raises an error. Old-tuple key carried in the UPDATE / DELETE event, per REPLICA IDENTITY id email name balance updated_at DEFAULT → primary key id minimal WAL · needs a PK USING INDEX → unique index cols email minimal WAL · UNIQUE, NOT NULL FULL → entire prior row id email name balance updated_at high WAL · full-row match NOTHING → no old key UPDATE / DELETE rejected if published no WAL, but breaks CDC Pitfall: an unchanged out-of-line TOAST column is sent as an "unchanged" marker, not its value — a consumer that reads absent-as-NULL will erase it. Carry the prior value forward on UPDATE.
The identity setting is the width of the old-tuple key in every UPDATE/DELETE event: from a single primary-key column under DEFAULT to the whole prior row under FULL, or nothing at all under NOTHING.

Replica Identity Semantics

REPLICA IDENTITY changes nothing about INSERT — a new row is always shipped in full — but it dictates the old-tuple key on UPDATE and DELETE, which is how the subscriber or CDC consumer finds the row to modify. Each setting trades index requirements and WAL volume against the ability to replicate those operations at all. The table below fixes the exact behavior, targeting PostgreSQL 14–17.

REPLICA IDENTITY Old-tuple key emitted Requirement WAL / CDC impact
DEFAULT The primary-key columns only A PRIMARY KEY must exist Minimal extra WAL. Correct default for CDC; UPDATE/DELETE fail if the table has no PK
USING INDEX idx The columns of the named index Index must be UNIQUE, not partial, not deferrable, on NOT NULL columns Minimal extra WAL. Use when the natural key is a unique index other than the PK
FULL Every column of the prior row No index required High extra WAL on wide/updated rows; downstream matches on the full old row (PG 16+ can use a subscriber index)
NOTHING Nothing — Zero WAL, but any UPDATE/DELETE on a table that publishes them raises cannot update/delete ... because it does not have a replica identity

Two consequences follow directly. First, a table with no primary key and REPLICA IDENTITY DEFAULT behaves like NOTHING for updates and deletes — the identity is default, but there is no key to log, so those operations are rejected the moment the table is published for them. Second, FULL is a correctness fallback, not a performance choice: it makes any table replicable without a key, but it logs the entire prior row into WAL on every change and forces the subscriber to scan for a full-row match, which is why it belongs only on small, low-churn tables. The way these old-tuple keys travel inside the pgoutput frames is detailed in the CDC pipeline guide.

Diagnostic Patterns

Audit every published table’s identity before you trust the stream — a missing or wrong identity is invisible until the first UPDATE or DELETE fails. The relreplident column in pg_class encodes the setting as a single character:

sql
-- Replica identity of every published table. 'd'=default 'i'=index 'f'=full 'n'=nothing.
SELECT n.nspname AS schema, c.relname AS table,
       CASE c.relreplident
         WHEN 'd' THEN 'default (PK)' WHEN 'i' THEN 'using index'
         WHEN 'f' THEN 'full'          WHEN 'n' THEN 'nothing'
       END AS replica_identity
FROM pg_publication_tables pt
JOIN pg_class c     ON c.relname  = pt.tablename
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = pt.schemaname
ORDER BY replica_identity, schema, table;

The dangerous combination is relreplident = 'd' on a table with no primary key. Find those before they break replication:

sql
-- Published tables using DEFAULT identity but lacking a primary key => UPDATE/DELETE will fail.
SELECT pt.schemaname, pt.tablename
FROM pg_publication_tables pt
JOIN pg_class c     ON c.relname = pt.tablename
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = pt.schemaname
WHERE c.relreplident = 'd'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary
  );

When a table uses USING INDEX, confirm the referenced index still satisfies the constraints — dropping or altering it silently invalidates the identity:

sql
-- The index backing a USING INDEX identity must remain UNIQUE, non-partial, NOT NULL.
SELECT c.relname AS table, ix.relname AS identity_index,
       i.indisunique, i.indpred IS NOT NULL AS is_partial
FROM pg_class c
JOIN pg_index i  ON i.indrelid = c.oid AND i.indisreplident
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE c.relreplident = 'i';

Any row where indisunique is false or is_partial is true means the identity index no longer qualifies and updates will error until it is fixed.

Safe Deployment Sequence

Changing REPLICA IDENTITY is a fast catalog-only ALTER TABLE that takes an exclusive lock for an instant, but it changes what downstream consumers receive, so sequence it so no in-flight change is misinterpreted.

  1. Pick the identity from the key you actually have. If the table has a stable primary key, DEFAULT is correct and needs no change. Only reach for USING INDEX when the CDC join key is a different unique column, and reserve FULL for keyless tables you cannot add a key to.

  2. For USING INDEX, create the qualifying index first. It must be unique, non-partial, non-deferrable, and on NOT NULL columns:

    sql
    CREATE UNIQUE INDEX CONCURRENTLY customers_email_uk ON customers (email);
-- email must be NOT NULL for the index to be eligible as a replica identity.

  3. Set the identity. The change is instantaneous and affects only WAL written after it:

    sql
    ALTER TABLE customers REPLICA IDENTITY USING INDEX customers_email_uk;
-- or: ALTER TABLE customers REPLICA IDENTITY FULL;   -- keyless fallback only

  4. Validate the emitted key without advancing the slot, by peeking at a test change:

    sql
    -- Inspect one UPDATE's old-tuple in the stream before pointing a consumer at it.
SELECT data FROM pg_logical_slot_peek_changes('cdc_slot', NULL, 1,
  'proto_version', '4', 'publication_names', 'cdc_pub');

  5. Revert. The change is symmetric and non-destructive: ALTER TABLE customers REPLICA IDENTITY DEFAULT; restores the primary-key behavior instantly. Never switch a busy table to FULL without first checking its WAL impact under load — on a wide, frequently-updated row it can dominate total WAL generation.

Pipeline Integration

A CDC consumer must apply changes keyed on whatever old-tuple the identity emits, and it must handle the unchanged-TOAST case correctly or it will corrupt data. Under pgoutput, an out-of-line TOASTed column that an UPDATE did not modify is sent as an unchanged marker rather than its value; a consumer that treats an absent column as NULL will overwrite the stored value with null. The safe pattern is an upsert that only writes columns the event actually carried:

python
# Apply an UPDATE keyed on the replica-identity old key, preserving unchanged TOAST columns.
def apply_update(cur, event):
    key_cols   = event["old_key"]        # from replica identity: PK, index cols, or full row
    changed    = event["new"]            # only columns present in the pgoutput new-tuple
    # Build SET only for columns actually delivered — never null an unchanged TOAST value.
    set_clause = ", ".join(f"{c} = %({c})s" for c in changed)
    where      = " AND ".join(f"{c} = %(k_{c})s" for c in key_cols)
    params = {**changed, **{f"k_{c}": v for c, v in key_cols.items()}}
    cur.execute(f"UPDATE customers SET {set_clause} WHERE {where}", params)

Three rules keep this correct at scale. First, key both UPDATE and DELETE on the old-tuple the identity provides — do not assume the primary key is present, because under USING INDEX it is the index columns, and under FULL it is the whole prior row. Second, make apply idempotent: delivery is at-least-once, so a redelivered DELETE for an already-deleted row must be a no-op, and a replayed UPDATE must converge. Third, if you must run FULL, batch small and expect the extra WAL — export the per-table WAL contribution so an identity change does not silently blow your retention budget, correlating it with the generation rate covered in monitoring WAL generation and retention.

Failover handling. REPLICA IDENTITY is stored in the catalog and travels with the schema, so a logical subscriber inherits it, but a rebuilt or pg_dump-restored table can lose a USING INDEX identity if the index is recreated under a different name. After any failover or restore, re-run the identity audit above before resuming the stream, so a silently-reset identity does not turn every UPDATE into a NOTHING-style rejection.

Authoritative references