I’m writing a new vector search SQLite Extension

The initial v0.1.0 release will only have linear scans, but I want to support ANN indexes like IVF/HNSW in the future! Wanted to focus on fullscans first to make things easier.

In my experiments you can get pretty far with linear scans using sqlite-vec. Depends on the number of dimensions of course, but I'd expect it can handle searching 100's of thousands of vectors to maybe a million with sub-second searches, especially if you tweak some settings (page_size, mmap_size, etc.). And if you quantize your vectors (int8 reduces size 4x, binary 32x) you can get even faster, at the expense of quality. Or use Matryoshka embeddings[0] to shave down dimensions even more.

Building sql.js-httpvfs would be cool! Though I'm using the "official" SQLite WASM builds which came out after sql.js, so I don't think it'll be compatible out of the box. Would be very curious to see how much effort it would be to make a new HTTP VFS for SQLite's new WASM builds

[0] https://huggingface.co/blog/matryoshka

That's great to hear, thanks for sharing! Will definitely have benchmarks to share later

Would love to hear about the scale of the vector data you're working with in your local app. Do you actually find yourself with > 1 million vectors? Do you get away with just storing it all in-memory?

I don't have plans for compressed vector storage. There is support for int8/binary quantization, which can reduce the size of stored vectors drastically, but impacts performance quite a lot. I'd like to support something like product quantization[0] in the future, though!

No plans for using usearch or another vector search library, either. I want to keep dependencies low to make compilingeasy, and I want full control for how vectors are stored on-disk and in-memory.

[0] https://www.pinecone.io/learn/series/faiss/product-quantizat...

It's not possible to match pgvector's syntax with SQLite. SQLite doesn't have custom indexes, and since I want to have full control of how vectors are stored on disk, the only real way to do that in SQLite is with virtual tables[0]. And there's a few other smaller details that don't match (postgres operators like <->/<=>, SQLite's limited virtual table constraints, etc.), so matching pgvector wasn't a priority.

I instead tried to match SQLite's FTS5 full text search[1] extension where possible. It solves a similar problem: storing data in its own optimized format with a virtual table, so offering a similar API seemed appropriate.

Plus, there's a few quirks with the pgvector API that I don't quite like (like vector column definitions), so starting from scratch is nice!

[0] https://www.sqlite.org/vtab.html

[1] https://www.sqlite.org/fts5.html

He says in the blog post and here that this isn't finished yet

Hey really cool to see re sqlite-vss+langchain! You could try a langchain integration now, there's an (undocumented) sqlite-vec pypi package you can install that's similar to the sqlite-vss one. Though I'd only try it for dev stuff now (or stick to alpha releases), but things will be much more stable when v0.1.0 comes out. Though I doubt the main SQL API (the vec0 table) syntax will change much between now and then.

Benchmarking for this project is a bit weird, since 1) only linear scans are supported, and 2) it's an "embeddable" vector search tool, so it doesn't make a lot of sense to benchmark against "server" vector databases like qdrant or pinecone.

That being said, ~generally~ I'd say it's faster than using numpy and tools like txtai/chromadb. Faiss and hnswlib (bruteforce) are faster because they store everything in memory and use multiple threads. But for smaller vector indexes, I don't think you'd notice much of a difference. sqlite-vec has some support for SIMD operations, which speeds things up quite a bit, but Faiss still takes the cake.

Went back, and forth a lot, but the short version:

1. Having directly access to SQLite's C APIs is really useful, especially for BLOB I/O and some rarely-used APIs that sqlite-vec uses and aren't available in some SQLite/Rust bindings

2. Writing in Rust for this project would have meant adding a few dependencies which would make some builds a bit more complicated

3. Pure C means easier to compile for some targets I wanted to support, like WASM/mobile devices/raspberry pis

4. A single sqlite-vec.c/h file means you can drag+drop a single file into your C projects and "it just works"

5. I wanted full control over what stays in memory and what doesn't, and it's a bit easier to do so in C (at least in my brain)

6. Most vector search operations aren't too complicated, so I feel comfortable manually handling memory access. You work with fixed length vectors, pretty easy to do manual checks for. If it required a lot of string parsing/validation or other dicey work, then Rust would have been a godsend, but vector search specifically fits C pretty well.

7. Ton of C/C++ vector search examples I could reference from Faiss/hnswlib/annoy

It will! I've neglected that project a bit, but in some of the alpha builds I was able to get WASM to work, I just have documented or blogged about it yet. SQLite extensions in WASM written with sqlite-loadable-rs are a bit large, mostly because of Rust. But it's possible!

re distance functions: current L2+cosine for float/int8 vectors, and hamming for bit vectors. There are explicit vec_distance_l2()/vec_distance_cosine()/vec_distance_hamming() SQL functions, and the vec0 table will implicitly call the configured distance function on KNN queries.

re comparison to sqlite-vss: In general, since sqlite-vss uses Faiss, it's much faster at KNN queries, and probably faster at fullscans. Faiss stores everything in memory and uses multithreading for >10k vectors, so hard to beat that. sqlite-vec on the other hand doesnt use as much memory (vectors are read chunk-by-chunk), but it still relatively fast. There are SQLite settings like page_size/mmap_size that can make things go faster as well.

For writes (ie INSERT/UPDATE/DELETE), sqlite-vec is much much faster. sqlite-vss requires a full index re-write for all writes, even single vector stuff. sqlite-vec on the other hand only writes to the effected vectors, so it's MUCH more performant than sqlite-vss in that workflow specifically. I view it as sqlite-vss is more OLAP focused (many fast read and slow writes), and sqlite-vec is more OLTP focused (fast-enough reads and fast writes).

I agree, brute-force hamming distance is surprisingly super fast, especially for resource-constraint environments! Really looking forward to more embedding models to properly support binary vectors.

And yea, I'm still mulling over how ANN queries will work. My initial thought would be to add it to the vector column definition, like:

  CREATE VIRTUAL TABLE vec_items USING vec0(
    title_embeddings float[768] indexed by HNSW(m=32)
  )

Or something like that. But that means you'd need to recreate the table from scratch if you wanted to change the index. SQLite doesn't have custom indexes, so it's tricky for virtual tables. Then there's the question of how you train it to begin with, so lots to explore there.

  )

Ya I'll put a license soon, will be MIT

Ya you'll be able to do things like:

  SELECT 
    rowid,
    distance
  FROM vec_items
  WHERE rowid IN (SELECT rowid FROM ...)
    AND title_embeddings MATCH ?
  ORDER BY distance
  LIMIT 10

^ This would do a KNN style query, k=10, but only include results where the item's rowid is included in the (...) subquery. This is a half-measure for metadata filtering, by pre-filtering which items should be considered in the KNN search.

It's currently a bit slow, and not the best solution. Eventually there will be support for metadata column in vec0 tables, which with trickery should be much faster, but isn't planned for v0.1.0.

Appreciate the link; but would still like to know how well it works.

If you have a link for that, I’d be much obliged

Thank you! As you keep posting your progress, and I hope you do, adding these references would probably help warding off crusty fuddy-duddys like me (or at least give them more to research either way) ;)

OK, when you feel it's time, feel free to ping me on my repo, and I'll look into it.

A custom Wasm blob definitely works: it's an explicit design goal of my bindings that one can bring their own Wasm (e.g. because one wants to configure the build differently, or bought the SEE extension, or something). And if your extension turns out much like FTS5 that would work.

Still, "one big Wasm blob" is less flexible than I'd like, because all connections in an app (currently? maybe this could be lifted) need to use the same Wasm blob. Then (and if you want to import different extensions...) it becomes a fight over who got to initialize the global variable last.

So, I've been on-and-off looking into what it would take to "dynamically link" a C extension as a second Wasm, but... I'm not even sure it's possible.

You’ve done a great job communicating the project, in every aspect. Nice work. I’m excited to try this out!

Hey, you’re welcome! Looking forward to trying this at some point. Sooner if rust bindings are available, later if I have to contribute them.

My embedding is already binary, presumably I can bind a blob instead of the call to vec_quantize_binary?

OP is correct, the official WASM SQLite build is a sync only build and doesn't support async VFSs (which a IndexedDB VFS needs to be as it's an async api, unless you load the whole file into memory).

The best option for IndexedDB backed WASM SQLite is wa-sqlite, it offered both a sync and async build and a bunch of different VFSs, including an IndexedDB one. Note however that the async builds add significant overhead and reduce performance.

The most performant VFS is the "OPFS access handle pool" VFS that Roy developed for wa-SQLite and the official build also adopted as an option. That would be my recommendation for now.

Awesome! Thanks