PolarDB:Global secondary indexes (GSIs)

GSI

A standard GSI partitions index data by a column other than the primary table's partition key. When a query matches the GSI's partition key, PolarDB-X routes the query to the relevant partition and looks up the primary table for any remaining columns.

Example: The user table is partitioned by user_id. To support efficient queries by name, create a GSI on name.

CREATE TABLE user(
  user_id bigint,
  name varchar(10),
  addr varchar(30),
  GLOBAL INDEX `g_i_name` (name) PARTITION BY HASH(name),
  PRIMARY KEY(user_id)
) PARTITION BY KEY(user_id);

With g_i_name in place, a query filtering by name routes to the matching partition of the index rather than scanning all partitions of user.

UGSI

A unique global secondary index (UGSI) has all the properties of a standard GSI and additionally enforces a global UNIQUE constraint across the entire distributed table.

Example: The user2 table is partitioned by user_id. To guarantee that phone is globally unique across all partitions, create a UGSI on phone.

CREATE TABLE user2(
  user_id bigint,
  phone varchar(20),
  addr varchar(30),
  UNIQUE GLOBAL INDEX `g_i_phone`(phone) PARTITION BY HASH(phone),
  PRIMARY KEY(user_id)
) PARTITION BY KEY(user_id);

Clustered GSI

By default, a clustered GSI stores all columns from the primary table — not just the index key and any covering columns. Because every column is present in the index, queries never need to look up the primary table, eliminating lookup overhead entirely.

The trade-off: a clustered GSI occupies the same amount of disk space as the primary table.

Example: The order_tbl table is partitioned by order_id. To support fast queries by user_id without any primary table lookups, create a clustered GSI on user_id.

CREATE TABLE order_tbl(
  order_id bigint,
  user_id bigint,
  addr varchar(30),
  info text,
  create_time datetime,
  CLUSTERED INDEX `cg_i_user`(user_id) PARTITION BY HASH(user_id),
  PRIMARY KEY(order_id)
) PARTITION BY KEY(order_id);

When querying by user_id, PolarDB-X routes the query to the matching partition of cg_i_user and reads all required data directly from the index.

When to use each type