Secondary Indexes on Cluster Tables

DistributedIndexingQuery

Beta Works · surface still evolving · Equality, ranges and LIKE 'prefix%' are served from the index end to end; CREATE INDEX is an orchestrator API call in this release, not yet a SQL statement in cluster mode.

What it accelerates

With an index on a column, these shapes stop scanning the table and read only the rows that can match:

SELECT * FROM users WHERE id = 5000;                      -- point lookup
SELECT * FROM orders WHERE amount >= 100 AND amount < 110; -- numeric range
SELECT * FROM orders WHERE amount > 99990;                 -- one-sided range
SELECT * FROM users WHERE email = 'ana@example.com';       -- text equality
SELECT * FROM users WHERE email >= 'm' AND email < 'n';    -- text range
SELECT * FROM users WHERE email LIKE 'ana%';               -- prefix search
SELECT COUNT(*) FROM users WHERE email LIKE 'ana%';        -- and aggregates over any of them

The answer is always exactly what a full scan would return. An index only decides which rows are visited; every visited row is still checked against the whole predicate, and deleted or not-yet-committed rows are skipped as they would be anywhere else.

Creating an index

In this release an index is declared through the orchestrator API:

aetherius_orchestrator::catalog::create_index(topology, table_id, "email", column_ordinal)?;

Every worker owning the table builds the index for the files it holds and reports how many were built. From then on, a file that arrives without its index — a new INSERT, a file rewritten by the vacuum — gets one the first time it is opened. Indexes live beside the data they index, inside the tenant’s directory, so they are isolated per tenant and travel with the tenant’s snapshots.

BIGINT and VARCHAR

A BIGINT index keys on the value itself. A VARCHAR index keys on the first eight characters of each string. Strings that share those characters share a key, so a lookup visits all of them and verifies each against the full string before it counts — a prefix collision costs a few extra rows, never a wrong answer. Data whose values have long common prefixes (user00042@…, user00043@…) gains less from a text index than data whose first characters vary.

When the index is not used

• A range wider than a quarter of the file’s rows is answered by a scan instead; a probe that visits most of the table would cost more than the scan it replaces.
• Only one indexed column is used per query — the first one the WHERE bounds. Other conditions still filter the visited rows.
• Equality takes precedence over a range on the same column.
• LIKE patterns other than 'prefix%', and numeric literals outside the 32-bit range, are evaluated without the index.

A query that cannot use its index still returns the correct rows.

Keeping it honest

Each of these is counted on the worker: lookups served from an index, range probes, rows visited, prefix hits rejected by the full-string check, and hints that fell back to a scan. The planner counts the hints it emitted. A query that was supposed to be indexed and was not shows up as a fallback, not as a slower wall clock.

VARCHAR in Cluster Tables →

Text columns, comparisons and LIKE.

Deletes, Updates & the Vacuum →

Indexes are rebuilt when a file is compacted.

Index Types →

The single-node index families.