Running pgvector in Production: One Year of Lessons

About a year ago, pgvector workloads started showing up on customer clusters in earnest. Embeddings tables that used to live in a side service became columns on existing tables. RAG pipelines that used to require a dedicated vector database started running comfortably alongside the rest of the application's data, on the same Postgres instance.

For most workloads, this is the right architecture. Keeping vectors next to the rows they describe — products, documents, support tickets, transcripts — eliminates an entire class of synchronisation bugs and operational complexity. You get joins, transactions, and the same backup story as the rest of your data. That is genuinely valuable.

But pgvector is not a drop-in. The patterns that work in a notebook with 10,000 vectors stop working at 10 million. The query planner makes choices that look reasonable in EXPLAIN output and turn out to be catastrophic in production. Some of the configuration knobs that the documentation treats as defaults are, in practice, the things you will tune the most.

This post collects what we have learned from running pgvector across a few dozen production workloads in the last twelve months. None of it is a secret. All of it is the kind of thing you only really internalise after the second or third 2 a.m. page.

The two index types and what they actually mean

pgvector ships with two approximate nearest neighbour index types: ivfflat and hnsw. The choice between them gets framed as a tradeoff, which is true, but it is framed in a way that obscures what the tradeoff actually is in practice.

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph where each vector is connected to its approximate neighbours. Queries traverse the graph from the top layer down. It is the right default for the overwhelming majority of workloads. Build time is slower, memory footprint is higher, but query latency is excellent and degrades gracefully as the table grows.

IVFFlat partitions the vector space into lists using k-means and searches a configurable number of those lists per query. It is fast to build and uses less memory, but its quality depends entirely on the data distribution at index creation time. If your data drifts — and embedding distributions almost always drift, because real users do not generate queries from the same distribution as your seed data — IVFFlat recall degrades silently.

The pattern we see repeatedly: a team launches with IVFFlat because the build is fast and the documentation example uses it. Six months later, recall is mysteriously lower than it was at launch. The fix is to rebuild the index, which is non-trivial because the table is now ten times larger and the rebuild needs maintenance_work_mem tuned correctly to avoid spilling to disk and taking a day.

Our default recommendation now: start with HNSW. Use IVFFlat only if you have a hard memory ceiling and your data distribution is genuinely stable.

The filtered query problem

This is the single most common production pitfall and it deserves its own section.

You have a vectors table with a million rows. You build an HNSW index on the embedding column. You run a query like:

SELECT id, content
FROM documents
WHERE workspace_id = $1
  AND deleted_at IS NULL
ORDER BY embedding <=> $2
LIMIT 20;

Locally, with a few thousand rows per workspace, this is instant. In production, with a workspace that has 200,000 rows out of a 5-million-row table, this query is suddenly taking 800 milliseconds and hammering the CPU.

The reason: pgvector's index returns approximate nearest neighbours from the entire indexed set. Postgres then applies the WHERE clause filter and discards anything that does not match. If your filter is selective, the planner has to keep walking the index, fetching candidates, and discarding most of them, until it finds enough matches to satisfy your LIMIT. The hnsw.ef_search parameter controls how aggressively it searches, and the default of 40 is almost never enough for filtered queries on large tables.

The fixes, in order of preference:

• Use partial indexes. If most of your queries filter by tenant or workspace, build the index WHERE workspace_id = .... This is the single biggest performance win available, and it is woefully underused.
• Increase hnsw.ef_search at the session level for queries with selective filters. We typically set 100–200 for filtered queries and leave the default for unfiltered.
• Pre-filter into a CTE when the filter is extremely selective (returns less than 5% of the table). Sometimes a sequential scan over a small filtered set is genuinely faster than walking the index and discarding.
• Consider partitioning by the high-cardinality filter dimension (workspace_id, customer_id, etc.) once a single tenant approaches several million vectors. This is heavier surgery but it is the right answer at scale.

Memory math you actually need to do

HNSW indexes live in memory. If they do not fit in shared_buffers and the OS page cache, query latency falls off a cliff. The math is uncomfortable but inescapable:

For a table with N rows, embedding dimension D, and HNSW parameter m (default 16), a reasonable estimate of index size is roughly:

index_size_bytes ≈ N × (D × 4 + m × 8) × 1.3

For 10 million 1536-dimensional OpenAI embeddings with default m, that is around 87 GB. If your machine has 64 GB of RAM, the index will not fit, and you will spend the rest of your life wondering why p99 latency is 1.2 seconds.

Two practical levers:

• Reduce dimensions. Many embedding models support reduced-dimension outputs (Matryoshka embeddings, OpenAI's dimensions parameter). Going from 1536 to 768 dimensions halves the storage cost and, for most retrieval tasks, costs you almost nothing in recall.
• Use halfvec. pgvector 0.7 introduced half-precision vectors. For most embedding models the recall impact is negligible and the storage cost halves again. Combine with reduced dimensions and a 1536-dimensional float32 workload becomes a 768-dimensional float16 workload — a 4× reduction.

We have customers running 50 million vectors comfortably on 128 GB hosts using these techniques. We have also seen 5 million vectors fail to perform on 256 GB hosts because nobody did the math.

The build cost nobody mentions

Building an HNSW index on a large table is not a free operation. On a 10-million-row table with default maintenance_work_mem, it can take hours. With maintenance_work_mem set appropriately and parallel workers configured, it can take 20 minutes. The difference is configuration that almost nobody sets correctly the first time.

The two settings that matter:

• maintenance_work_mem — should be at least the size of the in-memory graph during build. For 10 million 768-dim vectors, set this to 8 GB or more. The default of 64 MB will cause the build to spill to disk and run for an order of magnitude longer.
• max_parallel_maintenance_workers — HNSW build parallelises well. Set this to roughly half your physical cores.

We pre-tune these on customer clusters as part of pgvector enablement, but if you are running pgvector yourself, this is the first place to look when an index build takes too long.

Recall is not a single number

The most common mistake we see in evaluation: measuring recall on a synthetic benchmark, getting 99%, declaring victory, and then watching customers complain about quality six months later.

Recall on the queries you have today is not the same as recall on the queries you will have in six months. As your corpus grows and your users learn what your system can do, query distribution shifts. A pgvector configuration that returns the right answer 99% of the time on launch-week queries can return the right answer 92% of the time on month-six queries without anything in your code changing.

The discipline that helps: keep a held-out evaluation set sourced from real production queries, refresh it quarterly, and re-measure recall against it after every embedding model change, every index parameter change, and every significant corpus growth. Treat it like a regression test.

When to reach for a dedicated vector database

We are obviously biased toward keeping vectors in Postgres. For most workloads we see, that bias is correct. But there are honest cases where a dedicated vector database is the right answer:

• Single-table vector counts above 100 million, especially with frequent writes. The HNSW build cost and memory footprint genuinely become a Postgres-shaped problem at this scale.
• Sub-10ms p99 latency requirements on queries with no useful filter. pgvector can hit this on small datasets but it is not where it is strongest.
• Workloads dominated by vector operations with no relational component. If your vectors do not need to join to anything, the operational simplicity of keeping them in Postgres disappears.

For everything else — which is most things — the operational and developer experience benefits of pgvector outweigh the performance ceiling.

What we tell customers today

The summary we give to teams adopting pgvector on Aureus, in roughly the order it matters:

1. Use HNSW unless you have a specific reason not to.
2. Reduce embedding dimensions where the model supports it. Use halfvec. Both decisions compound.
3. Build partial indexes for your dominant filter dimension before you need them.
4. Tune maintenance_work_mem before your first index build and again before every rebuild.
5. Set hnsw.ef_search per query when filtering selectively. Do not rely on the default.
6. Maintain a real evaluation set. Refresh it. Re-run it.
7. Plan capacity for index size, not just row count. Do the memory math.

None of this is exotic. All of it is the difference between pgvector working in production and pgvector becoming the next item on the migration backlog.

If you are running embeddings workloads on Postgres and want help getting the configuration right — or you are about to and want to skip the expensive lessons — we are happy to talk through it.