| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Improve logical replication usability when tables lack primary keys |
| Date: | 2025-11-10 08:06:36 |
| Message-ID: | CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
* 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/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Fallback-default-replication-identity-to-full.patch | application/octet-stream | 8.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jim Jones | 2025-11-10 08:14:49 | Re: display hot standby state in psql prompt |
| Previous Message | Andreas Karlsson | 2025-11-10 08:00:40 | Re: Some efforts to get rid of "long" in our codebase |