pgvector has become the default way to add vector search to PostgreSQL, and for good reason: you keep embeddings next to the relational data they describe, in the same database you already back up, monitor, and trust. But there is a widening gap between the demo and the production workload, and it usually shows up in the benchmarks. As The New Stack recently explained, the pgvector benchmarks that look great on a laptop tell you almost nothing about how the same setup behaves at real scale.

That gap matters in Houston. Energy companies are building RAG pipelines over decades of well logs, drilling reports, and equipment maintenance records; medical organizations around the Texas Medical Center are searching clinical literature and imaging metadata; logistics firms tied to the Port of Houston are retrieving documents across millions of shipments. These are multi-million-row workloads with 1,536-dimension embeddings — exactly the conditions where naive pgvector setups fall over. Here is how to benchmark honestly, and how to tune so your index survives contact with production.

The laptop-to-production gap

A benchmark on 10,000 vectors at 128 dimensions looks clean: queries return in milliseconds, and index builds finish before your coffee cools. Run the same setup at 5 million vectors and 1,536 dimensions and the rules change. An HNSW index build consumes significantly more RAM than a query does, builds can run for hours, and after every deploy or failover the first users eat a cold-cache penalty while the graph index warms up. The query planner’s cost estimates on filtered vector queries can also swing wildly as the dataset grows.

The lesson is not that pgvector is broken. It is that scale changes which problems matter. Benchmark on representative data at representative scale — before you pick an index type, not after.

Benchmark before you commit

The most overlooked step in a pgvector rollout is benchmarking your own workload. Community numbers give you a rough sense of what is possible, but your results will vary with vector dimensions, data distribution, and dataset size. Measure three things at the scale you expect to hit: query latency, index build time, and search recall. That hour spent benchmarking now saves a much longer rearchitecture later.

-- Benchmark on realistic data: 5M rows, 1536-dim embeddings
\timing on
CREATE INDEX documents_embedding_idx
  ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Then measure query latency at your target recall:
SET hnsw.ef_search = 40;
SELECT count(*) FROM (
  SELECT id FROM documents
  ORDER BY embedding <=> '[0.012, ...]'::vector
  LIMIT 20
) q;

IVFFlat vs. HNSW: pick deliberately

The IVFFlat-versus-HNSW decision is a workload fit question. IVFFlat builds faster and produces more compact indexes, which makes it a solid choice for periodic batch loads or modest datasets; you control the speed/recall tradeoff with lists and probes. One critical caveat: IVFFlat needs training data to create effective partitions, so build it after your data is loaded, not before.

HNSW wins when you need low query latency and high recall under frequent queries. Its graph structure traverses faster, but index creation takes longer and uses more memory. Tune ef_search (how broadly the algorithm explores per query) and m (connections per node). Whichever index you choose, benchmark the parameters against your real query patterns, and store the winning values alongside the index definition — when your team updates the embedding model, the dimensionality and distribution change, and the tuning must change with them.

-- HNSW for query-heavy workloads
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
SET hnsw.ef_search = 40;   -- raise for recall, lower for speed

-- IVFFlat for batch-loaded, moderate datasets
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);
SET ivfflat.probes = 10;

Hybrid retrieval beats raw ANN

Too many teams treat pgvector as a standalone vector store that happens to live in Postgres. That leaves the biggest performance win on the table: SQL filters that narrow the candidate set before the approximate nearest neighbor scan ever runs. In multi-tenant applications, filtering by tenant ID, language, content type, or date range first often improves query performance by an order of magnitude. You can go further with a two-stage pipeline: run a fast ANN query for the top-N candidates, then re-rank those candidates in SQL using exact distance plus business logic such as freshness, permissions, or popularity weighting — all inside a single transaction.

SELECT d.id, d.title, d.embedding <=> $1 AS distance
FROM documents d
WHERE d.tenant_id = $2      -- narrow first
  AND d.lang = 'en'
  AND d.published_at > now() - interval '1 year'
ORDER BY d.embedding <=> $1
LIMIT 20;

Partition like you filter, and prewarm

Partitioning purely by data volume misses the point. Partition on the fields that correlate with your query filters — if every query filters by tenant, partition by tenant — and build per-partition vector indexes so the planner prunes entire partitions at plan time. Cold-cache performance is the other trap: after a deploy or failover, the pages backing your vector index are not in memory, and the first users pay for it. The pg_prewarm extension loads hot pages into shared buffers before traffic arrives, so bake it into your deployment process.

-- Load index pages into shared buffers after deploy/failover
SELECT pg_prewarm('documents_embedding_idx');

Know the boundaries

pgvector is under active development, supports specific PostgreSQL versions, and offers no auto-tuning layer — memory allocation, query optimization, and index configuration are on you, just like any serious Postgres performance work. If you need sub-20ms latency across tens of millions of vectors, you may eventually graduate to a purpose-built vector database. But starting with pgvector lets you validate the use case and understand your query patterns without standing up separate infrastructure first, and you will migrate with far better knowledge of what you actually need.

What separates the teams that succeed is treating pgvector like any other serious Postgres workload: benchmark representative data at representative scale, tune index parameters deliberately instead of trusting defaults, and design queries that use the full SQL toolkit. For Houston teams running document search, retrieval-augmented generation, or semantic search over years of operational data, that discipline is the difference between a demo that impresses and a system that stays fast in production.