Choosing a vector store, and when you do not need one

The vector database market grew quickly enough that adopting one became the default first step in building retrieval. It is worth resisting that default long enough to ask whether the system needs a separate datastore at all, because a second datastore brings synchronisation, consistency and operational cost that a Postgres extension does not.

Start with the database you already run

If you run PostgreSQL, pgvector gives you vector similarity search inside the database that already holds your documents, your tenancy model and your access control. That co-location is worth more than most benchmark differences.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  document_id uuid NOT NULL,
  chunk_index integer NOT NULL,
  content     text NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}',
  embedding   vector(1536) NOT NULL
);

-- HNSW: better recall and query speed than IVFFlat, slower to build.
-- Match the operator class to your distance metric — cosine here.
CREATE INDEX ON document_chunks
  USING hnsw (embedding vector_cosine_ops);

-- The index that actually matters in a multi-tenant system: the one
-- that makes the metadata filter cheap.
CREATE INDEX ON document_chunks (tenant_id, document_id);

The single strongest argument for this approach is that tenant isolation, row-level security, joins to your domain tables and your existing backup and recovery all apply automatically. A separate vector store means reimplementing each of those.

What a dedicated vector database genuinely buys

Those are real advantages at real scale. They are not advantages for a corpus of fifty thousand chunks, where the honest comparison is one datastore against two.

Metadata filtering is the decision that bites

Almost every production retrieval system needs filtered search: this tenant's documents, this date range, this document type. How a store implements that filter matters far more than its raw similarity throughput.

• Post-filtering retrieves the top K by similarity, then discards non-matching rows. If the tenant's documents are not in the global top K, the result is empty — a severe correctness failure in multi-tenant systems.
• Pre-filtering restricts the candidate set before similarity search. Correct, and what you want.
• Query with a filter, not by retrieving broadly and filtering in application code — that pattern silently loses relevant results.
• Test filtered recall explicitly with a tenant whose documents are not globally most similar; this is where post-filtering systems fail.

Hybrid search improves answers more than index choice

Dense embeddings capture meaning and are weak on exact tokens — product codes, error identifiers, names, acronyms. Keyword search is the opposite. Combining them fixes a category of failure that no amount of embedding tuning will.

-- Reciprocal rank fusion: combine dense and keyword rankings without
-- needing the two scores to be on comparable scales.
WITH dense AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
  FROM   document_chunks
  WHERE  tenant_id = $2
  ORDER  BY embedding <=> $1 LIMIT 50
),
keyword AS (
  SELECT id, ROW_NUMBER() OVER (
           ORDER BY ts_rank(to_tsvector('english', content),
                            plainto_tsquery('english', $3)) DESC) AS rank
  FROM   document_chunks
  WHERE  tenant_id = $2
    AND  to_tsvector('english', content) @@ plainto_tsquery('english', $3)
  ORDER  BY rank LIMIT 50
)
SELECT COALESCE(d.id, k.id) AS id,
       COALESCE(1.0/(60 + d.rank), 0) + COALESCE(1.0/(60 + k.rank), 0) AS score
FROM   dense d FULL OUTER JOIN keyword k ON d.id = k.id
ORDER  BY score DESC
LIMIT  10;

That is roughly thirty lines of SQL against the database you already run, and it typically produces a larger improvement in answer quality than switching vector stores would.

Chunking affects quality more than storage does

Retrieval quality is largely decided before anything is stored. A well-chosen chunking strategy in pgvector beats a poor one in the most sophisticated vector database available.

• Split on document structure — headings, sections — rather than at fixed character counts that cut mid-sentence
• Overlap adjacent chunks modestly so a fact spanning a boundary is retrievable from either side
• Keep a chunk's heading path in its text, so an isolated chunk still carries its context
• Store the document title and section in metadata, and include them in what you pass to the model
• Match chunk size to the question shape: specific factual questions favour smaller chunks, synthesis favours larger

A defensible order of work

1. 01Build retrieval on pgvector, or whatever your primary database offers.
2. 02Get chunking right, measured against an evaluation set rather than by inspection.
3. 03Add hybrid search. This is usually the largest single quality gain available.
4. 04Add reranking if evaluation still shows relevant passages ranked below irrelevant ones.
5. 05Only then consider a dedicated vector store, and only if a measured constraint — corpus size, query latency under load, contention with transactional traffic — justifies the second datastore.

Most systems never reach step five, and the ones that do arrive there with a clear measurement showing why, rather than a preference formed before the first document was indexed.