Re: Proposal: Global Index for PostgreSQL

On Tue, Jun 10, 2025 at 3:21 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

Thanks Bruce for your thoughts on this.

> On Mon, Jun 9, 2025 at 03:28:38PM +0530, Dilip Kumar wrote:
> > On Mon, Jun 9, 2025 at 2:03 PM Nikita Malakhov <hukutoc(at)gmail(dot)com> wrote:
> > > Global Indexes is a very interesting functionality that has both significant advantages
> > > and drawbacks, and the community seems not ready to accept it without very strong
> > > motivation.
> >
> > I understand that this is a hard problem and needs changes in many
> > critical modules. I don't think there should be a problem with the
> > motivation of this work, but I believe the main issue lies in the
> > project's complexity.
> ...
> > In general, users ideally wouldn't use a global index everywhere. It
> > really comes down to their specific use case – they should only opt
> > for a global index when they can't effectively partition their data
> > without one. The idea is that the amount of data in the sort space
> > should essentially be the same as if the table wasn't partitioned at
> > all. That's a good point for consideration. I agree that global
> > indexes shouldn't be a default choice for every use case. They're most
> > beneficial when a user's data access patterns inherently prevent
> > effective partitioning without them. In such scenarios, the amount of
> > data in the sort space would ideally remain comparable to an
> > unpartitioned table.
>
> There are certainly use cases where this would be helpful, but I think
> the big question is whether it would have so many negatives that most
> people who try to use it would eventually remove it.

Yeah that's a very valid point.

I have heard that
> happened to other relational systems who support global indexes, so I
> think we have to consider that possibility. The problem is you might
> need to actually write the patch to find out.

I've actually drafted the patch, and while it still has open issues to
tackle, I believe it's ready for some interesting experimentation. For
instance, I've observed significant performance gains during index
scans on non-partition key columns, where scanning a single global
index outperforms appending scan results from thousands of local
indexes. However, I've also noted performance regression in
insert/update cases when a global index is present, which is expected
as we need to insert into a large index, so we need to evaluate what
is acceptable and what not.

My plan is to submit the patch in the next commitfest, including
performance data. Before submission, I'll clean up the code further
and add TODO comments wherever additional work is required.

--
Regards,
Dilip Kumar
Google