pgvector in Production: HNSW, Filtering, and Tuning

pgvector turns Postgres into a real vector database — no second system, no sync jobs, just an ORDER BY on a new column type. It scales to tens of millions of rows on a single node, but the defaults, the filtered-search gotchas, and the point where it stops being the right tool all matter more than the "just add an extension" pitch suggests.

📖 Read the full guide: pgvector in Production: What the Quickstart Skips

TL;DR

• pgvector is a real production vector store for most teams. Tens of millions of rows on one decent node is fine.
• Start with HNSW, and I mean it. IVFFlat only when build time or maintenance_work_mem forces your hand.
• The index is only used for ORDER BY ... LIMIT n, and only when the opclass matches the operator. Mismatch gives you a seq scan with no warning.
• Filtered search is where people get burned: either brute force over a subset, or 3 rows back when you asked for 10.
• Since 0.8.0, iterative index scans fix the over-filtering case. Turn them on deliberately, with the bounds set.
• Measure recall against exact ground truth. Do not eyeball result quality and call it good.
• The ceiling is single-node. There is no built-in sharding of a vector index.

The setup

The reason pgvector wins arguments is boring: the embedding sits in the same row as tenant_id, created_at, and status. Filtering is a WHERE clause. No dual write, no sync job, no reconciliation script that someone will have to debug at 3am after a partial failure. I have watched teams bolt on a second datastore before they measured a single query against Postgres, and then spend a quarter keeping two systems consistent.

There's a companion video that covers this at a whiteboard level (pgdba on YouTube). This post goes deeper: exact SQL, real EXPLAIN output, tuning tables, and the edge cases that bite.

Install, types, and what a vector costs you

CREATE EXTENSION vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  tenant_id   int         NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  content     text        NOT NULL,
  embedding   vector(1536)
);

Four types exist. vector (4-byte float), plus halfvec, sparsevec, and bit, all added in 0.7.0. vector stores up to 16,000 dimensions, but HNSW and IVFFlat indexes on it only support up to 2,000. halfvec is 2-byte floats, halves storage, and indexes up to 4,000 dimensions. If you're at 3072 dims and want an index, halfvec is the answer.

Storage math is simple and worth doing on a napkin before you provision: 4 × dimensions + 8 bytes. At 1536 dims that's 6,152 bytes per row. Five million rows is about 30 GB of embedding data alone.

That data doesn't live where you think:

=> \d documents
                Table "public.documents"
   Column   |           Type           | Storage
------------+--------------------------+----------
 id         | bigint                   | plain
 tenant_id  | integer                  | plain
 content    | text                     | extended
 embedding  | vector(1536)             | external

=> SELECT pg_size_pretty(pg_relation_size('documents'))     AS heap,
          pg_size_pretty(pg_total_relation_size('documents')) AS total;
  heap   | total
---------+--------
 412 MB  | 34 GB

pgvector sets EXTERNAL storage on the vector type, so every 6 KB embedding goes out of line into the TOAST relation. pg_relation_size() on the main table lies to you by a factor of eighty. Always use pg_total_relation_size() when sizing disk, and watch TOAST growth separately, not just row count in \dt+.

Distance operators and opclasses

Get this pair right or your index is decorative.

Operator	Distance	Opclass	Notes
<->	L2 / Euclidean	vector_l2_ops
<=>	Cosine	vector_cosine_ops	Most common default
<#>	Negative inner product	vector_ip_ops	Returns negative values
<+>	L1 / taxicab	vector_l1_ops	Added 0.7.0, HNSW only

A vector_cosine_ops index will not serve a <-> query. You get a sequential scan, no error, no notice, and a p99 that quietly triples once the table grows. This is the single most common pgvector bug I get called about.

If your embeddings are unit-normalized (OpenAI's text-embedding-3 models are), cosine distance and negative inner product rank identically, and inner product is cheaper to compute. If that's you, don't just benchmark it as a maybe — build with vector_ip_ops and query with <#>. It's free performance with no accuracy cost.

HNSW vs IVFFlat: how I choose

	HNSW	IVFFlat
Added in	0.5.0	0.4.0
Build time	Slow	Fast
Build memory	High; falls back to a slower two-pass build if the graph exceeds maintenance_work_mem	Modest
Index size	Larger	Smaller
Recall at speed	Better	Good, degrades faster
Build params	m (16), ef_construction (64)	lists
Query param	hnsw.ef_search (40)	ivfflat.probes (1)
Empty table	Fine, create it before load	No. Needs representative data for k-means centroids
Data drift	Tolerates it	Centroids stale as data shifts; rebuild periodically

-- default choice
CREATE INDEX idx_embedding_hnsw ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- when build time genuinely matters
CREATE INDEX idx_embedding_ivf ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);

pgvector's own sizing guidance for IVFFlat: lists = rows / 1000 up to 1M rows, lists = sqrt(rows) above that. Start probes at sqrt(lists). For 5M rows that's roughly 2,236 lists and 47 probes — do this arithmetic for your actual row count rather than copying a lists value out of a blog post.

IVFFlat's centroid requirement also means it can't be built empty and filled incrementally the way HNSW can. If your pipeline bulk-loads first and indexes after, that's a non-issue; if it streams rows in continuously, it's a real workflow constraint.

Making the build not take all night

SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 7;  -- 8 workers total

Parallel HNSW builds landed in 0.6.0 and scale reasonably. Bulk load first, then build. Building the index and then inserting 5M rows through it is dramatically slower.

CREATE INDEX CONCURRENTLY avoids the write lock but takes two table passes and is meaningfully slower, and it can't run inside a transaction block. On a live system I still use it; on a migration window I don't.

Watch progress:

SELECT phase, round, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

        phase         | round | tuples_done | tuples_total
----------------------+-------+-------------+--------------
 building index: ...  |     0 |     3120000 |      5000000

If round climbs above 0, your graph didn't fit in maintenance_work_mem and you're in the two-pass path. Cancel, raise it, restart.

Proving the index is used

The good plan:

=> EXPLAIN (ANALYZE, BUFFERS)
   SELECT id, content FROM documents
   ORDER BY embedding <=> '[0.014,-0.221,...]'::vector LIMIT 10;

 Limit  (actual time=1.842..4.061 rows=10 loops=1)
   Buffers: shared hit=2188 read=94
   ->  Index Scan using idx_embedding_hnsw on documents
         (actual time=1.839..4.055 rows=10 loops=1)
         Order By: (embedding <=> '[0.014,-0.221,...]'::vector)
         Buffers: shared hit=2188 read=94
 Execution Time: 4.238 ms

Drop the LIMIT, or swap <=> for <->, and you get this:

 Sort  (actual time=9412.663..9680.114 rows=5000000 loops=1)
   Sort Key: ((embedding <-> '[...]'::vector))
   Sort Method: external merge  Disk: 412104kB
   ->  Seq Scan on documents (actual time=0.031..6119.882 rows=5000000 loops=1)
         Buffers: shared hit=1204 read=3821194
 Execution Time: 9844.291 ms

Now measure recall instead of trusting it:

SET LOCAL hnsw.ef_search = 40;

WITH q AS (SELECT '[0.014,-0.221,...]'::vector AS v),
ann AS (
  SELECT id FROM documents, q ORDER BY embedding <=> q.v LIMIT 10
),
truth AS (
  SELECT id FROM documents, q ORDER BY embedding <=> q.v LIMIT 10
)
SELECT count(*) / 10.0 AS recall_at_10
FROM ann JOIN truth USING (id);

For ground truth, run the truth half in a separate transaction with SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; so it brute-forces. Loop over 200 held-out query vectors and average. On a 5M-row corpus I typically see recall@10 around 0.94 at ef_search = 40, 0.98 at 100, 0.99+ at 200, with latency roughly tripling from the first to the last. Pick your point on that curve with numbers, not vibes.

One hard rule: hnsw.ef_search must be at least your LIMIT, or you won't reliably get the row count you asked for.

Filtered search: the part that bites everyone

SELECT id, content FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> $1 LIMIT 10;

Failure mode one, the planner uses the btree:

 Limit  (actual time=812.443..812.449 rows=10 loops=1)
   ->  Sort  (actual time=812.441..812.444 rows=10 loops=1)
         Sort Key: ((embedding <=> '[...]'::vector))
         ->  Bitmap Heap Scan on documents (actual rows=418302 loops=1)
               Recheck Cond: (tenant_id = 42)
               Heap Blocks: exact=61044
               ->  Bitmap Index Scan on idx_documents_tenant  (actual rows=418302)
 Execution Time: 812.6 ms

Exact brute force over the subset. Perfect when the tenant has 800 rows, awful at 400k.

Failure mode two, post-filtering: the HNSW scan returns its candidates, the WHERE eliminates most of them, and you get rows=3 back from a LIMIT 10. No error. Just quietly incomplete results, usually noticed by a customer.

Four fixes, ranked

1. Iterative scans (0.8.0+)

The index keeps scanning until enough rows survive the filter.

SET LOCAL hnsw.iterative_scan = 'relaxed_order';
SET LOCAL hnsw.max_scan_tuples = 40000;      -- default 20000
SET LOCAL hnsw.scan_mem_multiplier = 2;      -- default 1

WITH ann AS (
  SELECT id, content, embedding <=> $1 AS dist
  FROM documents
  WHERE tenant_id = 42
  ORDER BY embedding <=> $1
  LIMIT 10
)
SELECT * FROM ann ORDER BY dist;

hnsw.iterative_scan takes off, strict_order, or relaxed_order. relaxed_order is faster but can return rows slightly out of distance order, hence the re-sorting CTE. IVFFlat only supports off and relaxed_order, bounded by ivfflat.max_probes. Set max_scan_tuples higher for very selective filters, or you'll still under-return.

2. Partial indexes

For known high-traffic filter values:

CREATE INDEX idx_docs_hnsw_t42 ON documents
  USING hnsw (embedding vector_cosine_ops)
  WHERE tenant_id = 42;

True pre-filtering. Cost is one index per value, so reserve it for your five biggest tenants, not ten thousand of them.

3. Partitioning

By tenant or time with an HNSW index per partition. Same idea, scalable: the planner prunes partitions before the ANN scan runs, so each partition's index only ever sees its own rows.

4. CTE pre-filter with exact distance

Use when the filter is very selective (a few thousand rows). Skip the index entirely and compute exact distances.

And the correction I make constantly: you cannot stash the embedding in a btree INCLUDE column.

=> CREATE INDEX idx_tenant_embedding ON documents (tenant_id) INCLUDE (embedding);
ERROR:  index row size 6216 exceeds btree version 4 maximum 2704 for index "idx_tenant_embedding"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.

Btree tuples cap at roughly one third of an 8 KB page. 6,152 bytes was never going to fit.

Operational reality

Re-generating embeddings is a full row rewrite. Postgres MVCC writes a new row version for any update, even one unrelated to the embedding, and with a 6 KB TOASTed vector that means the TOAST chunks get rewritten too, plus HNSW graph churn absorbing the change. A nightly job that re-embeds 10% of a 5M-row table generates a lot of WAL, which your replicas have to receive and replay. Watch replication lag on the first run.

If you regenerate embeddings in bulk — new model version, new chunking strategy — expect index bloat and plan a REINDEX CONCURRENTLY afterward rather than letting autovacuum fight a losing battle. HNSW build work happens on the primary and ships as WAL, so a REINDEX CONCURRENTLY on a 30 GB index is a replication event, not just a local one. Schedule it accordingly.

Also worth knowing: pgvector is available as a managed extension on RDS/Aurora, Cloud SQL/AlloyDB, and Azure Database for PostgreSQL. Adopting it rarely means self-hosting.

Where pgvector stops being the right answer

There is no distributed vector index. One node, one memory budget, and that budget competes directly with the shared_buffers your OLTP workload needs. There's no native BM25 or hybrid fusion, and no reranking stage.

My rule: if you're past roughly 50M vectors at 1536 dims, or you need sub-10ms p99 at hundreds of millions of vectors, or vector work is starving your transactional workload of memory, start looking. Before you leave Postgres entirely, try pgvectorscale, which adds a StreamingDiskANN index for larger-than-memory workloads and keeps the "embedding next to metadata" model. After that, Qdrant, Milvus, and Pinecone exist for exactly this reason. Measure before you migrate — I've watched teams add a second database before they'd run a single recall benchmark on the first one.

Pre-production checklist

1. Opclass matches the operator in every query path.
2. Every vector query has ORDER BY and a LIMIT.
3. hnsw.ef_search >= the largest LIMIT you issue.
4. Recall@10 measured against exact ground truth across at least 100 held-out queries.
5. Iterative scan configured, with max_scan_tuples and scan_mem_multiplier set explicitly.
6. Filtered queries have a plan you've read, not one you assume.
7. Partial or partitioned indexes in place for your highest-traffic filter values.
8. Index build is scripted, reproducible, and timed on production-sized data.
9. maintenance_work_mem sized so pg_stat_progress_create_index never shows round > 0.
10. Monitoring on index size, TOAST growth, pg_total_relation_size, and vector query p99.
11. WAL volume checked after your first bulk re-embedding run.

If you'd rather have something check the schema side of this for you, MyDBA has a free health check that flags missing or mismatched indexes and prints the exact CREATE INDEX for your tables.