SQLite is Enough. Lexical, Semantic, and Hybrid Search with scrydb
This paper introduces scrydb, a lightweight Python library that enables lexical, semantic, and hybrid search within SQLite by leveraging FTS5 and sqlite-vec, while demonstrating its effectiveness and efficiency through evaluation on various information retrieval benchmarks.
Original paper licensed under CC BY 4.0 (http://creativecommons.org/licenses/by/4.0/). This is an AI-generated explanation of the paper below. It is not written or endorsed by the authors. For technical accuracy, refer to the original paper. Read full disclaimer
In the vast landscape of modern information retrieval, the challenge is not merely finding a needle in a haystack, but finding the right needle among billions of similar ones, quickly and without burning through the world's energy. For decades, the solution has relied on two distinct approaches. The first is lexical search, a method that treats text like a library card catalog, matching the exact words a user types against the words in a document. The second is semantic search, which attempts to understand the meaning behind the words, matching concepts even when the specific vocabulary differs. To power this deeper understanding, computers convert text into long lists of numbers called embeddings, which act as mathematical fingerprints for meaning. However, storing and comparing these fingerprints for millions of documents usually requires massive, expensive servers and complex software that runs constantly in the background, creating a barrier for smaller projects and making it difficult to share research results as a single, self-contained package.
A researcher has introduced a new tool called scrydb, which challenges the assumption that powerful search requires heavy infrastructure. Built on top of SQLite, a database system that fits into a single file and needs no server to run, this library packs the entire search process—documents, word indexes, and meaning fingerprints—into one compact package. The researcher demonstrated that by simplifying how these meaning fingerprints are stored and compared, they could perform high-quality searches on a standard laptop without needing the massive, specialized systems typically required for such tasks. Their work suggests that for small to medium-sized collections, the heavy machinery of modern search is often unnecessary, and a single, portable file can do the job just as well.
The core innovation lies in how the system handles the mathematical fingerprints of meaning. Usually, these fingerprints are stored as high-precision numbers that take up a lot of space and require significant computing power to compare. The researcher found a way to shrink these fingerprints drastically by converting them into simple patterns of ones and zeros, a process that reduces their size by a factor of thirty-two. Instead of comparing complex numbers, the system compares these binary patterns using a method that counts how many bits differ between them. This allows the computer to scan through millions of documents in a fraction of the time it would take with the full, high-precision versions. The system also retains the ability to use the full, high-precision versions if a user needs the absolute highest accuracy, but it can do so by first using the fast, small versions to narrow down the list of candidates, saving time and energy.
To test if this approach actually works, the researcher evaluated scrydb against eight different real-world datasets, ranging from financial questions to scientific fact-checking and medical research. They compared their results against the standard benchmarks used by the industry, which typically rely on the most powerful, full-precision systems available. The findings were striking: on four of the eight datasets, the lightweight system performed just as well as, or even better than, the heavy-duty industry standard. On the remaining datasets, the difference in performance was so small that it was barely noticeable. In many cases, the system could find the best answers by first scanning the entire collection with the tiny, fast fingerprints and then only checking the top few hundred results with the more detailed, high-precision versions. This two-step process meant that the system achieved nearly the same quality of results as a full scan but in a fraction of the time.
The speed of the system depends heavily on the size of the collection and the method used. When searching through a collection of over half a million documents, the system using the fast, binary fingerprints could return an answer in less than a second. Even when the researcher added a second step to refine the results with higher precision, the total time remained practical for a single user on a standard computer. However, the researcher was careful to note the limits of this approach. While the system is incredibly efficient for collections up to a few million documents, it does not scale infinitely. If the collection grows to tens of millions or billions of items, the time required to scan every single document would become too long, and the specialized, distributed systems used by large tech companies would still be necessary. The system is not a replacement for those massive networks but rather a powerful alternative for smaller, self-contained projects.
Beyond the technical performance, the researcher highlighted a significant benefit for the scientific community: reproducibility. Because the entire search engine, including the documents and the mathematical fingerprints, lives in a single file, it can be shared, archived, and rerun by anyone with a simple click. This eliminates the need to share complex bundles of configuration files, separate database dumps, and vector store snapshots that often break when moved between different computers. A researcher can now hand over a single file that contains everything needed to repeat an experiment exactly as it was done originally. This makes the process of sharing scientific findings much more reliable and accessible, ensuring that the work can be verified and built upon by others without the friction of incompatible software environments.
The study concludes that the trade-off between speed and accuracy is not as rigid as previously thought. By using a simple, single-file database and clever compression techniques, it is possible to build a search system that is both fast and accurate enough for most practical needs. The researcher emphasizes that this does not mean the large, complex systems are obsolete; they remain essential for massive, real-time applications serving millions of users simultaneously. However, for the vast number of smaller projects, research experiments, and personal archives, the heavy infrastructure is often overkill. The new tool offers a way to achieve high-quality search results with a fraction of the resources, proving that sometimes, the most powerful solution is the one that fits in a single file.
Drowning in papers in your field?
Get daily digests of the most novel papers matching your research keywords — with technical summaries, in your language.