Lance datasets

Lance (opens in a new tab) is a columnar format for data that you search by meaning as well as filter: it stores embedding vectors next to ordinary columns and can index them. Thunderduck reads and writes Lance datasets in your own object-storage catalogs, and runs vector, full-text and hybrid search from SQL, with the same catalog paths and credentials as the other file formats.

A Lance dataset is a directory, usually named something.lance.

Lance datasets are available on Thunderduck's managed cloud and on bring-your-own-cloud installs. An install can turn them off; there the query is refused with "Lance datasets aren't available on this installation yet".

Reading a dataset

Use the dataset's path, starting with the catalog name, as a table string. The path must end in .lance (or .lance/):

SELECT id, title
FROM 'raw_files/embeddings/docs.lance'
WHERE published > DATE '2026-01-01';

Search

Three functions search a dataset. The dataset path must be the first positional argument and a string literal (not a column or an expression, and not a named argument); the second argument is the column to search.

-- nearest neighbours of a query vector
SELECT * FROM lance_vector_search(
  'raw_files/embeddings/docs.lance', 'vec',
  [0.12, 0.8, 0.33]::FLOAT[3], k = 10
);
 
-- full-text search
SELECT * FROM lance_fts(
  'raw_files/embeddings/docs.lance', 'body', 'open table formats', k = 10
);
 
-- vector and full-text combined
SELECT * FROM lance_hybrid_search(
  'raw_files/embeddings/docs.lance', 'vec',
  [0.12, 0.8, 0.33]::FLOAT[3], 'body', 'open table formats', k = 10
);

Search uses an index when the dataset has one. Thunderduck doesn't create indexes.

Writing a dataset

Export a query's result with COPY … TO, as for other formats (see Exporting files):

COPY (SELECT id, title, vec FROM staging.docs)
TO 'raw_files/embeddings/docs.lance' (FORMAT lance);

MODE is optional and takes 'overwrite' or 'append':

COPY (SELECT * FROM staging.new_docs)
TO 'raw_files/embeddings/docs.lance' (FORMAT lance, MODE 'append');

With no MODE the export creates the dataset, and fails ("already exists") if the dataset is already there. Add MODE 'overwrite' to replace it or MODE 'append' to add rows. No other Lance option is accepted. The result is a summary of the dataset path and the rows written.

With FORMAT lance, a target that doesn't end in .lance gets .lance added ('raw_files/embeddings/docs' writes docs.lance), and a trailing / is dropped. As for reads, the path can't contain whitespace.

Which catalogs

Lance datasets work in these catalog types:

• Amazon S3, with stored access keys or an IAM role
• Cloudflare R2
• Google Cloud Storage
• Azure Blob

Lance paths into any other catalog (query results, Glue, Nessie, Basin, ClickHouse) are refused, as are ATTACH … (TYPE lance) and Lance namespaces.

Two catalogs on one bucket

If two catalogs point at the same bucket or container, on any provider, Lance queries on them are refused, including when one of them has no credentials. Remove one of the catalogs, or point it at a different bucket or container.

IAM-role S3 catalogs

For an S3 catalog connected with an IAM role, Lance uses a session that lasts the shorter of your company's run timeout and one hour. A query that runs longer than about an hour may fail. Buckets with SSE-KMS encryption work if the role is allowed to use the key.

What is refused

Beyond the limits above, a Lance query is refused when:

• the path has .., ?, # or any whitespace
• the dataset name is built from columns or expressions rather than written as a literal
• it calls a Lance blob or maintenance function
• a write statement (INSERT, UPDATE, DELETE, …) contains a string that looks like a Lance path

Cost and sizing

Lance scans report row estimates, so a query is sized the way any other query is: from the plan's estimate.