Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Mon, 15 Dec 2025 22:47:12 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Chao Li" <li.evan.chao@gmail.com>
Cc: "Postgres hackers" <pgsql-hackers@lists.postgresql.org>,
 "Amit Kapila" <amit.kapila16@gmail.com>
Message-Id: <7cd087f7-c914-4c3c-9556-dafcfd38eefb@app.fastmail.com>
In-Reply-To: <7BC72FB9-2235-414F-9D45-EA2C8A2FF09A@gmail.com>
References: 
 <CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com>
 <ca7182b5-6817-4a74-802d-c3756f64f514@app.fastmail.com>
 <7BC72FB9-2235-414F-9D45-EA2C8A2FF09A@gmail.com>
Subject: Re: Improve logical replication usability when tables lack primary
 keys
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/7cd087f7-c914-4c3c-9556-dafcfd38eefb%40app.fastmail.com>
Precedence: bulk

On Sun, Dec 14, 2025, at 10:57 PM, Chao Li wrote:
> I=E2=80=99ve thought this over and discussed it with our field teams. =
It looks=20
> to us that introducing a new GUC like default_replica_identity does no=
t=20
> really address our pain point.
>
> Our core requirement is to allow tables without a primary key to use=20
> FULL as the replica identity, while tables with a primary key should=20
> continue to use DEFAULT.
>

As Amit said in [1], this proposal is not viable because of WAL volume. =
Since
you don't know if table foo without primary key will replicate, it defau=
lts to
log the old tuple even if you never add this table to a publication.

> If we add default_replica_identity and set it to FULL, then a newly=20
> created table that does have a primary key would also end up using=20
> FULL, which is definitely not what we want.
>

You can propose a different behavior. Let's say FULL_NO_PK.

> As you mentioned, PostgreSQL already has a fallback from DEFAULT to=20
> NOTHING. What we actually want is the ability to customize this=20
> fallback, so that users can choose whether DEFAULT falls back to=20
> NOTHING or to FULL. Customizing the fallback via a new GUC would also=20
> allow field teams to set this option per database.
>
> If we do want to add default_replica_identity, I think that should be=20
> treated as a separate topic. By the way, could you explain what use=20
> case you have in mind for it?
>

It is an alternative way for ALTER TABLE ... REPLICA IDENTITY to set a
non-default value. It also has the advantage of applying to multiple tab=
les if
you are in the same session. If the majority of your tables has a non-de=
fault
replica identity, it would drastically reduce the number of ALTER TABLE =
...
REPLICA IDENTITY lines in your dump.

I think it is unlikely that we would change the replica identity default=
 value
but we could consider additional ones. Hence, the default_replica_identi=
ty plus
a new value could improve your scenario. I don't have the complete pictu=
re so I
cannot propose a feasible solution. Maybe what I said is enough or maybe=
 not.

Regarding the default_replica_identity proposal, it is just a value to u=
se
while creating a table.


[1] postgr.es/m/CAA4eK1KzjxO-qWjWSox6e6AWH4FVU5ZPEgeZ+na=3Deyov7umutg@ma=
il.gmail.com


--=20
Euler Taveira
EDB   https://www.enterprisedb.com/