Source-linked AI summary
SQLite is Enough. Lexical, Semantic, and Hybrid Search with scrydb
Timo Breuer
TL;DR
Information-retrieval experiments often require separate document stores, lexical indexes, vector stores, and orchestration. scrydb packages lexical, semantic, and hybrid search in SQLite, and its evaluation shows that a compact single-file design can remain close to a full-precision baseline while exposing clear scale limits.
Problem
Retrieval pipelines commonly involve costly semantic search and separate resources, motivating a lightweight, reproducible design that balances effectiveness, latency, and resource requirements.
Method
scrydb combines SQLite FTS5 for lexical search, sqlite-vec for binary, int8, or full-precision semantic search, and optional reranking and rank fusion in one database file.
Results
Across eight datasets, the best scrydb configuration trails the full-precision MTEB baseline by a mean of 0.006 nDCG@10 and meets or exceeds it on four datasets.
Takeaways & Limitations
scrydb provides a shareable, archivable, and rerunnable retrieval artifact for small-to-medium-scale information-retrieval experiments.
Takeaways & Limitations
Exhaustive binary-code scanning is not a substitute for the sublinear query time and production-scale capabilities of purpose-built ANN systems.
Abstract
from arXiv · showhide
This work introduces scrydb, a Python library that enables lexical, semantic, and hybrid search within SQLite. For lexical search, scrydb leverages SQLite's full-text search extension FTS5. Semantic search builds on sqlite-vec, a SQLite extension for vector search. Furthermore, the library allows users to rerank and fuse retrieval results to combine both lexical and semantic approaches, providing a lightweight solution for downstream tasks in information retrieval (IR) or agentic search. We evaluate scrydb on various IR benchmark datasets and demonstrate its effectiveness in text retrieval based on keyword matching, semantic similarity, and rank fusion. In addition, we provide insights into query latency and the trade-off between efficiency and effectiveness. scrydb is available under the MIT license.
1 Introduction
scrydb packages lexical, semantic, and hybrid retrieval resources and operations into a single SQLite file. It evaluates effectiveness and efficiency across BEIR datasets while providing reusable open-source resources.
- scrydb stores documents, a lexical index, and embeddings in one SQLite database without a server process or separate vector store.The artifact is designed to be shared, archived, and rerun as a single file.
- Lexical search, semantic search, reranking, and rank fusion are implemented in one library rather than orchestrated across multiple systems.FTS5 provides lexical search, while sqlite-vec supports semantic search at multiple precision levels.
- Single-file storage colocates raw documents, indexes, and embeddings, reducing an IR resource to one shareable and archival artifact.The paper characterizes SQLite as an archival-grade storage format for reproducible resources.
- The evaluation spans BEIR datasets and examines query latency, retrieval effectiveness, vector precision, and trade-offs between quality and resource requirements.Results are compared with MTEB baseline results.
- scrydb is released under the MIT license with prebuilt databases, embeddings, and final retrieval runs for reproduction and follow-up research.The package and evaluated resources are intended to support future baselines and reuse.
2 Related Work
Related work situates scrydb at the intersection of efficient neural retrieval, reproducibility, compact embeddings, and embedded database search. Existing approaches trade storage, operational complexity, exactness, and query-time efficiency in different ways.
- Efficiency and lower computational or environmental costs have become important concerns in neural information retrieval.The ReNeuIR workshop series provides a forum for sustainable retrieval research and best practices.
- Reproducibility is linked to sustainability, and scrydb uses SQLite to support single-file reproducibility with a preferred archival dataset format.The Library of Congress lists SQLite in its Recommended Formats Statement.
- Dense retrieval maps queries and documents into a shared embedding space, but exhaustive comparison against every embedding is costly at scale.This cost motivates approximate nearest-neighbor search.
- ANN systems such as Faiss and HNSW trade exactness for sublinear query time.They use inverted-file, product-quantization, or graph-based indexes.
- Binarization and scalar quantization reduce embedding-index memory footprints, with sign-based binarization tracing to locality-sensitive hashing and SimHash.Product quantization provides a complementary approach.
- Managed vector databases offer ANN indexing and hybrid retrieval as standalone services, while pgvector and DuckDB add vector search within relational or embedded database designs.scrydb shares the embedded, single-file design point described for DuckDB.
3 Efficient Semantic Search Within SQLite
scrydb makes semantic search in SQLite more practical by using lower-precision embedding representations and exhaustive distance comparisons. Binary codes minimize storage and comparison cost, while int8 preserves more information at a higher cost.
- Full Embeddings and Cosine Similarity: Full-precision cosine similarity is accurate but exhaustive high-dimensional computation is memory- and compute-intensive.This motivates lower-precision representations.
- Embedding Binarization and Hamming Distance: Each dense embedding is binarized component-wise into a d-dimensional bit vector using a Heaviside step quantizer.The quantizer assigns one bit for positive components and zero otherwise.
- Embedding Binarization and Hamming Distance: Binary embeddings use 32× less storage than 32-bit floating-point embeddings by packing d bits into d/8 bytes.This representation is designed to reduce storage and comparison cost.
- Embedding Binarization and Hamming Distance: Semantic search compares the binary query code with every binary document code using Hamming distance computed by bitwise XOR and population count.The method enables semantic search over the full index within SQLite.
- Scalar Quantization to int8: int8 quantization maps normalized embedding components from [−1, 1] to int8, producing a representation four times smaller than float32.Unlike binary codes, int8 retains per-dimension magnitude and ranks by cosine similarity.
4 Software Library and Implementation Details
The scrydb library exposes SQLite-backed lexical, semantic, and hybrid retrieval through a lightweight Python interface. Its APIs support multiple precisions, reranking, batch workflows, and storage of documents, queries, and embeddings.
- Software Library and Implementation Details: scrydb installs from PyPI with pip install scrydb and uses prebuilt SQLite extension binaries without local compilation.The package requires only numpy and tqdm at runtime unless optional machine-learning extras are used.
- SQLite Extensions: FTS5 provides transactional lexical retrieval through an inverted-index virtual table queried with MATCH.scrydb also exposes snippet and highlight functions for keyword-in-context output.
- SQLite Extensions: sqlite-vec stores float32, int8, and binary vectors in separate vec0 tables within the same database.The full index can be scanned at each precision, and one precision can rerank candidates from another.
- API and Retrieval Workflow: Figure 1 presents an end-to-end workflow from corpus indexing through interactive or batch retrieval and TREC-run output.The public Index object owns the SQLite connection and acts as a context manager.
- API and Retrieval Workflow: Documents and queries can be supplied as JSONL paths or iterable records, with embeddings generated through Sentence Transformers or provided precomputed.This decouples the library from any particular embedding model.
- API and Retrieval Workflow: search() and batch_search() select lexical, semantic, or hybrid retrieval, semantic precision, and optional second-stage reranking.Supported semantic representations are binary, int8, and float.
- API and Retrieval Workflow: The Index exposes stored records and embeddings through mapping-like interfaces, while batch results export to six-column TREC format or pandas.Binary and int8 embedding variants are accessible alongside full embeddings.
5 Experimental Evaluations
The evaluation compares thirteen lexical, semantic, and hybrid retrieval configurations across eight BEIR datasets against a full-precision MTEB baseline, measuring effectiveness and query latency. Results show that binary-first semantic retrieval preserves top-ranked effectiveness while substantially reducing latency, whereas hybrid fusion is dataset-dependent.
- Experimental setup: Thirteen scrydb configurations are evaluated across eight BEIR datasets against Qwen3-Embedding-8B full-precision MTEB results.The evaluation covers lexical, semantic, reranked, and RRF-fused pipelines on a single consumer machine.
- Retrieval effectiveness: Reranking improves nDCG@10 over raw BM25 on every dataset, with Hamming reranking capturing nearly all of the gain except on Touché.Touché shows only a marginal nDCG@10 gain, while RR slightly degrades.
- Retrieval effectiveness: Semantic retrieval over binarized embeddings outperforms BM25 in nDCG@10 on all eight datasets.The results indicate that the binarized embedding signal is more informative than exact lexical overlap in these experiments.
- Retrieval effectiveness: 164.5 ms vs. 822.5 ms: Hamming + cosint8 matches exhaustive cosint8 in top-10 effectiveness while costing roughly one-fifth of its mean latency.The configurations have identical P@10 and nDCG@10 to three decimals across all eight datasets, with a small AP difference on TREC-COVID.
- Retrieval effectiveness: Four of eight datasets meet or exceed the MTEB baseline, while the TREC-COVID deficit is reproduced by exhaustive full-precision search before compression.The reported comparisons are FiQA, Quora, SciFact, and Touché; the TREC-COVID gap is 0.885 versus 0.950 nDCG@10.
- Retrieval effectiveness: RRF is best on exactly one dataset, Touché, and trails the stronger component ranking on the other seven.The results support using hybrid fusion selectively rather than as a uniform default.
- Query latency: 36×: exhaustive int8 cosine averages 822.5 ms versus 22.9 ms for Hamming, while full-precision cosine averages 1998.1 ms.Plain Hamming remains practical at 81.5 ms on Quora’s 523K-document corpus; multi-second costs arise from stacked exhaustive high-precision scans.
6 Discussion
scrydb packages retrieval resources in one SQLite file and tests whether this compact design preserves effectiveness and manageable latency. The results support small-to-medium-scale use while identifying scalability limits relative to purpose-built vector systems.
- 0.006 mean nDCG@10 separates the best scrydb configuration from the full-precision MTEB baseline across eight datasets.It meets or exceeds the baseline on four datasets despite using embeddings binarized to one thirty-second of their original size.
- Exhaustive scanning sacrifices sublinear query time, horizontal scalability, and concurrent serving compared with purpose-built vector databases and ANN systems.Those systems additionally target sharding, replication, real-time updates, GPU acceleration, and production operational tooling.
- 0.15 ms per thousand documents is the marginal Hamming-search latency, with about 1.3 ms of fixed per-query overhead.Linear extrapolation remains under one second for a few million documents but reaches several seconds around twenty million.
- 32× embedding compression keeps several-million-document semantic indexes within a few gigabytes of memory on consumer machines.A 4096-dimensional vector occupies 512 bytes when binarized, compared with 16 KB at full 32-bit precision.
7 Conclusion
The conclusion presents scrydb as a self-contained SQLite retrieval resource designed for reproducibility rather than as a replacement for scalable vector-database infrastructure. Its appropriate use depends on corpus scale and serving requirements.
- scrydb packages lexical, semantic, and hybrid search with documents, indexes, and embeddings in one self-contained SQLite file.The artifact can be shared, archived, and rerun like a dataset file, supporting more reproducible retrieval experiments.
- scrydb is intended for cases where a single, easily archived file fits the task’s scale and serving requirements, while heavier systems remain appropriate when they do not.The conclusion frames this as a requirements-based choice rather than a categorical preference for lightweight tools.