I have a pretty specific vision of what v0.1.0 of this extension will look like, but it'll take a few more weeks to get there. This blog post was more for letting users of sqlite-vss (a previous vector search SQLite extension I wrote) know what will be coming next. There will be a much bigger release when that is ready.

But in general, I'm super excited to have an easy embeddable vector search alternative! Especially one that runs on all operating system, in WASM, mobile devices, Raspberry Pis, etc. I personally I'm trying to run a lil' semantic search app on my Beepy[0], which is a ton of fun to play with.

[0] https://beepy.sqfmi.com/

* Which distance functions does this support? Looks like it supports binary vectors already -- is Hamming distance supported?

* How does the performance compare with sqlite-vss? I'm curious about the profiling numbers -- both in terms of query speed, as well as memory usage.

Overall, this looks absolutely fantastic, and I love the direction you're heading with all of this.

> Though initially, sqlite-vec will only support exhaustive full-scan vector search. There will be no "approximate nearest neighbors" (ANN) options. But I hope to add IVF + HNSW in the future!

I think this is 1000% the correct approach -- kudos for not over-complicating things initially! I've shipped on-device vector search (128-bit binary vectors, Hamming distance) and even with a database size of 200k+ entries, it was still fast enough to do full brute-force distance search on every camera frame -- even running on crappy phones it was fast enough to get 10+ fps, and nicer phones were buttery-smooth. It's amazing how frequently brute-force is good enough.

That said, for implementing ANN algorithms like HNSW and whatnot, my first thought is that it would be slick if these could be accomplished with a table index paradigm -- so that switching from brute-force to ANN would be as simple as creating an index on your table. Experimenting with different ANN algorithms and parameters would be accomplished by adjusting the index creation parameters, and that would let developers smoothly evaluate and iterate between the various options. Maybe that's where your mind is going with it already, but I figured I would mention it just in case.

Overall, awesome writeup, and fantastic project!!

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)
  )

  )

Also do you build with sqlite-httpvfs? This would go together great https://github.com/phiresky/sql.js-httpvfs

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

Additionally, sqlite-vec takes advantage of some SQLite specific APIs, like BLOB I/O [0], which I hope would speed things up a ton. It's a ton more work, coming up with new storage solutions that are backed by SQLite shadow tables, but I think it'll be work it!

And I also like how sqlite-vec is just a single sqlite-vec.c file. It makes linking + cross-compiling super easy, and since I got burned relying on heavy C++ dependencies with sqlite-vss, a no-dependency rule feels good. Mostly inspired by SQLite single-file sqlite3.c amalgamation, and Josh Baker's single file C projects like tg[1].

[0] https://www.sqlite.org/c3ref/blob_open.html

[1] https://github.com/tidwall/tg

You are right. I don't yet support pluggable storage systems, but you can check the Lantern's fork of USearch. It may have the right capabilities, as they wanted the same level of integration for Postgres.

Our current SQLite extension brings "search APIs" to SQLite but not the index itself. Think of it as a bundle of SIMD-vectorized Cosine/Dot/L2/Hamming/Jaccard... (for vectors) and Levenshtein/Hamming/NW (for text) distances coming from SimSIMD and StringZilla. Unlike USearch, the latter are pure C 99 header-only libraries, in case you need smth similar.

Best of luck with your project!

Did you ever consider making it syntax compatible with pgvector, in order to have a common SQL vector DSL? I am sure the benefits are much smaller than the disadvantages, but I'm curious if it's possible.

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

Love your work by the way, I have been using sqlite-vss on a few projects already.

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?

Benchmark is also not everything, easiness to embed and integrate with existing sqlite apps could make sqlite-vec stand out and help with adoption.

At the risk of fulfilling a HN stereotype, may I ask why you ditched Rust for this extension?

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

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

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.

Do you intend to compress the vector storage in any way and do you intend to implement your own vector search algorithms or reuse some already optimized libraries like usearch?

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...

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.

I wouldn't consider it a "this or that" decision. While txtai does combine Faiss and SQLite, it could also utilize this extension. The same task was just done for Postgres + pgvector. txtai is not tied to any particular backend components.

That being said, txtai offers way more than sqlite-vec, like builtin embedding models and other nice LLM features, so it's definitely apples to oranges.

With this, what DuckDB just added and pgvector, we're seeing a blurring of the lines. Back in 2021, there wasn't a RDBMS that had native vector support. But native vector integration makes it possible for txtai to just run SQL-driven vector queries...exciting times.

I think systems that bet on existing databases eventually catching up (as is txtai's model) vs trying to reinvent the entire database stack will win out.

I guess I’m confused. This is honestly the problem that most vector storage faces (“curse of dimensionality”) let alone the indexing.

I assume that you meant 768 dimensions * 8 bytes (for a f64) which is 6144 bytes. Usually, these get shrunk with some (hopefully minor) loss, so like a f32 or f16 (or smaller!).

If you can post how you fit 768 dimensions in 96 bytes, even with compression or trie-equivalent amortization, or whatever… I’d love to hear more about that for another post.

Ninja edit: Unless you’re treating each dimension as one-bit? But then I still have questions around retrieval quality

I'm definitely interested in something like this, but need to think through how I could distribute this separate from SQLite in my Wasm based Go bindings. So far everything C is bundled, because it's a lot simpler than Wasm "dynamic linking".

Also, you've mentioned incremental BLOB I/O, and you probably know this already, but keep in mind that BLOB I/O is never random access, as large BLOBs are stored as a linked list of pages.

I'm a big fan of your wazero SQLite bindings! I actually plan on providing 1) CGO bindings to sqlite-vec and 2) a custom WASI build sqlite-vec that can be used in go-sqlite3 directly. My plan was to build a sqlite3.wasm file using the build scripts in your repo. If you did want to support it in your project directly, I think you could just drop the sqlite-vec.c/h file in go-sqlite3/sqlite3 and be good to go

Re incremental Blob I/O: I learned that the hard way! It's definitely the limiting factor on query speed for sqlite-vec. I've found that keeping the chunks relatively low in size (low MB's) and increasing the page_size strikes a good balance, but page_size in particular has consequences. PRAGMA mmap_size also helps out a ton, since it seems to keep pages in memory and makes overflow lookups faster, but that of course means a ton more memory usage. It's a difficult balance!

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.

I keep thinking what can I do in the Typescript/Next.js/React ecosystem that's very useful to a technical niche but I haven't had the inspiration yet.

I started with code first — the extension itself is already mostly written[0]. But it's one of those "20% effort for 80% of the work," where the last 20% I need to write (error handling, fuzzy tests, correctness testing) will take 80% of the time. But people already have questions about the current status of `sqlite-vss`, so I figured this "work-in-progress" blog post could answer some questions.

Though I do like the idea of starting with docs first! Especially with SQLite extensions, where it all really matters what the SQL API looks like (scalar functions, virtual tables, etc.). I definitely did a lot of sketching of what the SQL part of sqlite-vec should look like before writing most of the code.

[0] https://github.com/asg017/sqlite-vec/blob/main/sqlite-vec.c

With sqlite-vec's builtin binary quantization, you should be able to do something like:

  CREATE VIRTUAL TABLE vec_files USING vec0 (
    contents_embedding bit[1536]
  );

  INSERT INTO vec_files(rowid,contents_embedding) 
    VALUES (
      (1, vec_quantize_binary( /* 1536-dimension float vector here*/))
    )

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

When running inside the browser, is sqlite-vec able to persist data into the browser-native indexdb? Or, is this part left to the user? If you can share the thinking there I would appreciate it, even if that is, "no thinking yet!"

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.

I'd expect the rate of evolution to be significantly different between the two parts, and if you are using an algorithm library adopted by others, you may get progress "for free".

1. https://github.com/brainless/dwata

... and to address your tone, did you know, you can give feedback with empathy?