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

Hi,

On Fri, May 22, 2026 at 10:18 AM Ethan Mertz <ethan.mertz@gmail.com> wrote:

Hello hackers,

I'd like to reopen the discussion on index selection for logical replication apply for replica identity full. Since PostgreSQL 14, replica identity full is able to make use of existing indexes [1][2] (authors in CC) when replicating UPDATE or DELETE operations.

Today, when identifying which index to use for the update or delete, the first suitable index is chosen by OID order, which generally corresponds to creation order. If the chosen index has low cardinality, the lookup may perform no better than a sequential scan. While avoiding replica identity full is generally recommended, some users need to maintain REPLICA IDENTITY FULL to support downstream logical consumers that require full row images. These users would also like performant PostgreSQL to PostgreSQL replication.

I propose improving the index selection heuristic to prefer unique indexes, favoring those with fewer columns. Previous discussion in the linked threads avoided invoking the planner for full index selection; the heuristic I propose serves as a middle ground. A unique index guarantees that each tuple match requires at most one index scan, and among unique indexes, fewer columns means a narrower, more efficient lookup. I have attached a patch implementing this check.

+1

I think it's true that for unique indexes, fewer keys lead to better
performance. But the same is not necessarily true for non-unique
indexes: more keys could narrow the search space. I think it would be
better to address the cases where there are no unique indexes on the
subscriber. Even if we don't have dedicated handling for non-unique
indexes, we can at least leave some comments.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

I think it's true that for unique indexes, fewer keys lead to better
performance. But the same is not necessarily true for non-unique
indexes: more keys could narrow the search space.

Agreed. I have amended the patch to keep the existing behavior for
non-unique
indexes.

I think it would be
better to address the cases where there are no unique indexes on the
subscriber. Even if we don't have dedicated handling for non-unique
indexes, we can at least leave some comments.

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.

Thank you,

Ethan Mertz
SDE, Amazon Web Services

On Thu, May 28, 2026 at 6:34 PM Masahiko Sawada <sawada.mshk@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

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]https://debezium.io/documentation/reference/stable/connectors/postgresql.html [2]https://docs.databricks.com/aws/en/oltp/projects/lakebase-cdf. 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
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.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Tue, Jun 16, 2026 at 7:21 AM Ethan Mertz <ethan.mertz@gmail.com> wrote:

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.

I'm convinced that there are legitimate uses for capturing both
before- and/or after-image of the rows.

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.

I'm not sure we need another option for this. My concern is that just
choosing a unique index over a non-unique one can lead to suboptimal
apply performance if the unique index has more bloat.

Do we know the cost of invoking the planner for index selection on the
subscriber? AFAICS, the selection happens on the first apply or
whenever the relcache entry gets invalidated - so it's infrequent [1]/* * Finding a usable index is an infrequent task. It occurs when an * operation is first performed on the relation, or after invalidation * of the relation cache entry (such as ANALYZE or CREATE/DROP index * on the relation). */.
If that's the case, why not invoke the planner to make a
better-informed decision? It would account for bloat, size,
selectivity etc. I think it would be worth measuring the performance
impact before proceeding with the just unique vs. non-unique approach.

[1]: /* * Finding a usable index is an infrequent task. It occurs when an * operation is first performed on the relation, or after invalidation * of the relation cache entry (such as ANALYZE or CREATE/DROP index * on the relation). */
/*
* Finding a usable index is an infrequent task. It occurs when an
* operation is first performed on the relation, or after invalidation
* of the relation cache entry (such as ANALYZE or CREATE/DROP index
* on the relation).
*/

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Dear Bharath, Ethan,

Do we know the cost of invoking the planner for index selection on the
subscriber? AFAICS, the selection happens on the first apply or
whenever the relcache entry gets invalidated - so it's infrequent [1].
If that's the case, why not invoke the planner to make a
better-informed decision? It would account for bloat, size,
selectivity etc. I think it would be worth measuring the performance
impact before proceeding with the just unique vs. non-unique approach.

Personally I'm still cautious to call planner code because of the maintenance cost.
I'm not sure it's helpful to extend codes only for the REPLICA IDENTITY FULL case.
But I see your point a bit, no one may not have compared these execution costs.

Regarding the code, it looked basically good, but one concern is that the
selection could be slightly worse if several indexes are defined; it needs to
iterate all the index entries in any cases. Can you prove the performance is
acceptable?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Hi all,

Thanks for picking this up, Ethan, and for the CC.

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>, 22 Haz 2026 Pzt, 13:05
tarihinde şunu yazdı:

Dear Bharath, Ethan,

Do we know the cost of invoking the planner for index selection on the
subscriber? AFAICS, the selection happens on the first apply or
whenever the relcache entry gets invalidated - so it's infrequent [1].
If that's the case, why not invoke the planner to make a
better-informed decision? It would account for bloat, size,
selectivity etc. I think it would be worth measuring the performance
impact before proceeding with the just unique vs. non-unique approach.

Personally I'm still cautious to call planner code because of the
maintenance cost.
I'm not sure it's helpful to extend codes only for the REPLICA IDENTITY
FULL case.
But I see your point a bit, no one may not have compared these execution
costs.

Some history that may help, since the planner keeps coming up. The early

versions of the original patch (v1-v22) did use the planner: we built a
dummy PlannerInfo, made col = $1 AND ... restrictinfos, called
create_index_paths(), and picked the cheapest Path. So we already had
the cost and size awareness that is being discussed now.
We removed it in v23 [1]/messages/by-id/CACawEhUN=+vjY0+4q416-rAYx6pw-nZMHQYsJZCftf9MjoPN3w@mail.gmail.com. The reason was not the cost of calling the
planner -- we agreed that index selection is rare (only on first apply
or after a relcache invalidation), so that cost was fine.

We removed it because of design and maintenance concerns: see Tom's
comment [2]/messages/by-id/3466340.1673117404@sss.pgh.pa.us about using planner internals inside execReplication.c (his
point
was that we would need to replace the existing logic there, not add on top
of it),
and Amit felt the extra maintenance was not worth it. A planner-based
approach would need to answer those points again.

One more note on the bloat idea: the chosen index is cached until the
next invalidation. So a planner-based choice also gets stale between
ANALYZE runs, just like a heuristic one. Calling the planner once at
relcache-open time does not track bloat over time.

I propose improving the index selection heuristic to prefer unique indexes,

On the heuristic itself, I am only mildly in favor, and I want to be
honest about how narrow the benefit is. It only helps when a usable
unique index already exists on the subscriber but is not picked first.

But in that case the correct answer is REPLICA IDENTITY USING INDEX (or
a primary key) on that index, which we already recommend. The case that
really pushes people to use REPLICA IDENTITY FULL: no unique key is
possible, only non-unique indexes is exactly the case this patch
leaves unchanged. Even Ethan's own benchmark uses a table that has a
unique index on "id", which would be better served by setting it as the
replica identity.

So I would describe this as a small, low-risk improvement to the default
choice, I am fine with it on that basis.

Regarding the code, it looked basically good, but one concern is that the

selection could be slightly worse if several indexes are defined; it needs
to
iterate all the index entries in any cases. Can you prove the performance
is
acceptable?

On the concern about scanning all indexes: I do not think it is a
problem. We already walk the full index list today; this change only
removes the early return. It runs only on first apply or after an
invalidation, and the work is bounded by the number of indexes, which is
small. A quick microbenchmark should confirm it is tiny next to the
apply work itself.

Thanks,
Onder

[1]: /messages/by-id/CACawEhUN=+vjY0+4q416-rAYx6pw-nZMHQYsJZCftf9MjoPN3w@mail.gmail.com
/messages/by-id/CACawEhUN=+vjY0+4q416-rAYx6pw-nZMHQYsJZCftf9MjoPN3w@mail.gmail.com
[2]: /messages/by-id/3466340.1673117404@sss.pgh.pa.us

Dear Onder,

Some history that may help, since the planner keeps coming up....

Thanks for the clarification. I cannot recall so detailed discussions.

On the concern about scanning all indexes: I do not think it is a
problem. We already walk the full index list today; this change only
removes the early return. It runs only on first apply or after an
invalidation, and the work is bounded by the number of indexes, which is
small. A quick microbenchmark should confirm it is tiny next to the
apply work itself.

Yes, I agreed it's not a serious problem. just I wanted to see such the micro
bench.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Hi,

On Wed, Jun 17, 2026 at 9:18 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

I'm not sure we need another option for this. My concern is that just
choosing a unique index over a non-unique one can lead to suboptimal
apply performance if the unique index has more bloat.

I spent some time experimenting with the patch to see whether it can
end up choosing a more-bloated unique index when a less-bloated
non-unique index that can find the same row is available, and whether
that makes the apply slower.

When the apply worker looks up the local row to update, it scans the
chosen index using a dirty snapshot and walks matching row versions
until it reaches the live one. The slow part is how many dead versions
it walks, not the tree height. If updates change a non-unique index
column but not the unique index's columns, the non-unique index's
entries spread across different keys while the unique index's pile up
under the same key, making its chain longer.

I created a non-unique index (chosen today by creation order) and a
unique index (which the patch would prefer), both giving one row per
search key. I bloated both - the unique index grew to 4.5 GB against
450 MB for the non-unique one. I then replicated 1000 point updates
spread across the table. Patched spent 13s in the index lookup against
11s for the HEAD, 20% slower. The gap is small for this workload, but
I expect it to get bigger at scale - update-heavy workloads, longer
version chains, wider index keys, indexes of hundreds of GBs size with
limited memory, and concurrent activity on the subscriber etc.

It might be worth factoring in the index size when more than one index
is usable unless others think otherwise. Since the replica identity
index is only re-picked on relcache invalidation, the choice could go
stale as bloat grows, so the apply worker might need to re-check the
replica identity index choice periodically.

Happy to hear thoughts on this.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi,

On the heuristic itself, I am only mildly in favor, and I want to be
honest about how narrow the benefit is. It only helps when a usable
unique index already exists on the subscriber but is not picked first.

But in that case the correct answer is REPLICA IDENTITY USING INDEX (or
a primary key) on that index, which we already recommend. The case that
really pushes people to use REPLICA IDENTITY FULL: no unique key is
possible, only non-unique indexes is exactly the case this patch
leaves unchanged. Even Ethan's own benchmark uses a table that has a
unique index on "id", which would be better served by setting it as the
replica identity.

So I would describe this as a small, low-risk improvement to the default
choice, I am fine with it on that basis.

I fully agree with this assessment of the change. It is both convenient and
simple for the apply worker to make a clearly better choice if the user
hasn't
specified the correct index to use as the replica identity. To further
justify
this patch, we have seen that this mistake has been made by real users
which then caused them pain through increased replication lag.

After some thought, I decided it would be best to align the change better
with this goal (making a simple decision), and therefore I removed the
logic to choose based on the number of key columns. Thus, I propose a
new patch (attached to this email) which only selects the first unique
index and returns early. This may partially address the feedback around
looping through the indexes.

Furthermore, this simplification makes the behavior more focused and
simple for users to understand when multiple indexes are involved.
Incorporating other aspects of the index (including the key column logic
which I had in v1-v3) would likely make the behavior less intuitive for
users.

Yes, I agreed it's not a serious problem. just I wanted to see such the

micro

bench.

ACK. I will perform some tests on tables with many indexes to see if there
is any performance degradation, and I will share the results shortly.

It might be worth factoring in the index size when more than one index
is usable unless others think otherwise. Since the replica identity
index is only re-picked on relcache invalidation, the choice could go
stale as bloat grows, so the apply worker might need to re-check the
replica identity index choice periodically.

I partially spoke on this point earlier in my message, but my opinion is
that either apply keep the heuristic simplistic, or apply should go into
full
query planning. In addition, adding relation size to the heuristic would
make
the behavior both dynamically but also less predictable. For users
this might be difficult to understand.

Thank you,
Ethan Mertz
SDE, Amazon Web Services

Hi,

On Tue, Jun 23, 2026 at 2:09 PM Ethan Mertz <ethan.mertz@gmail.com> wrote:

It might be worth factoring in the index size when more than one index
is usable unless others think otherwise. Since the replica identity
index is only re-picked on relcache invalidation, the choice could go
stale as bloat grows, so the apply worker might need to re-check the
replica identity index choice periodically.

I partially spoke on this point earlier in my message, but my opinion is
that either apply keep the heuristic simplistic, or apply should go into full
query planning. In addition, adding relation size to the heuristic would make
the behavior both dynamically but also less predictable. For users
this might be difficult to understand.

I want to make sure we don't regress in the case where the unique
index has more bloat than the non-unique index. I experimented with a
small dataset where the unique index grew to 4.5 GB due to bloat and
observed that the patched was about 20% slower (a couple of seconds at
this scale, but in production with larger tables, more concurrent
activity, more bloat and limited memory, I would expect the gap to be
wider). Would you be able to provide some additional data points on
this before we proceed further? That would help confirm the heuristic
holds up across different bloat conditions.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi,

I want to make sure we don't regress in the case where the unique
index has more bloat than the non-unique index. I experimented with a
small dataset where the unique index grew to 4.5 GB due to bloat and
observed that the patched was about 20% slower (a couple of seconds at
this scale, but in production with larger tables, more concurrent
activity, more bloat and limited memory, I would expect the gap to be
wider). Would you be able to provide some additional data points on
this before we proceed further? That would help confirm the heuristic
holds up across different bloat conditions.

I agree that choosing a bloated unique index could lead to worse
performance than a non-bloated non-unique index in certain cases.
However, I think there are many cases where the more bloated, larger
index would perform significantly better than a less bloated index with
worse
selectivity. Without the exact numbers, it should be clear to see that the
example from my original email would be one of those cases. Deciding
which index to choose based off of size alone and not taking into account
other statistics would likely lead to many wrong decisions.

Moreover, I would argue that even if the choice of a unique index led to
worse performance, it should not be considered a regression. Today
index selection is essentially random, therefore, there is no guarantee
about which index is chosen. I'd argue that a savvy user must assume
that the worst index is chosen when reasoning about performance. In
addition, given that this patch will likely only be applied on a new major
version, any stability of index ordering for the selection would be changed
during the dump and restore.

I'd reiterate as well that this is a small incremental improvement which I
found would be helpful in a few situations that I have observed in user
workload. I don't think that this excludes any future optimizations
including
more factors such as size/bloat, but those must be considered in
combination with other statistics. I'd be interested in looking into and
helping
out with the development of those features in the future.

Best,
Ethan
SDE, Amazon Web Services