Re: [PATCH] Improving index selection for logical replication apply with replica identity full - Mailing list pgsql-hackers
| From | Ethan Mertz |
|---|---|
| Subject | Re: [PATCH] Improving index selection for logical replication apply with replica identity full |
| Date | |
| Msg-id | CAA9pdKe076mY_ThwYWkFZa_PceLVmDhdeuKiJnQ38uJCjG2wgg@mail.gmail.com Whole thread |
| In response to | Re: [PATCH] Improving index selection for logical replication apply with replica identity full (Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>) |
| Responses |
Re: [PATCH] Improving index selection for logical replication apply with replica identity full
|
| List | pgsql-hackers |
Hi,
> IDENTITY FULL, the publisher WAL-logs the full before-image of rows
> for UPDATE and DELETE operations, which means more WAL generation on
> the publisher side. What's the motivation for users to choose REPLICA
> IDENTITY FULL despite this overhead? Is it primarily to support
> downstream logical consumers that need full row images? If yes, what
> are those downstream consumers doing with the full row images?
Several PostgreSQL logical replication consumers outside of the publication
subscription replication framework require REPLICA IDENTITY FULL for
their features. Please see a couple examples I researched briefly, but there
are probably more that I'm not aware of [1] [2]. I have also found that some
users utilize REPLICA IDENTITY FULL to create auditing solutions.
This change is most intended to smooth out the replication performance
for users when their configuration is not precise about which index to use.
Savvy users can update to use REPLICA IDENTITY INDEX on the subscriber
to have their updates and deletes applied with the index of their choice even
when REPLICA IDENTITY is full on the publisher.
> Also, I think the heuristic could go beyond just unique vs. non-unique
> index preference. Factors like index bloat and index size could also
> help make a better choice. For example, say there are two unique
> indexes on a table - one on a few text columns and another on a bigint
> column. Due to non-HOT updates or updates to the indexed columns
> (which can happen both on the publisher and the subscriber), bloat on
> the text index grows faster compared to the bigint index. On the
> subscriber, if the index with more bloat is chosen, lookups become
> slower due to more pages to traverse - even though both are unique
> indexes that the heuristic would treat equally. Of course, fully
> accounting for all these factors may amount to invoking the planner -
> but even a simple check on index size (relpages) between
> otherwise-equal candidates could help.
> index preference. Factors like index bloat and index size could also
> help make a better choice. For example, say there are two unique
> indexes on a table - one on a few text columns and another on a bigint
> column. Due to non-HOT updates or updates to the indexed columns
> (which can happen both on the publisher and the subscriber), bloat on
> the text index grows faster compared to the bigint index. On the
> subscriber, if the index with more bloat is chosen, lookups become
> slower due to more pages to traverse - even though both are unique
> indexes that the heuristic would treat equally. Of course, fully
> accounting for all these factors may amount to invoking the planner -
> but even a simple check on index size (relpages) between
> otherwise-equal candidates could help.
I'm wary of making this logic more intelligent than it already is. My intention
was to focus on the most dramatic improvements in replication speed, but still
keep the behavior that users must configure more fine grain control as needed
with REPLICA IDENTITY INDEX or by adding a primary key.
I agree with your point. In my opinion, a better path forward would be a new
feature allowing logical apply workers to optionally invoke the planner, configured
at the subscription level, rather than building increasingly comprehensive
heuristics outside of planning.
> Some comments on the patch.
Thank you, I have attached a new revision which addresses these comments.
Best,
Ethan Mertz
SDE, Amazon Web Services
Thank you, I have attached a new revision which addresses these comments.
Best,
Ethan Mertz
SDE, Amazon Web Services
On Mon, Jun 15, 2026 at 10:55 PM Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Fri, May 29, 2026 at 6:43 AM Ethan Mertz <ethan.mertz@gmail.com> wrote:
>
> I have amended the patch to include a comment explaining the behavior for
> non-unique indexes.
>
> Without invoking the planner, I figure it would be difficult to reason about
> performance of a scan on a non-unique index. I think a beneficial future
> optimization would allow users to selectively invoke the planner for their
> logical apply processes. (Possibly a new configuration in the subscription).
>
> Attached is the updated patch.
Thanks for the patch. The results look nice - going from 53 seconds to
under 1 second for 1,000 point updates is a significant improvement.
I'm curious to know a bit more about the use-case. With REPLICA
IDENTITY FULL, the publisher WAL-logs the full before-image of rows
for UPDATE and DELETE operations, which means more WAL generation on
the publisher side. What's the motivation for users to choose REPLICA
IDENTITY FULL despite this overhead? Is it primarily to support
downstream logical consumers that need full row images? If yes, what
are those downstream consumers doing with the full row images?
Also, I think the heuristic could go beyond just unique vs. non-unique
index preference. Factors like index bloat and index size could also
help make a better choice. For example, say there are two unique
indexes on a table - one on a few text columns and another on a bigint
column. Due to non-HOT updates or updates to the indexed columns
(which can happen both on the publisher and the subscriber), bloat on
the text index grows faster compared to the bigint index. On the
subscriber, if the index with more bloat is chosen, lookups become
slower due to more pages to traverse - even though both are unique
indexes that the heuristic would treat equally. Of course, fully
accounting for all these factors may amount to invoking the planner -
but even a simple check on index size (relpages) between
otherwise-equal candidates could help.
Some comments on the patch.
1/
+ q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname
= 'test_idx_select_uniq';}
Use pg_stat_force_next_flush before reading the stats.
2/
+ q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname
= 'test_idx_select_uniq';}
How about making this more predictable - instead of relying on the
scan count being just 1, capture idx_scan before and after, then
compare (after > before or after = before + 1). Although there are no
concurrent queries on these tables, it makes the test predictable.
3/
+ * If multiple usable indexes exist, unique indexes are preferred (they
+ * guarantee at most one tuple per scan), and among unique indexes those with
+ * fewer key columns win. The first usable non-unique index is accepted
+ * without further ranking.
Comment before the function and inside the function seems redundant -
can we dedup into one comment?
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
Attachment
pgsql-hackers by date: