Lance Extension¶

The Lance extension provides support for managing Lance tables and querying them in SQL, via DuckDB.

Lance is a DuckDB core extension with no independent lance-duckdb version number. Releases follow DuckDB, and binaries target the corresponding DuckDB build and platform. See the version and release policy.

Installation¶

To install and load the extension, run these commands in your DuckDB CLI:

INSTALL lance;
LOAD lance;

To refresh installed extensions from their repositories for your current DuckDB build, run:

UPDATE EXTENSIONS;

This does not upgrade DuckDB or select an independent lance-duckdb release. Availability depends on DuckDB publishing a compatible Lance artifact.

Examples¶

All examples below reuse a small dataset with three rows (duck, horse, dragon) and a vector column with representative values. In the real world, you'd have a high-dimensional array generated by an embedding model, and a much larger Lance dataset.

Write to a Lance dataset¶

Use DuckDB's COPY ... TO ... to materialize query results as a Lance dataset.

COPY (
  SELECT *
  FROM (
    VALUES
      ('duck', 'quack', [0.9, 0.7, 0.1]::FLOAT[3]),
      ('horse', 'neigh', [0.3, 0.1, 0.5]::FLOAT[3]),
      ('dragon', 'roar', [0.5, 0.2, 0.7]::FLOAT[3])
  ) AS t(animal, noise, vector)
) TO './lance_duck.lance' (FORMAT lance, mode 'overwrite');

Query a Lance dataset in DuckDB¶

Query the data using SQL in DuckDB.

SELECT *
  FROM './lance_duck.lance'
  LIMIT 5;

This returns:

┌─────────┬─────────┬─────────────────┐
│ animal  │  noise  │     vector      │
│ varchar │ varchar │     float[3]    │
├─────────┼─────────┼─────────────────┤
│ duck    │ quack   │ [0.9, 0.7, 0.1] │
│ horse   │ neigh   │ [0.3, 0.1, 0.5] │
│ dragon  │ roar    │ [0.5, 0.2, 0.7] │
└─────────┴─────────┴─────────────────┘

Note: The Lance dataset doesn't have to be on local disk. You can query S3 paths directly as follows. To access object store URIs (such as s3://...), configure a TYPE LANCE secret.

CREATE SECRET (
  TYPE LANCE,
  PROVIDER credential_chain,
  SCOPE 's3://bucket/'
);

SELECT *
  FROM 's3://bucket/path/to/lance_duck.lance'
  LIMIT 5;

Vector search¶

The examples use fixed-size FLOAT[3] vectors. Search scores below are rounded.

The extension lets you run fast vector search on the Lance table. This returns the _distance (smaller is closer, so sort in ascending order for nearest neighbors). The example vector here is similar to the query "duck".

SELECT animal, noise, vector, _distance
  FROM lance_vector_search(
    './lance_duck.lance',
    'vector',
    [0.8, 0.7, 0.2]::FLOAT[3],
    k = 1,
    prefilter = true
  )
  ORDER BY _distance ASC;

animal  noise  vector           _distance
duck    quack  [0.9, 0.7, 0.1]  0.02

From a performance standpoint, the extension pushes down projections, filters and more to the Lance level where possible. This ensures that you get all the performance benefits of using Lance as the vector store.

Full-text search¶

Run keyword-based BM25 search as shown below. This returns a _score, which is sorted in descending order to get the most relevant results.

SELECT animal, noise, vector, _score
  FROM lance_fts(
    './lance_duck.lance',
    'animal',
    'the brave knight faced the dragon',
    k = 1,
    prefilter = true
  )
  ORDER BY _score DESC;

animal  noise  vector           _score
dragon  roar   [0.5, 0.2, 0.7]  0.9808292

Just like in the case with vector search, the search operations are happening at the Lance level, although the instructions came in from DuckDB via SQL.

Hybrid search¶

Hybrid search combines vector and FTS scores, returning a _hybrid_score in addition to _distance / _score. To get the most relevant results, sort in descending order.

SELECT animal, noise, vector, _hybrid_score, _distance, _score
  FROM lance_hybrid_search(
    './lance_duck.lance',
    'vector',
    [0.8, 0.7, 0.2]::FLOAT[3],
    'animal',
    'the duck surprised the dragon',
    k = 2,
    prefilter = false,
    alpha = 0.5,
    oversample_factor = 4
  )
  ORDER BY _hybrid_score DESC;

animal  noise  vector           _hybrid_score  _distance  _score
duck    quack  [0.9, 0.7, 0.1]  0.75           0.02       0.9808292
dragon  roar   [0.5, 0.2, 0.7]  0.33088228     0.59       0.9808292

Organize tables via Lance namespaces¶

A namespace is an open specification that describes access to and operations on a collection of Lance tables. When you ATTACH a local directory (containing a Lance dataset) as a Lance namespace, you can create new datasets using CREATE TABLE or CREATE TABLE AS SELECT. The dataset is written to <namespace_root>/<table_name>.lance.

ATTACH './lance_duck' AS lance_ns (TYPE LANCE);

CREATE TABLE lance_ns.main.duck_animals AS
  SELECT *
  FROM (
    VALUES
      ('duck', 'quack', [0.9, 0.7, 0.1]::FLOAT[3]),
      ('horse', 'neigh', [0.3, 0.1, 0.5]::FLOAT[3]),
      ('dragon', 'roar', [0.5, 0.2, 0.7]::FLOAT[3])
  ) AS t(animal, noise, vector);

You can then query the namespace as follows:

SELECT count(*) FROM lance_ns.main.duck_animals;

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      3       │
└──────────────┘

As your table management needs grow, namespaces help keep things organized, by naming tables for easier access.