DOCSEARCHbyAlgolia
Submit Docs

sqlite-vss

repository·main·Indexed 24 days ago

https://github.com/asg017/sqlite-vss

A SQLite extension that provides vector similarity search capabilities using the Faiss library, enabling semantic search and recommendation tasks directly within a SQLite database. It includes bindings for Node.js, Python, Elixir, Go, and plugins for Datasette and sqlite-utils.

Tokens
15.8K
Snippets
67
Records
102
Agent score
84%

What's inside sqlite-vss

  1. What is sqlite-vss?

    main

    sqlite-vss (SQLite Vector Similarity Search) is a SQLite extension based on Faiss that enables vector search capabilities within SQLite. It is designed for building semantic search engines, recommendation systems, or question-answering tools. It follows a "Bring-your-own-vectors" model, meaning it is compatible with any embedding data (e.g., from OpenAI, HuggingFace, or sentence-transformers).

    WARNING

    sqlite-vss is not in active development. The author recommends using sqlite-vec for a similar but easier-to-use experience.

  3. Understand the core features and trade-offs of sqlite-vss

    main

    Core Features

    • Embeddable: Runs in the same process as your application without a separate server or configuration.
    • Pure SQL: Uses standard SQL for all vector operations (inserting and querying); no specialized DSL or API is required.
    • Configurable: Supports customization of vector indexing via Faiss factory strings.
    • Easy Distribution: Available via multiple package managers for languages including Python, Node.js, Ruby, Deno, Go, and Rust.

    Trade-offs and Disadvantages

    • Scalability Limits: Not designed for millions of users or extremely write-heavy applications where SQLite's limits are reached.
    • Faiss Dependency: Relies on Faiss, which can make self-compilation difficult and may present platform-specific challenges.
    • Development Maturity: The project is relatively young and has a small development team, which may result in implementation-specific limitations.

  5. Choosing a vector format for Python

    main

    When deciding how to represent vectors in Python for sqlite-vss:

    • Storage in vss0 tables: Inserting as JSON or "raw bytes" has the same effect on the storage size of the vss0 virtual table, as it is converted to the compact Faiss format regardless.
    • Storage in regular SQLite columns: Use the "raw bytes" format (via struct.pack or numpy.tobytes) to achieve much more compact storage compared to JSON strings.
    • Performance: The "raw bytes" method is generally faster than json.dumps(), particularly when working with numpy arrays.

  6. Compare sqlite-vss with dedicated vector databases

    main

    When deciding between sqlite-vss and dedicated vector databases (such as Pinecone, Milvus, or Qdrant), consider the following:

    • Dedicated Vector Databases: Generally scale better, handle higher write throughput, support more concurrent users, and offer advanced metadata-filtering on queries.
    • sqlite-vss: Better for use cases where you want to avoid maintaining an additional server, prefer an open-source solution, and where the performance of SQLite is "fast enough" for your requirements.

  7. Create and query virtual tables with vss0

    main

    The vss0 module is used to create virtual tables for storing and querying vectors. The API is similar to the fts5 Full-Text Search extension.

    Creating a table

    Define a virtual table using vss0 and specify the number of dimensions for each embedding column.

    Inserting data

    You can insert vectors into vss0 tables as JSON or raw bytes.

    Querying (k-nearest neighbors)

    Use the vss_search function in the WHERE clause to find the nearest neighbors to a target embedding.

    Limitations

    • UPDATE operations are not currently supported.
    • Small INSERT/DELETE operations can be slow; it is recommended to use transactions and batch operations.
    -- Create a virtual table with 384-dimensional embeddings
create virtual table vss_articles using vss0(
  headline_embedding(384),
  description_embedding(384),
);

-- Insert vectors from an existing table
insert into vss_articles(rowid, headline_embedding)
  select rowid, headline_embedding from articles;

-- Query for the 100 nearest neighbors to the embedding in row #123
select rowid, distance
from vss_articles
where vss_search(
  headline_embedding,
  (select headline_embedding from articles where rowid = 123)
)
limit 100;

-- Batch operations using transactions
begin;

delete from vss_articles
  where rowid between 100 and 200;

insert into vss_articles(rowid, headline_embedding, description_embedding)
  values (:rowid, :headline_embedding, :description_embedding);

commit;

  8. Optimize vector search with Faiss factory strings and IVF

    main

    By default, sqlite-vss uses the factory string "Flat,IDMap2", which can become slow as the database grows. You can provide custom Faiss factory strings to control how the index is stored and queried.

    Using an Inverted File Index (IVF) (e.g., "IVF4096,Flat,IDMap2") can significantly speed up large database queries by using centroids, but it requires a training step.

    Training an IVF index

    To train an index, perform an INSERT command within a single transaction using the special operation="training" constraint.

    -- Create a table with a custom IVF factory string
create virtual table vss_ivf_articles using vss0(
  headline_embedding(384) factory="IVF4096,Flat,IDMap2",
  description_embedding(384) factory="IVF4096,Flat,IDMap2"
);

-- Train the index using existing data
insert into vss_ivf_articles(operation, headline_embedding, description_embedding)
  select
    'training',
    headline_embedding,
    description_embedding
  from articles;

  10. Build debug or release versions of `sqlite-vss`

    main

    You can build sqlite-vss in two modes: Debug (faster to compile, slower at runtime) or Release (slower to compile, faster at runtime). You can choose between loadable extensions or static archives.

    Debug Builds

    Debug files are placed in dist/debug.

    • Loadable extension: make loadable (produces .dylib, .so, or .dll files).
    • Static archives: make static (produces .a and .h files for static linking).

    Release Builds

    Release files are placed in dist/release.

    • Loadable extension: make loadable-release.
    • Static archives: make static-release.

    Note: The Rust and Go bindings use the static archive approach. When linking manually, you may need flags similar to: -I./dist/debug -lsqlite_vector0 -lsqlite_vss0 -llfaiss_avx2

  11. Compile `sqlite-vss` on MacOS or Linux

    main

    MacOS (x86_64)

    You may need llvm. Install via Homebrew:

    brew install llvm

    If you encounter compilation errors, explicitly set the compilers and flags:

    export CC=/usr/local/opt/llvm/bin/clang
export CXX=/usr/local/opt/llvm/bin/clang++
export LDFLAGS="-L/usr/local/opt/llvm/lib"
export CPPFLAGS="-I/usr/local/opt/llvm/include"

    Linux (x86_64)

    Install these dependencies before compiling:

    sudo apt-get update
sudo apt-get install libgomp1 libatlas-base-dev liblapack-dev libsqlite3-dev
    brew install llvm
# or
sudo apt-get install libgomp1 libatlas-base-dev liblapack-dev libsqlite3-dev

  12. Build and run the headlines example with Docker

    main

    The example provides Docker commands to build an image and run it with specific volume mounts for the database and data files.

    Build the image:

    docker build -t x .

    Run the container (standard):

    docker run -p 8001:8001 -v $PWD/test.db:/mnt/test.db --rm -it  x

    Run the build script inside the container: This command mounts the build script, the database, and a JSON dataset to run a custom build process.

    docker run -p 8001:8001 --rm -it \
  -v $PWD/build.py:/build.py \
  -v $PWD/test.db:/test.db \
  -v $PWD/News_Category_Dataset_v3.json:/News_Category_Dataset_v3.json \
  x python3 /build.py /test.db /News_Category_Dataset_v3.json
    docker build -t x .

docker run -p 8001:8001 -v $PWD/test.db:/mnt/test.db --rm -it  x

docker run -p 8001:8001 --rm -it \
  -v $PWD/build.py:/build.py \
  -v $PWD/test.db:/test.db \
  -v $PWD/News_Category_Dataset_v3.json:/News_Category_Dataset_v3.json \
  x python3 /build.py /test.db /News_Category_Dataset_v3.json