REFRESH INDEX¶

Index the fragments an existing index does not yet cover, without rebuilding the ones it does.

Overview¶

Appending to a table creates new fragments, and an existing index does not cover them until it is maintained. REFRESH INDEX builds index data only for those uncovered fragments and adds it to the index, so cost scales with the newly appended data rather than with the table.

This is the incremental counterpart to re-running CREATE INDEX, which rebuilds every fragment. Both run distributed across Spark executors.

OPTIMIZE INDEX fills the same missing coverage but runs on the Spark driver rather than distributing the build, and can also merge existing segments. Use REFRESH INDEX to parallelize the build across executors; use OPTIMIZE INDEX for driver-side maintenance or to merge segments after either command.

Syntax¶

ALTER TABLE lance.db.users REFRESH INDEX user_id_idx;

Options¶

Option names are case-insensitive: WITH (NUM_SEGMENTS = 8) and WITH (num_segments = 8) are the same option.

Index method options — such as rows_per_zone for zonemap or the tokenizer settings for fts — are accepted as well, and are applied to the segments this command builds. See Index Method Options for when you need to pass them.

Examples¶

Refresh after appending data¶

INSERT INTO lance.db.users VALUES (100, 'new row');

ALTER TABLE lance.db.users REFRESH INDEX user_id_idx;

Refresh after compacting¶

OPTIMIZE replaces compacted fragments with new ones. A zonemap or bloomfilter index records physical row addresses, which a rewrite invalidates, so its coverage drops and a refresh restores it:

OPTIMIZE lance.db.users;

ALTER TABLE lance.db.users REFRESH INDEX zone_idx;

Whether an index needs this depends on what it stores; see Compaction below for which methods keep coverage after a rewrite.

Control build parallelism¶

ALTER TABLE lance.db.users REFRESH INDEX user_id_idx WITH (num_segments = 8);

Populate a deferred index¶

An index created with train = false covers no fragments, so refreshing it builds it over the whole table through the normal distributed path:

ALTER TABLE lance.db.users CREATE INDEX idx_id USING zonemap (id) WITH (train = false);

ALTER TABLE lance.db.users REFRESH INDEX idx_id;

The deferred index holds no built data to inherit method options from, so pass them to the refresh that populates it — see Index Method Options.

Check coverage before and after¶

SHOW INDEXES reports how much of the table each index covers:

SHOW INDEXES FROM lance.db.users;

A num_unindexed_fragments above zero means a refresh has work to do.

Output¶

A refresh with nothing to do returns zeros and commits no new table version.

fragments_indexed counts the fragments actually covered, not the fragments planned: if a concurrent operation retires one while the build runs, it is excluded and a warning names it.

How It Works¶

1. Planning: the driver resolves the named index, subtracts its fragment coverage from the table's fragments, and splits the remainder into batches, each a contiguous run of fragments sized to balance row counts. Contiguity keeps OPTIMIZE able to group the fragments a segment covers.
2. Distributed Build: each batch becomes a Spark task that builds one uncommitted index segment. Uncommitted segments are invisible to readers, so a failed build cannot affect query results.
3. Transactional Commit: the driver commits the new segments as part of the same logical index. Segments covering already-indexed fragments are retained, so existing coverage is preserved and the change is atomic.

Planning and building both run against a single pinned table version, so every task sees the fragment set the driver planned over.

Index Method Options¶

REFRESH INDEX builds its segments from the options in its own WITH clause; options you leave out fall back to the index type's defaults rather than to whatever the index was built with. Lance records a built index's parameters inside the index itself, not as table metadata a command can read back, so pass the same options to the refresh that you passed to CREATE INDEX:

ALTER TABLE lance.db.users CREATE INDEX idx_id USING zonemap (id) WITH (rows_per_zone = 2048);

ALTER TABLE lance.db.users REFRESH INDEX idx_id WITH (rows_per_zone = 2048);

For most methods each segment is queried on its own and records its own configuration, so a mismatch changes performance characteristics rather than results.

fts (or inverted) is the exception: a full-text index is read with one configuration for all of its segments, and a set that disagrees cannot be queried. REFRESH INDEX therefore compares what it built against the segments it would join and fails without committing when they differ, leaving the index exactly as it was.

Re-run with the original options, or rebuild with CREATE INDEX, which replaces every segment and so has nothing to agree with. An fts index built by an older Lance version can also record a configuration the current one does not reproduce even from the same options; a full rebuild is the fix there.

Notes and Limitations¶

• Index Methods: supported for the same methods as CREATE INDEX: zonemap, btree, bitmap, label_list, ngram, bloomfilter, rtree, and fts (or inverted). A vector index cannot be created from Spark SQL either, so refreshing one is rejected and pointed at the Lance SDK rather than at CREATE INDEX, which could not build it.
• Full-Build Options: train, build_mode, and rows_per_range are rejected. Range-mode btree redistributes and sorts the whole table, which an incremental refresh does not do — use CREATE INDEX to rebuild that way.
• Method Options Are Not Inherited: see Index Method Options.
• Indexes Created Before Coverage Tracking: a segment that predates fragment coverage tracking has no fragment bitmap, so its coverage cannot be determined. REFRESH INDEX rejects such an index rather than silently rebuilding the whole table; rebuild it with CREATE INDEX.
• Multi-Field Indexes: an index keyed on more than one field, or carrying columns beyond its key, is rejected — a refreshed segment would declare only the key. Rebuild it with CREATE INDEX.
• System Indexes: Lance-maintained indexes, including fragment-reuse and MemWAL indexes, cannot be refreshed.
• Compaction: OPTIMIZE replaces the fragments it compacts with new ones. Whether that costs the index its coverage depends on what the index stores. zonemap and bloomfilter record physical row addresses, which the rewrite invalidates, so they lose coverage and need a refresh afterwards. btree, bitmap, label_list, ngram, rtree and fts record row ids, which follow their rows, so compaction carries their coverage over and a refresh reports fragments_indexed = 0.
• Stale zonemap Coverage: a partially covered zonemap index falls back to scanning uncovered fragments in full, so query results stay complete but filtered queries do more I/O than a fully covered index. Refresh after appending or compacting to restore selective reads.
• Concurrent Retirement: if a concurrent operation retires a fragment while a refresh is building it, that fragment is left out of the commit and named in a warning, and fragments_indexed counts only what was covered. Re-run the refresh to pick up whatever replaced it. A refresh whose fragments were all retired commits nothing and fails instead.
• Concurrent REFRESH INDEX: do not run two refreshes of the same index concurrently. The second will either fail with a version conflict or succeed and add duplicate segment coverage over the same fragments. SHOW INDEXES makes duplicate coverage visible via num_segments; merge segments with OPTIMIZE INDEX or rebuild with CREATE INDEX.
• Concurrent DROP INDEX: do not drop an index while a refresh of it is in flight. The refresh re-resolves its target immediately before committing and fails if it is gone, but the check and the commit are separate transactions and Lance does not currently treat a concurrent same-name drop as a conflict. A DROP INDEX landing in that window is undone: the index reappears covering only the fragments the refresh built. Tracked upstream in lance#6806.
• Segment Growth: each refresh adds segments to the index, and Lance can only compact fragments that are covered by the identical set of index segments. So accumulated refreshes progressively narrow what OPTIMIZE can group: run OPTIMIZE before REFRESH INDEX rather than after, and merge segments with OPTIMIZE INDEX or rebuild with CREATE INDEX when the count grows. SHOW INDEXES reports num_segments.

See Also¶

• CREATE INDEX
• OPTIMIZE INDEX
• SHOW INDEXES
• DROP INDEX
• OPTIMIZE