PostgreSQL Replica Identity for Logical Replication

calendar Apr 20, 2026 / · 4 min read · postgresql replication administration pg_class  ·
Share on: linkedin copy

Check Replica Identity Settings in PostgreSQL

Logical replication in PostgreSQL requires each replicated table to have a replica identity. The replica identity tells PostgreSQL which columns to include in the WAL record for UPDATE and DELETE operations so the subscriber can identify the row being changed.

If replica identity is misconfigured, updates and deletes either fail on the subscriber or replicate incorrectly. This query shows the current replica identity setting for every table in your database so you can audit and fix any gaps before enabling a publication.

Sample Code

 1SELECT
 2    c.relname                          AS table_name,
 3    n.nspname                          AS schema_name,
 4    CASE c.relreplident
 5        WHEN 'd' THEN 'DEFAULT (primary key)'
 6        WHEN 'f' THEN 'FULL (all columns)'
 7        WHEN 'i' THEN 'INDEX (specific index)'
 8        WHEN 'n' THEN 'NOTHING'
 9    END                                AS replica_identity,
10    c.relreplident                     AS replica_identity_code
11FROM
12    pg_catalog.pg_class c
13    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
14WHERE
15    c.relkind = 'r'
16    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
17ORDER BY
18    n.nspname,
19    c.relname;

Notes: Works on PostgreSQL 9.4 and later (replica identity was introduced in 9.4 with logical replication foundations). Requires at least SELECT on pg_class and pg_namespace, which is available to all roles.

Code Breakdown

  • pg_catalog.pg_class — The system catalog that stores one row for every table, view, index, sequence, and other relation. For tables, relkind = 'r'.
  • c.relreplident — A single character column that stores the replica identity setting: d (default), f (full), i (specific index), or n (nothing).
  • pg_catalog.pg_namespace — Maps schema OIDs to schema names. Joined on c.relnamespace = n.oid.
  • CASE c.relreplident — Converts the single-character code into a human-readable label.
  • relkind = 'r' — Filters to ordinary tables only, excluding views, indexes, and sequences.
  • Schema exclusion — Drops pg_catalog and information_schema system schemas from results so only user tables appear.

Key Points

  • DEFAULT — Uses the primary key to identify rows. This is the standard setting for most tables. If a table has no primary key, DEFAULT behaves the same as NOTHING.
  • FULL — Writes every column to WAL for each update and delete. This always works but produces significantly more WAL volume and replication overhead.
  • INDEX — Uses a specific unique index (not necessarily the primary key) to identify rows. Useful when the primary key is unsuitable for replication.
  • NOTHING — No row identity information is written. Updates and deletes on this table will fail on the subscriber with an error.

Insights and Explanations

Why replica identity matters for logical replication:

When you create a PostgreSQL publication with CREATE PUBLICATION and then run CREATE SUBSCRIPTION on the subscriber, PostgreSQL streams WAL records for each change. For INSERT operations this is straightforward — the full new row is available. For UPDATE and DELETE, PostgreSQL needs a way to identify which row changed on the subscriber side.

Replica identity is how PostgreSQL encodes that row identity into the WAL record:

  • With DEFAULT, the primary key columns are written as the "old" key in WAL.
  • With FULL, all columns are written.
  • With NOTHING, no key information is written — the subscriber cannot identify the row.

Finding tables that will break replication:

 1SELECT
 2    n.nspname AS schema_name,
 3    c.relname AS table_name
 4FROM
 5    pg_catalog.pg_class c
 6    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 7WHERE
 8    c.relkind = 'r'
 9    AND c.relreplident = 'n'
10    AND n.nspname NOT IN ('pg_catalog', 'information_schema');

Any table with NOTHING in a publication will cause replication errors for updates and deletes.

Fixing replica identity:

Set replica identity to use the primary key (standard approach):

1ALTER TABLE mytable REPLICA IDENTITY DEFAULT;

Set to FULL when no primary key exists:

1ALTER TABLE mytable REPLICA IDENTITY FULL;

Use a specific unique index:

1ALTER TABLE mytable REPLICA IDENTITY USING INDEX mytable_unique_idx;

Performance considerations:

FULL replica identity can significantly increase WAL volume on write-heavy tables because every column is written twice (old and new values) for each update. Use DEFAULT or INDEX wherever possible. Only fall back to FULL for tables without a suitable unique key.

Additional Considerations

  • Permissions: Superuser or table owner required to run ALTER TABLE ... REPLICA IDENTITY.
  • Partitioned tables: Replica identity must be set on each partition individually, not just the parent table.
  • Generated columns: Generated columns are excluded from FULL replica identity WAL records in PostgreSQL 15+.
  • pg_dump: pg_dump does not export replica identity settings. If you restore a database that feeds a publication, reset replica identity after restore.

References

Posts in this series