Experimenting with a Global Index in PostgreSQL: Design, Implementation, and Challenges
Presented by:
Dilip Kumar
Dilip Kumar, Staff Software Engineer at Google and a PostgreSQL contributor!
Dilip is specialised in database internals and has made significant contributions to PostgreSQL, particularly in parallel query, performance, scalability enhancements, and logical replication. His notable contributions include implementing Logical Replication for In-Progress Transactions, Optimizing SLRU, Introducing Parallel Bitmap Heap Scan and Parallel Merge Join, and enabling "Block-Level CREATE DATABASE Support." Currently, he is exploring the implementation of global indexes for PostgreSQL.
Dilip is also an active participant in PostgreSQL open-source conferences, sharing insights and fostering collaboration within the community.
No video of the event yet, sorry!
The main limitation is that unique keys must include the partition key, which can be restrictive for many use cases. A global index addresses this issue by enabling unique indexes that span the entire partitioned hierarchy, removing the need for partition key inclusion in primary keys.
In this talk, we share our experience experimenting with the implementation of global indexes in PostgreSQL. We present the overall design, including necessary changes to storage structures, handling of partition attachment and detachment, and strategies to improve vacuum operations for global indexes. Additionally, we discuss the technical challenges encountered during this experiment, such as identifying the optimal vacuum strategy, ensuring efficient locking across the partition hierarchy. Our findings aim to provide insights into the feasibility and potential benefits of global indexes in PostgreSQL, as well as offer recommendations for future improvements.
- Date:
- Duration:
- 45 min
- Room:
- Conference:
- PGConf India, 2026
- Language:
- Track:
- Difficulty:
- Medium