Обсуждение: Improve logical replication usability when tables lack primary keys

On Mon, Nov 10, 2025 at 1:36 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> * BACKGROUND
>
> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information
Systems).The situation can be summarized as follows: 
>
> - A central DB operations team maintains the main database and configures logical replication for all tables.
> - Multiple third-party application vendors are allowed to create new tables in that database.
> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY
DEFAULT`requires a primary key, such tables silently fail to replicate. 
> - The DB operations team must then spend significant effort identifying the affected tables and correcting them
manually.
>

Can you share an example of how we silently fail to replicate? Won't
in such cases UPDATE/DELETE will anyway raise an ERROR?

--
With Regards,
Amit Kapila.

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.


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

Hi Amit,

Thanks for asking.

> On Nov 11, 2025, at 19:18, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>
>> * BACKGROUND
>>
>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information
Systems).The situation can be summarized as follows: 
>>
>> - A central DB operations team maintains the main database and configures logical replication for all tables.
>> - Multiple third-party application vendors are allowed to create new tables in that database.
>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY
DEFAULT`requires a primary key, such tables silently fail to replicate. 
>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them
manually.
>>
>
> Can you share an example of how we silently fail to replicate? Won't
> in such cases UPDATE/DELETE will anyway raise an ERROR?
>

Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the update/delete.

However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently
failto replicate. 

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

> On Nov 11, 2025, at 20:09, Euler Taveira <euler@eulerto.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,
myproposal 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
becausethis 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
fallbackto full. I gave up that because it’s too much complicated than other solutions, and that would also required to
updateall 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
toupdate existing table’s replication identity; 2) the change only needs to be applied in the wal-sender side; 3)
withoutturning 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
mightbe 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/

> On Nov 11, 2025, at 20:09, Euler Taveira <euler@eulerto.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.
>

I’ve thought this over and discussed it with our field teams. It looks to us that introducing a new GUC like
default_replica_identitydoes 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
primarykey 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
alsoend 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
customizethis fallback, so that users can choose whether DEFAULT falls back to NOTHING or to FULL. Customizing the
fallbackvia 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
youexplain what use case you have in mind for it? 

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

On Tue, Nov 11, 2025 at 6:11 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> Hi Amit,
>
> Thanks for asking.
>
> > On Nov 11, 2025, at 19:18, Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Mon, Nov 10, 2025 at 1:36 PM Chao Li <li.evan.chao@gmail.com> wrote:
> >>
> >> * BACKGROUND
> >>
> >> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information
Systems).The situation can be summarized as follows: 
> >>
> >> - A central DB operations team maintains the main database and configures logical replication for all tables.
> >> - Multiple third-party application vendors are allowed to create new tables in that database.
> >> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY
DEFAULT`requires a primary key, such tables silently fail to replicate. 
> >> - The DB operations team must then spend significant effort identifying the affected tables and correcting them
manually.
> >>
> >
> > Can you share an example of how we silently fail to replicate? Won't
> > in such cases UPDATE/DELETE will anyway raise an ERROR?
> >
>
> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the
update/delete.
>
> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may
silentlyfail to replicate. 

But other than UPDATE/DELETE for what operation we need RI, I mean
INSERT would work without any RI and UPDATE/DELETE will fail on the
publisher itself without setting RI, so can you explain the exact case
where it will silently fail to replicate?

--
Regards,
Dilip Kumar
Google

> On Dec 15, 2025, at 11:28, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Tue, Nov 11, 2025 at 6:11 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>
>> Hi Amit,
>>
>> Thanks for asking.
>>
>>> On Nov 11, 2025, at 19:18, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>>
>>> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>>>
>>>> * BACKGROUND
>>>>
>>>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information
Systems).The situation can be summarized as follows: 
>>>>
>>>> - A central DB operations team maintains the main database and configures logical replication for all tables.
>>>> - Multiple third-party application vendors are allowed to create new tables in that database.
>>>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY
DEFAULT`requires a primary key, such tables silently fail to replicate. 
>>>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them
manually.
>>>>
>>>
>>> Can you share an example of how we silently fail to replicate? Won't
>>> in such cases UPDATE/DELETE will anyway raise an ERROR?
>>>
>>
>> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the
update/delete.
>>
>> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may
silentlyfail to replicate. 
>
> But other than UPDATE/DELETE for what operation we need RI, I mean
> INSERT would work without any RI and UPDATE/DELETE will fail on the
> publisher itself without setting RI, so can you explain the exact case
> where it will silently fail to replicate?
>
> --
> Regards,
> Dilip Kumar
> Google

Hi Dilip,

Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email,
attachedv1 patch is a PoC that has implemented the logic. 

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

On Mon, Dec 15, 2025 at 9:06 AM Chao Li <li.evan.chao@gmail.com> wrote:
>
> > On Dec 15, 2025, at 11:28, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Tue, Nov 11, 2025 at 6:11 PM Chao Li <li.evan.chao@gmail.com> wrote:
> >>
> >> Hi Amit,
> >>
> >> Thanks for asking.
> >>
> >>> On Nov 11, 2025, at 19:18, Amit Kapila <amit.kapila16@gmail.com> wrote:
> >>>
> >>> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <li.evan.chao@gmail.com> wrote:
> >>>>
> >>>> * BACKGROUND
> >>>>
> >>>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information
Systems).The situation can be summarized as follows: 
> >>>>
> >>>> - A central DB operations team maintains the main database and configures logical replication for all tables.
> >>>> - Multiple third-party application vendors are allowed to create new tables in that database.
> >>>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY
DEFAULT`requires a primary key, such tables silently fail to replicate. 
> >>>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them
manually.
> >>>>
> >>>
> >>> Can you share an example of how we silently fail to replicate? Won't
> >>> in such cases UPDATE/DELETE will anyway raise an ERROR?
> >>>
> >>
> >> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the
update/delete.
> >>
> >> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may
silentlyfail to replicate. 
> >
> > But other than UPDATE/DELETE for what operation we need RI, I mean
> > INSERT would work without any RI and UPDATE/DELETE will fail on the
> > publisher itself without setting RI, so can you explain the exact case
> > where it will silently fail to replicate?
> >
>
> Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email,
attachedv1 patch is a PoC that has implemented the logic. 
>

So, without patch, there is no way we can silently replicate the
UPDATE/DELETE. Ideally, users should alter the tables and make RI as
FULL in such cases if they don't have PK for such tables. Falling back
to FULL for DEFAULT when the table doesn't have PK based on GUC has a
downside that it will increase WAL volume by a large amount. I think
it should be done specific to tables that users want to replicate. I
don't know what is a good way to give to users who don't want to do
the required setup but if we really want to provide something, it is
better to allow such a thing via the publication option instead. I
think it would be good to do such an enhancement if we have more
community support and some other users also appreciate such a feature.
Otherwise, adding something which is specific to a particular user
sounds like a recipe of maintenance burden especially when we already
provide a way to achieve the same thing as is required by the user.

--
With Regards,
Amit Kapila.

On Sun, Dec 14, 2025, at 10:57 PM, Chao Li wrote:
> I’ve 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.
>

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 defaults 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
> created table that does have a primary key would also end up using
> 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
> 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?
>

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 tables if
you are in the same session. If the majority of your tables has a non-default
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_identity plus
a new value could improve your scenario. I don't have the complete picture 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 use
while creating a table.


[1] postgr.es/m/CAA4eK1KzjxO-qWjWSox6e6AWH4FVU5ZPEgeZ+na=eyov7umutg@mail.gmail.com


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

> On Dec 15, 2025, at 13:48, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Dec 15, 2025 at 9:06 AM Chao Li <li.evan.chao@gmail.com> wrote:
>>
>>> On Dec 15, 2025, at 11:28, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>>>
>>> On Tue, Nov 11, 2025 at 6:11 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>>>
>>>> Hi Amit,
>>>>
>>>> Thanks for asking.
>>>>
>>>>> On Nov 11, 2025, at 19:18, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>>>>
>>>>> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>>>>>
>>>>>> * BACKGROUND
>>>>>>
>>>>>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information
Systems).The situation can be summarized as follows: 
>>>>>>
>>>>>> - A central DB operations team maintains the main database and configures logical replication for all tables.
>>>>>> - Multiple third-party application vendors are allowed to create new tables in that database.
>>>>>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY
DEFAULT`requires a primary key, such tables silently fail to replicate. 
>>>>>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them
manually.
>>>>>>
>>>>>
>>>>> Can you share an example of how we silently fail to replicate? Won't
>>>>> in such cases UPDATE/DELETE will anyway raise an ERROR?
>>>>>
>>>>
>>>> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the
update/delete.
>>>>
>>>> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may
silentlyfail to replicate. 
>>>
>>> But other than UPDATE/DELETE for what operation we need RI, I mean
>>> INSERT would work without any RI and UPDATE/DELETE will fail on the
>>> publisher itself without setting RI, so can you explain the exact case
>>> where it will silently fail to replicate?
>>>
>>
>> Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email,
attachedv1 patch is a PoC that has implemented the logic. 
>>
>
> So, without patch, there is no way we can silently replicate the
> UPDATE/DELETE. Ideally, users should alter the tables and make RI as
> FULL in such cases if they don't have PK for such tables. Falling back
> to FULL for DEFAULT when the table doesn't have PK based on GUC has a
> downside that it will increase WAL volume by a large amount.

I agree that this downside exists, but it is an inherent cost that users must accept if they choose to replicate all
tables,including those without a primary key. In practice, users who opt into such a configuration are typically aware
ofthe WAL overhead and make that trade-off consciously. 

> I think it should be done specific to tables that users want to replicate.

That is why I mentioned earlier that the new GUC should only be configurable at the database level (via ALTER
DATABASE).However, I agree that there is still a risk that a user could mistakenly set it in postgresql.conf, thereby
makingit effective for the entire cluster. 

> I don't know what is a good way to give to users who don't want to do
> the required setup but if we really want to provide something, it is
> better to allow such a thing via the publication option instead.

Using a publication-level option could also work. One complication, however, is that a table can belong to multiple
publications.For example, if table_a belongs to both pub_a and pub_b, and only pub_a is configured with
fallback_to_fullwhile pub_b keeps the default behavior (fallback_to_none), then the effective behavior for table_a
wouldneed to remain fallback_to_none, meaning that UPDATE/DELETE would still not be allowed if table_a has not a
primarykey. 

> I think it would be good to do such an enhancement if we have more
> community support and some other users also appreciate such a feature.
> Otherwise, adding something which is specific to a particular user
> sounds like a recipe of maintenance burden especially when we already
> provide a way to achieve the same thing as is required by the user.

Let me elaborate on that point.

My company has a very large user base in China, with over 100K deployments across multiple industries. However, there
iscurrently a significant gap between this large user population and direct participation in the PG community. I joined
thecompany in July this year as a full-time contributor to the PG community, and one of my responsibilities is to help
bridgethis gap and bring real-world user feedback into community discussions. 

As I mentioned in my earlier email, this requirement comes from large-scale deployments. The database owners in these
environmentshave operational models that may not always align with what we consider the ideal or fully optimized setup,
butthey are the result of years of accumulated practice and operational experience. For these users, the proposed
featurewould significantly simplify their day-to-day operations and reduce operational friction. 

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

On Tuesday, December 16, 2025 2:47 PM Chao Li <li.evan.chao@gmail.com> wrote:
> > On Dec 15, 2025, at 13:48, Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > So, without patch, there is no way we can silently replicate the
> > UPDATE/DELETE. Ideally, users should alter the tables and make RI as
> > FULL in such cases if they don't have PK for such tables. Falling back
> > to FULL for DEFAULT when the table doesn't have PK based on GUC has a
> > downside that it will increase WAL volume by a large amount.
> 
> I agree that this downside exists, but it is an inherent cost that users must
> accept if they choose to replicate all tables, including those without a primary
> key. In practice, users who opt into such a configuration are typically aware of
> the WAL overhead and make that trade-off consciously.
> 
> > I think it should be done specific to tables that users want to replicate.
> 
> That is why I mentioned earlier that the new GUC should only be configurable
> at the database level (via ALTER DATABASE). However, I agree that there is
> still a risk that a user could mistakenly set it in postgresql.conf, thereby
> making it effective for the entire cluster.
> 
> > I don't know what is a good way to give to users who don't want to do
> > the required setup but if we really want to provide something, it is
> > better to allow such a thing via the publication option instead.
> 
> Using a publication-level option could also work. One complication, however,
> is that a table can belong to multiple publications. For example, if table_a
> belongs to both pub_a and pub_b, and only pub_a is configured with
> fallback_to_full while pub_b keeps the default behavior (fallback_to_none),
> then the effective behavior for table_a would need to remain
> fallback_to_none, meaning that UPDATE/DELETE would still not be allowed if
> table_a has not a primary key.

I think the common approach for combining options between publications is to
use an "OR" logic. For example, if at least one publication's option is true, we
treat the option as true for a given table. This pattern is evident in
CheckCmdReplicaIdentity(), where we conduct replica identity checks if any
publication replicates INSERTs/UPDATEs for the table even if some other publications
do not replicate.

And I also prefer using a publication option as it's always beneficial to
minimize unnecessary WAL generation whenever possible.

Best Regards,
Hou zj

On Wed, Dec 17, 2025, at 8:09 AM, Zhijie Hou (Fujitsu) wrote:
>
> And I also prefer using a publication option as it's always beneficial to
> minimize unnecessary WAL generation whenever possible.
>

The ship has sailed a long time ago (version 9.4 to be precise -- commit
07cacba983ef). The row identifier property was defined as an SQL command (ALTER
TABLE ... REPLICA IDENTITY) and *not* a publication property. IMO that's the
correct design because row identifier is a table property. Extend this concept
to a publication property is the wrong direction. It is confusing and complex.

Each table needs to say what's its row identifier. The user created a table
without primary key. Well, create a primary key. There are dozens of thousands
of objects. Use a script. I would suggest a way to disallow or add a warning
message while creating the publication or adding new tables, however, the FOR
ALL TABLES and FOR TABLES IN SCHEMA were mentioned. There isn't a reliable way
to guarantee that a publication with UPDATE and/or DELETE option contains only
tables with pk, RI FULL or RI USING INDEX. The fact that there is no rows in the
pg_publication_rel for these clauses, makes validating the CREATE/ALTER
PUBLICATION commands more difficult. (I prefer deterministic commands and when I
saw an object definition saying "including objects created in the future", my
first question is: what's the drawbacks and caveats?)

I don't think the current behavior is lacking documentation; the REPLICA
IDENTITY concept is explicitly in the logical replication chapter [1]. 


[1] https://www.postgresql.org/docs/current/logical-replication-publication.html


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

On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <euler@eulerto.com> wrote:
>> Each table needs to say what's its row identifier. The user created a table
>> without primary key. Well, create a primary key. There are dozens of thousands
>> of objects. Use a script.
> However, I’d like to share a user perspective regarding the "use a
> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> *in-database automation*. If users still need to maintain external
> scripts to monitor and `ALTER` new tables to prevent replication
> errors, it significantly diminishes the value of that automation.
>

As I tried to explain in the previous email, the problem with FOR ALL TABLES
and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
relations; the list of relations is collected at runtime.

When I suggested "use a script" I was referring to fix the logical replication
setup regarding the lack of primary key. There is no need to have an automation
outside the database, use an event trigger. If your lazy user doesn't create
the primary key, assign REPLICA IDENTITY FULL. Something like

-- This example is far from being a complete solution for fixing the lack of
-- primary key in a logical replication scenario.
-- ALTER TABLE should be supported too
CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
    rec record;
    ricnt integer := 0;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.command_tag = 'CREATE TABLE' THEN
            SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
            RAISE NOTICE 'ricnt: %', ricnt;
            IF ricnt = 0 THEN
                EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
            END IF;
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER event_trigger_for_replica_identity
    ON ddl_command_end
    EXECUTE FUNCTION event_trigger_for_replica_identity();

CREATE TABLE event_trigger_test_1 (a int);
\d+ event_trigger_test_1
CREATE TABLE event_trigger_test_2 (a int primary key);
\d+ event_trigger_test_2
CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
\d+ event_trigger_test_3
--ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
--\d+ event_trigger_test_3

DROP EVENT TRIGGER event_trigger_for_replica_identity;
DROP FUNCTION event_trigger_for_replica_identity;
DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;

8<----------------------------------------------------------------------------8<

> Additionally, tables without Primary Keys are valid SQL and extremely
> common in enterprise environments (e.g., audit logs, data warehousing).
> In large-scale deployments, enforcing PKs on every single table isn't
> always practical.
>

I'm not saying users shouldn't create tables without a primary key. I'm arguing
that this decision should take into account what adjustments need to be made to
use these tables in logical replication.

>
> I think the goal of this proposal is not to change the underlying table
> property design, but rather to seek a mechanism (like a Publication
> option) to ensure this automation functions safely without external
> intervention. It is simply about allowing the database to handle these
> valid, common scenarios gracefully when automation is enabled.
>

You didn't get it. You already have one property to handle it and you are
proposing to add a second property to handle it.

I think you are pursuing the wrong solution. IMO we need a solution to enforce
that the logical replication contract is valid. If you create or modify a table
that is part of a publication, there is no validation that that table complies
with the publication properties (update and delete properties should require an
appropriate replica identity). We should close the gaps in both publication and
table.


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

> On Dec 18, 2025, at 22:49, Euler Taveira <euler@eulerto.com> wrote:
>
> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <euler@eulerto.com> wrote:
>>> Each table needs to say what's its row identifier. The user created a table
>>> without primary key. Well, create a primary key. There are dozens of thousands
>>> of objects. Use a script.
>> However, I’d like to share a user perspective regarding the "use a
>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
>> *in-database automation*. If users still need to maintain external
>> scripts to monitor and `ALTER` new tables to prevent replication
>> errors, it significantly diminishes the value of that automation.
>>
>
> As I tried to explain in the previous email, the problem with FOR ALL TABLES
> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> relations; the list of relations is collected at runtime.
>
> When I suggested "use a script" I was referring to fix the logical replication
> setup regarding the lack of primary key. There is no need to have an automation
> outside the database, use an event trigger. If your lazy user doesn't create
> the primary key, assign REPLICA IDENTITY FULL. Something like
>
> -- This example is far from being a complete solution for fixing the lack of
> -- primary key in a logical replication scenario.
> -- ALTER TABLE should be supported too
> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
>        RETURNS event_trigger LANGUAGE plpgsql AS $$
> DECLARE
>    obj record;
>    rec record;
>    ricnt integer := 0;
> BEGIN
>    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>    LOOP
>        IF obj.command_tag = 'CREATE TABLE' THEN
>            SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
>            RAISE NOTICE 'ricnt: %', ricnt;
>            IF ricnt = 0 THEN
>                EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
>            END IF;
>        END IF;
>    END LOOP;
> END;
> $$;
>
> CREATE EVENT TRIGGER event_trigger_for_replica_identity
>    ON ddl_command_end
>    EXECUTE FUNCTION event_trigger_for_replica_identity();
>
> CREATE TABLE event_trigger_test_1 (a int);
> \d+ event_trigger_test_1
> CREATE TABLE event_trigger_test_2 (a int primary key);
> \d+ event_trigger_test_2
> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> \d+ event_trigger_test_3
> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> --\d+ event_trigger_test_3
>
> DROP EVENT TRIGGER event_trigger_for_replica_identity;
> DROP FUNCTION event_trigger_for_replica_identity;
> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
>
> 8<----------------------------------------------------------------------------8<
>
>> Additionally, tables without Primary Keys are valid SQL and extremely
>> common in enterprise environments (e.g., audit logs, data warehousing).
>> In large-scale deployments, enforcing PKs on every single table isn't
>> always practical.
>>
>
> I'm not saying users shouldn't create tables without a primary key. I'm arguing
> that this decision should take into account what adjustments need to be made to
> use these tables in logical replication.
>
>>
>> I think the goal of this proposal is not to change the underlying table
>> property design, but rather to seek a mechanism (like a Publication
>> option) to ensure this automation functions safely without external
>> intervention. It is simply about allowing the database to handle these
>> valid, common scenarios gracefully when automation is enabled.
>>
>
> You didn't get it. You already have one property to handle it and you are
> proposing to add a second property to handle it.
>
> I think you are pursuing the wrong solution. IMO we need a solution to enforce
> that the logical replication contract is valid. If you create or modify a table
> that is part of a publication, there is no validation that that table complies
> with the publication properties (update and delete properties should require an
> appropriate replica identity). We should close the gaps in both publication and
> table.
>

If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG
wegenerally prefer explicit over implicit behavior, and predictability over magic. 

Based on the discussion so far, I think we share the following design goals:

1) Keep replica identity as a table property.
2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
3) Avoid global or implicit behavior changes.
4) Preserve explicit opt-in for higher WAL cost.
5) Keep the logical replication contract explicit and enforceable.

I’ve been thinking about whether adding a new replica identity could meet these goals.

Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.

What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our
focuson the design, not argue the name for now.) 

With this approach:

1) Replica identity remains a table property.
2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
3) No new GUCs are required.
4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary
keylater does not silently break UPDATE/DELETE replication. 
5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication
evenwithout a PK, at the cost of higher WAL volume. 

This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has
conditionalbehavior (PK fallback 
to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.

After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for
environmentsthat want this behavior consistently. But that would only make sense if we first agree on the table-level
mechanism.

I’m interested in whether this direction aligns better with the goals above.

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

On Fri, Dec 19, 2025 at 1:39 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> > On Dec 18, 2025, at 22:49, Euler Taveira <euler@eulerto.com> wrote:
> >
> > On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> >> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <euler@eulerto.com> wrote:
> >>> Each table needs to say what's its row identifier. The user created a table
> >>> without primary key. Well, create a primary key. There are dozens of thousands
> >>> of objects. Use a script.
> >> However, I’d like to share a user perspective regarding the "use a
> >> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> >> *in-database automation*. If users still need to maintain external
> >> scripts to monitor and `ALTER` new tables to prevent replication
> >> errors, it significantly diminishes the value of that automation.
> >>
> >
> > As I tried to explain in the previous email, the problem with FOR ALL TABLES
> > and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> > relations; the list of relations is collected at runtime.
> >
> > When I suggested "use a script" I was referring to fix the logical replication
> > setup regarding the lack of primary key. There is no need to have an automation
> > outside the database, use an event trigger. If your lazy user doesn't create
> > the primary key, assign REPLICA IDENTITY FULL. Something like
> >
> > -- This example is far from being a complete solution for fixing the lack of
> > -- primary key in a logical replication scenario.
> > -- ALTER TABLE should be supported too
> > CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
> >        RETURNS event_trigger LANGUAGE plpgsql AS $$
> > DECLARE
> >    obj record;
> >    rec record;
> >    ricnt integer := 0;
> > BEGIN
> >    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
> >    LOOP
> >        IF obj.command_tag = 'CREATE TABLE' THEN
> >            SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
> >            RAISE NOTICE 'ricnt: %', ricnt;
> >            IF ricnt = 0 THEN
> >                EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
> >            END IF;
> >        END IF;
> >    END LOOP;
> > END;
> > $$;
> >
> > CREATE EVENT TRIGGER event_trigger_for_replica_identity
> >    ON ddl_command_end
> >    EXECUTE FUNCTION event_trigger_for_replica_identity();
> >
> > CREATE TABLE event_trigger_test_1 (a int);
> > \d+ event_trigger_test_1
> > CREATE TABLE event_trigger_test_2 (a int primary key);
> > \d+ event_trigger_test_2
> > CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> > \d+ event_trigger_test_3
> > --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> > --\d+ event_trigger_test_3
> >
> > DROP EVENT TRIGGER event_trigger_for_replica_identity;
> > DROP FUNCTION event_trigger_for_replica_identity;
> > DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
> >
> > 8<----------------------------------------------------------------------------8<
> >
> >> Additionally, tables without Primary Keys are valid SQL and extremely
> >> common in enterprise environments (e.g., audit logs, data warehousing).
> >> In large-scale deployments, enforcing PKs on every single table isn't
> >> always practical.
> >>
> >
> > I'm not saying users shouldn't create tables without a primary key. I'm arguing
> > that this decision should take into account what adjustments need to be made to
> > use these tables in logical replication.
> >
> >>
> >> I think the goal of this proposal is not to change the underlying table
> >> property design, but rather to seek a mechanism (like a Publication
> >> option) to ensure this automation functions safely without external
> >> intervention. It is simply about allowing the database to handle these
> >> valid, common scenarios gracefully when automation is enabled.
> >>
> >
> > You didn't get it. You already have one property to handle it and you are
> > proposing to add a second property to handle it.
> >
> > I think you are pursuing the wrong solution. IMO we need a solution to enforce
> > that the logical replication contract is valid. If you create or modify a table
> > that is part of a publication, there is no validation that that table complies
> > with the publication properties (update and delete properties should require an
> > appropriate replica identity). We should close the gaps in both publication and
> > table.
> >

If we want, we can ensure that any table added to that specific
publication (that has an option replica_identy='full') would
automatically override the default to FULL, if PK is not available.
This information can be cached to avoid overhead.

>
> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In
PGwe generally prefer explicit over implicit behavior, and predictability over magic. 
>

You haven't told why we can't consider a custom event trigger as
suggested by Euler for customers who are not willing to change the RI
default explicitly for each table. I think it is worth considering
providing a custom solution outside core-postgres for your customers
for this specific case.

> Based on the discussion so far, I think we share the following design goals:
>
> 1) Keep replica identity as a table property.
> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
> 3) Avoid global or implicit behavior changes.
> 4) Preserve explicit opt-in for higher WAL cost.
> 5) Keep the logical replication contract explicit and enforceable.
>
> I’ve been thinking about whether adding a new replica identity could meet these goals.
>
> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>
> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our
focuson the design, not argue the name for now.) 
>
> With this approach:
>
> 1) Replica identity remains a table property.
> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
> 3) No new GUCs are required.
> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a
primarykey later does not silently break UPDATE/DELETE replication. 
>
> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE
replicationeven without a PK, at the cost of higher WAL volume. 
>
> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already
hasconditional behavior (PK fallback 
> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
>
> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for
environmentsthat want this behavior consistently. But that would only make sense if we first agree on the table-level
mechanism.
>

I don't much like the database-level option as it expects a new
default to be introduced. I think the internal working will almost be
same as the option at publication-level.

--
With Regards,
Amit Kapila.

> On Dec 22, 2025, at 19:48, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Dec 19, 2025 at 1:39 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>
>>> On Dec 18, 2025, at 22:49, Euler Taveira <euler@eulerto.com> wrote:
>>>
>>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <euler@eulerto.com> wrote:
>>>>> Each table needs to say what's its row identifier. The user created a table
>>>>> without primary key. Well, create a primary key. There are dozens of thousands
>>>>> of objects. Use a script.
>>>> However, I’d like to share a user perspective regarding the "use a
>>>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
>>>> *in-database automation*. If users still need to maintain external
>>>> scripts to monitor and `ALTER` new tables to prevent replication
>>>> errors, it significantly diminishes the value of that automation.
>>>>
>>>
>>> As I tried to explain in the previous email, the problem with FOR ALL TABLES
>>> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
>>> relations; the list of relations is collected at runtime.
>>>
>>> When I suggested "use a script" I was referring to fix the logical replication
>>> setup regarding the lack of primary key. There is no need to have an automation
>>> outside the database, use an event trigger. If your lazy user doesn't create
>>> the primary key, assign REPLICA IDENTITY FULL. Something like
>>>
>>> -- This example is far from being a complete solution for fixing the lack of
>>> -- primary key in a logical replication scenario.
>>> -- ALTER TABLE should be supported too
>>> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
>>>       RETURNS event_trigger LANGUAGE plpgsql AS $$
>>> DECLARE
>>>   obj record;
>>>   rec record;
>>>   ricnt integer := 0;
>>> BEGIN
>>>   FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>>>   LOOP
>>>       IF obj.command_tag = 'CREATE TABLE' THEN
>>>           SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
>>>           RAISE NOTICE 'ricnt: %', ricnt;
>>>           IF ricnt = 0 THEN
>>>               EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
>>>           END IF;
>>>       END IF;
>>>   END LOOP;
>>> END;
>>> $$;
>>>
>>> CREATE EVENT TRIGGER event_trigger_for_replica_identity
>>>   ON ddl_command_end
>>>   EXECUTE FUNCTION event_trigger_for_replica_identity();
>>>
>>> CREATE TABLE event_trigger_test_1 (a int);
>>> \d+ event_trigger_test_1
>>> CREATE TABLE event_trigger_test_2 (a int primary key);
>>> \d+ event_trigger_test_2
>>> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
>>> \d+ event_trigger_test_3
>>> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
>>> --\d+ event_trigger_test_3
>>>
>>> DROP EVENT TRIGGER event_trigger_for_replica_identity;
>>> DROP FUNCTION event_trigger_for_replica_identity;
>>> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
>>>
>>> 8<----------------------------------------------------------------------------8<
>>>
>>>> Additionally, tables without Primary Keys are valid SQL and extremely
>>>> common in enterprise environments (e.g., audit logs, data warehousing).
>>>> In large-scale deployments, enforcing PKs on every single table isn't
>>>> always practical.
>>>>
>>>
>>> I'm not saying users shouldn't create tables without a primary key. I'm arguing
>>> that this decision should take into account what adjustments need to be made to
>>> use these tables in logical replication.
>>>
>>>>
>>>> I think the goal of this proposal is not to change the underlying table
>>>> property design, but rather to seek a mechanism (like a Publication
>>>> option) to ensure this automation functions safely without external
>>>> intervention. It is simply about allowing the database to handle these
>>>> valid, common scenarios gracefully when automation is enabled.
>>>>
>>>
>>> You didn't get it. You already have one property to handle it and you are
>>> proposing to add a second property to handle it.
>>>
>>> I think you are pursuing the wrong solution. IMO we need a solution to enforce
>>> that the logical replication contract is valid. If you create or modify a table
>>> that is part of a publication, there is no validation that that table complies
>>> with the publication properties (update and delete properties should require an
>>> appropriate replica identity). We should close the gaps in both publication and
>>> table.
>>>
>
> If we want, we can ensure that any table added to that specific
> publication (that has an option replica_identy='full') would
> automatically override the default to FULL, if PK is not available.
> This information can be cached to avoid overhead.
>
>>
>> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In
PGwe generally prefer explicit over implicit behavior, and predictability over magic. 
>>
>
> You haven't told why we can't consider a custom event trigger as
> suggested by Euler for customers who are not willing to change the RI
> default explicitly for each table. I think it is worth considering
> providing a custom solution outside core-postgres for your customers
> for this specific case.

Thanks for raising this. Let me clarify why we don’t consider a custom event trigger a satisfactory solution in
practice,even though it is technically possible. 

I discussed this with our field teams, and some customers have indeed experimented with event-trigger-based solutions
before.However, they generally don’t prefer them for this use case. 

First, the required logic is non-trivial and fragile. The trigger would need to track table creation, primary key
creationand removal, and distinguish between cases where REPLICA IDENTITY FULL was set implicitly versus explicitly by
theuser. Handling all these cases correctly makes the solution feel like a workaround rather than a robust enforcement
mechanism.

Second, event triggers introduce operational risk. They need to be installed, monitored, and maintained separately from
thecore system. If a trigger is accidentally dropped, disabled, or modified, the behavior silently changes, which is
particularlyrisky for replication semantics. 

Third, customers place much higher trust in core PostgreSQL behavior than in custom scripts layered on top. Issues
causedby core behavior are seen as something that can be understood, worked around, or fixed by upgrading, whereas
failurescaused by custom triggers are harder to diagnose and are often attributed to the overall solution quality. 

For these reasons, while event triggers can work as a stopgap, our customers strongly prefer a solution where the
replicationcontract is enforced by core PostgreSQL rather than external mechanisms. 

>
>> Based on the discussion so far, I think we share the following design goals:
>>
>> 1) Keep replica identity as a table property.
>> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
>> 3) Avoid global or implicit behavior changes.
>> 4) Preserve explicit opt-in for higher WAL cost.
>> 5) Keep the logical replication contract explicit and enforceable.
>>
>> I’ve been thinking about whether adding a new replica identity could meet these goals.
>>
>> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>>
>> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our
focuson the design, not argue the name for now.) 
>>
>> With this approach:
>>
>> 1) Replica identity remains a table property.
>> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
>> 3) No new GUCs are required.
>> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a
primarykey later does not silently break UPDATE/DELETE replication. 
>>
>> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE
replicationeven without a PK, at the cost of higher WAL volume. 
>>
>> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already
hasconditional behavior (PK fallback 
>> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
>>
>> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for
environmentsthat want this behavior consistently. But that would only make sense if we first agree on the table-level
mechanism.
>>
>
> I don't much like the database-level option as it expects a new
> default to be introduced. I think the internal working will almost be
> same as the option at publication-level.

That’s fair. I agree that a database-level option wouldn’t be fundamentally different from a publication-level solution
andwould likely share most of the same internal mechanics. 

At this point nothing is decided yet; we’re still exploring different approaches and trying to understand the
trade-offs.

I have a question to better understand how a publication-level approach would behave in edge cases.

Since replica identity is defined on tables and a table can belong to multiple publications, how should UPDATE/DELETE
behandled if the same table is added to two publications with different expectations? 

For example, suppose a table without a PK is added to:
- pub_a, which does not require FULL (or effectively falls back to NONE)
- pub_b, which requires FULL for UPDATE/DELETE

In this case, should UPDATE/DELETE on the table be allowed at all, and if so, based on which publication’s semantics?
Whatdo you think? 

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