Install
Install in 7 s

Replica identity

Last checked · 2 min read

Replica identity is a Postgres table setting that decides which old values are written to the write-ahead log when a row is updated or deleted: the primary key (DEFAULT), a chosen unique index, every column (FULL) or nothing. It matters only for logical decoding, and it decides whether a reader can tell what a row was before.

The four settings

ALTER TABLE … REPLICA IDENTITY changes the information written to the write-ahead log to identify rows that are updated or deleted, and has no effect except when logical replication is in use.1 The options:

  • DEFAULT records the old values of the primary key columns. It is the default for ordinary tables, and on a table with no primary key it behaves like NOTHING.1
  • USING INDEX records the old values of the columns in a named unique index, which must be unique, not partial, not deferrable, and cover only NOT NULL columns.1
  • FULL records the old values of all columns in the row.1
  • NOTHING records no information about the old row.1

In the example, both statements first ran under DEFAULT: the update of row 42 carries only its new values, and the delete says that row 43 is gone, not what it held. After FULL, the update of row 41 carries the old status, 'paid', next to the new one. The empty transaction 757 is the ALTER TABLE itself: logical decoding doesn’t carry schema changes.3

When it breaks writes

Replica identity isn’t only about what a reader sees. A table whose replica identity is NOTHING, DEFAULT without a primary key, or USING INDEX with a dropped index can’t support UPDATE or DELETE while it is in a publication that replicates them: those statements fail on the publisher. INSERT still works.2 Adding a table without a primary key to a publication can therefore make an application’s updates start failing.

Why audits and undo want FULL

An audit of what an agent changed has to answer two questions for every row: what is it now, and what was it before? Under DEFAULT, an update that doesn’t touch the key carries the new row and nothing of the old values, and a delete carries only the key. You can see that something changed, but not from what.

FULL answers both, which is also what undo needs: to put a row back, you need its old values. The trade-off is volume. Every update and delete on the table writes the whole old row to the WAL, so busy, wide tables produce more WAL and more for a reader to process. Set FULL on the tables an agent can change, not on every table in the database.

Checking a table

SELECT relname,
       CASE relreplident WHEN 'd' THEN 'default' WHEN 'f' THEN 'full'
                         WHEN 'i' THEN 'index'   WHEN 'n' THEN 'nothing' END AS replica_identity
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace;

Tables without a primary key deserve a second look either way: under DEFAULT they log nothing about old rows, and many tools that read the change stream can’t tell their rows apart.

An example

PostgreSQL 18.6 (test_decoding): the same table under DEFAULT, then FULLtext
shop=# UPDATE public.orders SET status = 'shipped' WHERE id = 42;   -- DEFAULT
shop=# DELETE FROM public.orders WHERE id = 43;
shop=# SELECT data FROM pg_logical_slot_get_changes('cdc_demo', NULL, NULL);
 BEGIN 755
 table public.orders: UPDATE: id[integer]:42 status[text]:'shipped'
 COMMIT 755
 BEGIN 756
 table public.orders: DELETE: id[integer]:43
 COMMIT 756

shop=# ALTER TABLE public.orders REPLICA IDENTITY FULL;
shop=# UPDATE public.orders SET status = 'refunded' WHERE id = 41;
shop=# SELECT data FROM pg_logical_slot_get_changes('cdc_demo', NULL, NULL);
 BEGIN 757
 COMMIT 757
 BEGIN 758
 table public.orders: UPDATE: old-key: id[integer]:41 status[text]:'paid' new-tuple: id[integer]:41 status[text]:'refunded'
 COMMIT 758

How Deedglass uses it

Deedglass's Postgres connector check fails a watched table whose replica identity isn't FULL, and its scan marks a table undo-ready when its replica identity is FULL, or when it has a primary key and a replica identity other than NOTHING. With the old values in the change log, Deedglass can say what a row was before the agent changed it, and build undo from them.

Related on this site

Terms

Posts

In the docs

Sources

  1. 1PostgreSQL 18 documentation: ALTER TABLE (REPLICA IDENTITY) · postgresql.org · checked 18 October 2026
  2. 2PostgreSQL 18 documentation: Publication · postgresql.org · checked 18 October 2026
  3. 3PostgreSQL 18 documentation: Logical replication restrictions · postgresql.org · checked 18 October 2026
  4. 4Deedglass docs: The Postgres connector · docs.deedglass.com · checked 18 October 2026
  5. 5Deedglass docs: Scan in 60 seconds · docs.deedglass.com · checked 18 October 2026
  6. 6Deedglass source: the Postgres connector check (watched tables) · github.com · checked 18 October 2026
  7. 7Deedglass source: the scan's undo-ready column · github.com · checked 18 October 2026

Last checked 18 October 2026. We re-check the glossary every month. If something here is wrong, write to hello@deedglass.com.