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: Mon, 15 Dec 2025 09:57:49 +0800
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>,
 Amit Kapila <amit.kapila16@gmail.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <7BC72FB9-2235-414F-9D45-EA2C8A2FF09A@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/7BC72FB9-2235-414F-9D45-EA2C8A2FF09A%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

I=E2=80=99ve 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.

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.

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?

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