# Lance and LanceDB > Lance is an open-source columnar table format that stores embeddings, images and other large binary columns alongside ordinary ones in object storage. It has vector and full-text indexes built into the table, so similarity search is just an ORDER BY distance in your SQL query. [Lance](https://lance.org/) and [LanceDB](https://docs.lancedb.com/) are a perfect extension to the MotherDuck stack if you're thinking about: - **Multimodal tables.** Images, audio, video, and documents sit in the same table as the embeddings derived from them and the columns you filter on. Lance is built for random access into those large values, so fetching one image doesn't mean reading its neighbors. - **Vector indexes past ten million rows.** Beyond a few million vectors, an exact scan stops being viable and you need a real approximate nearest neighbor index. Lance keeps one next to the data and updates it as the table grows. Each side then does what it's built for. Lance holds the vectors, the blobs, and the index. MotherDuck holds the governed tables you join those vectors against, the aggregations over ML scores, and the reporting analysts run on top. The `lance` DuckDB extension makes the pair queryable from one session: attach a Lance dataset and MotherDuck side by side, and a single `SELECT` reaches both. ## What runs where `lance` is a DuckDB core extension that runs in the client process, so the split has three practical consequences: - Every Lance read happens wherever your DuckDB client runs: your machine, a customer facing application, or a [Flight](/key-tasks/flights/). A query issued from the MotherDuck Web UI or a Dive can't reach a Lance dataset. - A query that joins Lance data to a MotherDuck table pulls the MotherDuck side down to the client. Filter and aggregate on the MotherDuck side first when the table is large. - Lance keeps the nearest neighbor index. MotherDuck has no approximate nearest neighbor index over array columns, so a similarity search over a loaded table computes distances row by row. :::note The Lance extension is not yet available on MotherDuck cloud, but you can still run it in a Flight. ::: ## Attach Lance alongside MotherDuck Install the extension, attach the LanceDB directory as a database, and attach MotherDuck next to it. Both live in the same session, so one `CREATE TABLE` moves the data: ```sql INSTALL lance; LOAD lance; -- Attach the LanceDB directory, not an individual .lance dataset ATTACH '' AS lance_db (TYPE lance); ATTACH 'md:'; CREATE OR REPLACE TABLE my_db.characters AS SELECT * FROM lance_db.characters; ``` There's no Parquet step in between. Fixed-size vector columns land as native array types: a `fixed_size_list[512]` in Lance becomes `FLOAT[512]` in MotherDuck, and struct columns keep their fields. Because both databases are attached to the same session, you can also join across them without loading anything: ```sql SELECT s.title, s.category, h.score FROM lance_db.stories AS s JOIN my_db.story_metrics AS h USING (id); ``` :::note To read one dataset by path instead of attaching a directory, the extension's scan function is `__lance_scan`, with two leading underscores. Prefer `ATTACH ... (TYPE lance)` and query the attached tables by name. ::: ### Read Lance from cloud storage Lance resolves cloud credentials through its own credential chain, not through DuckDB's. A `TYPE s3` secret is only visible to the DuckDB session and Lance ignores it. Instead create a `TYPE lance` secret. The parameter names differ from the `s3` secret type: ```sql CREATE OR REPLACE SECRET lance_s3 ( TYPE lance, ACCESS_KEY_ID '', SECRET_ACCESS_KEY '', REGION '' ); ``` | Secret type | Key parameter | Secret parameter | | :--- | :--- | :--- | | `s3` | `KEY_ID` | `SECRET` | | `lance` | `ACCESS_KEY_ID` | `SECRET_ACCESS_KEY` | The `lance` type also supports `PROVIDER credential_chain` to pick up an instance profile or a local AWS profile. See [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) for how MotherDuck stores secrets. ## Sync Lance into MotherDuck on a schedule A [Flight](/key-tasks/flights/) runs Python on MotherDuck compute, and its DuckDB client can install `lance` the same way a local session does. That makes a Flight the place to keep a MotherDuck table in step with a Lance dataset in object storage. Pin `duckdb` in the Flight's requirements to a version MotherDuck supports, so a new DuckDB release doesn't break the run: ```text duckdb== ``` Read that version from the API: ```bash > curl -s https://api.motherduck.com/latest_supported_duckdb_version.txt ``` The Flight builds the `lance` secret from its own environment, attaches the dataset, and replaces the destination table: ```python import os import duckdb def main(): con = duckdb.connect("md:") con.execute("INSTALL lance; LOAD lance;") con.execute( """ CREATE OR REPLACE SECRET lance_s3 ( TYPE lance, ACCESS_KEY_ID $key_id, SECRET_ACCESS_KEY $secret, REGION $region ) """, { "key_id": os.environ["AWS_ACCESS_KEY_ID"], "secret": os.environ["AWS_SECRET_ACCESS_KEY"], "region": os.environ["AWS_REGION"], }, ) con.execute(f"ATTACH '{os.environ['LANCE_URI']}' AS lance_db (TYPE lance)") con.execute('CREATE DATABASE IF NOT EXISTS "my_db"') con.execute( """ CREATE OR REPLACE TABLE my_db.main.stories AS SELECT * FROM lance_db.stories """ ) if __name__ == "__main__": main() ``` Set `LANCE_URI` and `AWS_REGION` as Flight config, and store the two AWS keys as Flight secrets. See [Authentication, config, and secrets](/key-tasks/flights/flights-authentication-config-and-secrets) for how those reach the run, and [Packages and recommended libraries](/key-tasks/flights/packages-and-runtime) for dependency pinning. A Flight can't reach files on your machine, so point it at object storage. :::tip When the Flight already holds a LanceDB client object, hand DuckDB the Arrow table directly with `con.register("src", table.to_arrow())` and skip the attach. The handoff is zero-copy, and array and struct columns survive it unchanged. ::: This example replaces the destination table on every run, which fits a dataset small enough to reload. For a large append-only dataset, read the fragments added since the last run with the `lance` Python package and keep the last synced dataset version as a watermark table in MotherDuck. ## Keep nearest neighbor search in Lance The extension pushes search down into Lance, so the index does the work and DuckDB reads only the rows that come back: | Function | Search | | :--- | :--- | | `lance_vector_search` | Nearest neighbor over a vector column | | `lance_fts` | Full-text search over a text column | | `lance_hybrid_search` | Both arms, fused | ```sql SELECT name, _distance FROM lance_vector_search( '.lance', 'vector', [0.2, 0.9, 0.4, 0.9]::FLOAT[], k => 2 ); ``` ```text ┌─────────────┬───────────┐ │ name │ _distance │ │ varchar │ float │ ├─────────────┼───────────┤ │ Merlin │ 0.0 │ │ King Arthur │ 1.1799998 │ └─────────────┴───────────┘ ``` `_distance` is how far each row's vector sits from the vector you searched with, so a smaller number is a closer match. `k` caps how many rows come back. Search results are ordinary rows, so join them to a MotherDuck table to add the columns Lance doesn't hold. A table function can't take a subquery as an argument, so put the query vector in a variable first: ```sql SET VARIABLE query_vector = ( SELECT vector FROM lance_db.stories WHERE id = 33501177 ); SELECT s.title, s._distance, m.score FROM lance_vector_search( '.lance', 'vector', getvariable('query_vector'), k => 20 ) AS s JOIN my_db.story_metrics AS m ON m.id = s.id ORDER BY s._distance; ``` For search that runs entirely inside MotherDuck, with no Lance dataset involved, see [Text search in MotherDuck](/key-tasks/ai-and-motherduck/text-search-in-motherduck). ## Generating the vectors MotherDuck's [`embedding` function](/sql-reference/motherduck-sql-reference/ai-functions/embedding) covers text. It returns `FLOAT[512]` with `text-embedding-3-small` and `FLOAT[1024]` with `text-embedding-3-large`, either of which you can write into a Lance dataset. For anything else, including image embeddings, run the model where you control it and write the vectors to Lance from there. A Flight is a reasonable host for that: it takes arbitrary Python packages, so a local model or a call out to a provider both work on a schedule. :::warning `text-embedding-3-small` and a 512-dimensional image model such as CLIP produce vectors of the same width in different vector spaces. Distances between them are meaningless. Keep one model per vector column, and record which model wrote it. ::: ## Limitations - MotherDuck doesn't read Lance server-side, so the Web UI, Dives, and any client without the `lance` extension can't query a Lance dataset. Load the data into MotherDuck first. - MotherDuck has no approximate nearest neighbor index. Once a vector column is loaded, a similarity search over it computes distances row by row, which suits a small table and gets slow on a large one. - The `embedding` function takes text only. --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Ffile-formats%2Flance%2F&page_title=Lance%20and%20LanceDB&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.