Author here — happy to answer any questions! This is more of a "I'm working on a new project" rather than an official release, the extension itself is still a work-in-progress. A link to the project: https://github.com/asg017/sqlite-vec
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.
* 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.
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.
Would this implement indexing strategies like HNSW? Linear scan is obviously great to start out with, and can definitely be performant, especially if your data is in a reasonable order and under (say) 10MB, so this shouldn't block a beta release.
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
Thanks for sharing! I've looked into usearch before, it's really sleek, especially all their language bindings. Though I want sqlite-vec to have total control over what stays in-memory vs on-disk during searches, and most vector search libraries like usearch/hnswlib/faiss/annoy either always store in-memory or don't offer hooks for other storage systems.
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].
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.
Really nice to see Wasm there, usually vector search in sqlite wasn't available in the browser.
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.
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!
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!
I originally added sqlite-vss (your original vector search implementation) on Langchain as a vectorstore. Do you think this one is mature enough to add on Langchain, or should I wait a bit?
Love your work by the way, I have been using sqlite-vss on a few projects already.
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.
This is awesome! We used Qdrant vector DB for a local AI RAG app https://recurse.chat/blog/posts/local-docs#vector-database, but was eyeing up sqlite-vss as a lightweight embedded solution. Excited to see you are making a successor. Would be interested to learn about latency and scalability benchmarks.
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 think we need > 1 million vector yet. But we plan to target folder of local document such as obsidian vault or store web search results which could rack up a large number of vectors. Persistence on disk is also a desired feature when looking at the choices because we don't want to reindex existing files.
Benchmark is also not everything, easiness to embed and integrate with existing sqlite apps could make sqlite-vec stand out and help with adoption.
Very exciting and I can’t wait to try it. Dependency issues are the reason I am currently not using sqlite-vss (i.e. not using an index for my vector searches), but man do I wish for better performance.
At the risk of fulfilling a HN stereotype, may I ask why you ditched Rust for this extension?
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
This is awesome. In past I worked on adding support for meta-data queries to faiss DB, by hooking up faiss db with SQlite, but this approach (Sqlite extension) is much more cleaner and better. Not sure about performance though.
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?
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.
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.
Author of txtai here - great work with this extension.
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.
Ya I worded this part awkwardly - I was hinting that querying a vector index and joining with metadata with sqlite + sqlite-vec (in a single SQL join) will probably be faster than other methods, like txtai, which do the joining phase in a higher level like Python. Which isn't a fair comparison, especially since txtai can switch to much faster vector stores, but I think is fair for most embedded use-cases.
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.
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.
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.
Author here - ya "binary vectors" means quantizing to one bit per dimension. Normally it would be 4 * dimensions bytes of space per vector (where 4=sizeof(float)). Some embedding models, like nomic v1.5[0] and mixedbread's new model[1] are specifically trained to retain quality after binary quantization. Not all models do tho, so results may vary. I think in general for really large vectors, like OpenAI's large embeddings model with 3072 dimensions, it kindof works, even if they didn't specifically train for it.
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) ;)
Re storing vectors in BLOB columns: ya, if it's not a lot of data and it's fast enough for you, then there's no problem doing it like that. I'd even just store then in JSON/npy files first and see how long you can get away with it. Once that gets too slow, then try SQLite/redis/valkey, and when that gets too slow, look into pgvector or other vector database solutions.
For SQLite specifically, very large BLOB columns might effect query performance, especially for large embeddings. For example, a 1536-dimension vector from OpenAI would take 1536 * 4 = 6144 bytes of space, if stored in a compact BLOB format. That's larger than SQLite default page size of 4096, so that extra data will overflow into overflow pages. Which again, isn't too big of a deal, but if the original table had small values before, then table scans can be slower.
One solution is to move it to a separate table, ex on an original `users` table, you can make a new `CREATE TABLE users_embeddings(user_id, embedding)` table and just LEFT JOIN that when you need it. Or you can use new techniques like Matryoshka embeddings[0] or scalar/binary quantization[1] to reduce the size of individual vectors, at the cost of lower accuracy. Or you can bump the page size of your SQLite database with `PRAGMA page_size=8192`.
I also have a SQLite extension for vector search[2], but there's a number of usability/ergonomic issues with it. I'm making a new one that I hope to release soon, which will hopefully be a great middle ground between "store vectors in a .npy files" and "use pgvector".
Re "do embeddings ever expire": nope! As long as you have access to the same model, the same text input should give the same embedding output. It's not like LLMs that have temperatures/meta prompts/a million other dials that make outputs non-deterministic, most embedding models should be deterministic and should work forever.
This is very useful appreciate the insight. Storing embeddings in a table and joining when needed feels like a really nice solution for what I'm trying to do.
Observable has other open source libraries with similar "generic" names: Plot, Runtime, Inputs. When speaking generically, most people say "Observable Plot" or "Observable Runtime." In projects where people already know about them, then I say "Plot" or "Inputs" without much fuss.
I imagine most people will say "Observable Framework" when talking out in the open, and "Framework" on established projects.
This is an awesome project, I love SQLite extensions and I think they have a ton of use. Giants props to Dan here, I haven't seen many SQLite extensions written in Zig, and I'm learning a ton reading through the source code.
The column-oriented data is stored in large BLOBs inside of regular SQLite tables. It uses the SQLite incremental BLOB I/O API [0] to incrementally read/write data in a column oriented way.
However, this project (and other SQLite extensions) will eventually hit a limit with SQLite's virtual table API. When you create a virtual table, you can perform a number of optimizations on queries. For examples, SQLite will tell your virtual table implementation the WHERE clauses that appear on the virtual table, any ORDER BYs, which columns are SELECT'ed, and other limited information. This allows extension developers to do things like predicate + projection pushdowns to make queries faster.
Unfortunately, it doesn't offer many ways to make analytical queries faster. For example, no matter what you do, a `SELECT COUNT(*) FROM my_vtab` will always iterate through every single row in your virtual table to determine a count. There's no "shortcut" to provide top-level counts. Same with other aggregate functions like SUM() or AVERAGE(), SQLite will perform full scans and do calculations themselves.
So for this project, while column-oriented datasets could make analytical queries like that much faster, the SQLite API does limit you quite a bunch. I'm sure there are workarounds around this (by custom UDFs or exposing other query systems), but would be hefty to add.
That being said, I still love this project! Really would love to see if there's any size benefit to this, and will definitely contribute more when I get a chance. Great job Dan!
Thanks for the kind words, Alex! You have probably written more great sqlite extensions than anyone, so your feedback is very meaningful.
You are right that there are limitations. The sqlite virtual table API is very clearly designed with row-oriented access in mind. However, there are still ways that stanchion makes analytical queries faster: lazily loading segments (which are the BLOBs that contain the actual values) so data that is not accessed is skipped, using encodings like bit packing to reduce the size of data that needs to be traversed on disk, and allowing users to specify a clustered index to ensure records are ordered in a way that allows querying to minimize data access.
One area that I want to explore more deeply is the xFindFunction callback in the virtual table API. It allows the a virtual table to "overload" a function with its own implementation. I believe there are some opportunities to work around some of the limitations you are describing, but I'm not even sure at this point if they can apply to aggregate functions.
This is all theoretical until there are solid benchmarks, which is something that I want to add in the near term. And if you know of any workarounds to the limitations that you think may be useful, I am all ears!
That's great to hear! The clustered index sounds really cool. Especially since SQLite tells you about ORDER BYs in xBestIndex (with the nOrderBy[0]), so it would be super cool to have super-fast ORDER BYs with those.
Very interested to see how xFindFunction works for you. One limitation I've found is that you don't know if a user uses a xFindFunction inside of xBestIndex (ie at query time), unless 1) it's part of a WHERE clause and 2) only two arguments are provided, the first being a column value and the 2nd any literal. I've found this limiting in the past, only having 1 argument to work with in that narrow case. But I'm sure there's clever tricks there!
One trick I've noticed: You can kindof detect a COUNT(*) with the `colUsed` field in xBestIndex. In that case, `colUsed` will be 0 (ie 0 columns are requested), so you can use that as a signal to just iterate over N times instead of accessing the underlying data. Still slow, but you can probably do something like ~1 million/sec, but better than accessing the data that many times!
I may be missing something, but I'm not sure you need to detect COUNT(*) specifically.
For a query that's just SELECT COUNT(*) FROM… SQLite will just iteratively call xNext/xEof to count rows. As long as you don't actually load data until some is requested by xColumn, what's the point?
And the above (lazily loading only the requested columns) is exactly what I'd expect this extension to be doing already.
That's disappointing about xFindFunction. Once I start digging into it more, I will let you know if I find any other clever tricks that you might be able to use in your extensions as well.
Have you ever reached out to the sqlite team about limitations in the virtual table mechanism that you have encountered? I'm curious how open they are to extending what is possible with virtual tables.
Just one note on using the incremental BLOB I/O API that you might want to consider (if you're not aware of it, I wasn't, found it unintuitive) is that blobs larger than page size are stored in linked lists of pages, and there is no true random access; accessing a large blob at a large offset touches all database pages til that offset.
This is true, and I had the similar issues with the virtual table API. They did add some things, but they won't do everything. There are some other problems with SQLite as well. However, there are benefits of SQLite, and I wrote some extensions, too.
Author here, happy to answer any questions! My "Introducing sqlite-vss: A SQLite Extension for Vector Search" [0] blog post has more details about the motivations behind this extensions, along with a demo. Includes a semantic search engine hosted on Datasette, build with sqlite-vss and sentence-transformers, on fly.io. Simple and cheap!
Though my favorite part about this extension (and all my other SQLite extensions[1]): you can `pip install sqlite-vss` for Python, `npm install sqlite-vss` for Node.js, or use https://deno.land/x/sqlite_vss for Deno! The underlying SQLite extension has been compiled on different platforms and uploaded to pip/npm/deno.land/x to make distribution easier. There's also pre-compiled extensions on each Github release if you just want to use it with the sqlite3 CLI.
Currently working on getting Mac M1 arm builds, hopefully by end of this week [2]
I haven't worked much with Ruby, but would love to try it! These python package don't even need to compile native extensions, the pre-compiled SQLite extension just gets stuffed into the package under `package_data`.
For ruby gems, do you know if it's possible to include arbitrary files into a gem?
AFAIK when a gem is compiled, a makefile or a rake-file is invoked - and can be made to do just about anything. Add some ruby in the gemspec - and it should be possible to distribute almost anything as a platform specific gem.
Ed: note that sqlite3 for ruby is an example of how to build and distribute pre-compiled, native ruby extensions[1]. I suppose it might be possible to just fork it, and massage the build to spit out/include dlls (via cargo build or whatever).
That would be doing much more than creating a sqlite3-some_ext gem that depends on the sqlite3 gem, though.
I guess if you just wrapped some extensions in a gem, there would be some way to find them on the filesystem / append to a relevant path setting after install?
It's possible but very difficult. For extensions built purely in C, you can statically compile extensions into a SQLite WASM build, which I have a few demos of with sqlite-lines [0] and sqlite-path[1].
For extensions but in Rust however, it's much more difficult. Matt @tantaman has some success cross compiling his cr-sqlite [2] project to WASM, but it's quite complex.
SQLite extensions typical rely on dlopen() to load dynamic libraries as an extension. WASM doesn't really have that, so you either have to statically compile your extension in your WASM build (which is difficult for non-C languages bc SQLite is written in C), or hack around some barely-supported WASM features that emulate dlopen(). Though I'm not the best with WASM, so hopefully someone with more WASM experience chimes in to help! It's something I'm tracking in this issue for the `sqlite-loadble-rs` project [3]
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/