DuckDB : your RAG fits in a file, not a cluster

A project manager proudly walked me through his RAG architecture last week. Pinecone Starter at €200 a month, Elasticsearch autoscaling on AWS, managed Postgres for the metadata, FastAPI on ECS Fargate, the whole thing orchestrated by LangChain. Volume actually indexed: 84,000 chunks drawn from his company’s technical manuals. That’s, in HNSW float32 over 1536 dimensions, around 500 MB of vectors. Which an entry-level M1 MacBook holds in its RAM without flinching while VS Code, Chrome and Spotify are running.

Welcome to the great infrastructure scam of enterprise RAG.

The stack you’re being sold

The canonical enterprise RAG architecture, 2024 edition, comes in four distinct services, sold as “best of breed” by just about every vendor and every consultant. To answer a user query of the type “find me the invoices for client X that mention a late-payment penalty,” you typically need:

1. A dedicated vector store for semantic search. Pinecone (SaaS, ~€70 to €200/month to start), Qdrant or Weaviate (self-hosted, a Docker container to maintain, RAM-hungry, monitoring to set up). Its one and only role: store embedding vectors and do cosine similarity.
2. A full-text engine for keyword search. Elasticsearch leading the pack, with its 2 GB of RAM minimum and its JVM to tune, or Meilisearch / Typesense as lighter options. Indispensable because semantic search alone systematically misses exact terms: if you search for “invoice FR-2025-04711,” an embedding will return vaguely similar invoices, not the right one. BM25 finds the exact number. Any serious RAG needs both mechanisms in parallel.
3. A relational database to store the tabular metadata and enable structured filtering. Because neither the vector store nor the full-text index can answer “invoice for client 42, paid, dated between January and March 2025, amount over €1,000.” So Postgres or MySQL on top, with the consistency to maintain between the three systems.
4. An orchestration layer to make all of it talk to each other. LangChain, LlamaIndex, or your own FastAPI code. Its cognitive load quickly becomes intense: receive the query, call the vector store, call the BM25 index, call the SQL database, retrieve the metadata for all the candidates, filter, do the hybrid re-ranking via Reciprocal Rank Fusion, return the final top-K, call the LLM with the assembled context.

Four services to monitor. Three data sources to synchronize. A custom reconciliation layer to code and maintain. Three backups to orchestrate consistently or risk a misaligned index. And of course, three network calls that add up in latency on every user query, with partial-error handling worthy of a banking distributed system: “the vector store responds but not Elasticsearch, now what?”

Monthly cost on a mid-sized project: between €150 and €400 of cloud infrastructure. Skills required to operate it: a DevOps engineer, a data engineer, a backend dev. Time to production: between two and four weeks of pure plumbing, before even having indexed the first useful document.

This is the stack taught today in 90% of RAG tutorials, found in 90% of agency PoCs, and billed to 90% of the SME clients who never needed it.

DuckDB, or the burial of complexity

DuckDB is an embedded analytical database, created in 2019 within Amsterdam’s Centrum Wiskunde & Informatica, and which in five years has become the reference tool of modern data engineering. The pitch fits in one sentence: it’s SQLite, but for analytics instead of transactions.

Like SQLite: zero server, zero configuration, a single library embedded in your application, one .duckdb file that holds your whole database. No daemon to launch, no port to open, no user permissions to manage, no updates to coordinate.

Unlike SQLite: a columnar engine (not row-based), vectorized, optimized for queries that aggregate millions of rows in a few milliseconds. The SQL is very close to PostgreSQL: analytical windows, recursive CTEs, native ARRAY and STRUCT types, built-in JSON, plus a few well-thought-out syntactic sugars no other DBMS offers (SELECT * EXCLUDE, GROUP BY ALL, direct querying of a remote CSV or Parquet).

All of this has been known to the data community for three years. What’s less known is the silent revolution that has taken place on the RAG front since 2024: DuckDB has become one of the reference tools for this specific use, and for five concrete reasons.

What DuckDB takes in, what DuckDB gives back

Before getting into the SQL, let’s clear up an ambiguity that comes up systematically in my readers’ questions: DuckDB stores three things in the same row, and it takes three things as input.

For each piece of indexed document (typically a chunk of 200 to 800 tokens), your application code sends DuckDB:

1. The plain text of the chunk, in standard UTF-8, in a TEXT column. This is what will be sent to the LLM as context at generation time.
2. The precomputed embedding vector of the chunk, as an array of floats, in a FLOAT[N] column. This is the semantic index of the text, the thing that enables similarity search.
3. The structured metadata (date, author, client, source, page, language, etc.) in classic SQL columns. This is what enables filtering before or after the vector search.

A crucial point that surprises beginners: DuckDB does not compute the embeddings, exactly like PostgreSQL with pgvector or like Pinecone. Vectorization is an application step that happens upstream, generally via an API call to OpenAI, Voyage AI, Mistral Embed, Cohere, or a local model via SentenceTransformers. DuckDB receives the already-computed vector and stores it. Same at query time: your application vectorizes the user question with the same embedding model as the one used at indexing (changing models between the two steps completely breaks the search), then sends that vector to DuckDB for comparison.

Concretely, an insertion looks like this:

INSERT INTO docs (id, content, embedding, source_doc, page, date, client_id)
VALUES (
  42,
  'The contract provides for a late-payment penalty of 0.5% per business day beyond the contractual deadline of 30 days following invoice issuance.',
  [0.0231, -0.1872, 0.0938, ...]::FLOAT[1536],  -- vector precomputed upstream
  'contract_Dupont_2025-03.pdf',
  4,
  '2025-03-15',
  42
);Code language: SQL (Structured Query Language) (sql)

The upstream pipeline that produces these three entries (PDF extraction, chunking, vectorization) is a subject in itself. I detailed it in two dedicated articles I’ll point you to if you’re starting from scratch: RAG explained like no one else does for the overall vision of the pipeline, and Visual parsing: why your PDFs are sabotaging your RAG for the critical step of clean text extraction from your source documents.

The rest of this article assumes we’re at that point: you have your text chunks, your precomputed vectors, your metadata. The question we deal with from here on is: where do I put them and how do I query them?

Native vector search

The vss extension (Vector Similarity Search), maintained by the DuckDB team itself although still labeled experimental in the documentation, adds to DuckDB an HNSW index (Hierarchical Navigable Small World), which is the standard algorithm of approximate vector search, the same one used by Qdrant, Pinecone or pgvector. The choice of the embedding model that feeds this index obviously remains an independent decision, to be settled upstream. Installation and use:

INSTALL vss; LOAD vss;

CREATE TABLE docs (
  id INTEGER,
  content TEXT,
  date DATE,
  client_id INTEGER,
  embedding FLOAT[1536]
);

CREATE INDEX hnsw_idx ON docs USING HNSW (embedding);

-- Find the 5 semantically closest chunks
SELECT id, content,
       array_cosine_similarity(embedding, ?::FLOAT[1536]) AS score
FROM docs
ORDER BY score DESC
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

You get a complete vector store, functional, performant, in a 30 MB embedded library. For the majority of enterprise RAG projects (fewer than 10 million indexed chunks), it’s strictly equivalent in performance to the dedicated solutions, and infinitely simpler to deploy.

Hybrid RAG made trivial

This is undoubtedly the point where DuckDB crushes the competition. The fts extension adds BM25 full-text search. Combined with vss and the standard SQL engine, it lets you write in a single query what required a whole application choreography in the four-service stack:

SELECT content,
       array_cosine_similarity(embedding, ?::FLOAT[1536]) AS vec_score,
       fts_main_docs.match_bm25(id, 'user query') AS bm25_score
FROM docs
WHERE date >= '2025-01-01'
  AND client_id = 42
  AND langue = 'fr'
ORDER BY (0.7 * vec_score + 0.3 * bm25_score) DESC
LIMIT 10;Code language: SQL (Structured Query Language) (sql)

Semantic search. BM25 search. Structured filtering on three metadata fields. Weighted re-ranking. Final top-K. All in one SQL query, locally, in a few milliseconds. No network call, no Python-side reconciliation, no distributed transactions to coordinate. Compare with the canonical stack above: we’ve gone from four services to orchestrate to an eight-line SQL statement. I’d already opened the debate on SQL/BM25 alternatives to the dedicated vector database; DuckDB now provides the canonical implementation of it.

The Text-to-SQL pattern that closes the loop

The other massive use of DuckDB in the AI world is RAG-augmented Text-to-SQL. The typical pattern:

1. The user asks a question in natural language (“what are the 10 products with the highest margin over the past quarter?”).
2. A RAG retrieves the relevant schema of the database (tables, columns, sample values, business descriptions) from a vector store.
3. The LLM generates the appropriate SQL query.
4. DuckDB executes it directly, because it can read CSV, Parquet, JSON, Excel, and even connect to a remote MySQL or Postgres in read-only mode, all without prior ETL.
5. The LLM comments on the returned result.

DuckDB stands out in this stack because it ticks all the boxes at once. Its SQL dialect is very standard, so the public LLMs (Claude, GPT, Gemini) master it better than exotic dialects. Its execution is trivially sandboxable in read-only mode, which prevents a creative LLM from running a DROP TABLE. Its error messages are explicit, which enables the self-correction loop (the LLM regenerates the query from the error message). And its marginal cost of execution is nil, since there’s no network connection or authorization to validate.

All the major AI frameworks now have native DuckDB integration: LangChain, LlamaIndex, Vanna.ai, PandasAI, dbt, Dagster. This isn’t a niche fad, it has become a de facto standard that no one announced loudly.

The operational comparison

Here, for a mid-sized enterprise RAG project (a few hundred thousand indexed chunks, the typical application load of an SME), is the concrete delta between the two approaches:

A factor of 10 to 20 on infrastructure cost. A factor of 5 to 10 on RAM consumption. A factor of 10 on latency. A factor of 5 on time to production. And a reduction in technical headcount that takes the project from “you need a team” to “a senior developer handles it on Tuesday afternoon.”

For strictly equivalent performance below 10 million indexed vectors. Which is the exact scope of 95% of real enterprise RAG projects.

The sovereignty argument that closes the debate

Beyond the mere infrastructure saving, DuckDB solves a problem the canonical stack systematically makes worse: data localization.

When you index a client’s internal manuals, their support tickets, their invoices, their contracts, into Pinecone, you are by construction sending the entire semantic content of those documents to a service hosted in the United States, hence legally subject to the CLOUD Act. Embeddings aren’t encrypted at rest for querying, they are vector representations that can be exploited directly. Anyone with access to your Pinecone index can approximately reconstruct the content of your original documents through embedding inversion (a technique documented since 2023, with several public papers).

For a client subject to the GDPR, to HDS, to NIS 2, or simply mindful of the confidentiality of their strategic documents, it’s a contractual time bomb. The DPO opens a ticket, the project is frozen for six months, the PoC buried along the way.

DuckDB completely reverses this equation. The database fits in a local file. The server it runs on can be a European VPS at €15 a month, or even a Docker container on the client’s NAS. The embeddings never leave the client’s infrastructure. The only network egress is the API calls to the LLM for final generation, which can themselves be routed to Claude on AWS Bedrock Paris, Mistral in France, or a local open-source model (Llama, Mixtral, Qwen) if sensitivity demands it.

It’s the direct application of what I wrote in my article Sovereign AI: why local open source has become the only choice for independence in 2026. The RAG’s data infrastructure must follow the same logic as the inference model. Letting your embeddings out of your house is exactly as serious as letting your prompts out.

“What about horizontal scaling, then?”

That’s the legitimate question of the backend developer who has read this far. If DuckDB is an embedded library and not a server, how does it behave when my FastAPI application scales across ten containers behind a load balancer?

The proven deployment pattern fits in three words: concurrent read-only opening. The .duckdb file that holds your index is typically stored on a shared volume (NFS, EFS on AWS, Azure Files, or a Kubernetes RWX volume) or loaded into RAM at each container’s startup from an S3 or MinIO-compatible bucket. DuckDB perfectly supports the simultaneous opening of the same file in READ_ONLY mode by N distinct processes, which can all read and query in parallel without lock or coordination.

import duckdb
con = duckdb.connect('/mnt/shared/rag-index.duckdb', read_only=True)
# Multiple FastAPI containers can do this simultaneously
Code language: PHP (php)

The indexing, for its part, happens in a separate batch job (nightly cron, GitHub Action, dbt pipeline) that produces a new versioned .duckdb file. The application containers switch over to the new version at the next startup, or via a hot-reload if you insist. It’s exactly the build-deploy-serve pattern we were already applying to Lucene indexes fifteen years ago, infinitely simpler to operate.

For 99% of SME B2B workloads, a single application container with a local read-only .duckdb file serves several hundred requests per second without flinching. Horizontal scaling isn’t even a question.

The honest limits

No tool is universal, and I’d rather list the limits before Pinecone’s salespeople take care of it.

The “experimental” status of the vss extension. Let’s be precise: despite its massive adoption in production since 2024, the DuckDB team still officially labels vss as experimental in its documentation, including in the recent 2026 releases. In practice, the performance is there and stability poses no problem observed in the field. But it means two things: API changes can still introduce compatibility breaks on version upgrades, and the vendor SLAs you might sign with your client have to account for it. For a PoC or a reasoned production deployment, it changes nothing. For a core-business critical stack with a contractual 99.99% SLA, you write an explicit paragraph in the technical documentation.

HNSW prefiltering. A precise technical point that will please the purists: by default, the vss extension applies the WHERE clauses after the search in the HNSW index, not before. The concrete consequence: on a very selective metadata filter (typically “documents for client 42, dated 2025”) combined with a vector search at K=10, you can end up with fewer than 10 final results, because the HNSW index surfaced 10 semantically close candidates, of which only 3 satisfy the metadata filter. The dedicated vector stores (Qdrant, Weaviate, recent pgvector) handle prefiltering natively and don’t have this issue. A community extension hnsw_acorn fixes this limitation on the DuckDB side, but it isn’t yet integrated into the core. In practice, you work around it by increasing K (K=100 instead of K=10) then filtering afterward. Negligible computational overhead on SME volumes. And there’s an additional trick the purists will appreciate: since DuckDB is a beast on columnar scans, when the metadata filter is very restrictive (typically a single client that weighs only a few thousand rows out of the total), it’s often faster and more precise to disable the HNSW index and do an exact linear scan on the filtered subset, rather than going through the approximate index. You force this with a simple SET hnsw_enable_experimental_persistence = false on the session, or by using a sub-SELECT that materializes the filter first. On massive volumes with very selective filters, this is the only scenario where the four-service stack keeps a theoretical technical advantage today, and even then, this workaround neutralizes it in most cases.

DuckDB is not designed for high-frequency concurrent writes. A single writer at a time on the file. So there’s no point imagining DuckDB as the backend of your WooCommerce shop. Its zone of relevance remains analytical: a script or an application that opens the database, runs its heavy queries, and closes.

The HNSW index of the vss extension is not incremental on massive insertions. If you index a Twitter feed in real time at 10,000 new vectors per minute, you’ll have to rebuild the index periodically. Not dramatic on nightly batches, a deal-breaker on real-time streaming.

Beyond 50 to 100 million vectors, you leave DuckDB’s zone of relevance and it becomes legitimate to move to a dedicated vector store. At that scale, other more fundamental RAG pathologies start to show up anyway, to the point where the choice of storage engine becomes secondary. But let’s be honest: if your project indexes 100 million vectors, you’re no longer an SME and you have the means to have a dedicated data team.

Very high-concurrency multi-tenant management (1,000 simultaneous queries per second on the same database) is not the target. If your application has to serve 1,000 concurrent users on the same RAG database, you’re in the rare 1% of cases that genuinely justify a dedicated stack. Mainstream B2C SaaS is typically in this category. French SME B2B, which represents the overwhelming majority of projects, never is.

There you go. These limits cover maybe 5% of enterprise RAG projects. The remaining 95% had strictly no reason to pay €200 a month to Pinecone.

The quote that sums it all up

Jordan Tigani, former architect of BigQuery at Google and today co-founder of Motherduck (the cloud version of DuckDB), published in 2023 an essay that has remained famous in data circles: Big Data is Dead. His thesis: the majority of data projects in the world run on volumes that fit in the RAM of a laptop. We’ve normalized the use of Spark, Hadoop, Snowflake and Kubernetes out of habit, out of mimicry, out of skill signaling, but almost never out of real necessity.

This analysis transposes entirely to RAG. The majority of enterprise RAG projects index fewer than 100,000 documents. That fits in 2 GB of RAM. That fits in a file on a NAS. That fits in a Hetzner instance at €5 a month.

The four-service stack being sold today to every French SME doesn’t exist to solve a technical problem. It exists to sell recurring SaaS, to justify DevOps engagements, to inflate project budgets, and to let consultants bill “cloud-native architectures” on use cases that don’t warrant it.

DuckDB isn’t a technical alternative. It’s an economic re-education of the market.

To get started this afternoon

If you’re reading this with a RAG project asleep in a PowerPoint, or worse, in the middle of paying €300 a month to a Pinecone + Elasticsearch + RDS combo for 50,000 chunks, here’s the procedure:

brew install duckdb     # macOS
apt install duckdb      # Debian/Ubuntu

duckdb my_base.duckdbCode language: Bash (bash)

Then in the REPL:

INSTALL vss; LOAD vss;
INSTALL fts; LOAD fts;Code language: SQL (Structured Query Language) (sql)

You’ve just installed a vector store, a full-text engine and a relational SQL database. All in three commands, without opening a single port, without creating a single cloud account, without signing a single DPA. Now load your embeddings and write your first hybrid query.

Enterprise RAG has just gone back to being what it always should have been: a problem of well-written SQL, not a six-figure infrastructure project.

What’s left is explaining to your client why you’re billing 3 days instead of 3 weeks. That, as always, is the real problem.

And in passing, it’s also the exact definition of what a senior engineer has become in 2026. Value is no longer measured by the number of Kubernetes microservices he knows how to stack, nor by the number of Grafana dashboards he knows how to orchestrate. It’s measured by the number of lines of infrastructure he knows how to remove to solve the exact same problem. At the exact opposite of vibe coding, where layers of abstraction are stacked to the point of absurdity, the best data architects I know in 2026 are the ones who make their own role progressively useless, by turning distributed stacks of 12 services into 200-line Python scripts that fit in a single file.

It’s less glamorous to put on LinkedIn. It’s infinitely more profitable for the client. And it’s exactly what half the tech industry still refuses to admit, because its revenue structure rests on the opposite.