pgvector in Production: What the Quickstart Skips

pgvector in production: the parts the quickstart leaves out

Most teams reach for pgvector for a boring, correct reason: Postgres is already in the stack. The embedding lives in the same row as tenant_id, status, published_at and whatever else you filter on, so "find similar documents this user is allowed to see, published in the last 90 days" is a WHERE clause instead of a two-system join with an ID list shuttling over the network. You get transactions, backups, replication, point-in-time recovery and your existing monitoring for free.

<div style="position:relative;padding-bottom:56.25%;height:0;overflow:hidden;border-radius:0.75rem;margin:2rem 0;"> <iframe src="https://www.youtube.com/embed/aQV3wqdRMuQ" title="pgvector: Postgres as your vector database" style="position:absolute;top:0;left:0;width:100%;height:100%;border:0;" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe> </div>

▶ Watch on YouTube: pgvector: Postgres as your vector database

That case is mostly right. For tens of millions of embeddings alongside their metadata, pgvector is a perfectly serviceable Postgres vector database.

What it is not is a drop-in dedicated vector store. You own the index choice, the recall knobs, the query plans for filtered search, and the disk and vacuum consequences of stuffing 6KB columns into a table you also run OLTP traffic against.

The companion video is the six-minute version. This is the one with the SQL in it.

What the vector type actually is

CREATE EXTENSION IF NOT EXISTS vector;

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

vector stores float32: 4 bytes per dimension plus 8 bytes of overhead. A 1536-dimension embedding is 6,152 bytes. That single number drives almost everything else in this article.

The type accepts up to 16,000 dimensions, but you can only build an HNSW or IVFFlat index up to 2,000 dimensions. If your model emits 3,072 dims, you either reduce dimensions before storing, or use halfvec (2 bytes per dimension, added in 0.7.0), which indexes up to 4,000 dimensions with HNSW. pgvector 0.7.0 also brought sparsevec, L1 distance and binary_quantize.

Four distance operators, and each one needs a matching operator class. Get this wrong and your index is a very expensive decoration, because the planner will only use it when the ORDER BY expression uses the same operator the opclass implements.

| Operator | Distance | Opclass | Typical use | |---|---|---|---| | <-> | L2 / Euclidean | vector_l2_ops | Image embeddings, models trained with L2 | | <=> | Cosine | vector_cosine_ops | Most text embedding APIs (OpenAI, Cohere, most sentence-transformers) | | <#> | Negative inner product | vector_ip_ops | Normalised vectors, cheapest per comparison | | <+> | L1 / taxicab | vector_l1_ops | Rarer; 0.7.0+ |

One shortcut worth knowing: cosine and inner product give the same ordering for unit-length vectors. If your provider already returns normalised embeddings, vector_ip_ops with <#> saves the normalisation work on every comparison. Check whether your model normalises before you default to <=> out of habit — it's a five-minute check that saves a rebuild later.

Size it before you build it

This is the step teams skip, and it is the one that produces the 3am page.

| Dims | Bytes/vector | 1M rows | 10M rows | |---|---|---|---| | 384 | 1,544 | ~1.5 GB | ~15 GB | | 768 | 3,080 | ~3.1 GB | ~31 GB | | 1536 | 6,152 | ~6.2 GB | ~61 GB |

That is raw vector payload only. No index, no metadata, no page overhead, no free space map.

Now TOAST. Postgres considers values over roughly 2KB (TOAST_TUPLE_THRESHOLD) for out-of-line storage, so at 768 dims and above your embeddings almost certainly live in the table's TOAST relation, not the main heap. Three consequences:

Budget for the HNSW index in the same order of magnitude as the vector data itself. For 10M × 1536, plan on a 60GB table plus a similarly chunky index, and then ask what that does to your base backup window, your restore time objective, and whether maintenance_work_mem on this box can hold a graph that size during a build. If the answer to the last one is no, keep reading.

HNSW vs IVFFlat: pick one, on purpose

| | HNSW | IVFFlat | |---|---|---| | Build time | Slower | Faster | | Build memory | Higher | Lower | | Query performance at equal recall | Generally better | Generally worse | | Build on empty table | Yes, no training step | No, needs representative data first | | Incremental inserts | Handled by the graph | Centroids go stale as data drifts | | Build knobs | m (default 16), ef_construction (default 64) | lists | | Query knob | hnsw.ef_search (default 40) | ivfflat.probes (default 1) |

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

-- IVFFlat, only after the table holds representative data
CREATE INDEX documents_embedding_ivf
    ON documents USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 3163);   -- sqrt(10,000,000)

pgvector's documented starting points for IVFFlat: lists = rows / 1000 up to a million rows, sqrt(rows) above that, with probes starting around sqrt(lists). Those are starting points, not answers. You measure from there.

Default to HNSW. IVFFlat still wins in two situations: when build time or build memory is the binding constraint (a nightly rebuild on a modest box), and when the data is genuinely static so stale centroids never become a problem.

Query-time knobs are GUCs, which means SET LOCAL and per-transaction tuning. Your "user is waiting" search path and your batch dedup job do not need the same recall:

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id, body FROM documents ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Building the index without wrecking the box

pgvector wants the HNSW graph to fit in maintenance_work_mem during the build. If it does not fit, the build falls back to a much slower on-disk path, and "much slower" here means the difference between lunch and tomorrow.

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;   -- 0.6.0+ parallel HNSW builds

CREATE INDEX CONCURRENTLY documents_embedding_hnsw
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CONCURRENTLY avoids the ACCESS EXCLUSIVE lock, at the cost of two table scans, a longer runtime, and an invalid index left behind if it fails. On a 60GB table that failure mode is not theoretical. Check for it afterwards and drop anything with indisvalid = false.

Watch it from another session:

SELECT phase, blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

There's no excuse for building a multi-hour index blind. And remember the ordering rule: IVFFlat derives its centroids from the rows present at build time, so build it after loading. HNSW has no training step and can be created on an empty table, which is why it fits normal application deployment flow better.

Measure recall instead of guessing

Both index types are approximate. Results can and will differ from brute force. Without an index, a similarity query sequentially scans and computes every distance, which is slow and exactly correct: 100% recall by definition. That is your ground truth.

-- 1. Sample query vectors
CREATE TEMP TABLE probe AS
SELECT id AS qid, embedding FROM documents TABLESAMPLE SYSTEM (1) LIMIT 100;

-- 2. Exact answers, index disabled
SET LOCAL enable_indexscan = off;
SET LOCAL enable_indexonlyscan = off;
CREATE TEMP TABLE truth AS
SELECT p.qid, d.id
FROM probe p
CROSS JOIN LATERAL (
    SELECT id FROM documents ORDER BY documents.embedding <=> p.embedding LIMIT 10
) d;
RESET enable_indexscan;
RESET enable_indexonlyscan;

-- 3. Approximate answers at a chosen ef_search
SET LOCAL hnsw.ef_search = 40;
CREATE TEMP TABLE approx AS
SELECT p.qid, d.id
FROM probe p
CROSS JOIN LATERAL (
    SELECT id FROM documents ORDER BY documents.embedding <=> p.embedding LIMIT 10
) d;

-- 4. Recall
SELECT round(100.0 * count(*) FILTER (WHERE a.id IS NOT NULL) / count(*), 2) AS recall_pct
FROM truth t LEFT JOIN approx a USING (qid, id);

Run step 3 and 4 in a loop over ef_search values. A representative run on one of our workloads looked like this. Treat the shape as instructive and the numbers as belonging to that dataset, not yours:

| ef_search | recall@10 | p95 latency | |---|---|---| | 40 (default) | 92% | 4 ms | | 100 | 97% | 9 ms | | 200 | 99% | 18 ms | | 400 | 99.6% | 39 ms |

The interesting part is the top of the curve. Going from 200 to 400 bought 0.6 points of recall for double the latency. Somebody should decide, deliberately, whether that trade is worth it. Usually it is not.

Filtered similarity search: where it actually hurts

Here is the query everyone writes:

SELECT id, body
FROM documents
WHERE tenant_id = 42 AND status = 'published'
ORDER BY embedding <=> $1
LIMIT 10;

The planner has three shapes available:

1. Seq scan (or btree scan on the filter) + exact sort. Reads the matching subset, computes every distance, sorts. Exact. 2. Btree filter, then top-N sort. Same thing with a cheaper access path. 3. Vector index scan, then filter. Walks HNSW for nearest candidates, discards the ones that fail the filter.

Shape 3 has a nasty property: with a restrictive WHERE clause, LIMIT 10 can return fewer than 10 rows, because the index handed back its nearest candidates and the filter ate most of them. No error, no warning, just short results.

This cuts both ways. At the tiny end, a tenant with 87 matching rows will make the planner skip the vector index entirely and sort those 87 distances in memory — correct, exact, and faster than descending a graph for a candidate set that small. Here is a real-shaped plan from the other end: a filtered query over 18,744 matching rows out of 10 million, where the planner still ignores the vector index:

Limit  (cost=41207.88..41207.90 rows=10 width=48)
       (actual time=812.443..812.447 rows=10 loops=1)
  Buffers: shared hit=1204 read=88316
  ->  Sort  (cost=41207.88..41255.11 rows=18892 width=48)
            (actual time=812.441..812.443 rows=10 loops=1)
        Sort Key: ((embedding <=> '[...]'::vector))
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Bitmap Heap Scan on documents
              (cost=402.11..40799.31 rows=18892 width=48)
              (actual time=12.008..798.221 rows=18744 loops=1)
              Recheck Cond: (tenant_id = 42)
              Filter: (status = 'published'::text)
              Buffers: shared hit=1188 read=88304
              ->  Bitmap Index Scan on documents_tenant_idx
                    (actual time=6.552..6.552 rows=21093 loops=1)
Planning Time: 0.412 ms
Execution Time: 812.503 ms

Read it carefully before you "fix" it. 18,744 rows matched the filter out of 10 million. The planner computed 18,744 distances and took the top 10. That is 100% recall in 812ms, and most of the time went on read=88316 buffers, which at 1536 dims is TOAST detoasting, not arithmetic.

"The index isn't being used" is not automatically a bug. For a highly selective filter, exact brute force on the subset is the correct plan. If 800ms is too slow, the fix is fewer buffer reads, not a forced index scan that would return approximate results over the wrong candidate set.

The fixes, ranked

1. Iterative index scans (pgvector 0.8.0). The modern answer to under-filled results. The index keeps scanning until enough rows survive the filter.

SET LOCAL hnsw.iterative_scan = 'relaxed_order';  -- or 'strict_order'
SET LOCAL hnsw.max_scan_tuples = 20000;
SET LOCAL hnsw.scan_mem_multiplier = 2;

strict_order guarantees results come back in exact distance order; relaxed_order allows slight reordering and is usually faster. max_scan_tuples and scan_mem_multiplier bound how much extra work the scan is allowed to do, which matters because without a ceiling a pathological filter turns an index scan into a full graph walk. IVFFlat has the equivalent ivfflat.iterative_scan.

2. Partial indexes. If your filter is low-cardinality and stable, put it in the index:

CREATE INDEX documents_emb_t42
    ON documents USING hnsw (embedding vector_cosine_ops)
    WHERE tenant_id = 42 AND status = 'published';

Now the index only contains rows that already satisfy the predicate, so recall is measured against the right population. This is excellent for a handful of large tenants and unmanageable at ten thousand of them.

3. Native partitioning. Partition by tenant_id or by time, build a vector index per partition, let partition pruning do the filtering. More moving parts, and each partition's index needs its own build and maintenance budget.

4. Over-fetch then filter. Pull the top 200 from the index, filter in an outer query, take 10. Crude, occasionally correct, and strictly worse than iterative scans if you are on 0.8.0.

5. CTE forcing. WITH MATERIALIZED to pin the plan shape. Use it when you have proven the planner is wrong, not as a default.

The btree INCLUDE trap

You will see this suggested:

CREATE INDEX ON documents (tenant_id) INCLUDE (embedding);

It does not work for real embeddings. A btree index tuple cannot exceed roughly 2,704 bytes (about a third of an 8KB page), and index tuples cannot be stored out of line in TOAST. A 1536-dimension vector is 6,152 bytes on its own. You get:

ERROR:  index row size 6184 exceeds btree version 4 maximum 2704 for index "..."

The threshold sits around 674 dimensions for float32, so 384-dim vectors squeak through and 768-dim ones do not. Do not build a design on a trick that breaks the day someone upgrades the embedding model. Use partial HNSW indexes or iterative scans instead.

Churn, bloat and re-embedding day

Sooner or later you change embedding models, and someone proposes UPDATE documents SET embedding = ... across 10M rows.

Under MVCC, that update writes a new heap tuple and new TOAST chunks rather than overwriting in place. Your 60GB of vector data becomes roughly 120GB until vacuum reclaims the dead space, and vacuum cannot reclaim it until the old snapshots are gone. Every index on the table takes maintenance cost too. We have watched a re-embedding job double a 400GB table overnight and leave autovacuum chewing on the TOAST relation for the next two days while the disk alert stayed red.

Do it with a shadow column instead:

ALTER TABLE documents ADD COLUMN embedding_v2 vector(1536);

-- backfill in batches, with vacuum between them
UPDATE documents SET embedding_v2 = $1 WHERE id BETWEEN $2 AND $3;

CREATE INDEX CONCURRENTLY documents_emb_v2_hnsw
    ON documents USING hnsw (embedding_v2 vector_cosine_ops);

BEGIN;
ALTER TABLE documents RENAME COLUMN embedding TO embedding_old;
ALTER TABLE documents RENAME COLUMN embedding_v2 TO embedding;
COMMIT;

-- once you're satisfied
ALTER TABLE documents DROP COLUMN embedding_old;

Batching the backfill spreads the TOAST churn over time instead of dumping it on autovacuum in one pass. You still pay the space, but you pay it predictably, the swap is atomic and short, and rollback is a rename rather than a restore.

What to watch during and after:

Our notes on vacuum and bloat, index maintenance and what to actually monitor apply here unchanged. Vectors do not need special bloat theory, they just make ordinary bloat expensive.

Where pgvector stops being the right answer

Honest limits, without the vendor spin:

Before you leave, there is a middle ground worth exhausting:

1. Move to halfvec and halve the storage, then measure recall loss. Often it is negligible. 2. Try binary_quantize for a coarse first pass with exact rerank. 3. Try pgvectorscale, Timescale's extension layering StreamingDiskANN and statistical binary quantization on top of pgvector's type. 4. Move vectors to a separate Postgres instance with logical replication of the metadata. You keep the SQL and the tooling, and you stop the CPU contention. This solves more problems than people expect.

Note that on RDS, Aurora, Cloud SQL, AlloyDB and Azure Flexible Server, the available pgvector version lags upstream. Check yours before planning around 0.8.0 iterative scans.

The checklist

| Signal | Stay on pgvector | Start planning an exit | |---|---|---| | Vector count | Under ~50M | Over ~100M on one node | | Dimensions | ≤ 2,000 (or ≤ 4,000 with halfvec) | Above, without reduction | | p95 target | ≥ 50ms | Under 10ms with heavy concurrency | | Filter selectivity | Predictable, or covered by partial indexes | Arbitrary high-cardinality filters | | Write churn | Batched, scheduled | Continuous full-table re-embedding | | Index fits in RAM | Yes | No, and can't be made to |

If you are on the left column in every row, stop shopping. Tune ef_search, measure recall against ground truth, and get on with it.

One last thing, since half the problems above show up as ordinary Postgres pathology before they show up as vector problems: run a health check on the database before you tune anything vector-specific. The MyDBA free health check reads your catalogs and reports missing indexes and bloat with schema-specific CREATE INDEX statements you can paste, which in practice is how you discover that the 800ms filtered query above was slow because tenant_id had no btree index at all, not because HNSW was misbehaving. Fix the boring layer first. It is usually the whole problem.