Scalar Indexing

Applies to: Embedded ✅, Enterprise ✅ Scalar indexes accelerate filters on values such as numbers, categories and strings. Use them for selective table queries or metadata filters combined with vector and full-text search. LanceDB supports five types of scalar indexes:

BTREE: Stores column data in sorted order for binary search. Best for columns with many unique values.
BITMAP: Uses bitmaps to track value presence. Ideal for columns with few unique values (e.g., categories, tags).
LABEL_LIST: Special index for List<T> and LargeList<T> columns of primitive values supporting array_has_all and array_has_any queries.
FM: FM-Index over string or binary columns that accelerates substring search via contains(col, 'needle').
NGRAM: Trigram index over UTF-8 string columns that accelerates substring search via contains(col, 'needle'), along with some LIKE and regular-expression filters.

Use scalar indexes with:

Vector search (prefilter results using metadata)
Full-text search (combining keyword matching with structured filters)
SQL scans (optimizing WHERE clauses on scalar columns)
Key-value lookups (enabling rapid primary key-based retrievals)

Choosing the Right Index Type

Data Type	Filter	Index Type
Numeric, String, Temporal	`<`, `=`, `>`, `in`, `between`, `is null`	`BTREE`
Boolean, numbers or strings with fewer than 1,000 unique values	`<`, `=`, `>`, `in`, `between`, `is null`	`BITMAP`
List of low cardinality of numbers or strings	`array_has_any`, `array_has_all`	`LABEL_LIST`
String or binary (`Utf8`, `LargeUtf8`, `Binary`, `LargeBinary`)	`contains(col, 'needle')`	`FM`
String (`Utf8`, `LargeUtf8`)	`contains(col, 'needle')`, `LIKE`, regex	`NGRAM`

Scalar Index Operations

1. Build the Index

You can create multiple scalar indexes within a table. By default, the index will be BTREE, but you can always configure another type like BITMAP

tbl = db.open_table("scalar_index_build")
tbl.create_scalar_index("book_id")
tbl.create_scalar_index("publisher", index_type="BITMAP")

If you are using LanceDB Enterprise, the create_scalar_index API returns immediately, but the building of the scalar index is asynchronous. To wait until all data is fully indexed, you can specify the wait_timeout parameter on create_scalar_index() or call wait_for_index() on the table.

2. Check Index Status

index_name = "label_idx"
table.wait_for_index([index_name])

wait_for_index(...) waits until the named scalar indexes exist and index_stats(...) reports num_unindexed_rows == 0. If a table is receiving steady writes, that fully indexed state may not stabilize before the timeout. In LanceDB Enterprise, rows added after the index was built are indexed only once enough have accumulated, so a small remainder can also keep the wait from finishing.

3. Update the Index

Updating the table data (adding, deleting, or modifying records) requires that you also update the scalar index. In the embedded library, call optimize, which will trigger an update to the existing scalar index.

table.add([{"vector": [7, 8], "book_id": 4}])
table.optimize()

New data added after creating the scalar index will still appear in search results if optimize is not used, but with increased latency due to a flat search on the unindexed portion. LanceDB Enterprise does not support optimize(); instead it adds new rows to BTREE, BITMAP and LABEL_LIST indexes in the background once enough have accumulated. It does not update FM indexes automatically.

4. Run Indexed Searches

The following scan will be faster if the column book_id has a scalar index:

table = db.open_table("books")
result = table.search().where("book_id = 2").limit(10).to_pandas()

Scalar indexes can also speed up scans containing a vector search or full text search, and a prefilter:

table = db.open_table("book_with_embeddings")
table.search([1.2] * 2).where("book_id != 3").limit(10).to_pandas()

FM-Index for substring search

The FM index is a scalar index built over string or binary columns that accelerates substring lookups expressed as contains(col, 'needle'). Unlike the tokenized FTS index, which matches whole words after tokenization, the FM-Index matches arbitrary substrings of the raw bytes. It works well for URLs, file paths, identifiers, log lines, or any column where you search for a fragment rather than a word. Use the FM-Index when:

Filters use contains(col, 'needle') (substring), not equality or word search.
The column is Utf8, LargeUtf8, Binary, or LargeBinary.
You want substring matches without paying for tokenization, language analysis, or BM25 scoring.

Pick FTS instead when you need word-level relevance ranking, phrase queries, or language-aware tokenization.

Create an FM-Index

Build an FM-Index with the async create_index API by passing the Fm config in Python or Index.fm() in TypeScript. In Rust, use Index::Fm(FmIndexBuilder::default()). A deployment builds FM-Indexes too, but does not add new rows to them in the background as it does for the other scalar indexes.

from lancedb.index import Fm

await tbl.create_index("text", config=Fm())

import * as lancedb from "@lancedb/lancedb";
import { Index } from "@lancedb/lancedb";

await tbl.createIndex("text", { config: Index.fm() });

use lancedb::index::Index;
use lancedb::index::scalar::FmIndexBuilder;

tbl.create_index(&["text"], Index::Fm(FmIndexBuilder::default()))
    .execute()
    .await?;

After the index is built, substring filters use it automatically:

table.search().where("contains(text, 'needle')").limit(10).to_pandas()

list_indices() reports the index type as "Fm".

NGram index for substring search

The Python NGram configuration is not available in Python 0.41.0. Its example below uses the 0.42.0b8 client. Use the FM index above with Python 0.41.0. Rust’s 0.41.0 API already supports Index::NGram; the Python binding has a separate version requirement.

The NGram index is a scalar index over UTF-8 string columns. It tokenizes values into overlapping character n-grams (using Lance’s default trigram parameters) and uses them to accelerate contains(col, 'needle'), along with some LIKE and regular-expression filters. Unlike the FM index, which matches arbitrary substrings of the raw bytes, the NGram index is trigram based, so a query shorter than the n-gram length cannot be served by it.

Create an NGram index

Build an NGram index with the async create_index API by passing the NGram config in Python. In Rust, use Index::NGram(NGramIndexBuilder::default()).

from lancedb.index import NGram

await tbl.create_index("text", config=NGram())

use lancedb::index::Index;
use lancedb::index::scalar::NGramIndexBuilder;

tbl.create_index(&["text"], Index::NGram(NGramIndexBuilder::default()))
    .execute()
    .await?;

After the index is built, substring filters use it automatically:

table.search().where("contains(text, 'needle')").limit(10).to_pandas()

list_indices() reports the index type as "NGram".

Index UUID Columns

LanceDB supports scalar indexes on UUID columns (stored as FixedSizeBinary(16)), enabling efficient lookups and filtering on UUID-based primary keys.

To use FixedSizeBinary, ensure you have:

Python SDK version 0.22.0 or later
TypeScript SDK version 0.19.0 or later

1. Define UUID Type

import pyarrow as pa

2. Generate UUID Data

def generate_random_names():
    base_names = ["Alice", "Bob", "Carla", "David", "Eve", "Frank", "Grace"]
    letter = random.choice(string.ascii_uppercase)
    return f"{random.choice(base_names)} {letter}."

def generate_uuids(num_items):
    return [uuid.uuid4().bytes for _ in range(num_items)]

# Generate some UUIDs and random names
n = 7
uuids = generate_uuids(n)
names = [generate_random_names() for _ in range(n)]

3. Create Table with UUID Column

table_name = "index-on-uuid"

uuid_array = pa.array(uuids, pa.uuid())
name_array = pa.array(names, pa.string())
schema = pa.schema(
    [
        pa.field("id", pa.uuid()),
        pa.field("name", pa.string()),
    ]
)
data_table = pa.Table.from_arrays([uuid_array, name_array], schema=schema)
table = db.create_table(table_name, data=data_table, mode="overwrite")

4. Create and Wait for the Index

index_name = "id_idx"
table.create_scalar_index("id")
table.wait_for_index([index_name])

5. Perform Operations with the UUID Index

new_users = [
    {"id": uuid.uuid4().bytes, "name": "Hannah D."},
    {"id": uuid.uuid4().bytes, "name": "Ian B."},
]
# Insert or update using the UUID index
table.merge_insert(
    "id"
).when_matched_update_all().when_not_matched_insert_all().execute(new_users)

Index nested fields

You can build a scalar index on a field inside a struct column by passing the canonical dot-separated path to create_index. This is useful when filters target attributes nested under a metadata-style column, for example metadata.user_id or metadata.event.type. If a literal segment of the path itself contains a dot (for example a column named user.id nested inside metadata), wrap that segment in backticks so LanceDB can tell the dot apart from the path separator: metadata.`user.id`. list_indices() echoes the same canonical path back, so the column you pass in round-trips through index metadata regardless of nesting depth or escaping. Use the same path in filter predicates, for example metadata.user_id = 42. FTS and vector indexes follow the same path convention.

import pyarrow as pa
from lancedb.index import BTree

metadata_type = pa.struct(
    [
        pa.field("user_id", pa.int32()),
        pa.field("user.id", pa.int32()),
    ]
)
data = pa.Table.from_arrays(
    [
        pa.array([1, 2, 3], type=pa.int32()),
        pa.array(
            [
                {"user_id": 10, "user.id": 100},
                {"user_id": 20, "user.id": 200},
                {"user_id": 30, "user.id": 300},
            ],
            type=metadata_type,
        ),
    ],
    names=["user_id", "metadata"],
)
table = await db.create_table("nested_scalar_index", data)

# Index a nested struct field.
await table.create_index(
    "metadata.user_id", config=BTree(), name="nested_user_id_idx"
)

# Escape literal dots inside a segment with backticks.
await table.create_index(
    "metadata.`user.id`", config=BTree(), name="escaped_user_id_idx"
)

# `columns` is returned as the canonical path you passed in.
for index in await table.list_indices():
    print(index.name, index.columns)
# nested_user_id_idx  ['metadata.user_id']
# escaped_user_id_idx ['metadata.`user.id`']

Composite indexes that cover multiple columns aren’t supported yet. Each create_index call must target a single (possibly nested) field path.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Scalar Indexing

Choosing the Right Index Type

Scalar Index Operations

1. Build the Index

2. Check Index Status

3. Update the Index

4. Run Indexed Searches

FM-Index for substring search

Create an FM-Index

NGram index for substring search

Create an NGram index

Index UUID Columns

1. Define UUID Type

2. Generate UUID Data

3. Create Table with UUID Column

4. Create and Wait for the Index

5. Perform Operations with the UUID Index

Index nested fields

​Choosing the Right Index Type

​Scalar Index Operations

​1. Build the Index

​2. Check Index Status

​3. Update the Index

​4. Run Indexed Searches

​FM-Index for substring search

​Create an FM-Index

​NGram index for substring search

​Create an NGram index

​Index UUID Columns

​1. Define UUID Type

​2. Generate UUID Data

​3. Create Table with UUID Column

​4. Create and Wait for the Index

​5. Perform Operations with the UUID Index

​Index nested fields

Choosing the Right Index Type

Scalar Index Operations

1. Build the Index

2. Check Index Status

3. Update the Index

4. Run Indexed Searches

FM-Index for substring search

Create an FM-Index

NGram index for substring search

Create an NGram index

Index UUID Columns

1. Define UUID Type

2. Generate UUID Data

3. Create Table with UUID Column

4. Create and Wait for the Index

5. Perform Operations with the UUID Index

Index nested fields