Improve logical replication usability when tables lack primary keys
Chao Li <li.evan.chao@gmail.com>
From: Chao Li <li.evan.chao@gmail.com>
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-11-10T08:06:36Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add the notion of REPLICA IDENTITY for a table.
- 07cacba983ef 9.4.0 cited
Attachments
- v1-0001-Fallback-default-replication-identity-to-full.patch (application/octet-stream) patch v1-0001
* 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/