Hi, On Fri, May 22, 2026 at 10:18 AM Ethan Mertz <[email protected]> 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
