Improve logical replication usability when tables lack primary keys
| От | Chao Li |
|---|---|
| Тема | Improve logical replication usability when tables lack primary keys |
| Дата | |
| Msg-id | CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Improve logical replication usability when tables lack primary keys
Re: Improve logical replication usability when tables lack primary keys |
| Список | 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.
Вложения
В списке pgsql-hackers по дате отправления: