Indexes in partitioned tables

Local indexes

A local index maps one-to-one with the partitions in a parent table: the index has the same number of partitions, covering the same ranges. All keys in a given index partition reference rows in exactly one table partition, so each indexed partition is fully independent of the others.

PolarDB automatically maintains local index partitions when partitions are added, dropped, merged, or split — including hash partitions and hash subpartitions when they are added or merged.

Unique local indexes: To create a unique local index, the partition key columns must be a subset of the index columns. This constraint exists because uniqueness can only be enforced within a single partition; the partition key must guarantee that rows with the same index key always land in the same partition.

Why use local indexes:

• Partition maintenance operations (such as dropping or splitting a partition) only rebuild the affected index partition, not the entire index.

• Maintenance duration stays proportional to partition size.

• Each index partition can be managed independently.

Global indexes

A global index is a B-tree index that is partitioned independently of the parent table. A single global index partition can reference rows across all partitions in the parent table, which makes global indexes useful for queries that do not filter on the partition key.

Management trade-off: When a table partition is dropped or split, all partitions of the global index are affected. Global indexes do not support partition independence.

Only partitioned tables can have global indexes. For details, see Global indexes.

Syntax

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON 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] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) GLOBAL
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]

Examples

CREATE UNIQUE INDEX title_idx ON films (title);

CREATE UNIQUE INDEX title_idx ON films (title) global;