CDC Sink for PostgreSQL: REPLICA IDENTITY

• REPLICA IDENTITY determines which old column values PostgreSQL can include in logical replication messages for DELETE and UPDATE operations. For CDC Sink, it mainly matters for DELETE events from embedded tables.

• To process an embedded-table DELETE, CDC Sink needs the old values of the embedded row's join columns and primary key columns. The join columns locate the parent document; the primary key columns locate the embedded item to remove.

• This article explains when PostgreSQL's default setting is sufficient, when the table needs a different REPLICA IDENTITY, and whether CDC Sink can configure it automatically.

• In this article:

  • Why REPLICA IDENTITY matters
  • REPLICA IDENTITY options
  • When is REPLICA IDENTITY required?
  • Checking current setting
  • Automatic vs manual configuration

Why REPLICA IDENTITY matters

When CDC Sink receives a DELETE event for an embedded table, it needs to:

1. Find the parent document using the old row's join column values
2. Find the matching item within the embedded array or map using the embedded table's primary key values
3. Remove it

If the DELETE event does not include the old values for both sets of columns, CDC Sink cannot perform steps 1 or 2.

By default, PostgreSQL includes only the primary key columns in DELETE events.
If the embedded table's join columns are part of the primary key, this is sufficient.
If they are not, which is common when a table has a surrogate primary key,
the DELETE event will be missing the columns needed to route it.

UPDATE events carry the new row, so ordinary embedded updates can be routed under DEFAULT.
Old row values matter when an UPDATE changes the embedded row's join columns,
because CDC Sink must remove the item from the old parent document and add it to the new one.

REPLICA IDENTITY options

PostgreSQL supports four REPLICA IDENTITY modes:

Mode	Old row values available for DELETE and UPDATE events	Notes
DEFAULT	Primary key columns only	The default for every table. On a table without a primary key, no old row values are sent
FULL	All columns	Works for any table structure; increases WAL size
INDEX	Columns covered by the designated replica identity index	More targeted than FULL, but the selected index must cover all columns CDC Sink needs
NOTHING	No old row values	Insufficient for CDC Sink embedded table deletes

For CDC Sink, FULL is the simplest and most compatible choice. INDEX works if the designated replica identity index covers both the embedded table's join columns and its PrimaryKeyColumns.

When is REPLICA IDENTITY required?

Scenario	REPLICA IDENTITY needed?
Root table deletes where the document ID is built from the table's PostgreSQL primary key	No - DEFAULT sends the primary key columns
Configured table has no PostgreSQL primary key and DELETEs must be processed	Yes - under DEFAULT, PostgreSQL sends no old row values; use FULL or a suitable replica identity index
Embedded table deletes where all required join columns and PrimaryKeyColumns are covered by the PostgreSQL primary key	No - DEFAULT is sufficient
Embedded table deletes where any required join column is not covered by the PostgreSQL primary key	Yes - use FULL or a replica identity index that covers the join columns and PrimaryKeyColumns
Embedded table where OnDelete.IgnoreDeletes = true is set without an OnDelete.Patch	No - the DELETE event is discarded before routing
Embedded table with an OnDelete.Patch	Same as a processed delete - the DELETE event must still be routed so the patch can run
Deep-nested embedded tables	Apply the same rule to each embedded source table: the DELETE event must include the columns needed to find the root document, navigate the embedded path, and identify the item to remove

---

Example where REPLICA IDENTITY is needed:

• SQL

-- order_lines has a surrogate PK (line_id), with order_id as foreign key
-- order_id is NOT part of the primary key
CREATE TABLE order_lines (
    line_id   SERIAL PRIMARY KEY,   -- PK
    order_id  INT NOT NULL,         -- FK to orders (not part of PK)
    product   TEXT,
    qty       INT
);

-- Without REPLICA IDENTITY FULL, a DELETE event only contains line_id.
-- CDC Sink cannot determine which parent document (order_id) to update.
ALTER TABLE order_lines REPLICA IDENTITY FULL;

Example where REPLICA IDENTITY is NOT needed:

• SQL

-- If order_id is part of a composite PK, DEFAULT is sufficient
CREATE TABLE order_lines (
    order_id  INT NOT NULL,
    line_num  INT NOT NULL,
    product   TEXT,
    qty       INT,
    PRIMARY KEY (order_id, line_num)
);

Checking current setting

• SQL

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    c.relreplident,
    CASE c.relreplident
        WHEN 'd' THEN 'DEFAULT'
        WHEN 'f' THEN 'FULL'
        WHEN 'i' THEN 'INDEX'
        WHEN 'n' THEN 'NOTHING'
    END AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
ORDER BY n.nspname, c.relname;

The relreplident column:

Value	Meaning
d	DEFAULT (primary key only)
f	FULL (all columns)
i	INDEX (designated replica identity index)
n	NOTHING

Automatic vs manual configuration

If the PostgreSQL user configured in the CDC Sink connection string owns the affected embedded source table, or has SUPERUSER, CDC Sink can automatically set REPLICA IDENTITY FULL when that table's current replica identity is insufficient for embedded DELETE routing.

If that PostgreSQL user cannot alter the table, the task fails to start with a permissions error.
A database administrator must configure REPLICA IDENTITY before starting the task. See Manual Setup.