Re: Improve logical replication usability when tables lack primary keys - Mailing list pgsql-hackers

> On Nov 11, 2025, at 20:09, Euler Taveira <euler@eulerto.com> wrote:
>
> On Mon, Nov 10, 2025, at 5:06 AM, Chao Li wrote:
>> I evaluated a few approaches and am proposing the following:
>>
>> - Introduce a new GUC: `logical_replication_fallback_to_full_identity`.
>> - When enabled, if a table being logically replicated has no primary
>> key, the system automatically uses `REPLICATION IDENTITY FULL` for that
>> table.
>> - This setting can be applied at the database level, so large systems
>> do not need to enable it cluster-wide unless desired.
>> - When the WAL sender transmits relation metadata, if fallback has
>> occurred, it explicitly reports `FULL` as the replication identity to
>> the subscriber, so there is limited impact on the subscriber.
>>
>
> If I understand your proposal correctly, you want to add a new fallback to
> replica identity. We already have a fallback for DEFAULT that means no primary
> key is the same as NOTHING. I didn't like your proposal. It is too restrictive.
>
> However, I see some usefulness in introducing a GUC default_replica_identity.
> The proposal is similar to access method (default_table_access_method). The
> DEFAULT option selects the replica identity sets as default_replica_identity
> parameter. You need to add a new option (PRIMARY KEY); that should be the
> default value. (If we don't want to break the backward compatibility, this new
> option should fallback to NOTHING if there is no primary key. Another
> alternative is to have a strict and non-strict option. I prefer the former.) Of
> course, the USING INDEX option cannot be used. For pg_dump, you need to use SET
> command to inform the default_replica_identity value so tables with the same
> option as default_replica_identity doesn't emit an ALTER TABLE command.
>

Hi Euler,

Thank you very much for the valuable feedback. These are a lot of useful information. As I mentioned in my first email,
myproposal was just an initial implementation, I am open for discussion from the design perspective. 

Actually I explored the solution of adding a GUC for default_replication_identify. Let me briefly list solutions I
explored:

1. The first solution I explored was adding a GUC for replication_identify_fallback_method, possible options are
“nothing”and “full”. I gave up that because the solution is also an equivalent to the one I proposed of a bool option
(false->nothing,true->full) and a bool option is easier to use. 

2. Then I considered to add a GUC for default replication identity which is the same as you suggested. I gave up that
becausethis solution would require to update all existing tables’ replication identities. 

3. I also considered to add a new replication identity, I hadn't named it, but meaning was using primary key and
fallbackto full. I gave up that because it’s too much complicated than other solutions, and that would also required to
updateall existing tables’ replication identities. 

4. Finally I decided the one I proposed. The main reason I chose it is because 1) production deployments wouldn't need
toupdate existing table’s replication identity; 2) the change only needs to be applied in the wal-sender side; 3)
withoutturning on the GUC option, no any impact. 

Given there is a similar GUC option default_table_access_method (I wasn’t aware of that), I think 2 as you suggested
mightbe the direction to go along with. 

Let’s wait a few more days to see if other folks may comment as well.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/