Partitioned Table Global Indexes in PolarDB - PolarDB

Global indexes let you index any column of a partitioned table — not just the partition key — and enforce unique constraints across all partitions. This topic covers how global indexes work, how to create them, and what scan types they enable.

How it works

A local index maps one index partition to each table partition. Because its partitioning mirrors the table's partitioning, a local index must include the partition key to enforce a unique constraint.

A global index is a single B-tree index defined across the entire partitioned table. It spans all table partitions, so queries on the indexed column go through one index structure instead of scanning each partition separately. Unlike local indexes, a global index can be created on any column — including non-partition key columns — and it supports unique constraints on those columns.

Global partitioned indexes take this further: the index itself is partitioned, but its partitioning does not need to align with the table's partitioning. This is typically used on partitioned tables with large data volumes where additional query performance on the indexed partition key is valuable.

When to use a global index

Use the following decision guide to choose between a local index and a global index:

If the index columns include the partition key — use a local index. Done.
If the constraint must be unique and the index columns do not include the partition key — use a global index. Done.
If manageability is the priority — prefer a local index.
If the workload is OLTP and query response time matters — use a global index for better point-query performance.

Local index vs. global index

Dimension	Local index	Global index
Index structure	One partition per table partition	Single index spanning all table partitions
Partitioning alignment with table	Always aligned	Not required
Unique constraint on non-partition key	Not supported	Supported
Primary/foreign key on non-partition key	Not supported	Supported
Point-query performance on non-partition key	Lower	Significantly higher
Partition pruning	Yes (when query includes partition key)	Yes (optimizer and executor-level)

Create a global index

Syntax

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
[ GLOBAL/LOCAL/global_partitioned_index ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
global_partitioned_index:
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)}

Specify GLOBAL to create a global index. Without the keyword, a local index is created by default.

Examples

Create a global index on a non-partition key column:

CREATE UNIQUE INDEX m_city_id_idx_global ON measurement(city_id) GLOBAL;

Create a global partitioned index partitioned by range on the same table:

CREATE UNIQUE INDEX m_peaktemp_idx_global ON measurement(peaktemp) GLOBAL
PARTITION BY RANGE (peaktemp)
(
    PARTITION m_peaktemp_idx_global_p1 VALUES LESS THAN(1000),
    PARTITION m_peaktemp_idx_global_p2 VALUES LESS THAN(5000),
    PARTITION m_peaktemp_idx_global_p3 VALUES LESS THAN(MAXVALUE)
);

Usage notes

CONCURRENTLY is supported in CREATE INDEX statements for global indexes.
Global indexes can be range- or hash-partitioned, using the same syntax as data table partitioning.
Global indexes cannot be list-partitioned.
Global indexes can be partitioned on the table partition key.
The partitioning of a global index must be based on a left prefix of the index columns.
Range-partitioned global indexes support ordered scanning.
Hash-partitioned global indexes do not support sequential scans.
Global partitioned indexes can enforce unique constraints globally.
Global partitioned indexes support partition pruning at both the optimizer and executor level.
Global indexes cannot be created on non-partitioned tables or on child partitions of a partitioned table.
Global indexes cannot be created based on expressions.

Global index scans

Global indexes support the following scan types:

Global index scan
Global partitioned index scan
Global index-only scan
Global partitioned index-only scan
Global index bitmap scan
Global partitioned index bitmap scan
Global index parallel scan
Global only index parallel scan

Examples

All examples below use the measurement table and indexes created in the examples above.

Global index scan

EXPLAIN SELECT * FROM measurement WHERE city_id = 5;

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Global Index Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=20)
   Index Cond: (city_id = 5)
(2 rows)

Global partitioned index scan

EXPLAIN SELECT * FROM measurement WHERE peaktemp = 5;

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.50..8.52 rows=1 width=20)
   Index Cond: (peaktemp = 5)
(2 rows)

Global index-only scan

EXPLAIN SELECT city_id FROM measurement WHERE city_id = 5;

                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Global Index Only Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
   Index Cond: (city_id = 5)
(2 rows)

Global partitioned index-only scan

EXPLAIN SELECT peaktemp FROM measurement WHERE peaktemp = 5;

                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Global Partitioned Index Only Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
   Index Cond: (peaktemp = 5)
(2 rows)

Global index bitmap scan

EXPLAIN SELECT * FROM measurement WHERE city_id > 5 AND city_id < 10000;

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Append  (cost=1.03..14.47 rows=32 width=20)
   ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=20)
         Recheck Cond: ((city_id > 5) AND (city_id < 10000))
         Filter: ((city_id > 5) AND (city_id < 10000))
         ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((city_id > 5) AND (city_id < 10000))
   ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=20)
         Recheck Cond: ((city_id > 5) AND (city_id < 10000))
         Filter: ((city_id > 5) AND (city_id < 10000))
         ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((city_id > 5) AND (city_id < 10000))
   ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=20)
         Recheck Cond: ((city_id > 5) AND (city_id < 10000))
         Filter: ((city_id > 5) AND (city_id < 10000))
         ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((city_id > 5) AND (city_id < 10000))
   ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=20)
         Recheck Cond: ((city_id > 5) AND (city_id < 10000))
         Filter: ((city_id > 5) AND (city_id < 10000))
         ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((city_id > 5) AND (city_id < 10000))
(21 rows)

Global partitioned index bitmap scan

EXPLAIN SELECT peaktemp FROM measurement WHERE peaktemp > 5 AND peaktemp < 1000;

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Append  (cost=1.03..14.47 rows=32 width=4)
   ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=4)
         Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
         Filter: ((peaktemp > 5) AND (peaktemp < 1000))
         ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
   ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=4)
         Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
         Filter: ((peaktemp > 5) AND (peaktemp < 1000))
         ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
   ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=4)
         Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
         Filter: ((peaktemp > 5) AND (peaktemp < 1000))
         ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
   ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=4)
         Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
         Filter: ((peaktemp > 5) AND (peaktemp < 1000))
         ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
               Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
(21 rows)

Primary keys and foreign keys on non-partition key columns

In standard PostgreSQL, a unique constraint on a partitioned table requires the partition key to be part of the constrained columns. This means primary keys and foreign keys on non-partition key columns are not possible without global indexes.

PolarDB for PostgreSQL (Compatible with Oracle) removes this restriction. Any column of a partitioned table can be a primary key or a foreign key, because global indexes can enforce uniqueness on any column.

When a primary key is defined at table creation time, the system automatically selects the index type:

If the primary key columns include the partition key — local unique indexes are created.
If the primary key columns do not include the partition key — a global unique index is created.

The same rule applies to foreign keys.

Examples

Primary key on the partition key (local index created)

CREATE TABLE pk_rel(a INT PRIMARY KEY, b INT, c INT, d INT) PARTITION BY RANGE (a);

               Table "public.pk_rel"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | integer |           |          |
 d      | integer |           |          |
Partition key: RANGE (a)
Indexes:
    "pk_rel_pkey" PRIMARY KEY, btree (a)
Number of partitions: 0

Primary key on a non-partition key column (global index created)

CREATE TABLE pk_rel(a INT, b INT PRIMARY KEY, c INT, d INT) PARTITION BY RANGE (a);

               Table "public.pk_rel"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |
 b      | integer |           | not null |
 c      | integer |           |          |
 d      | integer |           |          |
Partition key: RANGE (a)
Indexes:
    "pk_rel_pkey" PRIMARY KEY, btree (b) GLOBAL
Number of partitions: 0

Note

To explicitly set an existing index as the primary key, use ALTER TABLE xxx ADD PRIMARY KEY USING INDEX xxx.

Foreign key referencing a non-partition key column

CREATE TABLE fk_rel(a INT, b INT REFERENCES pk_rel(b), c INT, d INT) PARTITION BY RANGE (a);

               Table "public.fk_rel"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |
 b      | integer |           |          |
 c      | integer |           |          |
 d      | integer |           |          |
Partition key: RANGE (a)
Foreign-key constraints:
    "fk_rel_b_fkey" FOREIGN KEY (b) REFERENCES pk_rel(b)
Number of partitions: 0

Limitations and management considerations

Global indexes provide query performance advantages but require careful consideration before use:

No expression-based global indexes: Global indexes cannot be created on expressions.
Non-partitioned tables: Global indexes cannot be created on non-partitioned tables or on child partitions of a partitioned table.
No list partitioning: Global indexes cannot be list-partitioned.
Left prefix requirement: The partitioning of a global index must be based on a left prefix of the index columns.
No sequential scan on hash-partitioned global indexes: Hash-partitioned global indexes do not support sequential scans.

Performance

The following benchmark data compares point-query and Data Manipulation Language (DML) performance across three table configurations. The test uses pgbench to generate 80,000 rows, then runs queries against a partitioned table (with local indexes or a global index) and a non-partitioned table.

Note

Transactions per second (TPS) results vary by hardware environment. These numbers are intended for relative comparison only.

Point query performance on non-partition keys

Configuration	Without prepared statement			With prepared statement
	1 concurrent	32 concurrent	64 concurrent	1 concurrent	32 concurrent	64 concurrent
Non-partitioned table	27,732	494,433	430,848	53,935	985,880	886,882
Partitioned table with local indexes	367	4,155	3,688	856	8,742	6,790
Partitioned table with a global index	19,006	308,128	262,941	45,090	820,924	731,557

TPC-B performance on non-partition keys

Both point queries and DML are included.

Configuration	Without prepared statement			With prepared statement
	1 concurrent	32 concurrent	64 concurrent	1 concurrent	32 concurrent	64 concurrent
Non-partitioned table	1,115	51,025	60,409	4,822	90,312	100,802
Partitioned table with local indexes	271	2,903	2,524	550	5,276	4,237
Partitioned table with a global index	3,453	36,320	39,941	4,334	69,040	75,232

Global indexes significantly improve performance for point queries and DML on non-partition key columns. The performance improvement is most pronounced in high-concurrency scenarios: a partitioned table with a global index reaches roughly 74x the TPS of one with local indexes for point queries at 32 concurrent connections.

The underlying reason is index probe efficiency. A local index on a non-partition key must scan all partition indexes to locate matching rows. A global index resolves the query with a single index probe, eliminating the per-partition overhead.