Re: Proposal: Global Index for PostgreSQL

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@gmail.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.  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.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.