Re: [PATCH] Improving index selection for logical replication apply with replica identity full

Hi,

> 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?

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.

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

[1]
https://debezium.io/documentation/reference/stable/connectors/postgresql.html
[2] https://docs.databricks.com/aws/en/oltp/projects/lakebase-cdf

On Mon, Jun 15, 2026 at 10:55 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> Hi,
>
> On Fri, May 29, 2026 at 6:43 AM Ethan Mertz <ethan(dot)mertz(at)gmail(dot)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
>