pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Allow the use of indexes other than PK and REPLICA IDENTITY on t
2+ messages / 2 participants
[nested] [flat]
* pgsql: Allow the use of indexes other than PK and REPLICA IDENTITY on t
@ 2023-03-15 03:38 Amit Kapila <akapila@postgresql.org>
2023-03-15 03:49 ` Re: pgsql: Allow the use of indexes other than PK and REPLICA IDENTITY on t Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Amit Kapila @ 2023-03-15 03:38 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Allow the use of indexes other than PK and REPLICA IDENTITY on the subscriber.
Using REPLICA IDENTITY FULL on the publisher can lead to a full table scan
per tuple change on the subscription when REPLICA IDENTITY or PK index is
not available. This makes REPLICA IDENTITY FULL impractical to use apart
from some small number of use cases.
This patch allows using indexes other than PRIMARY KEY or REPLICA
IDENTITY on the subscriber during apply of update/delete. The index that
can be used must be a btree index, not a partial index, and it must have
at least one column reference (i.e. cannot consist of only expressions).
We can uplift these restrictions in the future. There is no smart
mechanism to pick the index. If there is more than one index that
satisfies these requirements, we just pick the first one. We discussed
using some of the optimizer's low-level APIs for this but ruled it out
as that can be a maintenance burden in the long run.
This patch improves the performance in the vast majority of cases and the
improvement is proportional to the amount of data in the table. However,
there could be some regression in a small number of cases where the indexes
have a lot of duplicate and dead rows. It was discussed that those are
mostly impractical cases but we can provide a table or subscription level
option to disable this feature if required.
Author: Onder Kalaci, Amit Kapila
Reviewed-by: Peter Smith, Shi yu, Hou Zhijie, Vignesh C, Kuroda Hayato, Amit Kapila
Discussion: https://postgr.es/m/CACawEhVLqmAAyPXdHEPv1ssU2c=dqOniiGz7G73HfyS7+nGV4w@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/89e46da5e511a6970e26a020f265c9fb4b72b1d2
Modified Files
--------------
doc/src/sgml/logical-replication.sgml | 9 +-
src/backend/executor/execReplication.c | 112 ++++++++++-----
src/backend/replication/logical/relation.c | 212 ++++++++++++++++++++++++++++-
src/backend/replication/logical/worker.c | 56 ++++----
src/include/replication/logicalrelation.h | 4 +
src/test/subscription/meson.build | 1 +
6 files changed, 325 insertions(+), 69 deletions(-)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pgsql: Allow the use of indexes other than PK and REPLICA IDENTITY on t
2023-03-15 03:38 pgsql: Allow the use of indexes other than PK and REPLICA IDENTITY on t Amit Kapila <akapila@postgresql.org>
@ 2023-03-15 03:49 ` Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Amit Kapila @ 2023-03-15 03:49 UTC (permalink / raw)
To: Amit Kapila <akapila@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
On Wed, Mar 15, 2023 at 9:08 AM Amit Kapila <akapila@postgresql.org> wrote:
>
> Allow the use of indexes other than PK and REPLICA IDENTITY on the subscriber.
>
Sorry, this commit leads to failures in subscription tests because I
forgot to add a new test file. I am doing it now.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-03-15 03:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-15 03:38 pgsql: Allow the use of indexes other than PK and REPLICA IDENTITY on t Amit Kapila <akapila@postgresql.org>
2023-03-15 03:49 ` Amit Kapila <amit.kapila16@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox