Re: Improve logical replication usability when tables lack primary keys

On Sun, Dec 14, 2025, at 10:57 PM, Chao Li wrote:
> I’ve thought this over and discussed it with our field teams. It looks
> to us that introducing a new GUC like default_replica_identity does not
> really address our pain point.
>
> Our core requirement is to allow tables without a primary key to use
> FULL as the replica identity, while tables with a primary key should
> continue to use DEFAULT.
>

As Amit said in [1], this proposal is not viable because of WAL volume. Since
you don't know if table foo without primary key will replicate, it defaults to
log the old tuple even if you never add this table to a publication.

> If we add default_replica_identity and set it to FULL, then a newly
> created table that does have a primary key would also end up using
> FULL, which is definitely not what we want.
>

You can propose a different behavior. Let's say FULL_NO_PK.

> As you mentioned, PostgreSQL already has a fallback from DEFAULT to
> NOTHING. What we actually want is the ability to customize this
> fallback, so that users can choose whether DEFAULT falls back to
> NOTHING or to FULL. Customizing the fallback via a new GUC would also
> allow field teams to set this option per database.
>
> If we do want to add default_replica_identity, I think that should be
> treated as a separate topic. By the way, could you explain what use
> case you have in mind for it?
>

It is an alternative way for ALTER TABLE ... REPLICA IDENTITY to set a
non-default value. It also has the advantage of applying to multiple tables if
you are in the same session. If the majority of your tables has a non-default
replica identity, it would drastically reduce the number of ALTER TABLE ...
REPLICA IDENTITY lines in your dump.

I think it is unlikely that we would change the replica identity default value
but we could consider additional ones. Hence, the default_replica_identity plus
a new value could improve your scenario. I don't have the complete picture so I
cannot propose a feasible solution. Maybe what I said is enough or maybe not.

Regarding the default_replica_identity proposal, it is just a value to use
while creating a table.

[1] postgr.es/m/CAA4eK1KzjxO-qWjWSox6e6AWH4FVU5ZPEgeZ+na=eyov7umutg@mail.gmail.com

--
Euler Taveira
EDB https://www.enterprisedb.com/