Re: Proposal: Global Index for PostgreSQL - Mailing list pgsql-hackers

On Tue, Jul 1, 2025 at 7:12 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Hi Dilip,
>
> Happy to see you working on this.  It’s clear a lot of thought has
> gone into the design.


Thanks, Amit.  And thanks for your comment.

> On Tue, Jul 1, 2025 at 6:27 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > 6) Need to perform a performance test, for SELECT/UPDATE/INSERT cases,
> > we already know the VACUUM performance.
>
> One point I want to check my understanding of is around the locking
> implications of global index scans, especially in prepared statement
> scenarios.

Sure

> I’ve been working on improving how we handle partition locking during
> execution of generic plans. Specifically, I committed a patch to defer
> locking of partitions until after pruning during ExecutorStart(), so
> we avoid taking locks on partitions that aren’t actually needed --
> even when the plan contains scans on all partitions. That patch was
> later reverted, as Tom pointed out that the plan invalidation logic
> wasn't cleanly handled.

Yes I was following that thread, and at times when I was working on
locking for global index I had in mind that I would have to do
something to the locking after that patch is in.  Unfortunately that
got reverted and then I didn't put any effort in reconsidering how
locking is handled for global index.

 But the goal remains: to avoid locking
> unnecessary partitions, particularly in high-partition-count OLTP
> setups that use PREPARE/EXECUTE.

That makes sense.

> The proposed global index design, IIUC, requires locking all leaf
> partitions up front during planning, and I guess during
> AcquireExecutorLocks() when using a cached plan, because the index
> scan could return tuples from any partition. This seems to directly
> undercut that effort: we'd be back to generic plans causing broad
> locking regardless of actual runtime needs.

Just trying to understand the locking difference more with/without
global index, let's assume we have normal partitioned index on non
partition key column, and if we issue a scan on the partitioned table
then internally from expand_partitioned_rtentry() we will take lock on
all the partitions, because we can not prune any partition.
Similarly, with the global index also all the child partitions under
the top partition on which we have global index will be locked.  So in
this case we do not have a difference.

> I understand that this is currently necessary, given that a global
> index scan is a single node without per-partition awareness. But it
> might be worth considering whether the scan could opportunistically
> defer heap relation locking until it returns a tuple that actually
> belongs to a particular partition -- similar to how inserts into
> partitioned tables only lock the target partition at execution time.
> Or did I miss that inserts also need to lock all partitions up front
> when global indexes are present, due to cross-partition uniqueness
> checks?
>
> Let me know if I’ve misunderstood the design.

So there difference is in the cases, where we are directly operating
on the leaf table, e.g. if you inserting directly on the leaf
relation, currently we just need to lock that partition, but if there
is global index we need to lock other siblings as well (in short all
the leaf under the parent which has global index) because if the
global index is unique we might need to check unique conflict in other
leafs as well.  I believe when the table is partitioned, it might not
be the most preferred way to operate directly on the leaf, and with
global index only this case will be impacted where we are doing DML
directly on the leaf.  I am not sure in this case how much delay we
can do in locking, because e.g. for insert we will only identify which
partition has a duplicate key while inserting in the btree.

--
Regards,
Dilip Kumar
Google