Subject: Improve logical replication usability when tables lack primary keys

From: Chao Li <[email-masked]>
Date: Mon, 10 Nov 2025 16:06:36 +0800

* 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.

In practice, these environments would benefit from a safe fallback: if a
table has no primary key, logical replication should automatically switch
from `REPLICATION IDENTITY DEFAULT` to `FULL`, ensuring replication
continues rather than breaking.

I don't intend to debate whether this operational model is ideal; it is
simply the reality in many deployments. These database operations teams
have developed and refined their practices over many years, and as a
database vendor we have limited influence over how they manage their
environments.

* PROPOSED SOLUTION

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.

* NEXT STEPS

The attached patch is an initial implementation. It does not yet include
tests or documentation updates. I would appreciate feedback on the design
approach first. If the direction seems reasonable, I will proceed with
refining the patch and adding documentation and tests.

Thanks in advance for your review.

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

---

From: Amit Kapila <[email-masked]>
Date: Tue, 11 Nov 2025 16:48:54 +0530

On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email-masked]> 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.

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 11 Nov 2025 09:09:02 -0300

On Mon, Nov 10, 2025, at 5:06 AM, Chao Li wrote:
> - 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.
> 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/

---

From: Chao Li <[email-masked]>
Date: Tue, 11 Nov 2025 20:40:39 +0800

Hi Amit,

Thanks for asking.

> On Nov 11, 2025, at 19:18, Amit Kapila <[email-masked]> wrote:
> 

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 fail to replicate.

Best regards,

---

From: Chao Li <[email-masked]>
Date: Wed, 12 Nov 2025 10:21:40 +0800

> On Nov 11, 2025, at 20:09, Euler Taveira <[email-masked]> wrote:
> 

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,

---

From: Chao Li <[email-masked]>
Date: Mon, 15 Dec 2025 09:57:49 +0800

> 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_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,

---

From: Dilip Kumar <[email-masked]>
Date: Mon, 15 Dec 2025 08:58:20 +0530

> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently fail 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

---

From: Chao Li <[email-masked]>
Date: Mon, 15 Dec 2025 11:35:36 +0800

> On Dec 15, 2025, at 11:28, Dilip Kumar <[email-masked]> wrote:
> 

Hi Dilip,

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

Best regards,

---

From: Amit Kapila <[email-masked]>
Date: Mon, 15 Dec 2025 11:18:14 +0530

> Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email, attached v1 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.

---

From: "Euler Taveira" <[email-masked]>
Date: Mon, 15 Dec 2025 22:47:12 -0300

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=[email-masked]

---

From: Chao Li <[email-masked]>
Date: Tue, 16 Dec 2025 14:47:26 +0800

> On Dec 15, 2025, at 13:48, Amit Kapila <[email-masked]> wrote:
> 
> 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
> 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 it would be good to do such an enhancement if we have more

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 is currently a significant gap between this large user population and direct participation in the PG community. I joined the company in July this year as a full-time contributor to the PG community, and one of my responsibilities is to help bridge this 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 environments have operational models that may not always align with what we consider the ideal or fully optimized setup, but they are the result of years of accumulated practice and operational experience. For these users, the proposed feature would significantly simplify their day-to-day operations and reduce operational friction.

Best regards,

---

From: GRANT ZHOU <[email-masked]>
Date: Wed, 17 Dec 2025 01:32:39 -0800

On Tue, Dec 16, 2025 at 4:59 PM Chao Li <[email-masked]> wrote:

> 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.
>
> 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.
>
> My company has a very large user base in China, with over 100K deployments
> across multiple industries. However, there is currently a significant gap
> between this large user population and direct participation in the PG
> community. I joined the company in July this year as a full-time
> contributor to the PG community, and one of my responsibilities is to help
> bridge this 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 environments have
> operational models that may not always align with what we consider the
> ideal or fully optimized setup, but they are the result of years of
> accumulated practice and operational experience. For these users, the
> proposed feature would significantly simplify their day-to-day operations
> and reduce operational friction.
>

+1 on the importance of addressing these large-scale operational realities.

Beyond the scale issue, I believe there is a noticeable inconsistency
between the documentation's promise of automation and the actual behavior
of Replica Identity.

1. The "Practical Gap" of Schema Automation
According to the documentation for FOR TABLES IN SCHEMA [1], the feature
matches "all tables in the specified list of schemas, including tables
created in the future". This explicitly promises an unattended, automated
workflow for new tables.

However, this promise is immediately broken by the default Replica Identity
rules:
1) New tables are created with REPLICA IDENTITY DEFAULT [2] by standard.
2) For tables without a primary key, DEFAULT identity "cannot support
UPDATE or DELETE operations" and "attempting such operations will result in
an error on the publisher"[3].

This creates a logical trap: The system automatically adds the new table to
the publication (as promised), but then immediately fails on the first
UPDATE operation because the table creates with an incompatible default
identity.
This forces manual intervention (ALTER TABLE) in what is supposed to be an
automated workflow.

2. Regarding the solution:
I support Amit's suggestion of a Publication Option. It avoids the risks of
a global GUC while allowing users to explicitly opt-in to the trade-off
(accepting higher WAL volume) to ensure the automation provided by FOR
TABLES IN SCHEMA is functionally complete.

[1]
https://www.postgresql.org/docs/current/sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-FOR-TABLES-IN-SCHEMA
[2]
https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY
[3]
https://www.postgresql.org/docs/18/logical-replication-publication.html#LOGICAL-REPLICATION-PUBLICATION-REPLICA-IDENTITY

--
Grant Zhou at Highgo Software

---

From: "Zhijie Hou (Fujitsu)" <[email-masked]>
Date: Wed, 17 Dec 2025 11:09:30 +0000

On Tuesday, December 16, 2025 2:47 PM Chao Li <[email-masked]> wrote:
> 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.
> 
> 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.
> 
> 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

---

From: "Euler Taveira" <[email-masked]>
Date: Wed, 17 Dec 2025 17:49:03 -0300

> 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

---

From: GRANT ZHOU <[email-masked]>
Date: Wed, 17 Dec 2025 13:43:31 -0800

> to a publication property is the wrong direction. It is confusing and
Thanks for the detailed history. I completely understand and respect that
Replica Identity is designed as a table property.

> 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.

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 would suggest a way to disallow or add a warning

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.

--
Grant Zhou
Highgo Software

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 18 Dec 2025 11:49:02 -0300

On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> 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<

> 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.

> 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.

---

From: Chao Li <[email-masked]>
Date: Fri, 19 Dec 2025 16:08:39 +0800

> On Dec 18, 2025, at 22:49, Euler Taveira <[email-masked]> wrote:
> 

If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally 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 focus on 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 key 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 replication even 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 has conditional 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 environments that 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,

---

From: Amit Kapila <[email-masked]>
Date: Mon, 22 Dec 2025 17:18:41 +0530

> > DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;

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 PG we generally prefer explicit over implicit behavior, and predictability over…

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.

> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for environments that want this behavior consistently. But that would only make sens…

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.

---

From: Chao Li <[email-masked]>
Date: Tue, 30 Dec 2025 16:07:37 +0800

> On Dec 22, 2025, at 19:48, Amit Kapila <[email-masked]> wrote:
> 

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 creation and removal, and distinguish between cases where REPLICA IDENTITY FULL was set implicitly versus explicitly by the user. 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 the core system. If a trigger is accidentally dropped, disabled, or modified, the behavior silently changes, which is particularly risky for replication semantics.

Third, customers place much higher trust in core PostgreSQL behavior than in custom scripts layered on top. Issues caused by core behavior are seen as something that can be understood, worked around, or fixed by upgrading, whereas failures caused 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 replication contract is enforced by core PostgreSQL rather than external mechanisms.

> 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 and would 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 be handled 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? What do you think?

Best regards,

---

From: Chao Li <[email-masked]>
Date: Wed, 4 Feb 2026 15:21:32 +0800

> On Dec 30, 2025, at 16:07, Chao Li <[email-masked]> wrote:
> 
> 
> 

Hi Amit,

Following your suggestion, I implemented a PoC that adds a new publication parameter (tentatively named fallbackfull) to make the DEFAULT → FULL fallback behavior per-publication. I’m not attached to the parameter name — if we decide to go with the publication approach, I’m happy to adjust naming based on feedback.

After playing with this implementation for a couple of days, I ran into a few concerns:

1. Protocol extension required

If the DEFAULT → FULL fallback is triggered, the subscriber needs to know whether the corresponding publication has fallbackfull enabled in order to decide how to apply UPDATE/DELETE. That means we’d need to extend the logical replication protocol, e.g., by adding a new field to the RELATION message to carry the fallbackfull flag.

2. Impact on decoding plugins

Decoding plugins would need to understand this new flag. In my PoC, I updated pgoutput, but there may be third-party plugins that would also need changes. That feels like a compatibility risk.

3. Potential data-integrity issues

This is the most concerning part to me.

Consider a table t1 with REPLICA IDENTITY DEFAULT and no primary key, included in publication p1. By design, UPDATE/DELETE on t1 are not allowed.

However, a user could work around this by creating a dummy publication, adding t1 to it, and setting fallbackfull = true on that publication. This would effectively enable UPDATE/DELETE on t1.

Later, if the owner of p1 decides to enable fallbackfull on p1 to replicate t1, the subscriber of p1 may already be out of sync due to the earlier updates/deletes performed via the dummy publication. At that point, subsequent UPDATE/DELETE replication may fail or behave incorrectly.

From this perspective, allowing fallbackfull at the publication level seems to open the door to cross-publication interference and data divergence.

Given these concerns, I’m leaning toward keeping fallbackfull as a per-table option rather than a per-publication one. Curious to hear your thoughts.

I’ve attached v2 of the PoC implementing the publication-level approach for reference.

Best regards,

---

From: shveta malik <[email-masked]>
Date: Wed, 8 Apr 2026 15:52:14 +0530

> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.

Even though I like the idea of keeping replica identity as a table
property, this raises a question about the need for a new FORCE
option. With this approach, the user must explicitly opt REPLICA
IDENTITY FORCE for tables without a primary key (pt 4 in your design).
But if the user is already expected to take an explicit action, they
could simply set REPLICA IDENTITY FULL themselves at table creation
time or via ALTER TABLE. From what I understood, the original
requirement was to ensure that tables without a primary key do not
silently fail replication, and that this is handled automatically
without manual intervention. In contrast, the FORCE approach still
requires users to explicitly configure replica identity for each
table, which seems to reintroduce the same operational burden.

For example, instead of:
ALTER TABLE t1 REPLICA IDENTITY FORCE;
the user could simply do:
ALTER TABLE t1 REPLICA IDENTITY FULL

By the time, the user is explicitly configuring replica identity, they
would already need to understand the replication semantics, so it's
not clear what additional benefit FORCE provides over FULL. Am I
missing something here?

thanks
Shveta

---

From: shveta malik <[email-masked]>
Date: Wed, 8 Apr 2026 17:13:27 +0530

> If the DEFAULT → FULL fallback is triggered, the subscriber needs to know whether the corresponding publication has fallbackfull enabled in order to decide how to apply UPDATE/DELETE. That means we…

If the DEFAULT → FULL fallback is triggered, can we send 'full'  to
the subscriber? I think subscriber need not to know if FULl was set by
user or is FORCED and the protocol need not be extended.

> Decoding plugins would need to understand this new flag. In my PoC, I updated pgoutput, but there may be third-party plugins that would also need changes. That feels like a compatibility risk.

If we make the change described in pt1, I think the protocol and
subscriber sides will not need to change, but pgoutput would still
need to be updated when sending the RELATION metadata. Specifically,
instead of using the table’s configured replica identity directly, it
should use the effective replica identity.

pq_sendbyte(out, rel->rd_rel->relreplident -->effect _RI);

So IIUC, third-party plugins may also need such change.

> Given these concerns, I’m leaning toward keeping fallbackfull as a per-table option rather than a per-publication one. Curious to hear your thoughts.

I see your point. One possible approach could be that when any
publication is altered to enable the fallback option, the effective
replica identity for the affected table is promoted to FULL across all
publications.

i.e. for t1:
If RI_DEFAULT + no PK + pub->fallbackfull=true;
effective_replica_identity for t1 = FULL (across all pubs)

But then this brought us to the point that if we are deciding
effective_replica_identity for a table irrespective of publication,
then why not to make it table property?

thanks
Shveta

---

From: Chao Li <[email-masked]>
Date: Tue, 14 Apr 2026 11:47:12 +0800

> On Apr 8, 2026, at 18:22, shveta malik <[email-masked]> wrote:
> 

Hi Shveta,

Thanks for your feedback.

> time or via ALTER TABLE.

This is the tricky part. The need for this feature comes from some complicated operational models in practice. Table creation is often handled by an application team, which focuses on data logic and usually does not care about replication details. Data replication, on the other hand, is maintained by a different team, which does not care what data the tables contain, but only needs to ensure that all data is replicated correctly. In that kind of setup, we cannot expect REPLICA IDENTITY to be configured properly at table creation time.

Such operational models also imply that the database operations team is usually the central database team, with superuser privileges, while an application typically has privileges only on the tables it owns. The central team can set configurations at the database or cluster level. In most cases, what they want is to replicate all tables by default, except for a small number of tables that are explicitly excluded.

Actually, this feature request is coming from the replication team. Today, they have to manually identify newly created tables, usually by running some script, and then configure REPLICA IDENTITY for them. That takes effort and is error-prone. So what they want is a DEFAULT-to-FULL fallback when a table has no primary key.

That’s why my initial proposal was to add a GUC to control the fallback behavior, which would be convenient for the central database team.

> From what I understood, the original

---

From: Chao Li <[email-masked]>
Date: Tue, 14 Apr 2026 11:57:48 +0800

> On Apr 8, 2026, at 19:43, shveta malik <[email-masked]> wrote:
> 

This is true, but going back to the motivation for this feature, users do not want just another table-level property, because they can already do ALTER TABLE t REPLICA IDENTITY FULL today. A table-level property would therefore not help much. What users want from this feature is a mechanism that allows new tables to fall back from DEFAULT to FULL automatically.

Best regards,

---

From: shveta malik <[email-masked]>
Date: Tue, 14 Apr 2026 10:27:08 +0530

> Actually, this feature request is coming from the replication team. Today, they have to manually identify newly created tables, usually by running some script, and then configure REPLICA IDENTITY f…

Okay, I see. If the goal is specifically to address this use-case,
introducing a FORCE table property does not seem to solve the
underlying problem.  Using 'ALTER TABLE t REPLICA IDENTITY FORCE' is
effectively no different from 'ALTER TABLE t REPLICA IDENTITY FULL'.
In both cases, a manual action must still be taken on each table,
which is precisely what the replication team is trying to avoid.

---

From: shveta malik <[email-masked]>
Date: Tue, 14 Apr 2026 11:19:05 +0530

On thinking more about the initial design with a GUC-based approach, I
believe we already have a similar precedent where both a GUC and a
table/column-level property coexist. For example, the
default_toast_compression GUC allows setting a default compression
method globally, while users can still override it at the column level
during CREATE TABLE or ALTER TABLE. A similar approach could work in
our case as well.

Regarding the publication-level property, apart from the potential
data-integrity issues discussed earlier, I also have another concern.
If we introduce a publication-level fallback, we would effectively be
deciding what gets logged in WAL for a particular table (i.e., whether
to log REPLICA IDENTITY FULL) based on a publication parameter. This
does not seem quite right to me. Shouldn't WAL logging typically be
independent of publication configuration? Or do we already have a case
where WAL logging behavior depends on publication-level properties?

Thanks,
Shveta

---

From: Chao Li <[email-masked]>
Date: Tue, 14 Apr 2026 14:19:46 +0800

> On Apr 14, 2026, at 13:49, shveta malik <[email-masked]> wrote:
> 

Thanks for pointing out this.

Yes, one of solutions I initially considered was to introduce a GUC, say default_replica_identity_fallback, with a default value of “NONE”, which is the current behavior. We just need to support one more value “FULL”, then when a table has no primary key, its replica identity falls back to FULL. Users may freely set the table's replica identity to any value. And the database administrator can set the GUC at either cluster level or database level based on their operation practices.

> where WAL logging behavior depends on publication-level properties?

For this, I really want to hear back from Amit.

Best regards,