# Indexes

> MINMAX, BLOOM and POSTINGS secondary indexes — declaration, scope, lifecycle, and why an index never changes an answer.

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

## Index structures

| Structure | Use |
| --- | --- |
| `MINMAX` | Range pruning: per region of data, the smallest and largest value. |
| `BLOOM` | Membership: rules out regions that cannot contain a value. |
| `POSTINGS` | Exact lookup: maps each key value to the positions that hold it. |

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`:

```sql
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:

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


The full grammar:

```text
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](/docs/concepts/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.

```sql
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

| Relation | Shows |
| --- | --- |
| `system.table_indexes` | Each declared index: column, structure, scope, lifecycle state and granule rows. |
| `system.table_index_builds` | Each standalone index build: lifecycle, status code and source frontier. |
| `system.storage` | Per tablet: index generations, artifacts and the commits an index lags behind. |

---

Source: https://nyxdb.io/docs/concepts/indexes/