Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: Improve logical replication usability when tables lack primary
 keys
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <ca7182b5-6817-4a74-802d-c3756f64f514@app.fastmail.com>
Date: Wed, 12 Nov 2025 10:21:40 +0800
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <6F18CF89-F8F1-49DE-A85A-6ED2723FBE76@gmail.com>
References: 
 <CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com>
 <ca7182b5-6817-4a74-802d-c3756f64f514@app.fastmail.com>
To: Euler Taveira <euler@eulerto.com>
Archived-At: 
 <https://www.postgresql.org/message-id/6F18CF89-F8F1-49DE-A85A-6ED2723FBE76%40gmail.com>
Precedence: bulk


> On Nov 11, 2025, at 20:09, Euler Taveira <euler@eulerto.com> wrote:
>=20
> On Mon, Nov 10, 2025, at 5:06 AM, Chao Li wrote:
>> I evaluated a few approaches and am proposing the following:
>>=20
>> - Introduce a new GUC: =
`logical_replication_fallback_to_full_identity`.
>> - When enabled, if a table being logically replicated has no primary=20=

>> key, the system automatically uses `REPLICATION IDENTITY FULL` for =
that=20
>> table.
>> - This setting can be applied at the database level, so large systems=20=

>> do not need to enable it cluster-wide unless desired.
>> - When the WAL sender transmits relation metadata, if fallback has=20
>> occurred, it explicitly reports `FULL` as the replication identity to=20=

>> the subscriber, so there is limited impact on the subscriber.
>>=20
>=20
> 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.
>=20
> 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.
>=20

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 =E2=80=9Cnothin=
g=E2=80=9D and =E2=80=9Cfull=E2=80=9D. 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=E2=80=99 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=E2=80=99s too much complicated than other solutions, and =
that would also required to update all existing tables=E2=80=99 =
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=E2=80=99s 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=E2=80=99t aware of that), I think 2 as you suggested might be the =
direction to go along with.

Let=E2=80=99s 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/