Indexes

A secondary index lets a read skip data that cannot match. Indexes are derived state: they are built from the table, maintained through PSI, and never change the rows a query returns.

Index structures

Which structures a column accepts depends on its type. A FLOAT64 column accepts MINMAX only; a BOOL column accepts BLOOM and POSTINGS.

Declaring an index

Inline, as part of CREATE TABLE:

CREATE TABLE transfers (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  sender STRING NOT NULL,
  recipient STRING NOT NULL,
  amount UINT256 NOT NULL,
  transferred_at DATETIME64(3, 'UTC') NOT NULL,
  _index UINT128 NOT NULL,
  INDEX transfers_ts (transferred_at) USING MINMAX SCOPE HISTORY GRANULE 8192
)
MODE APPEND
SHARD BY (token) SHARDS AUTO
PARTITION BY MONTH(transferred_at)
ORDER BY (chain_id, token, _index)
STORAGE PROFILE COLUMNAR;

Or standalone, on a table that already holds data:

CREATE INDEX transfers_recipient
  ON transfers (recipient) USING POSTINGS SCOPE HISTORY;

The full grammar:

CREATE INDEX <index> ON <table> (<column>, …)
    USING MINMAX | BLOOM | POSTINGS
    [SCOPE CURRENT | HISTORY | BOTH]
    [GRANULE <rows>]

INDEX <index> (<column>, …) USING … [SCOPE …] [GRANULE …]

DROP INDEX [IF EXISTS] <index> ON <table>

The INDEX form is written inside the column list of CREATE TABLE. GRANULE sets how many rows one index entry summarizes.

Scope

A table can keep current state, history, or both (see Versioned tables). SCOPE chooses which one an index covers:

• CURRENT — the current row per key;
• HISTORY — every retained revision;
• BOTH — two independent index artifacts, one for each.

Without SCOPE, an index on an APPEND table covers history, and an index on an UPSERT or VERSIONED table covers current state.

CREATE INDEX balances_amount ON balances (amount) USING MINMAX SCOPE CURRENT;

Building an index on existing data

An index declared inline on a new table is active as soon as the table is. A standalone CREATE INDEX on a table that already holds rows moves through these states while the table stays readable and writable:

declared → building → backfilling → catching_up → active

CREATE INDEX carries no server deadline, so a long backfill runs to completion.

Indexes never change an answer

Every candidate row an index yields still passes the query's full predicate. An index that is missing, still building, or unreadable is skipped and the read falls back to scanning the authoritative data. An index changes how much data a read touches, not what it returns.

Index locality

Indexes are local to each tablet and its parts, and are maintained with the tablet's writes.

Observing indexes