Indexes

Why indexes exist, when to use them, what they serve, and how they work.

Version
Latest
v0.1.0 · latest 1 min read
On this page
  1. Why
  2. Practical
  3. What each serves
  4. What they don't do
  5. Internals (for contributors)
diagram
flowchart TD
    Q["query predicate"] --> E{"equality on indexed col?"}
    E -->|yes| B["B-tree lookup → RowIds → MVCC filter"]
    E -->|no| R{"range / order / time window?"}
    R -->|yes| RS["B-tree range scan (+ zone-map pruning on columnar)"]
    R -->|no| U{"uniqueness check?"}
    U -->|yes| UB["B-tree authority + transient unique gate"]
    U -->|no| SC["sequential scan"]
Diagram source · mermaidcopy included
mermaidsource
flowchart TD
    Q["query predicate"] --> E{"equality on indexed col?"}
    E -->|yes| B["B-tree lookup → RowIds → MVCC filter"]
    E -->|no| R{"range / order / time window?"}
    R -->|yes| RS["B-tree range scan (+ zone-map pruning on columnar)"]
    R -->|no| U{"uniqueness check?"}
    U -->|yes| UB["B-tree authority + transient unique gate"]
    U -->|no| SC["sequential scan"]

Why#

Tables are the authority; indexes are access paths for equality, range, ordering, time windows, and uniqueness. Without one, every predicate scans.

Unique and primary-key violations fail the statement with duplicate key value violates unique index (SQLSTATE 23505) — pair with ON CONFLICT DO NOTHING / DO UPDATE for idempotent writes. There is no REINDEX work to do: indexes are maintained on every write and verified on recovery, and REINDEX parses without engine effect.

Practical#

sqlsource
CREATE INDEX orders_user_idx ON orders (user_id);
CREATE UNIQUE INDEX users_email_uidx ON users (email);
CREATE INDEX events_ts_idx ON events (ts);
CREATE INDEX recent_big ON orders ((total)) WHERE total > 100;
  -- NO: partial predicates unsupported; use plain index

Correct expression form:

sqlsource
CREATE INDEX events_team_idx ON events ((payload->>'team'));

What each serves#

  • B-tree (persistent): point equality, unique/PK enforcement, ordered range/prefix scans, min/max bounds. Survives restarts via WAL replay — the durable authority for uniqueness.
  • ART (in-memory): coordinates hot lookups during the session; the persistent B-tree remains the authority after restart.

What they don't do#

No full-text, no vector KNN, no partial/expression-multi-column magic beyond single-expression keys, no planner cost choice in v0.1.0.

Internals (for contributors)#

Index maintenance is transactional: entries appear at commit, disappear at rollback, and concurrent writers coordinate on unique keys. Next: Create an index · Inspect a query

Was this page helpful?