Configure custom IMCI replication key - PolarDB for PostgreSQL - PolarDB

IMCI synchronizes data updates and deletions using the primary key by default. When a table lacks a primary key or uses an unordered one (such as a UUID), synchronization generates excessive random I/O and degrades performance. You can define a custom replication key on an ordered column to convert random I/O into sequential I/O.

Prerequisites

The table must have a column with monotonically increasing values in physical storage.

Considerations

Note the following constraints:

The replication key must reference a unique index, specified with the USING INDEX clause.
All columns in the unique index must be defined as NOT NULL.
For optimal performance, build the unique index on a monotonically increasing column, such as a SERIAL or BIGSERIAL column.

How it works

`REPLICA IDENTITY` mechanism

The REPLICA IDENTITY property controls which column values are written to the WAL (write-ahead log) to identify changed rows during logical replication. IMCI uses this mechanism to locate and update rows in the columnar store. REPLICA IDENTITY supports four modes:

Mode	Description	Use case
`DEFAULT`	Uses the primary key as the replication identity.	The default and recommended mode. No change is needed if the table has a primary key.
`USING INDEX`	Uses a specified unique index as the replication identity.	Specify an ordered unique index to improve efficiency when the table lacks a primary key or has an unordered primary key.
`FULL`	Writes the values of all columns in the row to the WAL.	Use only when a table has neither a primary key nor a unique index but must support UPDATE or DELETE operations.
`NOTHING`	Records no identifying information.	Use only for insert-only tables that do not support updates or deletes.

Ordered unique index as replication key

The core principle is to use the USING INDEX mode to set the replication key to a unique index on an ordered column, such as an auto-incrementing column.

Workflow:

Create a unique index on a physically ordered column, such as a SERIAL auto-incrementing column.
Run ALTER TABLE ... REPLICA IDENTITY USING INDEX ... to switch the table's replication identity to the new index.
During logical replication, the system decodes the ordered key values from the WAL.
IMCI uses the ordered key values to locate and update data in the columnar store via sequential I/O instead of random I/O, significantly boosting synchronization performance.

Procedure

Step 1: Create a unique index

Create a unique index on the ordered auto-incrementing column.

-- Assuming table t has an auto-incrementing column named serial_id
CREATE UNIQUE INDEX idx_t_serial_id ON t(serial_id);

Step 2: Change the replication identity

Switch the table's REPLICA IDENTITY to the unique index created in the previous step.
```
ALTER TABLE t REPLICA IDENTITY USING INDEX idx_t_serial_id;
```
Step 3: Create or rebuild the columnar store index

Ensure the columnar store index (CSI) includes the replication key column. If a CSI already exists, rebuild it for optimal performance.
```
-- Create a columnar store index that includes all columns
CREATE INDEX ON t USING csi;
```

Step 4: Compare performance

The following experiment demonstrates the performance improvement.

Test setup:

Create a table with an unordered primary key (random_id) and an ordered auto-incrementing column (serial_id).
```
create table t (random_id text primary key, serial_id serial, a text);
```

Insert 10 million rows of data.

insert into t(random_id, a)
  select md5(i::text), i::text
    from generate_series(1, 10000000) i;

Create a columnar store index and an ordered unique index, idx_a, on the serial_id column.
```
create unique index idx_a on t(serial_id);
create index on t using csi;
```

Test procedure:

Default mode test (using the unordered primary key random_id as the replication key):
```
-- Update 10,000 rows
UPDATE t SET a = '0' WHERE serial_id <= 10000;
```

Custom mode test (switching the replication key to the ordered unique index idx_a):

ALTER TABLE t REPLICA IDENTITY USING INDEX idx_a;
-- Update another 10,000 rows
UPDATE t SET a = '1' WHERE serial_id <= 10000;

Test results:

Replication key type	Default (unordered primary key)	Custom (ordered unique index)
Synchronization time (10,000 rows)	~0.5 seconds	~0.01 seconds

Conclusion

The ~50x improvement demonstrates that an ordered custom replication key is critical for efficient IMCI data synchronization.