Date: 2025-05-14
Time: 15:00–15:50
Room: Breakout Room 2
Level: Intermediate
In partitioned tables, one of the main limitations 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.