Choosing a vector database.

What actually matters when choosing a vector database?

What matters is whether the store returns the right passages under your real filters, and whether your team can run it safely. Raw query speed on an unfiltered benchmark is rarely the deciding factor for business document search.

How do the main options compare?

Each option below is a reasonable choice in the right situation. The table summarises documented capabilities, not benchmark results.

The pgvector README notes that HNSW gives a better speed-recall trade-off than IVFFlat but builds more slowly and uses more memory. HNSW itself, described by Malkov and Yashunin, searches a layered graph from coarse to fine, which is why it wants its working set in memory.

What is the filtering trap in approximate search?

Approximate indexes usually find nearest neighbours first and apply your WHERE clause afterwards, so selective filters can return far fewer results than you asked for. The pgvector documentation gives the arithmetic: if a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only about 4 rows will match on average. A query asking for the top 20 passages a user is allowed to see may get four, and nothing in the result tells you so.

Fixes, depending on the engine:

• Iterative scans. pgvector 0.8.0 and later can keep scanning the index until enough rows pass the filter, up to a configured limit.
• Partitions or partial indexes. For a small number of filter values, a partial index per value; for tenants, list partitioning or separate tables. The README warns that sharing one approximate index between tenants lets one tenant’s vectors affect another’s recall and speed.
• Filter-aware indexes. Qdrant builds extra filter-aware graph links from payload indexes, but only if the payload index exists before data is ingested, according to its indexing documentation.

A minimal pgvector setup for 3,072-dimension embeddings, which exceed the 2,000-dimension limit for vector indexes, stores half-precision vectors (indexable up to 4,000 dimensions):

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id         bigserial PRIMARY KEY,
    tenant_id  int NOT NULL,
    doc_id     text NOT NULL,
    body       text NOT NULL,
    updated_at timestamptz NOT NULL,
    embedding  halfvec(3072) NOT NULL
);

CREATE INDEX ON chunks USING hnsw (embedding halfvec_l2_ops) WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 100;
SET hnsw.iterative_scan = relaxed_order;

SELECT id, doc_id, body
FROM chunks
WHERE tenant_id = $1
ORDER BY embedding <-> $2
LIMIT 20;

OpenAI’s embeddings guide states its embeddings are normalised to length 1, so ordering by L2 distance gives the same ranking as cosine similarity. Check this for whichever embedding model you use before choosing a distance operator.

How do you benchmark a vector database on your own data?

Measure two kinds of recall, plus latency under load, on your own corpus. ANN recall compares the index with exact search on the same embeddings; retrieval recall compares the final results with human relevance labels, as set out in RAG retrieval evaluation. Public results such as ANN-Benchmarks, which plots recall against queries per second, are a useful map of algorithms but use standard datasets without your filters.

1. Use your real corpus and embedding model. Embed a representative sample, and generate a version at your expected size in two years.
2. Use real queries and filters. Replay logged queries with the tenant, permission and date filters they actually carry.
3. Compute ground truth by exact search. Brute-force top k on the same vectors gives the answer the index should approximate.
4. Sweep the search parameter. Vary ef_search (or the engine’s equivalent) and record ANN recall@10 against p95 latency at your expected concurrency.
5. Test the unglamorous operations. Bulk re-embedding, deletes under load, backup and restore, and a version upgrade.

import time
import numpy as np

def exact_top_k(q, X, k):
    sims = X @ q                           # normalised vectors: dot product equals cosine
    return set(np.argpartition(-sims, k)[:k].tolist())

def ann_recall_and_p95(queries, X, search, k=10):
    """search(q, k) must return row indices into X, with the same filters applied to X."""
    recalls, latencies = [], []
    for q in queries:
        start = time.perf_counter()
        got = set(search(q, k))
        latencies.append(time.perf_counter() - start)
        recalls.append(len(got & exact_top_k(q, X, k)) / k)
    return float(np.mean(recalls)), float(np.percentile(latencies, 95))

Run the benchmark with and without filters. A configuration that reaches high recall unfiltered and collapses under a 5% filter is the most common surprise.

What does a vector index cost to run?

Memory is the main cost driver, and it is easy to estimate. Raw vector size is rows times dimensions times bytes per value: one million chunks of 3,072-dimension float32 vectors is about 12.3 GB, and about 6.1 GB at half precision, before graph links and the text itself. Reducing dimensions, using half precision or quantising vectors cuts this, at some cost in recall that your benchmark should measure.

Other running costs are operational: rebuilding indexes when you change embedding model (plan to run old and new indexes side by side during the switch), vacuuming after heavy deletes (pgvector notes HNSW vacuuming can be slow and suggests reindexing first), and backups. Embedding and model calls usually cost more than storage; see managing LLM costs.

For New Zealand data location, both AWS (Asia Pacific (New Zealand), ap-southeast-6, per its Bedrock announcement) and Azure (New Zealand North) have regions here, so a self-hosted store can sit onshore. Our guide to data residency for AI systems covers the parts that still leave the country.

Where do vector stores fail in production?

The failures that matter are silent: queries succeed and return plausible but incomplete results.

• Filter starvation. Too few results after post-filtering. Detect by logging requested versus returned counts.
• Cross-tenant interference. One large tenant degrades recall for small ones in a shared index. Detect with per-tenant recall in your benchmark.
• Stale vectors. Text updated but embedding not, because the two live in different systems. Keep them in one transaction where you can, and alert on index lag.
• Distance mismatch. Index built for one metric while the embedding model expects another. Check the model documentation.
• Growth without retuning. IVFFlat list counts set when the table was small (pgvector suggests rows divided by 1,000 up to a million rows) stop fitting as data grows. Re-tune as you scale.
• Mixed embedding models. A partial re-embed leaves incomparable vectors in one index. Re-index fully, then switch.

What should most New Zealand teams choose?

Start with pgvector if you already run Postgres, keep text, metadata and vectors in one transaction, and add hybrid keyword search early. Move to a dedicated engine when your benchmark shows filtered recall, tenant isolation or scale is the bottleneck, not before. For internal search over sensitive documents, see building private AI search; for a team-level introduction, the lesson on building a searchable business knowledge base. Store selection is part of every RAG development project we run, and it is a decision you can revisit later if retrieval sits behind a clean interface. If you are weighing retrieval against other approaches entirely, read RAG versus fine-tuning.

• Real queries, filters and tenants are part of the benchmark
• ANN recall is measured against exact search on the same embeddings
• Retrieval recall is measured against human relevance labels
• Requested versus returned result counts are logged in production
• Tenants are isolated with partitions or separate collections
• Embedding dimensions fit the index limits, or half precision is used
• Text, metadata and vectors update together, and index lag is monitored
• Distance metric matches the embedding model’s documentation
• Memory is estimated from rows, dimensions and precision before launch
• Backups, restores and embedding-model changes have been rehearsed

Written by the Levy Street team in Wellington. Last updated 19 October 2026.