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
PostgreSQL14, replica identity full is able to make use of existing indexes [1][2] (authors in CC) when replicating
UPDATEor DELETE operations.
>
> Today, when identifying which index to use for the update or delete, the first suitable index is chosen by OID order,
whichgenerally corresponds to creation order. If the chosen index has low cardinality, the lookup may perform no better
thana sequential scan. While avoiding replica identity full is generally recommended, some users need to maintain
REPLICAIDENTITY FULL to support downstream logical consumers that require full row images. These users would also like
performantPostgreSQL to PostgreSQL replication.
>
> I propose improving the index selection heuristic to prefer unique indexes, favoring those with fewer columns.
Previousdiscussion in the linked threads avoided invoking the planner for full index selection; the heuristic I propose
servesas a middle ground. A unique index guarantees that each tuple match requires at most one index scan, and among
uniqueindexes, 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
