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
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#
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 indexCorrect expression form:
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