Latest / 1 More Paper / QuackIR: Retrieval in DuckDB and Other Relational Database Management Systems — Ge et al. (2025)

Transcript

QuackIR: Retrieval in DuckDB and Other Relational Database Management Systems — Ge et al. (2025) · 1 More Paper

• You’re listening to “QuackIR: Retrieval in DuckDB and Other Relational Database Management Systems,” by Y. Ge, Z. Chen, and J. Lin. Published in 2025. Abstract. Enterprises today are increasingly compelled to adopt dedicated vector databases for retrieval-augmented generation (RAG) in applications based on large language models (LLMs). As a potential alternative for these vector databases, we propose that organizations leverage existing relational databases for retrieval, which many have already deployed in their enterprise data lakes, thus minimizing additional complexity in their software stacks. To demonstrate the simplicity and feasibility of this approach, we present QuackIR, an information retrieval (IR) toolkit built on relational database management systems (RDBMSes), with integrations in DuckDB, SQLite, and PostgreSQL. Using QuackIR, we benchmark the sparse and dense retrieval capabilities of these popular RDBMSes and demonstrate that their effectiveness is comparable to baselines from established IR toolkits. Our results highlight the potential of relational databases as a simple option for RAG scenarios due to their established widespread usage and the easy integration of retrieval abilities. Our implementation is available at quackir.io. 1 Introduction. With the rise of large language models (LLMs) and retrieval-augmented generation (RAG), where search results are incorporated into prompts to provide additional context for LLMs, a variety of vector stores dedicated to vector search have emerged. The dominant narrative is that these vector stores are necessary for enterprises as part of their “AI stack”. The goal of this paper is to provide a potential alternative for the vector stores used in RAG with relational databases by introducing QuackIR, a retrieval toolkit dedicated to this approach of search using relational databases. Relational databases are an established fixture in the “data stacks” of many, if not most, enterprises, forming an integral component of existing data lakes. Having successfully withstood the test of time and numerous challengers, they have shown themselves to be indispensable, and it follows that many companies have already invested heavily in them. Thus, performing search directly using relational databases in the context of RAG applications is advantageous as it adds mini-mal additional complexity compared to integrating a separate dedicated vector store. To demonstrate the retrieval capabilities of relational databases, we explore three relational database management systems (RDBMSes) with QuackIR: DuckDB1 for its powerful analytics capabilities, SQLite2 for being the “go-to” embedded database, and PostgreSQL3 for its popular deploy-ment in production. Our contribution is QuackIR, a toolkit for information retrieval (IR) with RDBMSes. Using QuackIR, we evaluate the retrieval effectiveness of various RDBMSes and draw comparisons against established baselines, exhibiting the potential of relational databases in retrieval. Based on our results, we highlight DuckDB as a particularly promising candidate. QuackIR’s pipeline mirrors widely used IR toolkits such as Anserini and Pyserini, achieving feature parity and offering a mod-ular architecture conducive to extension and integration. We hope that QuackIR enables enterprises to build effective RAG systems directly on top of their existing relational database infrastructure. 2 Architecture We discuss our design and RDBMS-specific tech-nical details. QuackIR is open source; the full implementation is available at quackir.io. 2sqlite.org QuackIR supports sparse, dense, and hybrid retrieval. Sparse retrieval is based on keyword matching, where the terms in a document and their respective frequencies are represented by sparse vectors. This is also called full-text search. BM25 is the “classic” sparse retrieval algorithm, calculating the relevance scores of documents in re-gards to query tokens based on various factors such as the number of documents, document lengths, and given parameters that can be tuned to adjust the weight of said factors. Dense retrieval uses special models, e.g., transformers, to embed documents and queries into vectors that capture the semantic meaning of the text, hence the name vector search. Relevance scores are calculated by the cosine distance of document and query vectors. We implement dense retrieval with “flat” indexes in QuackIR, which is brute-force search for the exact nearest neighbour by scanning all the vectors and finding the top results based on the cosine distance. Hybrid retrieval fuses results from different retrieval techniques, such as sparse and dense retrieval, in an effort to create a list of results that improves upon both. We implement reciprocal rank fusion (RRF) in QuackIR, a popular hybrid retrieval method that combines together documents rescored according to their respective ranks in the base retrieval results. The pipeline and features of QuackIR parallel those of Anserini and Pyserini, comprising pre-processing, indexing, retrieval, and evaluation, with a modular design that is convenient to integrate java -cp anserini-1.0.0-fatjar.jar \ io.anserini.search.SearchCollection \ -index indexes/indexpath \ -topics path/to/queries \ -output runs/output.txt \ -bm25 -removeQuery python -m pyserini.search.lucene \ --threads 16 --batch-size 128 \ --index indexes/indexpath \ --topics path/to/queries \ --output runs/output.txt \ --output-format trec \ --hits 1000 --bm25 --remove-query python -m quackir.search \ --index indexname \ --topics path/to/queries \ --output runs/output.txt \ --db-type duckdb \ --db-path duck.db into existing enterprise workflows. A diagram of this pipeline can be found in Figure 1. Anserini and Pyserini are widely used IR toolkits that support the full retrieval pipeline through powerful utilities implemented in Java and Python, respectively. They offer various retrieval modes, includ-ing sparse, dense, and hybrid. Given Anserini and Pyserini’s widespread adoption within the IR research community, we use them as primary references when designing QuackIR. By conforming to their interface and design patterns, we aim to bridge the gap between academic IR toolkits and RDBMS-based industry applications. An example retrieval command in QuackIR, alongside corresponding commands in Anserini and Pyserini, is shown in Figure 2. def ftssearch(self, querystring, topn=5, tablename="corpus"): query = f"""WITH fts AS (SELECT, COALESCE(ftsmain{tablename}.matchbm25(id?, k:=0.9, b:=0.4), 0) AS score FROM {tablename}) SELECT id, score FROM fts WHERE score IS NOT NULL ORDER BY score DESC LIMIT {topn}; """ return self.conn.execute(query, [querystring]).fetchall 2.1 Design QuackIR wraps the SQL logic required for retrieval within the integrated RDBMSes; for instance, Figure 3 illustrates QuackIR’s sparse retrieval implementation using DuckDB’s full-text search. While the RDBMSes already provide the primary retrieval capabilities, e.g., the SQL query shown, the usage across different systems varies and can be difficult to master. QuackIR presents a natural Python inter-face that interoperates with Anserini and Pyserini, matching research-level effectiveness while being fully implemented within RDBMSes, reducing pro-duction complexity for industry practitioners and their existing relational database systems. Pre-Processing To generate terms for sparse retrieval, text is put through a tokenizer for stem-ming and to remove stopwords. QuackIR wraps Pyserini’s Lucene analyzer to use its Porter tokenizer for convenient tokenization. The built-in text processing functionalities of the RDBMSes are disabled wherever possible to ensure that retrieval effectiveness is evaluated without the interference of database-specific tokenization or normalization. That is, for our experiments, all documents and queries used for sparse retrieval are pre-tokenized with QuackIR. For dense retrieval, we use documents and queries pre-encoded with BGE-base-en-v1.5. Indexing Documents are indexed to facilitate efficient computation of relevance scores and fast retrieval. For indexing, QuackIR takes the path of the collection and inserts the contents of the file, or of all the files inside for a directory, into the specified database. QuackIR accepts jsonl files for sparse and dense indexes, and parquet files for dense indexes. A table with the appropriate columns is cre-ated based on the type of index: a text column for contents in a sparse index or an RDBMS-specific vector column for embeddings in a dense index. For dense indexes, simply loading the data into the database table is enough. For sparse indexes, there is an additional step for the actual indexing to make the collection statistics used for retrieval easily ac- from quackir.index import DuckDBIndexer from quackir import IndexType tablename = "corpus" indextype = IndexType.SPARSE indexer = DuckDBIndexer indexer.inittable(tablename, indextype) indexer.loadtable(tablename, corpusfile) indexer.ftsindex(tablename) indexer.close cessible. An example code snippet for indexing in QuackIR with DuckDB is shown in Figure 4. We do not consider the cost of the entire indexing pro-cess in our results; we assume a static corpus, as is the case with benchmarks, which makes indexing a one-time operation. Retrieval QuackIR supports a variety of retrieval needs, offering sparse, dense, and hybrid retrieval. An example code snippet for searching a sparse index in QuackIR with DuckDB is shown in Figure 5. For sparse retrieval, DuckDB and SQLite both at-tempt to implement BM25, but PostgreSQL does not. Even then, as described by Kamphuis et al. (2020), there are many variants of BM25, and the RDBMSes have implementation differences from Lucene’s formula used by Anserini, the baseline we compare against. For dense retrieval, supported by DuckDB and PostgreSQL, we use brute-force search with flat indexes to get the “baseline” effec- !   1 + N −dft+0.5 tftd · log   Llossy  dft+0.5 k1· 1−b+b· +tftd Lavg !   tftd·(k1+1) 1 + N −dft+0.5 · log    dft+0.5 Ld k1· 1−b+b· +tftd Lavg !   tftd·(k1+1) N −dft+0.5 · log    dft+0.5 Ld k1· 1−b+b· +tftd Lavg tiveness. Hybrid retrieval is implemented directly with SQL, querying both a sparse and a dense index and combining results by RRF. Following Cormack et al. (2009), we set k = 60 as the default value used in RRF for our experiments. As hybrid retrieval uses dense retrieval, it is also only supported in DuckDB and PostgreSQL. 2.2 DuckDB QuackIR supports sparse retrieval in DuckDB with its fts extension,4 one of DuckDB’s many powerful core extensions. DuckDB’s sparse index is called a “ftsindex”, which is comprised of several tables in the same database but with another schema, similar to an inverted index used by Lucene.5 With DuckDB’s extensive configurability, we are able to fully disable the built-in text processing by setting no stemmer, no stopwords, no regular expression parsing, no accent-stripping, and no lowercasing. We are also able to set the values of the BM25 parameters k1 and b to 0.9 and 0.4, respectively, following the Anserini defaults. How-ever, the BM25 formula DuckDB uses differs from that of Lucene’s in that it multiplies the score by (k1 + 1) and does not use caching for the document length metric. The formula is shown in row of Figure 6. QuackIR has dense retrieval in DuckDB as well. DuckDB is well-suited for vector search, as the required functionalities are natively available without additional extensions. Dense indexes use DuckDB’s built-in ARRAY datatype, and scoring uses its arraycosinesimilarity function. 2.3 SQLite Sparse retrieval is available in QuackIR with SQLite using its FTS5 extension.6 SQLite’s sparse index is a virtual table,7 which is an object that resembles a table but is in fact made of methods. Disabling the tokenizer is not an option, so we proceed with its Porter tokenizer. The BM25 parameters k1 and b have their values hard-coded at 1.2 and 0.75, respectively, so we cannot set them to match Anserini. The differences between the BM25 formulas of SQLite and Lucene consist of the same (k1 + 1) multiplier and precise document length accuracy as DuckDB. It also does not add 1 before taking the logarithm in the IDF term. The formula is shown in row of Figure 6. QuackIR does not support dense retrieval with SQLite. While there exist vector extensions for SQLite, none are widespread. Specifically, none are as established as pgvector, so we choose not to incorporate any, as it would be less applicable. 2.4 PostgreSQL QuackIR offers sparse retrieval in PostgreSQL with its built-in full-text search.8 Its sparse index is a generalized inverted index (GIN),9 which is an index designed for searching composite items, implemented as a B-tree. We use the “simple” configuration with no stopwords, resulting in tokens being lowercased without any other processing. While this evaluates PostgreSQL’s “default” full-text search capabilities and mirrors the setup of the other RDBMSes, since this approach does not use BM25 like DuckDB and SQLite, it is not nec-essarily a “fair” comparison for optimal effectiveness. Therefore, we also run experiments using the “english” configuration, which uses Snowball stemming for English, a natural choice for English retrieval, along with a document length normaliza-tion option that divides the score of a document by 1 + the logarithm of its length.10 We will refer to this as the “modified” configuration. Additionally, there is the option to configure how strongly tokens are bound together in the query. We choose the “OR” operator, which matches when at least one of the tokens in the query appears in the document, as it is the weakest binding. For fusion purposes, we use PostgreSQL with the “simple” configuration for the sparse component. QuackIR’s dense retrieval with PostgreSQL uses PostgreSQL’s popular pgvector extension,11 which integrates the vector datatype and adds support for vector similarity search to PostgreSQL. We use pgvector’s cosine distance for scoring. 3 Experiments Experiments are performed on an Azure instance equipped with 64 AMD EPYC 9V74 80-Core Pro-cessors, running Ubuntu 24.04.2 with 503 GB of memory. We evaluate on the BEIR dataset, a widely adopted benchmark com-prising a diverse set of real-world retrieval tasks across multiple domains, with established baselines for comparison. Specifically, the “flat” variant is used, where fields are concatenated prior to indexing. Latency is measured in queries per second (QPS). Retrieval effectiveness is evaluated with Pyserini and the qrels integrated into the tools submodule it shares with QuackIR, which provides relevance judgements for document–query pairs. We measure effectiveness with the nDCG@10 metric, which is calculated by the relevance and ranks of the top ten retrieved documents, where a higher score is better. 3.1 Sparse We begin by examining sparse retrieval. The effectiveness of DuckDB and SQLite come close to Anserini’s baseline with slight variations, and Post- greSQL underperforms. The differences between the scores that DuckDB and SQLite achieve from baseline are small enough for us to attribute them to the formula variations discussed earlier. DuckDB results are very close to baseline for almost all of the datasets, with less than 0.01 of difference, which is fairly minor. The exceptions to this are TREC-NEWS, Climate-FEVER, and Ar-guAna, being lower than baseline by 0.010, 0.016, and 0.079, respectively, and Signal-1M, which is higher than baseline by 0.01. SQLite achieves good effectiveness also, and results are near baseline for the most part, with 10 datasets exceeding baseline by less than 0.01, 13 datasets exceeding baseline by more than 0.01, and 6 datasets below baseline. Remarkably, the datasets below baseline are all below by 0.01 or more, indi-cating a non-trivial deviation. The datasets where SQLite underperforms, Touché 2020, NQ, Hot-potQA, FEVER, Climate-FEVER, and BioASQ, are all large collections, with the exception of Touché 2020, which falls in the “medium” subsec-tion and has the biggest difference, almost 0.1 below baseline. Among the “large” datasets, Signal-1M and DBpedia achieve results close to baseline but also contain the fewest queries in this group. Although DBpedia and BioASQ differ by only about one hundred queries, BioASQ includes more than three times as many documents and shows the smallest deviation from baseline among the below-baseline datasets. This suggests some loss of effectiveness in SQLite over many queries in very large corpora. Since PostgreSQL does not implement BM25, it would be unfair to expect it to conform to baseline. Nevertheless, from the perspective of effectiveness, the scores of its full-text search using the “simple” configuration are around 0.1–0.15 lower than baseline on average, except SCIDOCS and NFCorpus, where the difference is smaller, and ArguAna, where the difference is much larger. Compared to the “simple” configuration, results are consistently better with our “modified” configuration, though still below baselines. The amount of improvement gained by the different configuration varies con-siderably across datasets, ranging from less than 0.01 in NFCorpus to almost 0.19 in ArguAna. This brings scores with the “modified” configuration to about 0.04 less than baseline on average, which is still nonnegligible. In terms of efficiency, shown in Table 2 under the sparse subsection, SQLite is the fastest, followed by DuckDB, with PostgreSQL being considerably slower. The speed difference between SQLite and DuckDB is large for smaller datasets, but closes as the size of the corpora grows. PostgreSQL’s speed is slow to the point where we do not evaluate it on the medium or large datasets, as it takes longer than one second to process a query by the end of the small datasets. We only show the latency of PostgreSQL with the “simple” configuration as the “modified” configuration takes more than one second per query for all datasets, and we include its effectiveness only as a reference for what Post-greSQL’s full-text search is capable of achieving. 3.2 Dense Cosine similarity seems to be a much less disputed formula than BM25, as DuckDB and PostgreSQL both match the Anserini baseline exactly for dense retrieval tasks. PostgreSQL is initially faster on the smallest datasets, but this advantage quickly disappears within the “small” subset. For larger datasets, neither system performs practically, with query latency exceeding one second per query. 3.3 Hybrid Fusion results are typically better than both indi-vidual runs when two strong runs are combined. However, when the two base runs differ too much in effectiveness, the score is usually in between the two and thus lower than the maximum of the two original runs. This trend is present for the base-lines12 and the DuckDB and PostgreSQL results, where we use the “simple” configuration for Post-greSQL. Unfortunately, this also comes with worse latency, so we only evaluate the effectiveness for the small subset. Since DuckDB’s sparse results are close to baseline and the dense results matched exactly, it makes sense that the hybrid results are close to the hybrid baseline. The difference between results and baseline for sparse retrieval does not appear to have a large effect on the difference between hybrid results and baseline. That is, a large effectiveness difference in sparse runs does not necessarily lead to a large effectiveness difference in hybrid runs. Notably, the difference for NFCorpus increases from 0.001 for sparse to 0.011 for hybrid. On the other hand, the difference for ArguAna de-creases from 0.079 to 0.053, likely due to its strong dense retrieval results. The remaining outliers from sparse retrieval with large differences are not evaluated with hybrid retrieval due to latency. All other datasets have reasonably small differences. With PostgreSQL’s lacklustre results in sparse retrieval, it makes sense that hybrid retrieval is not as effective. Most results are lower than baseline by roughly 0.05, which is an improvement over the 0.1 difference with sparse retrieval, likely balanced by the effectiveness of dense retrieval. Exceptions are NFCorpus, which has a small difference of 0.01, and ArguAna, with a particularly large difference of 0.214, likely due to the gap in the scores of sparse results. Even then, by fusing with dense results, the gap between PostgreSQL’s results and baseline for ArguAna closes by around 0.1 compared to the gap in sparse results alone. Overall, we believe DuckDB demonstrates the most potential. In sparse retrieval, it has the most consistent scores compared to baselines, and its speed is competitive with SQLite when scaling. It also has the most flexible configurations for text processing, which is promising for future development. In dense retrieval, it boasts equal effectiveness and superior speed compared to PostgreSQL. Thus, we recommend DuckDB as especially wor-thy of interest for retrieval in relational databases. 4 Conclusion We introduce QuackIR, an IR toolkit with RDBMSes. Using QuackIR, we run experiments on DuckDB, SQLite, and PostgreSQL, drawing comparisons against existing baselines and demon-strating the viability of RDBMSes in retrieval. We hope QuackIR can be useful for industry practition-ers to take full advantage of their existing relational databases for their RAG applications. 5 Limitations We believe QuackIR is the first RDBMS-based IR toolkit, so there is much to be explored in this space. Some limitations are worth addressing. We do not consider the cost of indexing in our evaluation. After the indexing operation, sparse indexes in SQLite and PostgreSQL update auto-matically upon updates to the original table, while DuckDB requires explicit re-indexing to reflect any changes. This is not applicable in our experiments as we run the indexing operation once after all documents have been inserted into the table, but it would be worth considering for use cases where the collection is frequently updated. As is the case with many industry applications, scaling is an issue that needs to be addressed to create practical, deployable solutions. Both sparse and dense retrieval in QuackIR exhibit in-creases in query latency for large datasets across all the RDBMSes, with dense retrieval suffering a more pronounced degradation in performance even through the medium datasets, so improvements with speed would be helpful to the user experience. More efficient sparse and dense retrieval would also allow fusion retrieval to become practical. For the sake of establishing a baseline, we do not investigate many other extensions available. For example, we have only explored “flat” dense indexes with brute-force retrieval in QuackIR so far, but pgvector and DuckDB’s vss extension13 both offer hierarchical navigable small worlds (HNSW) indexes, which per-forms approximate nearest neighbor search, sacri-ficing recall for speed. Additionally, we use pgvector for dense retrieval in PostgreSQL for simplic-ity and as a “fair” comparison against DuckDB, but there exists pgvectorscale,14 a complement of pgvector built for scaling. It is shown15 to have better throughput than Qdrant, a dedicated vector store, though worse latency. PostgreSQL even has a vari-ant dedicated to search, ParadeDB,16 claiming to be an alternative to Elasticsearch. All of this is to say, given the popularity of relational databases, there are many extensions for RDBMSes and related applications worth studying that may be able to improve upon what we present here. They present opportunities that would further affirm our point of relational databases being sufficient for RAG, and also help address the scaling issues we identified. QuackIR focuses on retrieval in RDBMSes and does not currently have a complete set of “peripher-als”. For example, it lacks its own vector encoding capabilities. This can be addressed by develop-ments such as FlockMTL, a DuckDB extension that offers deep integration of LLM capabilities and retrieval-augmented generation. It further demonstrates the promise of DuckDB in retrieval and the power of the relational database’s popularity that resulted in the development of all these extensions. The current lack of integrations also opens opportunities to explore additional retrieval approaches, such as learned sparse retrieval with SPLADE for term expansion and reweighting. Such techniques could likely be in-corporated into QuackIR’s existing sparse pipeline using Anserini’s “fake words” strategy, which en-codes term weights as duplicate occurrences of the term. There is still a lot to explore in this direction. Acknowledgements. This research was supported in part by the Natural Sciences and Engineering Research Council (NSERC). We would like to thank Vivek Alamuri for his early contributions.