Re: Proposal: Global Index for PostgreSQL

On Fri, Jul 11, 2025 at 12:22 PM Amit Langote <amitlangote09@gmail.com> wrote:

> Thanks, I see that you've thought this through and opted for the safe
> route of locking all possible partitions during planning, both for
> SELECT and INSERT.
>
> That seems like the only viable option today, given how the executor
> assumes that each index scan targets a single heap relation which has
> already been opened and locked before execution begins. But even so, I
> think we should not assume locking is cheap. Even with improvements
> like fast-path locking or a higher max_locks_per_transaction, locking
> thousands of partitions still adds up. This can become noticeable even
> in regular query execution, since one of the motivations for global
> indexes is to reduce planning effort, for example by avoiding
> per-partition scan node generation. In such cases, the relative cost
> of locking can become a dominant part of query startup time.

That's right

> I do not have a better solution right now, but it is worth keeping
> this tradeoff in mind.

I agree.  Thanks for your opinion on this.

--
Regards,
Dilip Kumar
Google