Tuning Postgres and pgvector: the three knobs that matter

Almost every "pgvector doesn't scale" story I've investigated has turned out to be one of three settings, set wrong. Not a fundamental limit. A config line.

This post covers those three settings: what each one does, how to read the query plan that tells you which one is wrong, and the order to tune them in so you don't spend a day on the wrong knob.

The three knobs that matter most

Of the dozens of settings that touch vector search performance, three of them do almost all the work.

1. hnsw.ef_search (or ivfflat.probes). Query-time only. Trades recall for latency. The single most useful knob for ANN search. 2. shared_buffers. Cluster-wide. Decides whether your index lives in memory or pages from disk. The single most useful Postgres-level knob for vector workloads. 3. maintenance_work_mem plus max_parallel_maintenance_workers. Build-time. Already covered in Part 4; rebuild speed is part of the operational story.

Get those three right and you'll be fine. Skip them and tuning anything else is a rounding error.

ef_search: pick a point on the recall-latency curve

Every ANN query in pgvector with HNSW operates somewhere on a curve. On one axis is recall (how often the index returns the same top-k as an exact scan). On the other axis is latency (how long it takes). ef_search is the dial.

-- Per-session, before your query
SET hnsw.ef_search = 100;
 
-- Or per-transaction
SET LOCAL hnsw.ef_search = 100;

Default is 40. Increase it and recall goes up while latency goes up. Decrease it and the opposite. The relationship is not linear: doubling ef_search typically buys you a few percentage points of recall while roughly doubling latency. The curve flattens as it climbs.

The animation above is doing the work. Drag the slider, pick the operating point your product can afford. There's no "right" value. There's a value that hits your latency budget and a recall floor you're willing to live with.

A pattern that works well in practice: pick ef_search per query type, not globally. Latency-sensitive autocomplete-style searches run at 40 to 60. Full-text-fallback searches that the user is waiting on can run at 100 to 200. Background batch jobs run at 400.

// search.ts, sketched
type SearchKind = "interactive" | "background" | "eval";
 
const efSearch: Record<SearchKind, number> = {
  interactive: 60,
  background: 200,
  eval: 400, // crank for ground-truth-ish comparisons
};
 
await db.execute(sql`SET LOCAL hnsw.ef_search = ${efSearch[kind]}`);
// ... your search query here

For IVFFlat, the analogous knob is ivfflat.probes (default 1, which is almost always too low). The math is similar: more probes, higher recall, higher latency.

EXPLAIN ANALYZE on an ANN query

You cannot tune what you cannot read. The plan for an ANN query has a few specific things to look at, and once you know them, every later tuning move starts with the plan.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, body
FROM chunks
ORDER BY embedding <=> '[0.01, 0.02, ...]'
LIMIT 10;

The four things to look at, in order of how often they matter:

1. Is it an Index Scan using chunks_embedding_hnsw_idx? If you see Seq Scan, the index isn't being used. Reasons: operator mismatch, the table is small enough that the planner thinks a scan is cheaper (true and fine for development, not fine in production after the table grows), or the index is INVALID.

2. Buffers: shared hit=X read=Y. read= is disk reads. If read is non-zero for a query that should be hot, your index isn't in shared_buffers and you're paging from disk every time. This is the single biggest reason for "the query was fast yesterday, why is it slow today" stories.

3. Actual time vs planned time. A big gap means the planner's row estimate was wrong. For ANN this almost always traces back to selectivity of pre-filters (the WHERE clauses before the vector search). We'll see this hit hard in Part 6 with metadata filtering.

4. Loops. If the index scan ran 10 times instead of once, you've written something that effectively does the vector search per row of an outer query. Restructure the query.

Hover any node, read what it tells you and which knob touches it.

shared_buffers: the difference between memory and disk

The HNSW index for a 5M-row table at 1024 dimensions is roughly 5 to 10 GB, depending on m. If your shared_buffers is the default 128 MB, the index lives on disk and every query has to page it in. That's not slow tuning; that's slow architecture.

Rule of thumb: set shared_buffers to 25% of system RAM on a dedicated database box. On a 64 GB box, that's 16 GB. Most ANN workloads want the entire active index in shared_buffers, and 25% is usually enough headroom.

# postgresql.conf
shared_buffers = 16GB
effective_cache_size = 48GB   # 75% of RAM; the planner reads this
work_mem = 32MB               # per sort/hash; not the same as the above
maintenance_work_mem = 2GB    # used for index builds (Part 4)

effective_cache_size is an estimate, not an allocation. It tells the planner how much memory the OS page cache plus shared_buffers is likely to be. Wrong values here distort the plan choice in subtle ways. Set it generously.

The animation visualizes what changes when your index size crosses the shared_buffers line. As long as the working set fits, cache hits stay near 100% and the latency curve from earlier is honest. The moment it spills, p99 latency goes through the roof while p50 looks fine, which is the worst kind of incident.

Check it with this query:

SELECT
  pg_size_pretty(pg_relation_size('chunks_embedding_hnsw_idx')) AS index_size,
  pg_size_pretty(pg_settings.setting::bigint * 8192) AS shared_buffers
FROM pg_settings WHERE name = 'shared_buffers';

If the index is bigger than shared_buffers, you have a decision: more RAM, a smaller index (halfvec, fewer dimensions, smaller m), or accept the variable latency.

work_mem, and why it matters more than you'd think for vectors

work_mem is per-sort, per-hash, per-operation. ANN queries themselves don't use much of it, but the surrounding query (sorting, grouping, joining the metadata table) absolutely does. A work_mem set too low causes the surrounding query to spill to disk, and the latency you blamed on the vector search is actually the join writing temp files.

Default is 4 MB, which is absurdly low for any modern workload. 32 to 64 MB is a sensible starting point for a database with a few concurrent users. If you have a lot of concurrent connections, model it: work_mem × max concurrent sorts × max connections can easily exceed RAM. The formula is conservative; reality is forgiving because most queries don't hit the limit.

Per-query SETs are your friend

shared_buffers and effective_cache_size are cluster-wide. The fun stuff is per-session or per-transaction:

BEGIN;
SET LOCAL hnsw.ef_search = 200;
SET LOCAL work_mem = '128MB';
 
SELECT ...;
 
COMMIT;

SET LOCAL scopes the setting to the current transaction. Use this liberally. A nightly eval that wants the most accurate recall it can get? SET LOCAL hnsw.ef_search = 400; for the duration, restore on commit.

A tuning order that doesn't waste your week

Here's the order I'd actually go through, on a slow-but-working ANN setup.

1. Run EXPLAIN (ANALYZE, BUFFERS) on a slow query. Confirm the index is being used. If it isn't, fix that first.

2. Check read= on the index scan node. If it's non-zero, your problem is shared_buffers, not ef_search. Raise it, restart, retest.

3. Now move ef_search. With the index in memory, you're on the recall-latency curve. Pick your operating point.

4. Look at surrounding query operators. Sorts spilling to disk? Raise work_mem. Hash joins exploding? Same answer.

5. Only then think about index parameters. m, ef_construction, halfvec, smaller dimensions. Each of these is a rebuild, so confirm with your eval (Part 7) before committing.

Following this order saves you from the most common time-waster: spending a day tuning ef_search while your index is paging from disk.

From the trenches

Restart Postgres after changing shared_buffers. It's not a dynamic setting. A surprising number of "I changed shared_buffers and nothing happened" threads are someone who forgot the restart.

Watch for autovacuum against your vector table. Routine VACUUM is fine. But aggressive autovacuum during heavy ingestion can compete with the HNSW index for memory. If you see latency spikes correlated with autovacuum, tune autovacuum_vacuum_cost_delay and autovacuum_naptime for the table specifically.

SET hnsw.ef_search does not survive across pooler connections. If you use PgBouncer in transaction mode, every transaction gets a fresh connection. Either use SET LOCAL inside the transaction or move to session mode for the queries that need it.

The biggest single tuning win I've ever seen. A 5M-row table where queries were taking two seconds. Cause: shared_buffers at default, index size 6 GB, working set entirely from disk. After bumping to 16 GB and warming the cache, queries dropped to under 20ms. No application change. Part 5 in five lines.

Slow vector search is a settings problem

"Vector search is slow" is almost never about pgvector itself. It's about the settings around it. Read the plan first, get the index into memory, then pick your point on the recall-latency curve. That order fixes most slow setups in an afternoon.

Part 6 is what to do when neither vector search nor full-text alone is enough: hybrid search, reranking, and the retrieval-augmented generation loop, done with the same care we've just spent on tuning.