Beyond Quacking: Deep Integration of Language Models and RAG into DuckDB
Anas Dorbani, Sunny Yasser, Jimmy Lin, Amine Mhedhbi
cs.DB, cs.AI, cs.IR
2025-04-02
FlockMTL adds LLM scalar/aggregate functions and first-class PROMPT and MODEL objects to DuckDB. On a Kaggle bank-review scan, batching hits 7x chat maps and 48x embeddings.
Knowledge-heavy analytics mix table lookups with document reading, summarization, and reranking. LLMs made those pipelines easy to prototype. Running them still means gluing a DBMS, a search engine, and an orchestration layer: moving data, managing context, deciding when to cache, and rewriting when latency or cost targets change. The authors compare this to data management before the relational model. Execution choices sit on the engineer, and splitting systems throws away joint optimization.
FlockMTL's answer is to put LLM calls and RAG inside DuckDB SQL. Semantic steps become scalar and aggregate functions. Cost-based optimization owns batching and caching.
It ships as a DuckDB community extension (INSTALL / LOAD). New DDL objects, PROMPT and MODEL, sit beside TABLE and can be Local or Global. Edits are versioned; queries use the latest unless pinned, so application SQL can stay still. Models talk to OpenAI, Azure, or local Ollama.
Scalar functions map one tuple to one value: llmcomplete / llmcompletejson for generation, llmembedding for vectors, llmfilter for booleans, and fusion via rrf, combsum, and related combiners. Aggregates fold many tuples into one: llmreduce, listwise llmrerank, and llmfirst / llmlast. CTEs chain filter, summary, JSON extract, vector scan, BM25, fusion, and rerank in one statement. ASK turns a natural-language question into SQL that uses those functions.
Users write prompts for one tuple or one group. The system wraps them in a KV-cache-friendly meta-prompt with format rules and serialized rows, then packs as many tuples as the context window allows. If the output overflows, the batch shrinks by 10% until it fits; a single overflowing tuple becomes NULL. Caching and deduplicated prediction are named and not spelled out, for space.
This is a four-page VLDB 2025 demo. There is no end-to-end accuracy table and no numeric comparison with LangChain, Palimpzest, or TAG. The hard number is batching: on Kaggle Bank Review, a table scan plus one scalar FlockMTL function reaches up to about 7x for chat-completion maps and 48x for embeddings.
In the demo, a prompt such as "list reviews that mention technical issues and score severity" is compiled by ASK into SQL using llmfilter and friends. A plan inspector shows the auto-chosen batch size. Default serialization is XML, switchable to JSON or Markdown, and the full meta-prompt can be replaced with Jinja. The paper claims this is the first full hybrid-search pipeline inside SQL: embeddings on one path, DuckDB's full-text BM25 on the other, then fusion and llmrerank.
Once LLM calls live in an embedded analytics engine, an analyst can write "filter, then summarize, then extract fields" in SQL instead of dumping tables into Python. Versioned PROMPT and MODEL objects fit teams that edit prompts often and want queries to stay put. The 7x and 48x figures show how wasteful per-row API calls are in analytics; packing and dedup alone take a large bite.
This is incremental systems work, not a new inference algorithm. It is most useful if DuckDB is already the analytics engine. Federated scans of Postgres, MySQL, or Parquet ride DuckDB's extension ecosystem. When the cloud bills per token, batching cuts both latency and spend.
The demo barely measures quality. There is no false-positive rate for llmfilter, no nDCG for rerank, no executable rate for ASK, and no dollar cost. The 7x / 48x numbers come from one scan plus one scalar function; chained CTEs are unmeasured. Packing many rows into one decode can change the output distribution relative to per-row prompts; that bias is not tested.
Backends at the time were OpenAI, Azure, and Ollama. The meta-prompt defaults to XML; effects of JSON or Markdown on accuracy are left to the live demo. Caching and dedup are named without detail, so reproducing the speedups needs the repo. Users can still override batch size in the inspector, so optimization is not fully off their plate.