Partitioning is a powerful PostgreSQL tool for managing very large tables by splitting them into smaller chunks — partitions. The main performance gain comes when queries include conditions on the partitioning key, allowing the DBMS to scan only the relevant partitions (partition pruning). This can dramatically speed up queries. Partitioning also simplifies some maintenance tasks, such as quickly removing old data by dropping an entire partition.

However, when it comes to indexes on partitioned tables, PostgreSQL’s standard solutions have had some limitations. Until recently, unless you were querying on the partitioning key itself, developers had to rely almost entirely on local indexes.

Local indexes are created separately for each partition. Their upsides are simplicity and efficiency for operations within a single partition. But what if you need to enforce uniqueness of a column across the entire partitioned table, not just inside one partition? Or if you need to query data spanning multiple partitions? That’s where local indexes fall short.

What’s the problem with local indexes on partitioned tables?

  1. Uniqueness constraint. Standard PostgreSQL only lets you create a unique index (or UNIQUE/PRIMARY KEY constraint) on a partitioned table if the index includes all partitioning key columns. If you need uniqueness on a column that isn’t part of the partitioning key (say, user email in a table partitioned by registration date), you’re out of luck with vanilla PostgreSQL. Local indexes can’t help either since they only guarantee uniqueness within their partition.

  2. Query performance across partitions. Each local index “knows” only about its partition. If a query touches multiple partitions — say, fetching all customer orders over a year in a table partitioned by month — PostgreSQL has to consult the local indexes of every relevant partition one by one. With many partitions, that gets inefficient fast.

To address these issues, Postgres Pro Enterprise introduces global indexes. They work at the level of the entire partitioned table, ensuring both global uniqueness and faster queries spanning multiple partitions.

How do global indexes work?

Unlike local indexes "tied" to each partition, a global index is created once for the parent partitioned table and covers data across all partitions. Physically, it stores index entries for the chosen columns from every partition.

Searching with a global index in Postgres Pro happens in two steps:

  1. Search in the global index. When you query a partitioned table, Postgres Pro first checks the global index. The index is built on the columns you specify. A key detail: the partitioned table’s primary key columns are automatically added to the index as non-key columns (similar to INCLUDE). They’re stored in the index but not part of the tree search itself.

  2. Search data in the partition by primary key. Once the global index finds a matching entry, it uses the primary key values stored as non-key columns to quickly locate the right partition and row. This way, the system doesn’t have to scan all partitions.

Implementation details

  • Extension and access method. The feature is implemented in the pgpro_gbtree extension. Global indexes use a new gbtree access method — based on standard B-tree but adapted for partitioned tables.

  • Creating an index. The syntax looks familiar but requires the partitioned table to have a primary key and the index to be declared USING gbtree:

-- First install the extension
CREATE EXTENSION pgpro_gbtree;

-- Example: unique global index on the email column
-- (assumes my_partitioned_table has a PRIMARY KEY)
CREATE UNIQUE INDEX my_global_unique_index
    ON my_partitioned_table USING gbtree (email);

  • Planner logic. The planner uses a global index only if both of these are true:

    • The query has no partitioning-key condition that would allow partition pruning. If pruning is possible, it’s considered more efficient than the global index.

    • The query includes a condition on columns covered by the global index.

  • Virtual access method. To support fetching data through a global index, a special “virtual” access method was implemented for partitioned tables, which normally don’t appear as scan nodes in plans.

  • Size and structure. The global index itself is not partitioned. It’s a single index containing entries from all partitions. That means the standard PostgreSQL index size limit (typically 32 TB) applies. The expectation is that the index is smaller than the table, but the limit is worth noting.

  • Tie-in with the primary key. A global index implicitly depends on the table’s primary key. As long as a global index exists, you cannot drop the primary key (except via CASCADE, which also drops the index). Primary key columns also cannot be explicit index keys — they’re always included automatically using INCLUDE.

Performance

Testing shows the following trends:

  • Reads. Global indexes greatly speed up queries scanning many partitions. Performance is on par with using a dedicated helper index table and can be multiple times faster than local indexes when partitions are numerous (in one test with 100 partitions, about 7× faster).

  • Writes (INSERT/UPDATE). Inserts into a table with a global index are slower (in tests with 100 partitions, about 1.6–1.7× slower) than with a local B-tree index. This is because the global index enforces uniqueness internally — even for non-UNIQUE indexes — by checking for duplicates (key + primary key) and locking hash buckets to avoid races on concurrent inserts.

Operations and maintenance

  • DELETE / DETACH PARTITION. Like regular B-tree indexes, global indexes don’t immediately remove entries when rows are deleted. Entries for deleted rows or detached partitions remain in the index as "garbage". This is normal behavior. Global indexes also don’t auto-rebuild on SPLIT/MERGE.

  • VACUUM. To clean up obsolete entries from the global index, run VACUUM on the partitioned table.

  • AUTOVACUUM. Not supported yet for global indexes. The main challenge is collecting meaningful stats at the level of the whole partitioned table. But note: global indexes don’t use MVCC (they don’t store tuple versions), so they bloat far less than normal B-trees. As a result, the need for VACUUM is much less frequent, and sometimes unnecessary. When cleanup is needed, it must be done manually — but usually it’s rare.

Current limitations

Global indexes were released in Postgres Pro Enterprise 17.5.1 as experimental. Limitations include:

  • CONCURRENTLY not supported for creating or rebuilding (REINDEX), just like with regular indexes.

  • Expressions or predicates (WHERE) in CREATE INDEX are not supported.

  • Explicit specification of INCLUDE columns is not supported (PRIMARY KEY columns are added automatically).

  • No duplicate columns in the index key.

  • Primary key columns cannot be part of the index key.

  • Foreign keys (FOREIGN KEY) referencing a unique global index are not supported.

  • A table with a global index cannot be attached as a partition to another table.

  • CLUSTER not supported.

  • ON CONFLICT not applicable to unique global indexes.

Many of these may be lifted in future releases as the feature evolves and feedback comes in.

Use cases

Despite the limitations, global indexes are already a game-changer in some scenarios:

  • Enforcing global uniqueness. The killer use case: guaranteeing uniqueness of a value (email, contract number) across the whole partitioned table, not just per partition. A UNIQUE global index solves this — critical for finance, CRM, and accounting systems.

  • Faster queries on non-partitioning keys. If queries often filter on a non-partitioning column and touch many partitions (e.g., all customer orders by client_id in a date-partitioned orders table), a global index on client_idcan radically speed them up.

  • Simpler development. Developers don’t need to worry about partitioning details when writing queries filtered on globally indexed columns — the table feels like one whole.

Conclusion

Global indexes are an important addition to partitioning in Postgres Pro Enterprise. They solve long-standing pain points around global uniqueness and cross-partition query performance in large partitioned tables. The current implementation is experimental and comes with restrictions, but it already provides a powerful tool for specific workloads. We look forward to user feedback to further refine and extend this feature.