Re: Improve logical replication usability when tables lack primary keys

> On Nov 11, 2025, at 20:09, Euler Taveira <euler(at)eulerto(dot)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, my proposal 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 because this 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 fallback to full. I gave up that because it’s too much complicated than other solutions, and that would also required to update all 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 to update existing table’s replication identity; 2) the change only needs to be applied in the wal-sender side; 3) without turning 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 might be 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/