Why we chose DuckDB over pgvector for embedded semantic search at Josh AI

Why we chose DuckDB over pgvector for embedded semantic search at Josh AI

When we were building the candidate search engine at Josh AI, the obvious choice for a vector store was pgvector. We already had Postgres for transactional data, the operational story was simple, and most of the team had pgvector muscle memory from prior projects. We ended up choosing DuckDB instead. This post is the honest write-up of why.

The problem we were actually solving

Our search workload looks like this:

Recruiters paste natural-language queries like "senior React developer with AWS and a strong design eye, based in Bangalore."

A LangGraph workflow rewrites the query, generates a Cohere 1024-d embedding, and runs a similarity search against a per-campaign candidate pool that ranges from a few hundred to a few thousand vectors.

Top-K results get reranked by a Groq Llama-3.x model with a structured prompt.

The candidate count grows over the lifetime of a campaign but rarely exceeds the low tens of thousands per index.

So: many small indexes, low write throughput, fast read latency, and a strong requirement that the whole thing keeps working even when one of our LLM providers has a bad day.

The three options

pgvector

What we liked: single database, no extra moving parts, indexing is just SQL, transactional consistency with the rest of our domain data. We could re-use the connection pool.

What gave us pause: per-campaign indexes meant we'd either be partitioning a single big table or spinning up many small ones. Neither felt great. Vacuum behaviour on a write-heavy embedding column was a known sharp edge. And ANN performance under our specific query mix wasn't obviously better than alternatives.

FAISS

What we liked: raw speed, mature, well understood. We use it elsewhere at Introva.

What gave us pause: it's a library, not a database. We'd have to wrap it in our own persistence layer, handle process lifecycle, deal with mmap vs in-memory tradeoffs, and write custom code for the per-campaign isolation we wanted. For a product team with limited capacity, that's a lot of bespoke infrastructure.

DuckDB

What we liked: columnar, embedded, file-per-index gave us natural per-campaign isolation, can read Parquet directly, and the VSS extension gave us approximate nearest neighbour out of the box. We could ship a campaign's vector index as a single file, snapshot it, version it, move it around.

What gave us pause: smaller community than pgvector for this specific use case. Less battle-tested in production at our scale. Operational story for backups was less obvious.

What tipped it

Three things, in order of weight:

1. Per-campaign isolation came for free. Every campaign gets its own .duckdb file. Backup is a file copy. Deletion is a file delete. Compaction is rewriting one file. We never have to worry about one campaign's data poisoning another's query plan or vacuum schedule.

2. The read profile fit DuckDB's strengths. Our queries are bursty, embarrassingly parallel within a campaign, and read-mostly. DuckDB's columnar execution model and zero-config in-process design meant we could embed it directly in our FastAPI workers without an extra network hop.

3. We weren't actually saving operational complexity with pgvector. The hypothetical "just put it in Postgres" win evaporates the moment you start sharding by campaign or splitting reads to a replica. Once we accepted we'd be doing index-per-campaign either way, DuckDB's file-per-index model was simpler, not more complex.

What we accepted as a tradeoff

Smaller ecosystem. Tooling for monitoring, observability, and schema management around DuckDB is thinner. We've written more glue than we'd have written for Postgres.

No transactional guarantees with our main database. Index updates are eventually consistent with our Postgres domain data. For our use case (recruitment campaigns, not financial transactions), that's fine.

In-process means co-located. A worker process holds an open handle to the campaign file it's reading from. We had to be careful about worker recycling and file locking.

How it shaped the rest of the stack

The file-per-index model pushed us toward a few patterns that turned out to be useful independently:

ARQ task queue for index builds. A campaign's index is rebuilt asynchronously when its candidate pool changes. The rebuild writes to a new .duckdb file and atomically swaps it in. No locking, no online rebuilds.

Per-campaign cache keys. Query caching falls out naturally because the unit of caching is the same as the unit of indexing.

Confidence-gated routing. If the top-K similarity scores from DuckDB are below a threshold, we fall back to a broader query (or, in some cases, a Groq-mediated rewrite). The clean per-index abstraction made this easy to layer in.

What I'd change in v2

Migrate to DuckDB's native VSS HNSW index for the larger campaigns. We started with brute-force cosine because our pools were small enough. As pool sizes grow, the HNSW path becomes worth the indexing cost.

Better observability around index freshness. We have it; it's not as good as it could be.

Reconsider pgvector for the global "all candidates ever" index. There's a separate use case where we want to search across all candidates a recruiter has ever interacted with. That has different access patterns and pgvector might genuinely be the right tool.

The lesson

The default answer ("use the database you already have") is often correct, and was almost correct for us. What pushed us away from it wasn't performance — it was that our access pattern was naturally file-per-thing, and DuckDB models that elegantly. When the unit of work in your domain maps cleanly onto the unit of storage in your tool, the rest of the system gets simpler.

If you're making a similar decision, the question I'd ask is: what is the natural unit of isolation in your domain? If the answer is "one big shared corpus," pgvector is probably right. If it's "lots of small independent corpora," look harder at DuckDB.