Postgres REPLICA IDENTITY

Both logical decoding and logical replication use a table’s REPLICA IDENTITY. This is a way to say which row was changed by an UPDATE or DELETE. In other word it identifies the “old” row.

Logical decoding will use the replica identity to say which row was changed, if available, but if not then the information is simply omitted. No big deal.

In logical replication, the subscriber looks for the replica identity with each change, and it uses it to know which row to remove, either because it was replaced or because it was deleted. So you can always replicate inserts and truncates, but you can only replicate updates and deletes if you have an appropriate replica identity. In fact even on the publication side, Postgres will forbid changes without an appropriate replica identity, as we will see.

Normally a table has DEFAULT for its replica identity. This means it will use the table’s primary key (if present). You can’t set the replica identity when you create the table, but you can change it with ALTER TABLE. Besides DEFAULT, it can be USING INDEX <index> or FULL or NOTHING. The replica identity is stored in pg_class.relreplident.

I had a lot of questions about how each of these works. Mostly I wanted to know when things failed: creating/altering the table/publication, making the change, or receiving it. All my tests were done on Postgres 17 using the REL_17_0 tag after doing make world && make install-world. I wanted to replicate from one database to another in the same cluster, but that requires some (slightly) more complicated commands. To keep the SQL simple, I ran separate clusters, one with a database named sender, the other with a database named receiver. The sending cluster runs on 5432 to keep the receiver’s connection strings simpler.

On the sender I set wal_level like this (on a Mac):

sed -i '' -e '/wal_level/a\
wal_level=logical' ~/local/pgsql/data/postgresql.conf

On Linux I think this would be just:

sed -i '/wal_level/awal_level=logical' ~/local/pgsql/data/postgresql.conf

You can find the SQL for each test in this github repo.

NOTHING

Let’s start with NOTHING. This means there is no information about the old row. If we’re looking for failures, this is nice and simple.

Q1: What happens if you create a publication for it?

This is allowed:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION

Q2: What happens if you add it to an existing publication?

This is allowed too:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p;
CREATE PUBLICATION
sender=# alter publication p add table t;
ALTER PUBLICATION

Q3: What happens if you create a FOR ALL TABLES publication?

And this is allowed:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p for all tables;
CREATE PUBLICATION

Q4: What happens if you set a table to NOTHING when it already belongs to a publication?

It’s allowed:

sender=# create table t (a text);
CREATE TABLE
sender=# create publication p for table t;
CREATE PUBLICATION
sender=# alter table t replica identity nothing;
ALTER TABLE

Q5: What happens if you set a table to NOTHING when you already have a FOR ALL TABLES publication?

It’s allowed:

sender=# create table t (a text);
CREATE TABLE
sender=# create publication p for all tables;
CREATE PUBLICATION
sender=# alter table t replica identity nothing;
ALTER TABLE

Q6: What happens if you change an insert-only publication to an update publication, and it has a NOTHING table?

It’s allowed:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p for table t with (publish = insert);
CREATE PUBLICATION
sender=# alter publication p set (publish = 'insert,update,delete,truncate');
ALTER PUBLICATION

Q7: What happens if you change an insert-only FOR ALL TABLES publication to an update publication, and the database has a NOTHING table?

It’s allowed:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p for all tables with (publish = insert);
CREATE PUBLICATION
sender=# alter publication p set (publish = 'insert,update,delete,truncate');
ALTER PUBLICATION

Q8: What happens if you update a NOTHING table that is in a publication?

It’s not allowed!

This fails on the publisher side, even if there is no subscription:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION
sender=# insert into t values ('a');
INSERT 0 1
sender=# update t set a = 'b';
ERROR:  cannot update table "t" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

Q9: What happens if you delete from a NOTHING table that is in a publication?

This fails the same way:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity nothing;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION
sender=# insert into t values ('a');
INSERT 0 1
sender=# update t set a = 'b';
ERROR:  cannot delete from table "t" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

So to summarize, Postgres doesn’t validate publications up front, but only when you try to send through them a NOTHING update/delete.

FULL

FULL means all columns are combined to determine uniqueness. Of course that might still not be unique. So what happens if you update one of them but not the other?

Q1: What happens if you have two identical records and you delete one?

On the publisher:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity full;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION

On the subscriber:

receiver=# create table t (a text);
CREATE TABLE
receiver=# create subscription s connection 'dbname=sender' publication p;
NOTICE:  created replication slot "s" on publisher
CREATE SUBSCRIPTION

Back on the publisher:

sender=# insert into t values ('a');
INSERT 0 1
sender=# insert into t values ('a');
INSERT 0 1
sender=# select ctid, a from t;
 ctid  | a
-------+---
 (0,1) | a
 (0,2) | a
(2 rows)

sender=# delete from t where ctid = '(0,1)';
DELETE 1

And the receiver sees:

receiver=# select * from t;
 a 
---
 a
(1 row)

So we didn’t lose both rows! I guess that’s because we are replicating a delete of one row. Which one we delete doesn’t matter, but we’ll only delete one.

Q1: What happens if you have two identical records and you delete both?

On the publisher:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity full;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION

On the subscriber:

receiver=# create table t (a text);
CREATE TABLE
receiver=# create subscription s connection 'dbname=sender' publication p;
NOTICE:  created replication slot "s" on publisher
CREATE SUBSCRIPTION

Back on the publisher:

sender=# insert into t values ('a');
INSERT 0 1
sender=# insert into t values ('a');
INSERT 0 1
sender=# delete from t;
DELETE 2

And the receiver sees:

receiver=# select * from t;
 a 
---
(0 rows)

So both rows disappeared. That makes sense, because the receiver got two messages to delete a row like ('a').

Q3: What happens if you have two identical records and you update one?

I assume updating one of two identical rows will work the same, but let’s check:

sender=# create table t (a text);
CREATE TABLE
sender=# alter table t replica identity full;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION

receiver=# create table t (a text);
CREATE TABLE
receiver=# create subscription s connection 'dbname=sender' publication p;
NOTICE:  created replication slot "s" on publisher
CREATE SUBSCRIPTION

sender=# insert into t values ('a');
INSERT 0 1
sender=# insert into t values ('a');
INSERT 0 1
sender=# select ctid, a from t;
 ctid  | a
-------+---
 (0,1) | a
 (0,2) | a
(2 rows)

sender=# update t set a = 'b' where ctid = '(0,1)';
UPDATE 1

receiver=# select * from t;
 a 
---
 a
 b
(2 rows)

Yep!

So with FULL there are no errors on the publisher side nor on the subscriber side. The disadvantage is that everything is slower: logical decoding sends more data, and the subscriber must compare all the columns for equality.

I wonder if the subscriber will still use an index to apply changes if there is one? I haven’t tested that yet, but if I do I will put an update here.

USING INDEX <index>

This lets us choose a UNIQUE index, as long as none of its keys are nullable. Assuming the subscriber has the same uniqueness, it can quickly locate which rows to change. Here is the happy path:

sender=# create table t (a text not null unique);
CREATE TABLE
sender=# alter table t replica identity using index t_a_key;
ALTER TABLE
sender=# create publication p for table t;
CREATE PUBLICATION

In that case, pg_class.relreplident is set to i, and pg_index.indisreplident is set to true for that index. You can see which index is used with \d:

sender=# \d t
                Table "public.t"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 a      | text |           | not null | 
Indexes:
    "t_a_key" UNIQUE CONSTRAINT, btree (a) REPLICA IDENTITY

But what can we do to mess it up?

Q1: What happens if you use a not-unique index?

sender=# create table t (a text);
CREATE TABLE
sender=# create index idx_t_a on t (a);
CREATE INDEX
sender=# alter table t replica identity using index idx_t_a;
ERROR:  cannot use non-unique index "idx_t_a" as replica identity

We can’t even set the REPLICA IDENTITY.

Q2: What happens if you drop the index?

sender=# create table t (a text not null);
CREATE TABLE
sender=# create unique index idx_t_a on t (a);
CREATE INDEX
sender=# alter table t replica identity using index idx_t_a;
ALTER TABLE
sender=# drop index idx_t_a;
DROP INDEX
sender=# create publication p for table t;
CREATE PUBLICATION

Well you were able to drop it! The table’s relreplident is still i, but now there is no index with a true pg_index.indisreplident.

Let’s see what happens if we try to use it. On the subscriber:

receiver=# create table t (a text);
CREATE TABLE
receiver=# create subscription s connection 'dbname=sender' publication p;
NOTICE:  created replication slot "s" on publisher
CREATE SUBSCRIPTION

Back on the sender:

sender=# insert into t values ('a');
INSERT 0 1
sender=# update t set a = 'b';
ERROR:  cannot update table "t" because it does not have a replica identity and publishes updates

Oops! We were able to insert, but the update failed. Even on the sender, we weren’t able to make the change.

I’m dreaming of some other trickery with ALTER INDEX, but nothing that seems problematic is supported, like changing a unique index to a non-unique one.

So like NOTHING, failures happen when we update the table. But in addition there is validation before setting pg_index.indisreplident. If the index is not appropriate, we’ll fail there.

DEFAULT

The DEFAULT setting means to use the table’s primary key, if it has one. So if the table has a primary key, you’re all good. But what if it doesn’t? Is it the same as NOTHING?

Well we are allowed to set things up:

sender=# create table t (a text);
CREATE TABLE
sender=# create publication p for table t;
CREATE PUBLICATION

And on the receiver:

receiver=# create table t (a text);
CREATE TABLE
receiver=# create subscription s connection 'dbname=sender' publication p;
NOTICE:  created replication slot "s" on publisher
CREATE SUBSCRIPTION

Now we make some changes:

sender=# insert into t values ('a');
INSERT 0 1
sender=# update t set a = 'b';
ERROR:  cannot update table "t" because it does not have a replica identity and publishes updates

So again we can insert things, but not send updates. We fail before we even send over the change. In this test we didn’t even need to run anything on the receiver to cause the failure.

Since the failure is so late, there is no difference between starting with a primary key and dropping it partway through. Likewise with other ideas we tried for NOTHING, like adding the publication before the table or using FOR ALL TABLES. Also changing an insert-only publication to an update publication is permitted, but then the next update command fails.

Still let’s just try one simple case:

Q2: What happens if you drop the primary key from a table in a publication?

sender=# create table t (a text primary key);
CREATE TABLE
sender=# create publication p for table t;
CREATE PUBLICATION
sender=# alter table t drop constraint t_pkey;
ALTER TABLE
sender=# insert into t values ('a');
INSERT 0 1
sender=# update t set a = 'b';
ERROR:  cannot update table "t" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

No problem dropping the key, but then we get a failure making an update. Also this test shows that no subscription is required.

Conclusion

So in all four cases, the validation happens when you update/delete a row and try to push the change through a PUBLICATION. Your change gets rolled back, so there is no inconsistency. I’m happy about this, because it lowers the risk of tricking Postgres into publishing changes it shouldn’t. Also it makes things easier for replicating temporal tables. (Of course that was my ulterior motive!)

blog comments powered by Disqus Next: Postgres Logical Replication