public inbox for [email protected]  

help / color / mirror / Atom feed

From: Chao Li <[email protected]>
To: Euler Taveira <[email protected]>
Cc: Postgres hackers <[email protected]>
Subject: Re: Improve logical replication usability when tables lack primary keys
Date: Wed, 12 Nov 2025 10:21:40 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com>
	<[email protected]>



> On Nov 11, 2025, at 20:09, Euler Taveira <[email protected]> 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/

view thread (26+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Improve logical replication usability when tables lack primary keys
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox