Indexes

An index is a secondary data structure that speeds up a specific lookup pattern (equality, range, similarity, text search, spatial predicate) on a property — or on a composite of properties — of a type. Without an index, the query engine has to scan every record of the target type and evaluate the filter on each one. With the right index, it jumps straight to the matching records.

Indexes are first-class schema objects in ArcadeDB: they live in their own files, are kept in sync transactionally with the data, and survive restart and replication.

Why (and when) to add an index

Indexes are not free. Each insert, update, and delete also touches every index defined on the affected property, so a type with five indexes pays roughly five times the write cost of a type with none. The break-even point is the read/write ratio of the workload: if you read a property far more than you write it, an index is almost always worth it; if you mostly append and rarely filter, you can skip it.

Add an index when:

  • You repeatedly filter by a property in WHERE, JOIN, or graph traversal (e.g. WHERE email = ?, MATCH (u {username: $u})).

  • You sort or paginate by a property and need natural ordering (ORDER BY created_at).

  • You enforce a uniqueness constraint (e.g. no two users with the same email).

  • You need a different access pattern than ordered scans — vector similarity, full-text search, or spatial containment.

Skip an index when:

  • The type is small enough that a full scan is already fast (a few thousand records).

  • The property is write-hot and almost never queried.

  • The selectivity is poor — indexing a boolean column with two values rarely beats a scan.

Index Types at a Glance

Index What it does Use it for SQL keyword

LSM Tree (default)

Ordered key index on disk. O(log N) lookups, native range scans, natural ORDER BY.

Equality, range, ORDER BY, joins, uniqueness constraints.

LSM_TREE

Hash Index

Unordered hash index with O(1) point lookups, no range support.

Primary-key access, JOINs, edge traversal where ordering is irrelevant.

HASH

Dense Vector (HNSW)

HNSW graph over float32 (or int8) embeddings; configurable similarity metric.

Semantic similarity search over text/image/audio embeddings (RAG, recommendation).

LSM_VECTOR

Sparse Vector

Inverted posting lists on (dim_id, weight) pairs with WAND retrieval.

Learned-sparse retrieval (SPLADE, BGE-M3) with embeddings produced outside the database. For BM25 keyword ranking use the Full-Text index.

LSM_SPARSE_VECTOR

Full-Text

Lucene-backed tokeniser + analyser pipeline with native BM25 ranking (the default).

Natural-language keyword search ranked by BM25 relevance, fuzzy matching, "more like this", and the keyword leg of hybrid BM25 + vector search.

FULL_TEXT

Geospatial

Quad-key index over WKT geometries.

Spatial predicates (geo.within, geo.intersects, distance, bounding-box queries).

(geometry property)

Each index can additionally be made unique (rejects duplicates), case-insensitive (see COLLATE CI), and configured with a null strategy (skip nulls, error on null, or index nulls).

LSM Tree (default)

ArcadeDB’s default index uses a Log-Structured Merge Tree. New keys land in an in-memory write buffer; when the buffer fills, it is flushed to disk as an immutable sorted page. Background compaction periodically merges these pages so that lookups visit only a handful of files even as the index grows. You can also trigger a compaction on demand with COMPACT INDEX, for example to settle an index right after a bulk load. When you need to build a large LSM index over data that is already loaded, the sorted bulk build constructs it directly in compacted form and is dramatically faster than the record-at-a-time build.

Why ArcadeDB picks LSM by default:

  • Write-friendly: inserts hit memory first, no in-place page updates, no random-write amplification.

  • Compaction is cheap: data is already sorted, so merging two pages is a linear scan rather than a B+Tree rebalance.

  • Range and ordered access are native: keys are stored in order, so ORDER BY and WHERE x BETWEEN ? walk the index directly without an in-memory sort.

  • Space-efficient: keys are packed and compressed, with no half-empty B+Tree pages.

The classic B+Tree (the default in most relational databases) trades these write-side wins for slightly faster random-access reads on a steady-state index. If your workload is read-only and fits in memory, B+Tree can win small constants; under ingest pressure, mixed read/write, or large datasets on slow storage, LSM almost always wins. For the on-disk format and compaction details, see LSM-Tree Internals in the reference.

Large ranges: physical order and the scan fallback

Fetching records through an index costs one page access per record, in key order, which means in random order on disk. That is cheap for a selective condition and while the type fits the page cache, but a condition that matches a large share of the type - WHERE shipdate ⇐ '1998-09-02' over most of a table - is then far slower than simply scanning the type. So, when the statement aggregates the rows (count(), sum(), GROUP BY, …​) or sorts them with an ORDER BY the index does not serve, the SQL engine reads the matching index entries alone, before loading any record, and decides:

  • when more of them match than a share of the records the type holds, the index is set aside and the rows come from a scan of the type, filtered by the same condition;

  • otherwise the matching records are loaded in physical order (bucket by bucket, in the order they are stored), so every page is read once, in a forward sweep, however many of its records match.

In SQL both run in parallel wherever a scan of the type would: the scan is the parallel scan, and the records loaded in physical order are shared among its workers, a slice of the sorted record addresses each. An aggregation over them runs in those workers too, together with the conditions the index does not answer. The index entries themselves are read by one thread, so the more workers the scan has, the sooner it wins: the share is arcadedb.queryIndexMaxSelectivity (0.6 by default) for a sequential scan, divided by (1 + W) / 2 for a scan on W workers - 24% of the type on 4 workers, 6% on 18. The OpenCypher label scan runs on one thread, so it gives way at the sequential share.

The decision is taken on every execution with the actual parameter values. PROFILE shows the choice under EXTRACT VALUE FROM INDEX ENTRY (served by physical order, with loaded in parallel when the workers loaded the records, or served by full scan). OpenCypher does the same for a MATCH anchored on an index range whose RETURN aggregates or has an ORDER BY: PROFILE then shows NodeIndexRangeScan as served in physical order or served by label scan. An OpenCypher ORDER BY on the indexed property itself, under a LIMIT, is answered from the index order instead, so the scan stops at the first rows rather than reading the whole range (see keyset pagination). Over a whole label this holds in both directions when the property is declared MANDATORY and NOTNULL (the constraint is trusted: ALTER PROPERTY does not validate vertices written before it), when the only WHERE is x IS NOT NULL, and for an index created with NULL_STRATEGY INDEX, whose null keys are read from the end openCypher sorts them at (last ascending, first descending). Descending over a nullable property with the default null strategy still scans, because nulls sort first.

An OpenCypher WHERE that is an OR of equalities or IN lists on indexed properties is answered from the indexes, one seek per property with the vertices found by both returned once; with a disjunct on a property that has no index it scans. A MATCH whose filter uses only comparisons, arithmetic, string matching and IN on the node’s properties (no function calls) scans its label in parallel, like the SQL scan, when the type is large enough and no transaction is open.

A statement whose result can show the order the rows arrive in keeps reading them in key order exactly as before: one that returns its rows as they come, with no aggregation and no ORDER BY; one using an order-sensitive aggregate such as list() (SQL) or collect() (OpenCypher); and a GROUP BY with LIMIT or SKIP but no ORDER BY. The adaptive fetch applies to LSM_TREE indexes with the default collation and no BY ITEM/BY KEY/BY VALUE property, searched with =, <, ⇐, >, >= or BETWEEN, and never to a point lookup on a UNIQUE index. Setting arcadedb.queryIndexMaxSelectivity to 0 turns it off.

LSM indexes come in four flavours, picked at CREATE INDEX time:

  • UNIQUE — rejects duplicate keys.

  • NOTUNIQUE — allows duplicates; the index returns every record with that key.

  • FULL_TEXT — see Full-Text Index.

  • Special variants for vector and spatial workloads — see the rest of this page.

Hash Index

When you only ever do point lookups (WHERE id = ?) and never sort or range-scan, a hash index is the right tool: each lookup is one or two page reads regardless of index size, with none of LSM’s compaction overhead.

ArcadeDB uses extendable hashing, a disk-oriented variant:

  1. Each key is hashed to a binary code; the index maintains a global depth — the number of leading bits used as a prefix.

  2. A directory maps every 2globalDepth prefix to a bucket page. Multiple directory entries can point at the same bucket while the bucket’s local depth is shallower than the global depth.

  3. A lookup hashes the key, reads the directory entry for the matching prefix, and scans the target bucket page: every entry has a 1-byte tag (the low byte of its key hash), so only the entries whose tag matches are compared in full.

  4. When a bucket overflows, only that bucket splits — the local depth bumps by one, entries are redistributed, and the directory doubles only when the local depth exceeds the global depth.

Use case Hash LSM Tree

Point lookup (WHERE id = ?)

Best — O(1), 1–2 page reads

O(log N), several page reads

JOIN / edge traversal

Best — constant-time resolution

Good

Range scan (WHERE age > 30)

Not supported

Best — ordered iteration

ORDER BY on indexed field

Not supported

Best — natural ordering

Steady-state insert throughput

Consistent — no compaction

May dip during compaction

Two modes are available: UNIQUE_HASH (rejects duplicates) and NOTUNIQUE_HASH (allows duplicates). The same null strategies as LSM are supported (SKIP, ERROR, INDEX): with NULL_STRATEGY ERROR a record whose indexed property is null or missing fails the commit, as it does on an LSM index.

A hash index answers an equality on its whole key only. A composite hash index on (a, b) is used when a query holds both a and b equal to a value, never for a condition on a alone: such a query is answered by a scan or by another index, without an error. With NULL_STRATEGY INDEX, a unique index (UNIQUE or UNIQUE_HASH) accepts any number of records whose key is entirely null, and WHERE p IS NULL returns all of them.

The entries of a bucket page are not kept in key order, so an insert appends the entry and one slot instead of shifting the slot directory. The page size defaults to 4 KB for fixed-width keys (numbers, dates, links) and 16 KB when a key column is a STRING, BINARY or DECIMAL; on the workloads measured this made hash inserts as fast as LSM or faster, and point lookups about three times faster. Since 26.11.1 a NOTUNIQUE_HASH key keeps its record ids inline in its entry while they take up to a quarter of a page; past that they move to pages of their own, and adding a record to the key writes the last of those pages only. Inserting into a key costs the same whether it holds ten record ids or millions, so NOTUNIQUE_HASH also suits low-cardinality columns, and the pages freed by deletions are reused. Hash indexes created before 26.11.1 keep every record id inline, where a key with thousands of records is slow to insert into, until REBUILD INDEX moves them to the current layout. A 26.10.1 server refuses to open a database holding a hash index created or rebuilt by 26.11.1 or later, naming the unknown layout version: before such a downgrade, drop the hash indexes and recreate them after it. Hash indexes created before version 26.10.1 keep their sorted pages (and their page size) and keep working; REBUILD INDEX moves them to the current layout. The layout version is part of the index file name, and a server older than 26.10.1 does not check it: it would read the new pages as sorted ones and corrupt the index. Never open a database holding a hash index created or rebuilt by 26.10.1 or later with an older server; after a downgrade, drop and recreate the hash indexes first (since 26.10.1 the bucket pages also carry a bit in the header that older servers misread). Loading many records per key into a NOTUNIQUE_HASH index no longer slows down as the key grows (a regression of 26.10.1 snapshots, fixed before the release). Smaller pages mean more of them (about 16 times more than at 64 KB for the same data), so expect more, smaller files, and a lookup scans the tags of the page, so an explicit page size above 16 KB makes hash lookups slower than the default, not faster; the directory is copied on every doubling and the previous copy stays in the file until the next rebuild.

Dense Vector Index (LSM_VECTOR)

LSM_VECTOR indexes dense float32 (or quantized int8) embeddings using an HNSW graph layered on top of the LSM-Tree storage backbone. Use it for semantic similarity over text, image, or audio embeddings produced by a model — e.g. retrieval-augmented generation, "find similar products", or content-based recommendation.

What you get out of the box:

  • Similarity metrics: COSINE (default), DOT_PRODUCT, EUCLIDEAN.

  • Quantization: NONE, INT8, BINARY, or PRODUCT (index-internal compression that trades a bit of recall for a 4–8× memory reduction and a 2–3× search speed-up).

  • Wire encoding: store the property as ARRAY_OF_FLOATS or as a BINARY byte-per-dim payload (encoding: INT8) to cut HTTP and bucket size 4× when your model emits int8 natively.

  • Persistent, transactional, replicated: like every other ArcadeDB index, it survives restart, joins HA replication, and is updated inside the originating transaction.

See Vector Search for the full parameter table (dimensions, efSearch, maxConnections, beam width, multi-layer HNSW, on-graph storage) and worked examples in SQL, Cypher, and Java.

For wide vertices, declare the embedding property as EXTERNAL true so the bytes move out of the main bucket. Traversals that don’t project the vector stop paying for it in cache misses. See Store Embeddings in an EXTERNAL Property.

Sparse Vector Index (LSM_SPARSE_VECTOR)

Where dense vectors collapse meaning into a few hundred floats, sparse vectors keep only the non-zero positions of a high-dimensional vocabulary — typically tens of thousands of token slots with a handful of non-zero weights per document. They are the natural output of learned-sparse retrieval models (SPLADE, BGE-M3, OpenSearch sparse encoders) and of BM25-as-sparse-vector pipelines, and they excel at exact-term recall where dense embeddings get fuzzy.

LSM_SPARSE_VECTOR stores the data as an inverted index — a posting list per dimension keyed by (dim_id, rid, weight) — and retrieves with document-at-a-time WAND, pruning postings whose cumulative upper bound cannot beat the current top-K score.

Per-document storage is two parallel array properties on the same record:

  • ARRAY_OF_INTEGERS of non-zero dimension ids, and

  • ARRAY_OF_FLOATS of the matching weights (non-negative).

Pair a dense and a sparse index on the same record for hybrid retrieval: dense vectors recover paraphrased / semantically close matches, sparse vectors anchor on exact terms, and Reciprocal Rank Fusion combines both rankings without score-scale calibration. See Sparse Vector Search and the surrounding hybrid-retrieval examples for the full reference and worked queries.

Full-Text Index

FULL_TEXT is built on top of ArcadeDB’s LSM Tree — the index storage, ACID transactions, WAL, background compaction, replication, and HA all come for free from the underlying LSM implementation. Lucene is layered on top only for what it does best: the analyzer / tokenizer / stemmer pipeline that turns a free-text field into the searchable terms the LSM stores.

The result is a Lucene-quality search experience over text — tokenization, stemming, fuzzy match, "more like this" recommendations, relevance ranking — without the operational cost of running a separate Lucene index file format alongside the database.

Highlights:

  • LSM-Tree storage under the hood: same crash-safe writes, compaction, and replication path as every other ArcadeDB index — no separate Lucene segments to manage.

  • Pluggable analyzer per field (standard, stop-word, language-specific, custom) for tokenization and stemming.

  • Full Lucene query syntax at query time: phrase, proximity, fuzzy (~), wildcards, boolean operators, and caret term boosts (term^N).

  • BM25 relevance scoring (TF/IDF + document-length normalization, with tunable parameters and per-field boosts; legacy match-count CLASSIC scoring is still available) exposed as the $score query variable, so you can ORDER BY $score DESC and rank results.

  • "More like this" support to find documents similar to a given record by their token distribution.

For the full create-index syntax, analyzer configuration, query language, scoring, and the MLT (more-like-this) configuration, see Full-Text Index in the data-modeling guide. To tune BM25 see Similarity Models, and to combine BM25 with vector search see Hybrid Retrieval.

Geospatial Index

A geospatial index sits on a property holding a WKT geometry string (Point, LineString, Polygon, MultiPolygon). The index encodes each shape’s bounding region with a configurable quad-key precision so spatial predicates touch only the candidate cells instead of every row.

What it accelerates:

  • geo.within, geo.contains, geo.intersects and the other geo.* spatial predicates.

  • Bounding-box queries and "find everything within N km of point P".

  • Joins between two spatial types (which features lie inside which polygons).

For the full SQL surface, precision tuning, and worked queries, see Geospatial Index in the data-modeling guide.

Case-Insensitive Indexes (COLLATE CI)

The text indexes above are case-sensitive by default: "Hello" and "hello" are different keys. Add COLLATE CI to a property in the CREATE INDEX statement to fold case at index time. This affects LSM and Hash indexes; it does not apply to vector, sparse-vector, or full-text indexes (which have their own normalization story).

Effect:

  • Plain = and IN are case sensitive: WHERE Name = 'Hello World' matches only "Hello World", with or without the index. The index narrows the candidates and the condition is checked on the rows it returns, so the answer never depends on whether the optimizer used the index (since 26.11.1, issue #9403). For a case-insensitive lookup write it on Name.toLowerCase() (see below).

  • Range queries on the plain property (Name >= 'C', Name BETWEEN 'a' AND 'c') never use the index. The index holds lower-cased keys and probes with a lower-cased bound, which is not the case-sensitive comparison the predicate asks for, so these conditions are evaluated by a scan and answer exactly as they do without the index (since 26.10.1, issue #8932). To get a case-insensitive range, write it on Name.toLowerCase(), which the index serves (see below).

  • Unique constraints fold case: a UNIQUE index with COLLATE CI prevents inserting both "Admin" and "admin".

  • Original values are preserved in the document; only the index key is lowercased (using the root locale).

  • Sorting and aggregates ignore the index: because the index order is the lowercased order, ORDER BY, SQL min()/max(), Cypher range predicates (<, ⇐, >, >=), the native select() API range operators (including between) and Gremlin range has() on the property never use a COLLATE CI index for ordering, so they return the same result as without the index.

CREATE INDEX ON <type> (<property> COLLATE CI) <index-type>

Composite indexes can mix policies per property:

CREATE INDEX ON Product (Name COLLATE CI, Code) UNIQUE

Here Name is case-insensitive while Code remains case-sensitive.

The query optimizer recognizes .toLowerCase() on a property with a COLLATE CI index and rewrites the query to use the index directly:

SELECT FROM Product WHERE Name.toLowerCase() = 'hello world'

So existing applications that fold case in queries get the speedup for free.

The index is used for = and IN with any value, and the condition is still checked on the rows it returns, so Name.toLowerCase() = 'HELLO' (an upper-case value can never match a lower-cased name) correctly returns nothing. Ranges and BETWEEN on Name.toLowerCase() use the index only when their bounds are literals that are already lower case; with a mixed-case literal or a bound parameter the query is evaluated without the index. Since 26.10.1 the property name in toLowerCase() must match the indexed property exactly, including its case (issue #8560).

When to reach for it:

  • User-facing lookups: usernames, emails, product names — the end-user expects case-insensitive matching.

  • Deduplication: a uniqueness constraint that ignores case.

  • Replacing toLowerCase() patterns: the conversion happens once at insert time instead of on every query.

Building a large index over existing data (sorted bulk build)

Currently exposed through the Java API only (no SQL keyword yet).

Creating an index on a type that already holds data has to read every existing record and add its key to the index. The default path inserts those keys one at a time through the normal write buffer, exactly as if the records were being indexed live. That is fine for small and medium types, but it does not scale linearly: as the index grows, each insert does more work and the buffer flushes and compacts repeatedly mid-build, so the effective build rate degrades as it runs. On tens of millions of records the index step, not the data load, becomes the bottleneck.

The sorted bulk build is an opt-in, offline build strategy for exactly this situation. Instead of inserting keys one at a time, it:

  1. scans the records and extracts the same index keys the normal path would (scalars, composites, list/map expansions, nulls, and collation are all preserved);

  2. builds bounded, sorted runs in memory and spills them to disk when a configurable memory budget is reached, so the build stays within RAM regardless of dataset size;

  3. k-way merges the runs into one globally sorted key stream; and

  4. writes that stream directly as compacted on-disk pages.

Because the keys arrive already sorted, this replaces a growing sequence of against-a-growing-tree inserts with a bounded external sort followed by a sequential page write. The output is already in compacted form, so you also skip the post-build COMPACT INDEX step. On a 10-million-key index this reaches equivalent compacted output several times faster than the default build followed by a compaction.

database.getSchema().buildTypeIndex("Document", new String[] { "uri" })
    .withType(Schema.INDEX_TYPE.LSM_TREE)
    .withUnique(true)                // both unique and non-unique are supported
    .withBuildMode(IndexBuildMode.SORTED)
    .withBuildMemoryBudget(1L << 30) // optional: RAM budget in bytes; 0 (default) auto-sizes from the heap
    .withBuildMergeFanIn(8)          // optional: number of runs merged per pass (default 8, min 2)
    .create();

All three build options are optional: withBuildMemoryBudget bounds the RAM used before spilling (defaults to a fraction of the heap), withBuildMergeFanIn sets how many sorted runs are merged per pass, and withBuildSpillDirectory(Path) chooses where the temporary spill files are written (defaults to the database directory). They are ignored under IndexBuildMode.DEFAULT.

For a unique index, uniqueness is enforced directly on the globally sorted stream: two records that produce the same key are adjacent after the sort, so a duplicate is detected in a single pass (across all buckets, with no extra sort or in-memory hash set) and the build throws DuplicatedKeyException and publishes nothing. As with online unique indexes, an all-null key is exempt (SQL NULL != NULL), while a partial-null composite key is still constrained; the null strategy (SKIP, ERROR, INDEX) behaves exactly as it does for a normal build.

The sorted mode is an explicit operator choice with strict preconditions. It is restricted to:

  • the LSM_TREE index type (unique or non-unique),

  • a single-server, non-replicated database (not part of an HA cluster), and

  • no active transaction on the calling thread (the build runs in its own exclusive window).

If any precondition is not met, the build fails immediately, before creating any file — it does not silently fall back to the slower default path, so you always get the strategy you asked for. Everything else (online reads and writes, HA replication) is unaffected because unsupported requests are rejected up front. Leave withBuildMode unset (or set it to IndexBuildMode.DEFAULT) to keep the standard record-at-a-time build.

Parallel construction, an automatic warn-and-fall-back mode, HA/replicated operation, and a SQL syntax are planned as follow-ups.

What can be indexed

ArcadeDB indexes can be defined on properties of nearly any type, with the right index choice per data shape:

Property kind Indexable Typical index

Scalar (STRING, INTEGER, LONG, FLOAT, DOUBLE, DATE, DATETIME, BOOLEAN)

Yes

LSM_TREE for equality + range, HASH for point-only

Composite of N properties

Yes

LSM_TREE (ordered prefix scans) or HASH

STRING for natural language

Yes

FULL_TEXT

ARRAY_OF_FLOATS / BINARY embedding

Yes

LSM_VECTOR

ARRAY_OF_INTEGERS + ARRAY_OF_FLOATS (paired)

Yes

LSM_SPARSE_VECTOR

STRING containing WKT geometry

Yes

Geospatial index

LIST / ARRAY_OF_* (per element)

Yes

LSM_TREE indexed by values

MAP

Yes

LSM_TREE indexed by keys or by values
A MAP value indexed as a whole (without BY KEY/BY VALUE) is ordered by its sorted (key, value) pairs since v26.9.1, independently of the insertion order of the entries. An index of this kind created with an earlier version must be rebuilt after upgrading (REBUILD INDEX).

Unique and non-unique modes are available for the scalar and composite cases. For the exact SQL command syntax, see CREATE INDEX; to drop or rebuild after a schema change, see DROP INDEX and REBUILD INDEX; to force a compaction on demand (for example after a bulk load), see COMPACT INDEX.

See also