Re: [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(dot)mertz(at)gmail(dot)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