Vector Databases: Do You Need One, and Which Kind

A client came to us last year with a retrieval system that returned garbage for roughly a third of queries. They’d done everything the tutorials said: OpenAI embeddings, Pinecone, top-k of 5, stuff it in the prompt. The fix had nothing to do with the database. Their chunker was splitting on 1,000 characters with no overlap, cutting tables in half, and no keyword channel existed at all, so any query containing a product SKU returned nothing useful.

That’s the pattern we keep seeing. Teams reach for a vector database as the first decision and treat everything else as detail, when the database is usually the least important part of the stack. It matters eventually. It rarely matters first.

So here’s the honest version: when you actually need one, what the storage really costs, and how to choose between the two or three options that are genuinely different from each other.

When you actually need a vector database

Three questions, in order.

First: does your corpus fit in a context window? Modern long-context models handle 200k tokens comfortably. If your entire knowledge base is a 40 page handbook, retrieval is an optimisation, not a requirement. Stuff the whole thing in and cache the prefix. You’ll get better answers than any top-5 retrieval, and you’ll ship in an afternoon.

Second: is your query pattern actually semantic? Users searching an e-commerce catalogue by product name, SKU or brand want lexical matching. BM25 in Postgres, Typesense or Meilisearch handles that better and cheaper than embeddings ever will. Semantic search earns its place when the user’s words and the document’s words differ: “my payment bounced” against a page titled “Failed direct debit resolution”.

Third: how often does the corpus change? If it’s a static docs site rebuilt nightly, you can precompute embeddings into a flat file and load them into memory. A 50,000 chunk index at 768 dimensions is 150 MB. That fits in a Lambda. No database required.

If you answered “no, yes, constantly”, you need real embeddings storage. Otherwise you’re provisioning infrastructure to solve a problem you don’t have.

The storage math nobody does first

Work out the number before you pick a vendor. It decides the answer more often than any feature comparison does.

• text-embedding-3-small at 1,536 dimensions, float32: 6,144 bytes per vector.
• 1 million chunks: about 6 GB of raw vectors.
• HNSW adds graph edges on top. At m = 16 budget roughly 30 to 50 percent overhead. Call it 9 GB total.
• Add your text, metadata, and Postgres row overhead. Realistically 12 to 15 GB.

That’s a medium Postgres instance, not an infrastructure project. And you have levers. OpenAI’s v3 embedding models are trained with Matryoshka representation learning, so you can request 512 dimensions instead of 1,536 and lose surprisingly little quality on general retrieval. That’s a 3x cut. pgvector’s halfvec type stores 2 bytes per dimension instead of 4, halving storage again, and in our testing on a support-ticket corpus the recall@10 difference was under one point.

Binary quantization goes further: 1 bit per dimension, 32x smaller, then rescore the top 200 candidates against full-precision vectors to recover accuracy. Cohere’s embed models are explicitly trained for this. It’s a real technique at 50M+ vectors. It’s pointless at 200k.

pgvector vs Pinecone: the comparison that actually matters

Most pgvector vs Pinecone articles compare queries per second. That’s the wrong axis for 90 percent of projects. At a few hundred thousand vectors, both are fast enough that network latency dominates.

Here’s what we weigh instead.

Choose pgvector when

You already run Postgres. Your retrieval needs to join against application data (only chunks from documents this user’s org owns, that aren’t archived, updated in the last 90 days). You want transactional consistency between a document and its chunks, so a delete is a delete, not an eventually-consistent tombstone. And you’d rather have one backup strategy than two.

pgvector 0.8.0 fixed the thing that used to make it a bad choice: filtered search. Before iterative index scans, a selective WHERE clause would make HNSW return fewer than LIMIT rows because the filter ate the candidate set. Now you set hnsw.iterative_scan and it keeps scanning until it has enough. That single change moved pgvector from “fine for demos” to “fine for production” on filtered workloads.

Choose a dedicated store when

You’re past 20 to 50 million vectors, you have thousands of tenants needing hard isolation, or your write pattern is a constant firehose that would bloat and vacuum-thrash your primary. Pinecone’s serverless tier separates storage from compute, so idle namespaces cost almost nothing. That’s genuinely useful if you have 5,000 customers of whom 200 are active daily. Qdrant is the one we reach for on self-hosted work: the payload filtering is excellent, quantization is first-class, and the Rust core is predictable under load. Turbopuffer is worth a look if your access pattern is bursty and object-storage-backed economics appeal.

Weaviate and Milvus are capable and heavier. Running Milvus properly means running etcd, object storage and several node types. If you’re not at the scale that justifies that, you’ve bought a distributed system to store 400 MB of floats.

The ones people forget

SQLite with sqlite-vec is excellent for desktop apps, CLI tools and edge deploys. DuckDB’s VSS extension is great for analysis. LanceDB gives you columnar storage over Parquet-like files with no server at all, which is a very good fit for batch pipelines. And OpenSearch or Elasticsearch already does hybrid search with a mature BM25 implementation, which matters more than most vector-native stores admit.

Schema and index settings that actually move recall

Here’s a production-shaped pgvector schema. Note the generated tsvector column: you want the keyword channel from day one, not bolted on in month three.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE doc_chunk (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  document_id uuid NOT NULL REFERENCES document(id) ON DELETE CASCADE,
  content     text NOT NULL,
  -- halfvec: 2 bytes per dimension. 1536 dims = 3KB, not 6KB.
  embedding   halfvec(1536) NOT NULL,
  fts         tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Build this AFTER bulk loading. Building on an empty table then inserting
-- 2M rows took us 4x longer than loading first and indexing after.
CREATE INDEX docchunkembeddingidx ON docchunk
  USING hnsw (embedding halfveccosineops)
  WITH (m = 16, ef_construction = 64);

CREATE INDEX docchunkftsidx ON docchunk USING gin (fts);
CREATE INDEX docchunktenantidx ON docchunk (tenant_id);

Runtime knobs, set per session or per transaction:

SET hnsw.ef_search = 100;              -- default 40. Higher = better recall, slower.
SET hnsw.iterativescan = relaxedorder; -- pgvector 0.8+, essential with WHERE clauses
SET hnsw.maxscantuples = 20000;        -- ceiling so a bad filter can't scan the table

m = 16 and efconstruction = 64 are sane defaults. Raising efconstruction to 128 improves the graph and roughly doubles build time. On a 2.4M row table on 8 vCPUs, that was the difference between about 20 minutes and about 40 with maxparallelmaintenanceworkers = 7. Worth it once. efsearch is the knob you tune weekly, and it’s per-query, so you can run a cheap 40 for autocomplete and a 200 for the RAG path.

Skip IVFFlat unless you have a hard index-build-time constraint. HNSW costs more to build and more memory, and it wins on recall at every latency point we’ve measured.

Filtering is where implementations quietly break

Pre-filter or post-filter is the question that separates people who’ve run this in production from people who’ve read about it.

Post-filtering (fetch top 100 by vector, then apply WHERE tenant_id = ...) is fast and silently wrong. If a tenant owns 0.1 percent of your corpus, your top 100 will contain zero of their rows most of the time, and the endpoint returns an empty array with a 200 status. Nobody notices until a customer complains.

Pre-filtering is correct and can be slow, because it fights the ANN graph. The answers, in rough order of preference:

1. Partition by tenant. Postgres declarative partitioning on tenant_id with a per-partition HNSW index gives you pre-filtering for free, because the planner prunes to one partition. This is the cleanest option up to a few thousand tenants.
2. Iterative scans. pgvector 0.8’s relaxedorder mode handles moderate selectivity well. Cap it with maxscan_tuples so a pathological filter doesn’t turn into a sequential scan.
3. Namespaces. Pinecone namespaces and Qdrant collections do the same job at the vendor layer. Cheap, effective, and the reason people with many small tenants often land on a managed store.

Hybrid search and reranking beat index tuning

Pure dense retrieval fails on exactly the queries your users care about most: error codes, part numbers, proper nouns, negations. Reciprocal rank fusion is about thirty lines of SQL and reliably fixes it.

WITH q AS (SELECT websearchtotsquery('english', $3) AS tsq),
semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1::halfvec) AS rank
  FROM docchunk WHERE tenantid = $2
  ORDER BY embedding <=> $1::halfvec LIMIT 50
),
keyword AS (
  SELECT c.id, rownumber() OVER (ORDER BY tsrank_cd(c.fts, q.tsq) DESC) AS rank
  FROM doc_chunk c, q
  WHERE c.tenant_id = $2 AND c.fts @@ q.tsq LIMIT 50
)
SELECT c.id, c.content,
       -- k=60 is the constant from the original RRF paper; it works, leave it alone
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS score
FROM doc_chunk c
LEFT JOIN semantic s ON s.id = c.id
LEFT JOIN keyword  k ON k.id = c.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY score DESC LIMIT 20;

Then rerank. Take those 20 candidates, send them to a cross-encoder (Cohere Rerank, Voyage, or a self-hosted bge-reranker), and keep the top 5. A cross-encoder sees the query and document together instead of comparing two independently computed vectors. The quality gap is not subtle. On every project where we’ve measured it, adding a reranker moved answer quality more than any embedding model swap. Budget 100 to 300ms for the call and cache aggressively.

The retrieval layer is also where you enforce grounding. If nothing scores above a floor, return nothing and let the model say so. That’s the cheapest defence against a confident wrong answer. We go deeper on that in guardrails for customer-facing AI.

Operating it: pipelines, re-embedding and evaluation

Embedding is slow, rate-limited and fails. Never do it in the request that accepts the upload. Write the document, enqueue a job, embed in batches of 96 with exponential backoff, and let the UI show “processing”. We’ve written up the general shape of this in queues for webhook processing, and the same rules apply here: idempotency keys, a dead letter queue, and a visible retry count.

Plan for re-embedding, because you will change models. Store embeddingmodel and embeddingversion on every row, and design so two versions can coexist: write new vectors into a second column or a shadow table, backfill in the background, then flip a feature flag. A destructive in-place re-embed on a 5M row table is a multi-hour outage you didn’t schedule.

And measure. Build a gold set: 100 real user queries with the chunk IDs that should be returned, labelled by someone who knows the product. Then compute recall@10 from your index against an exact scan:

-- Ground truth: force pgvector to do exact nearest neighbour
BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SELECT id FROM doc_chunk
WHERE tenant_id = $2
ORDER BY embedding <=> $1::halfvec LIMIT 10;
COMMIT;

Compare the ID sets. If indexed recall@10 is above 0.95, stop tuning the index and go fix your chunking. If it’s 0.7, raise ef_search before you blame the embedding model. Run this in CI against a fixed snapshot so a config change can’t quietly degrade retrieval.