pgvector's whole pitch is that you don't need a separate vector database — embeddings live right next to your relational data, one Postgres instance, one connection pool, one thing to operate. That pitch holds up fine in a demo. It starts breaking in specific, predictable ways once real traffic hits it: queries that were 10ms in testing spike to multiple seconds, concurrent searches trigger OOM crashes, and index builds fail outright because nobody bumped maintenance_work_mem before running CREATE INDEX.

None of that is a knock on pgvector. It's what happens when a tool with a lot of tunable knobs gets deployed with the defaults left untouched. Here's what actually needs configuring.

Pick HNSW unless you have a real reason not to

pgvector gives you two index types for approximate nearest neighbor search — HNSW and IVFFlat — and they're not close for most RAG use cases.

IVFFlat builds fast and uses less memory, which sounds like a win until you notice that's the whole list of things it wins at. Recall is worse, and it gets worse specifically as your dataset grows — the opposite direction you want a production index degrading in. Reach for it only if you're genuinely RAM-constrained or you're indexing a massive, mostly-append-only log where rebuild speed actually matters more than search quality. Everything else, HNSW.

Here's a production build on a 1536-dimension column — the size you'll get from text-embedding-3-small or similar:

-- Bump maintenance memory before the build, or this will crawl or fail
SET maintenance_work_mem = '2GB';

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

Three parameters actually matter here:

• m (default 16) — max connections per graph layer. Push it to 24–32 if you need higher recall on complex embeddings, but expect a slower, heavier build in exchange.
• ef_construction (default 64) — candidate list size while building. 64–128 is the sane range; higher buys accuracy, not much else.
• hnsw.ef_search (default 40) — the one you tune at query time, not build time. Set it per session or transaction depending on whether you want precision or throughput:

SET hnsw.ef_search = 80;

Drop it to 20 when you need raw throughput over precision, push it to 100 when a query genuinely needs to be right.

The memory trap that actually causes the OOM crashes

HNSW isn't a B-Tree. It doesn't do tidy sequential lookups — traversal jumps around memory constantly. The moment your index no longer fits inside shared_buffers plus whatever RAM is actually free, every one of those jumps turns into a disk read. CPU pegs at 100%, but it's not doing search work, it's thrashing.

So size for it up front instead of finding out in production:

• Estimate the index footprint first. Rough formula: (dimensions × 4 bytes) + (m × 8 bytes) per vector. A million 1536-dim vectors at m = 16 lands around 6.5GB. That number needs to fit in memory, full stop.
• Set shared_buffers to 25–40% of total system RAM. The goal is keeping the working set of HNSW vectors cached, not just following a generic Postgres tuning rule of thumb.
• Keep work_mem at 64–128MB. It's tempting to raise this globally when a query feels slow, but work_mem is per-connection, and cranking it up across a high-concurrency search workload is exactly how you get the OOM crash you were trying to avoid.

I've seen this exact failure in a RAG pipeline where the index build succeeded fine in staging — small dataset, plenty of headroom — and then fell over in production the moment concurrent search traffic showed up. The index itself was correct. The server just didn't have room to hold it.

Vector search alone misses exact-match queries — pair it with full-text

Pure semantic search is genuinely bad at one specific thing: exact-string queries. Product SKUs, function names, error codes — anything where a user is searching for a literal string, not a concept, tends to fall through pure cosine-distance retrieval. The fix is hybrid search, combining vector similarity with Postgres's own full-text search via tsvector, merged using Reciprocal Rank Fusion:

WITH semantic_search AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) as rank
  FROM document_chunks
  ORDER BY embedding <=> $1
  LIMIT 20
),
keyword_search AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(tsv_content, plainto_tsquery('english', $2)) DESC) as rank
  FROM document_chunks
  WHERE tsv_content @@ plainto_tsquery('english', $2)
  LIMIT 20
)
SELECT
  COALESCE(s.id, k.id) as chunk_id,
  COALESCE(1.0 / (60 + s.rank), 0.0) + COALESCE(1.0 / (60 + k.rank), 0.0) AS rrf_score
FROM semantic_search s
FULL OUTER JOIN keyword_search k ON s.id = k.id
ORDER BY rrf_score DESC
LIMIT 10;

RRF doesn't need you to normalize the two ranking scales against each other, which is the usual headache with hybrid retrieval. It just cares about rank position in each result set, and the 60 constant is a standard damping factor — you generally won't need to touch it.

The settings that matter, in one place

pgvector doesn't need a rewrite to hold up in production. It needs its memory settings sized against the actual index it's going to build, and it needs a keyword fallback so it's not silently failing on the queries semantic search was never going to catch. Get those two things right and there's no real reason to reach for a separate vector database.