skaidb v0.320.9

Indexing guide

Everything about making skaidb queries fast: what index types exist, how to create them, how the planner picks one, and — most importantly — how to look at a slow workload and decide which index it needs.

Related: QUERY_SYNTAX.md (grammar), SEARCH.md (full-text), VECTOR.md (HNSW), LLM.md (the condensed LLM-facing reference).

Index types at a glance

Type Create Answers Notes
Primary key CREATE TABLE t (PRIMARY KEY (a [, b ...])) point reads, prefix-equality slices (+ one trailing range) the table IS the index; WHERE a = ? on PK (a, b) scans only that slice
Secondary CREATE INDEX i ON t (a, b, ...) equality/range filters, index-served ORDER BY, index-only counts composite = leftmost-prefix; see planner section
Multikey CREATE INDEX i ON t (a, tags[]) array element equality (tags = 'x' containment), exact index-only counts one [] component per index; entry per element
Global CREATE INDEX i ON t (a) WITH (global = true) full-tuple equality probes routed to the value's replica set (one round-trip, no scatter) ranges/partial prefixes fall back to scatter; backfill runs in the background after DDL; see GLOBAL_INDEXES.md
Vector CREATE VECTOR INDEX v ON t (emb) DIM n [USING cosine\|l2\|dot] NEAREST k-NN HNSW; snapshotted for fast restarts; DDL acks at schema-apply, local = building while the paged backfill runs (searches say "rebuilding — retry shortly")
Search CREATE SEARCH INDEX s ON t (body, title) [WITH (...)] MATCH(), SEARCH(), BM25 ranking, fast-field aggregations see SEARCH.md; fast fields answer GROUP BY/counts index-side

Memory tables (WITH (memory = true)) reject indexes — they are ephemeral by contract.

Managing indexes

CREATE INDEX IF NOT EXISTS i_mail_star ON mail (account, _tombstone, is_starred);
SHOW INDEXES;          -- name, table, type (secondary/global/vector/search,
                       -- with "(building)" while a backfill runs), paths
                       -- (multikey keep their []), and `local`: THIS node's
                       -- live state (ok/building/missing)
DROP INDEX IF EXISTS i_mail_star;
REBUILD INDEX i_mail_star;   -- every node re-derives ITS copy from its rows
CHECK INDEX i_mail_star;     -- audit THIS node's copy against its rows
CHECK INDEX ALL REPAIR;      -- ... every index of the database, and fix it
  • DDL broadcasts cluster-wide and acks at schema-apply; each node then pages its backfill in the background (brief locks, memory-flat — a 183k-row backfill takes a few minutes per node). A standalone server does the same: CREATE INDEX returns at once and a worker builds the index behind it, sizing each page to about 50 ms of lock hold (32–2048 rows, adapting to the disk) and resting at least as long as the hold between pages, so a mirror on a slow or busy disk keeps answering /ready and its own pulls while it builds. Every index a table gained is built in one scan of the table, however many were created together. SHOW INDEXES shows secondary (building) until that node completes; the planner never uses a building index, so queries fall back to their pre-index plans until then. A restart mid-backfill resumes it: the node re-queues every index still marked building at open (embedded opens finish them inline; server modes page them from the background worker) and continues from the cursor its worker last saved with the catalog — every 64 pages and at least every 5 s, after the page's entries were made durable (one fsync per index per page) — so an interrupted backfill can never strand an index as building forever, and it never rescans the rows already done.
  • Every statement, DDL included, lands in the query log with its real duration; slow ones also land in the slow-query log (see Spotting the missing index below).
  • Crash recovery retains row history until queued index maintenance is durable. If compaction must proceed past that retention window, or an equal-timestamp overwrite replaces the old body, a durable marker is set first. It is cleared again once the queued maintenance it covered has run, so only a process that stopped before then (a crash, a kill) leaves it behind; the next start reconciles that table's secondary and geo entries against its rows. A server does that in the background, in short lock holds, and starts serving at once; until it is done, index-only counts of that table read rows instead. An embedded open reconciles before it returns. Only marked tables incur that extra index scan. Full-text and vector catch-up never holds compaction back. Online partition adoption journals each moved page durably before deleting its legacy copies and checkpoints the search and vector indexes every few pages; a restart after a crash re-indexes the journaled rows.
  • The UI's Inventory tab lists every index with type, definition, approximate entries, and disk size.
  • ALTER TABLE ... RENAME COLUMN rewrites index definitions (including multikey markers) automatically.
  • REBUILD INDEX <name> re-derives an index from the rows. Each node holds its own copy of a secondary index over its own rows, and on a cluster where every member holds the whole table a filtered COUNT(*) is answered from the local copy. A copy that drifted from its rows therefore shows as counts that differ by the member answering, while the rows themselves agree. REBUILD INDEX broadcasts like DDL; each node wipes its copy and backfills it in pages from its own rows, building meanwhile (queries use their pre-index plans until it completes). It also rebuilds a vector or search index by name; a search index (as REBUILD SEARCH INDEX) is cleared and backfilled the same way on a server — paged in the maintenance worker, writes made meanwhile kept, MATCH/SEARCH on it erroring "rebuilding … retry shortly" until done (a crash mid-rebuild reopens the last committed index). A GLOBAL index is refused: its entries are cluster-wide rows kept by anti-entropy.
  • CHECK INDEX <name> | ALL [ON <table>] [REPAIR] finds such drift without rebuilding anything: on the node it is sent to (never scattered), it matches every entry the rows produce against the index through a hash ledger, and only where the two differ probes the rows' entries (an absent one is missing) and reads back the rows of the index's entries (an entry no stored row produces is stale; two current entries with one value on a UNIQUE index are a duplicate). On a UNIQUE index it also audits the reservation table: a live value without its reservation counts as missing, a reservation whose owner no longer holds the value (past the 10 s claim grace) as stale — the samples say "missing reservation" / "stale reservation", REPAIR fixes both, resume tokens gain a phase u, and on a cluster member the reservation audit runs only where the node holds the whole companion table (a skipped: status row says when it did not). A UNIQUE index over an array element created by an older release is listed as demoted: … (it enforces nothing and is opened as a plain index; recreate it). A GLOBAL index no member holds whole is audited per member: one row per member over the rows and entries it owns, statuses skipped: member unreachable|busy|predates the audit and partial when owner batches went unanswered, REPAIR healing per member. It reports counts plus a few sample primary keys — a clean index costs one sequential walk of the table and two of the index, no random read. It pages under short engine-lock holds (about 8 ms each, with a pause between pages for writers), so it is safe on a busy member. Its memory is one page, the ledgers (at most 12 MB for a table) and an 8 MB cache of its own: it does not fill the node's block or row caches, whatever the table's size, and on a node under memory pressure it stops at the next page as partial with a RESUME token. A row whose maintenance is still queued is counted in_flight, not drift. The statement timeout ends it as partial with a RESUME token. REPAIR fixes exactly what it found — each finding re-checked under a short write hold — on that node only, and the index keeps serving throughout, where REBUILD INDEX takes every member's copy out of service for its backfill. It covers every kind of index a node keeps: geo indexes like secondary ones; search indexes (a document per row with an indexable value, read in key order from the index's term dictionary alongside the rows; REPAIR re-indexes a missing document from the row and removes a stale one, committing after each short hold); vector indexes (a node per vector, walked in node-id order), whose drift it reports for REBUILD INDEX to fix; and GLOBAL indexes on a node that holds every row and entry of the table (repaired in that node's copy of the entry table, which anti-entropy spreads). ALL walks a table once for all of its indexes. QUERY_SYNTAX.md has the columns; CLUSTER_REPAIR.md has the audit runbook.
  • Event rollups are indexable tables. CREATE ROLLUP r ON t BUCKET 1h [BY (dims)] AGGREGATE (...) (QUERY_SYNTAX.md) materializes a bucket table with PRIMARY KEY (bucket, dims…); CREATE INDEX i ON r (total) on a dimension or an aggregate alias is an ordinary secondary index with every capability in this guide — range filters, ORDER BY total [DESC] LIMIT k as an index-ordered walk, index-only range counts (a rollup with RETENTION counts by scan, like any table with a ttl), EXPLAIN naming it. The rollup's refresh writes through the ordinary write path, so each pass keeps the index exact: an upserted bucket replaces its entry and a bucket the recompute no longer produces (its source rows were deleted) is removed with its entry, on every replica. The index replays with the rollup on schema sync, mirrors to witnesses under witness.mirror_indexes, lists in SHOW INDEXES / SHOW CREATE TABLE r, and drops with the rollup (or with the source, through the cascade). The bucket column needs no index: it leads the primary key, so WHERE bucket >= … AND bucket < … is a primary-key slice already.

Global (value-sharded) indexes

CREATE INDEX i_msgs_sender ON msgs (sender) WITH (global = true);

SELECT id, body FROM msgs WHERE sender = 'ada';            -- routed probe
SELECT id FROM msgs WHERE sender IN ('ada', 'bob');        -- one probe per value
EXPLAIN SELECT id FROM msgs WHERE sender = 'ada';
-- access:          global-index probe via 'i_msgs_sender' (routed to the value's replica set)
-- cluster.fan_out: global-index probe routed to the value's replica set

What it is. A regular secondary index is local: each node indexes only its own shard, so a non-PK equality probe must scatter to every member to gather candidates. A global index instead stores its entries in an internal replicated table placed on the ring by indexed value — all entries for sender = 'ada' live on one replica set. An equality probe routes there directly: one replica-set round-trip, like a PK point read, regardless of cluster size.

When to use it. Only when RF < members (a genuinely sharded cluster) and the hot shape is full equality on the indexed columns — the "fetch everything for this value" class (dedup probes, per-user/per-account lookups). On a full-copy cluster (RF ≥ members) every node already holds all data and a local index answers without any fan-out — global buys nothing there. Measured: at 2 members the two are at parity (the candidate re-read dominates); the routing win grows with member count since scatter cost scales with members and the probe does not.

What routes, what doesn't. - Routes: the full value tuple pinned by = or a literal IN list (composite (a, b) needs both pinned; IN lists cross-multiply into one probe range per tuple, up to 100). A multikey component (tags[]) routes on element equality like the local form. - Falls back to the scatter paths (still correct, just wider): value ranges (sender > 'a'), partial prefixes of a composite, IN lists past the cap, values hotter than 10 000 candidate rows, an entry replica set below read quorum, or the index still building. - Candidates are always quorum re-read and re-checked against the full WHERE, exactly like local-index candidates — a stale or orphaned entry can surface a candidate but never a wrong row.

Lifecycle. DDL acks at schema-apply; the coordinating node then backfills pre-existing rows through the replicated write path in the background and broadcasts readiness. Until then SHOW INDEXES reports global (building) and probes fall back to scatter. After that, every INSERT/UPDATE/DELETE maintains entries as part of the statement (one extra replicated write per changed indexed value, at the statement's write consistency, plus one point read of the row's old version on write). The anti-entropy pass verifies entries against rows both ways — a crash between a row write and its entry write self-heals within a repair interval.

Costs to keep in mind. Writes to the table pay the companion entry write + old-row read; multi-row INSERTs take a per-row path. Entries are rows in a hidden replicated table (__gidx__<name>), so they consume storage on their owners and participate in repair like any table. Local indexes remain the default and the better choice for everything except the sharded equality-probe shape.

Design details and internals: GLOBAL_INDEXES.md.

How the planner chooses (what your index must look like)

  • Leftmost prefix. A composite index (a, b, c) serves filters that pin a (then b, then c) by equality, plus one trailing range on the first unpinned column. WHERE b = 1 alone does not use (a, b).
  • Selectivity ranking. Among usable indexes, the one consuming the most equality columns (then a range) wins — a two-equality probe beats a sibling index that pins one column and spans half the table.
  • Global indexes are consulted last, on the cluster path only. The coordinator tries PK point reads, then local-index plans, then a global probe (full-tuple equality/IN only), then the scatter fallbacks. A local index on the same columns therefore wins over a global one — don't declare both on one shape.
  • A fully pinned primary key is a point-read set. Every PK column pinned by = or a literal IN list (bound array parameters included) resolves to exact candidate keys — one bloom-gated point read each (cross product on composite keys, ≤ 1000), never a scan; EXPLAIN shows point-read set. On a cluster the keys route to their replica sets, and ORDER BY <indexed> LIMIT k at QUORUM takes a distributed sorted top-k (see CLUSTER_OPERATIONS.md) instead of gathering the match set.
  • A bare min(col) / max(col) reads the index edge. SELECT max(ts) FROM events (one item, no GROUP BY, joins, ORDER BY or LIMIT) is planned as SELECT ts … ORDER BY ts DESC NULLS LAST LIMIT 1 whenever that ORDER BY would walk a secondary index: one entry read instead of a whole-table gather. NULLs are not indexed and sort last either way, so the aggregate's semantics hold; a filter the index cannot serve takes the ordinary aggregate path.
  • ORDER BY + LIMIT prefers a sorted walk. An index whose next-unpinned column matches the ORDER BY column serves the sort directly and stops at the limit ((account, date) for WHERE account = ? ORDER BY date DESC LIMIT 50). Both directions read about as many index entries as the limit needs — a DESC walk pages backward from the range's end — however many entries the range holds. Multi-key ORDER BY works when the leading sort key is the indexed one (tie groups re-sort exactly). But: when a strictly more selective equality index also covers the filter, the planner first probes its range (a capped, O(cap) peek) — if the filter matches almost nothing, it answers through the equality index instead of walking the whole sorted range finding nothing.
  • Index-only counts. A filtered COUNT(*) whose conjunctive equality/range filter is fully consumed by one index is answered from index-range cardinality — no row reads, safe at any table size. One NULL-safe negated equality (col != v OR col IS NULL) beside an index-only conjunction (covering, or the intersection below) counts by complement (two index-only counts); a BARE col != v cannot — SQL != excludes rows where col is absent, and no index sees those. Bound strictness is exact here: a >/< counts strictly, excluding the boundary value, so COUNT always agrees with the SELECT over the same predicate. A numeric range (internal_date >= ? AND internal_date < ?) is counted index-only too, with one guard: the store is schema-less, so Int(1) and Float(1.0) are distinct index keys while filter evaluation compares them numerically. The planner first checks which numeric encoding the index holds under the equality prefix; exactly one → the bounds are built in that encoding (integral and inclusive for Int/Timestamp, a strict bound stepping one unit in; inclusive-only for Float/Decimal) and the entry count is exact; mixed encodings → the count narrows through the union of per-encoding ranges and re-reads each candidate row, exact across types and still pruned. Numeric equalities stay on that re-read path. Several indexes together cover a conjunction no single one does: when every constrained column is consumed by some index — each taking its equality prefix plus at most one trailing range — the count is the size of the intersection of their row-key sets, read from the indexes alone (account = ? AND labels = ? AND internal_date >= ? AND internal_date < ? is the label index's keys ∩ the date index's keys). Ranges are sized first and the intersection declines past 1,000,000 keys per range, where the streamed count answers as before. Without it that shape counted through the paged quorum merge over its candidates' key span — with random row ids, the whole table's rows — and a mail dashboard's per-label count tripped the byte budget. A single index's range is counted in pages: passes of about a millisecond, each under its own hold of the engine read lock, so writes land between them instead of queueing behind the whole range (a member serving a coordinator's or a peer's count, and a standalone server serving a plain SELECT count(*) … WHERE …). The answer is the count one hold would give at the last pass — every entry written, removed or moved meanwhile counted as it then stands, once or not at all. Intersection counts are read in one hold.
  • Multiple NULL-safe exclusions use bounded row-key sets. For example, account = ? AND (labels != 'SPAM' OR labels IS NULL) AND (labels != 'TRASH' OR labels IS NULL) counts the account's primary keys minus the union of the two equality-index ranges. Primary-key stamps include missing/NULL labels and empty arrays; the union deduplicates overlapping labels and repeated array elements. Additional indexed positive equalities intersect their key streams. Every secondary index component must be equality-pinned, so all inputs share primary-key order; among such indexes the one pinning the most components (the narrowest range, e.g. (account, labels[]) over (labels[])) is read. Execution holds at most 256 keys per input page, with at most 32 predicates; it does not retain the account's candidate IDs or evaluate row bodies. Current SSTables supply primary stamps from value-free sidecars; legacy files without sidecars may decompress body blocks to obtain stamps. Server counts release the engine read lock between passes. Range-scoped mutation watches restart a changed input, while writes to other accounts do not restart it. After two restarts the query uses the ordinary row path. The planner also falls back for queued index maintenance, known stale entries, TTL, transactions, historical reads, unpinned/trailing ranges, numeric or timestamp-coercible literals, and unsupported scalar/array type coverage. Bounded type-prefix probes reject a plain index holding arrays and incompatible scalar types whose != result is SQL UNKNOWN; mixed-type multikey arrays conservatively use the row path as well. EXPLAIN names INTERSECTION, UNION, DIFFERENCE and fallback reasons. EXPLAIN ANALYZE reports local primary/index keys examined, peak buffered keys, restarts, and row fallbacks separately from rows evaluated; these counters do not include remote peers. ONE uses the local set. QUORUM and ALL require fresh matching count and eligible-key fingerprints from the required replicas, otherwise the normal row merge answers.
  • Bounded-but-not-covering counts prune on a cluster too. The distributed executor enforces the bounded range the local planner chose:
  • A candidate set larger than 256 keys (the point-read cap) takes the coordinator's paged merge with the candidate set applied as pages arrive, so the scan meter charges only the bounded range — SELECT, GROUP BY and COUNT(*) alike stay within scan_row_budget on a range that is a small fraction of the table.

  • COUNT(*) counts through the candidate slice, retaining nothing per row (quorum freshness is unchanged: the bound narrows the merge rather than answering from one replica).

  • A candidate set bounds the WALK, not just the output. Past 256 candidates, the coordinator merge is bounded by the candidate set's own key span: replicas page only that slice — never every key of the table — and the predicate is re-applied on the coordinator, so the result is exact.

This holds for every index-bounded shape, LIMIT and GROUP BY included. A grouped aggregation additionally streams through the candidate slice rather than materializing it: group state is O(groups), so a window spanning a million rows costs a handful of counters on the coordinator, not a million row payloads.

EXPLAIN reports this directly. The access row is the local plan, while cluster.access states what the coordinator executes and whether the bound is enforced distributed. If you are diagnosing a query that EXPLAIN calls bounded but that behaves like a scan, read cluster.access first. - Range bounds are retyped to the encoding the index holds. A numeric literal encodes under its own type tag, so an Int-bounded range would otherwise match only Int-tagged entries and miss rows stored under a sibling numeric type. The planner probes which encodings the index actually contains for the key prefix — exactly one → the range is built in that encoding (a single ascending range, so ORDER BY and early stop are unaffected); two or more → the index is skipped and the filtered scan answers, correct across types. Keep one numeric type per column to stay on the indexed path. - Mixed-type bounds fall back. v >= 10.5 AND v <= 20 puts the lower bound in FLOAT tag space and the upper in INT tag space, so no single slice expresses it; such a predicate takes the filtered scan. - Multikey gate. A multikey index is used only when every column through the [] component is equality-pinned (the element probe). Under that gate, counts are exact — the entry key embeds the row key, so duplicate elements in one array collapse. Ranges or sorts on the array column fall back to scans by design. - Arrays in a plain index. A plain (non-[]) component keys an array value as one entry — the whole array — while col = 'x' matches an array that contains 'x'. While a plain index stores arrays at a column the query bounds (under its equality prefix), a row query or a count served from rows (=, IN, ranges, LIKE prefixes, nested paths) reads the scalar range as always plus the index's whole-array entries under that prefix, each array row re-checked: the cost follows the arrays stored (deleted ones too, until compaction purges them), not the table, and EXPLAIN says "plus its whole-array entries". An index-ordered walk (ORDER BY the index serves), an index-only count (covering, intersection, index-set) and a cluster coordinator's routed index scan cannot re-check rows and skip such an index (the filtered scan answers). The array check reads at most 32 entry stamps per bounded column; a run of more than 32 deleted array entries counts as arrays until compaction purges them. Index an array-holding column as col[] to avoid all of this. Existing indexes need no rebuild. - Consistency matters. On a full-copy cluster, reads at consistency "one" answer non-covering counts, DISTINCT, and sorted+limited gathers with a single local pass; quorum reads pay a cross-replica page merge. Interactive/UI reads that tolerate a beat of staleness should send "one".

Spotting the missing index

The workflow that has caught every production shape so far:

  1. Read the slow-query log (slow.log; every statement is also in the query log with its duration). Group by statement shape — the masked literals make identical shapes easy to aggregate.
  2. Classify each recurring shape: - Point read? (WHERE pk = ?) — should be milliseconds at any size. If slow, the row itself is fat (oversized documents; move blobs out of rows) — no index will help. - Equality filter, no order? — needs a secondary index consuming every equality column: (a, b) for WHERE a = ? AND b = ?. Watch for the scan-budget error (scan budget exceeded) — it is the engine telling you a filter had no usable index. - Filter + ORDER BY x LIMIT n? — needs the equality columns followed by the sort column: (account, date); add trailing sort tiebreakers as further index columns. - Filtered COUNT(*)? — same index as the filter, and make it consume the whole filter so the count is index-only. !=/IS NULL complements need the positive-equality form indexed. - Array containment (tags = 'x' where tags is an array)? — a multikey index ( ..., tags[]). Without it the filter cannot be served by any scalar index and always scans. - Equality probe on a sharded (RF < members) cluster whose latency grows with member count? — that is the local-index scatter tax; a global index (WITH (global = true)) routes the probe to one replica set. Irrelevant on full-copy clusters. - Substring/text search? — there is no LIKE '%x%' fast path; use a search index and MATCH/SEARCH(). Counts and GROUP BY over search predicates can be answered by fast fields. - k-NN / semantic? — vector index + NEAREST.
  3. Verify with the application's real literals at its real consistency. A hand-written probe with a different type (string where the app sends an int) cross-type-compares to NULL, matches nothing, and tells you nothing.
  4. After creating the index, re-check the same log. The shape should either vanish or drop to the point-read/limit-bounded cost class.

Reading the query log

Every executed statement — DML, SELECT, and DDL alike — is recorded:

[query] 33ms SELECT * FROM accounts WHERE "email" = ? LIMIT ?
[query] 376769ms CREATE INDEX IF NOT EXISTS i_star ON mail (account, _tombstone, is_starred)
[slow-query] 6615ms SELECT count(*) FROM mail WHERE "_tombstone" = false AND "labels" = ?

Config keys (see INSTALL.md): query_log_enabled, query_log_masked (literals → ?), query_log_file / slow_query_log_file (separate sinks), slow_query_ms threshold. Errors are recorded on the statement line, so a timeout or budget kill is visible next to its SQL.

Costs and caveats

  • Indexes are write-amplification: every row write updates every index on the table (multikey: once per array element). Index only what queries need.
  • Backfills stream but still read the whole table once per node — schedule large ones accordingly; IF NOT EXISTS makes retries idempotent.
  • An index whose leading column is wrong for your filters is worse than useless — it can win selectivity ranking with a huge range. Prefer the most selective equality columns first, then the sort column.
  • Index-only counts are exact but reflect the local replica when read at consistency "one" — a count lagging a write by a beat.
  • An index entry is served only while the row still produces it. On a cluster, index maintenance is deferred off the write path, and a newer version of a row can be stored before an older version's maintenance runs: two writes to one row hand their maintenance over out of write order, and a write the member coordinates itself is indexed in place while a replica's older task for the row is still queued (as is a REBUILD INDEX). The applier works from the row as it is stored when the task runs: an older version's maintenance never removes an entry the stored row still produces, and new entries go in only for the stored version itself — so neither a leftover entry nor a missing one results, in any order and across an unclean stop (a member replays the writes whose maintenance was still queued at its next start) (skaidb_index_superseded_entries_kept_total counts the entries this kept that the index would otherwise have lost). The read path guards the rest: an entry for a version the row has moved past is skipped, counted (skaidb_index_stale_entries_total), logged, and removed by the background sweep once the sweep has re-read the row and found that it still does not produce it — a covering count declines to trust the index while such entries are queued. A long-lived index that reports many of them was written by a version without this guard; REBUILD INDEX <name> clears it in one pass.