4 ms·

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 w

• lsb 2y ago
  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. Also do you build with sqlite-httpvfs? This would go together great https://github.com/phiresky/sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs
  • alexgarcia-xyz 2y ago
    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 https://huggingface.co/blog/matryoshka
    • sroussey 2y ago
      Might have a look at this library: https://github.com/unum-cloud/usearch https://github.com/unum-cloud/usearch It does HNSW and there is a SQLite related project, though not quite the same thing.
      • alexgarcia-xyz 2y ago
        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]. [0] https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_open.html [1] https://github.com/tidwall/tg https://github.com/tidwall/tg
        • ashvardanian 2y ago
          USearch author here :) 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!
    • mkesper 2y ago
      Have a look at https://jkatz05.com/post/postgres/pgvector-performance-150x-speedup/ https://jkatz05.com/post/postgres/pgvector-performance-150x-... fp16 for indices seems to give a nice size compression without reducing quality with HNSW.
• xyc 2y ago
  This is awesome! We used Qdrant vector DB for a local AI RAG app https://recurse.chat/blog/posts/local-docs#vector-database 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.
  • alexgarcia-xyz 2y ago
    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?
    • xyc 2y ago
      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.
• nnx 2y ago
  Interesting idea. 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?
  • alexgarcia-xyz 2y ago
    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-quantization/ https://www.pinecone.io/learn/series/faiss/product-quantizat...
• TachyonicBytes 2y ago
  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.
  • alexgarcia-xyz 2y ago
    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 https://www.sqlite.org/vtab.html [1] https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
• jeanloolz 2y ago
  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.
  • davedx 2y ago
    He says in the blog post and here that this isn't finished yet
  • alexgarcia-xyz 2y ago
    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.
    • jeanloolz 2y ago
      Cool beans! I'll look into it soon then
• tipsytoad 2y ago
  Looks really nice, but the only concern I had — how does the perf compare to more mature libraries like faiss?
  • alexgarcia-xyz 2y ago
    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.
    • dmezzetti 2y ago
      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.
      • alexgarcia-xyz 2y ago
        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.
        • dmezzetti 2y ago
• CGamesPlay 2y ago
  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?
  • alexgarcia-xyz 2y ago
    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
• datadeft 2y ago
  Could this be implemented in Rust? Does that project (sqlite-loadable-rs) support WASM? https://observablehq.com/@asg017/introducing-sqlite-loadable-rs https://observablehq.com/@asg017/introducing-sqlite-loadable...
  • alexgarcia-xyz 2y ago
    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!
• HanClinto 2y ago
  Awesome work!! * 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!!
  • alexgarcia-xyz 2y ago
    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. )
• Technetium 2y ago
  Can you include a license? Will it be MIT like sqlite-vss?
  • alexgarcia-xyz 2y ago
    Ya I'll put a license soon, will be MIT
• bendavis381 2y ago
  Very very excited by this! I saw on the repo that `sqlite-vec` will support `row in`?
  • alexgarcia-xyz 2y ago
    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.
    • bendavis381 2y ago
      Nice. So if I understand correctly, pre-filtering means we'd get 10 results here if there are at least 10 returned from the subquery?
• israrkhan 2y ago
  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.