Indexes in partitioned tables

Index types of partitioned tables

PolarDB for PostgreSQL supports two index types on partitions:

Local indexes

In a local index of a partitioned table, the local index corresponds one-to-one with the partitions of the partitioned table and has the same number of partitions and the same partition ranges as its table. Each index partition is associated with one partition of the base table, so all keys in an index partition refer only to rows stored in a single table partition. The database automatically keeps index partitions in sync with their associated table partitions, so that each table index is independent of the others.

A local index is created by specifying the Local attribute. The index is partitioned on the same columns as the base table, creating the same number of partitions or subpartitions and giving them the same partition ranges as the corresponding partitions of the base table.

When partitions in the base table are added, dropped, merged, or split, or when hash partitions or subpartitions are added or merged, PolarDB for PostgreSQL automatically maintains the index partitions.

If the partition columns constitute a subset of the index columns, you can create a UNIQUE local index, which ensures that rows with the same index key are always mapped to the same partition.

Global indexes

A global index is a B-tree index that can also be partitioned, with its partitions independent of the base table on which it is created.

Unlike the one-to-one relationship between index partitions and table partitions in a local index, a global index partition can point to all table partitions.

Syntax

Create a local index

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 a global index

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 a local index

Create a local index title_idx on the title column of the films table:

CREATE UNIQUE INDEX title_idx ON films (title);

Create a global index

Create a global index title_idx on the title column of the films table:

CREATE UNIQUE INDEX title_idx ON films (title) global;