[Feature]: global index on a hypertable table (like pgpro-gbtree) #8623
Description
What problem does the new feature solve?
A global index provides the following benefits:
It enables a much faster search of rows of the partitioned table by a condition on the indexed columns. The search appears to be faster because the global index is not partitioned, and therefore, there is no need to perform index scans of individual partitions.
What does the feature do?
analogue of https://postgrespro.ru/docs/enterprise/current/pgpro-gbtree?lang=en
The pgpro_gbtree extension enables creation and use of a global index on a partitioned table.
A global index is an index that is created for one or several columns of a partitioned table and does not include the partition key. At the physical level, a global index on a partitioned table contains data related to the indexed columns for all the partitions, which means that the global index allows you to perform index scan without iterating through all the partitions.
A global index provides the following benefits:
It enables a much faster search of rows of the partitioned table by a condition on the indexed columns. The search appears to be faster because the global index is not partitioned, and therefore, there is no need to perform index scans of individual partitions.
It lifts a Postgres Pro restriction that does not allow creation of a unique index for columns that do not contain the partition key.
Now implementation:
https://docs.tigerdata.com/use-timescale/latest/hypertables/hypertables-and-unique-indexes/
You do not need to have a unique index on your hypertables.
When you create a unique index, it must contain all the partitioning columns of the hypertable.
=>
without a partition column it is impossible to create a unique index
=>
we cant handle with it: there is no need to perform index scans of individual partitions.
Implementation challenges
No response