Hybrid Search in Postgres with pgvector: Our Field Notes on HNSW, tsvector, and Reciprocal Rank Fusion

Headline: pgvector is a Postgres extension that adds vector column types and approximate-nearest-neighbour indexes, and used alone it is a mediocre search engine. Every retrieval bug our team shipped in a RAG feature was fixed by fusing pgvector similarity with Postgres full-text search through Reciprocal Rank Fusion, not by buying a vector database and not by swapping the embedding model.

Our first retrieval pipeline was one ORDER BY embedding <=> $1 LIMIT 5 and a prompt. It demoed well, then failed on the queries people actually type: exact error codes, function names, invoice numbers, product SKUs. Cosine similarity is perfectly happy to return five paragraphs that are about billing when the user typed a literal invoice ID that appears verbatim in exactly one row.

These are our notes from moving that pipeline to hybrid search on Postgres 17 with pgvector 0.8, keeping vectors in the same database as the application data instead of in a separate service.

Key takeaways

- pgvector 0.8.0 adds four vector types (vector, halfvec, bit, sparsevec) and two approximate-nearest-neighbour index types (HNSW and IVFFlat) to Postgres. It does not add keyword matching, reranking, or chunking.

- Pure vector search fails on exact identifiers. An embedding encodes meaning, so a literal token like ERR_MODULE_NOT_FOUND holds no privileged position in the vector space, while Postgres full-text search matches it exactly.

- Reciprocal Rank Fusion scores each row as the sum of 1 / (k + rank) across retrieval arms, with k = 60 as the common default, so cosine distance and ts_rank_cd never have to be normalised against each other.

- HNSW is the default index and IVFFlat is the exception. HNSW builds on an empty table and gives better recall per unit of latency, while IVFFlat builds faster and uses less memory but must be created after the table holds representative rows.

- A WHERE clause on an HNSW query can return fewer rows than the LIMIT asks for. The hnsw.iterative_scan setting added in pgvector 0.8.0 is the fix.

What does pgvector actually add to Postgres?

pgvector is a Postgres extension that adds vector column types, distance operators, and approximate-nearest-neighbour indexes to an ordinary Postgres database. You enable it with CREATE EXTENSION vector; and you get four storage types: vector (4-byte floats), halfvec (2-byte floats, added in pgvector 0.7.0), bit (binary quantisation), and sparsevec.

The distance operators are the part worth memorising, because choosing the wrong one silently degrades ranking instead of throwing an error. The <=> operator is cosine distance, <-> is L2 (Euclidean) distance, <#> is negative inner product, and <+> is L1 distance. OpenAI's text-embedding-3-small and most hosted embedding models return normalised vectors, so we use <=> with a vector_cosine_ops index and stop thinking about it.

The index operator class must match the query operator. An HNSW index built with vector_l2_ops is simply not used by a query that orders on <=>, and Postgres falls back to a sequential scan without complaining. Our first investigation into slow pgvector queries turned out to be exactly that mismatch, visible in a single EXPLAIN ANALYZE.

The schema we settled on keeps one table of chunks with one embedding column and a generated tsvector column, so keyword search can never drift out of sync with the body text: CREATE TABLE chunks (id bigserial PRIMARY KEY, document_id bigint NOT NULL, body text NOT NULL, embedding vector(1536) NOT NULL, tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);

Two indexes back that table: CREATE INDEX chunks_embedding_hnsw ON chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); and CREATE INDEX chunks_tsv_gin ON chunks USING gin (tsv);

What pgvector does not give you: tokenisation, keyword matching, reranking, chunking, or query rewriting. It is a similarity index, not a search product. Everything below exists because of that gap.

Why did pure vector search miss exact matches?

Pure vector search misses exact matches because an embedding encodes meaning rather than literal tokens. When a support agent searches for ERR_MODULE_NOT_FOUND, the embedding of that string lands near the general neighbourhood of module, import, and error, which is near enough to rank a generic troubleshooting page above the one paragraph containing the code verbatim.

Three query shapes broke consistently for us: exact error codes, product SKUs and order numbers, and rare proper nouns such as a customer's company name. All three share one property: the useful signal is a low-frequency token, and averaging it into a 1536-dimension chunk embedding dilutes it into noise.

Chunk size makes the dilution worse. A 1,500-token chunk produces one vector representing the average of everything in it, so a single decisive sentence barely moves that vector. Cutting chunks to roughly 200 to 400 tokens with a small overlap improved our retrieval more than any embedding-model change we tried.

Postgres full-text search has the exact opposite failure mode. The expression websearch_to_tsquery('english', 'how do I stop being billed') will not match a document titled Subscription termination, because the two share no stems. Lexical search nails identifiers and fails at paraphrase; vector search nails paraphrase and fails at identifiers. That complementary failure is the entire argument for hybrid search.

How do we combine full-text and vector search in one SQL query?

We run both searches as separate CTEs, rank each independently, then fuse the ranks with Reciprocal Rank Fusion in a single Postgres query. RRF ignores raw scores entirely and uses only position, which is why it needs no tuning: cosine distance lives on roughly 0 to 2, while ts_rank_cd is an unbounded relevance number, and any attempt to weight them directly turns into a magic-constant hunt.

Reciprocal Rank Fusion assigns each row a score of 1 / (k + rank) in every arm where it appears, then sums those scores. The constant k damps the influence of the very top ranks; k = 60 is the value from the original RRF paper and the one we have never needed to change.

The query shape is a semantic CTE ordering on embedding <=> $1::vector with LIMIT 50, a keyword CTE ordering on ts_rank_cd(tsv, websearch_to_tsquery('english', $2)) DESC with LIMIT 50, then a final SELECT that LEFT JOINs both CTEs and orders by COALESCE(1.0 / (60 + s.rank), 0.0) + COALESCE(1.0 / (60 + k.rank), 0.0) DESC.

Two details matter. The LEFT JOIN plus COALESCE pattern is what lets a row win by being strong in one arm alone, which is how an exact SKU match survives even when its embedding ranks nowhere. And websearch_to_tsquery is the right parser for user input, because it accepts quoted phrases and minus-prefixed exclusions and never throws a syntax error on hostile input the way to_tsquery does.

We retrieve 50 per arm and return 10. Retrieving more per arm costs almost nothing in Postgres and gives RRF enough overlap to be meaningful; retrieving 5 per arm produces two disjoint lists and fusion has nothing to fuse.

HNSW or IVFFlat: which pgvector index should we build?

Build HNSW unless index build time or memory is the binding constraint. HNSW is a graph index that supports incremental inserts and does not require the table to be populated first. IVFFlat partitions existing vectors into lists and must be built after representative data exists, or recall collapses.

- Build on an empty table: HNSW yes, IVFFlat no, because IVFFlat needs representative rows before the index is created.

- Build parameters: HNSW uses m and ef_construction, IVFFlat uses lists.

- Query-time knob: HNSW uses hnsw.ef_search with a default of 40, IVFFlat uses ivfflat.probes with a default of 1.

- Cost profile: HNSW builds slower and uses more memory, IVFFlat builds faster and uses less.

- Recall per unit of latency: HNSW is better, IVFFlat is lower at equivalent latency.

- Heavy insert workloads: HNSW handles them incrementally, IVFFlat degrades and needs periodic rebuilds.

The knob that actually changes results in production is hnsw.ef_search, which controls how many candidates the graph traversal keeps. It defaults to 40, and raising it trades latency for recall. We set it per transaction with SET LOCAL hnsw.ef_search = 100 rather than globally, so a background reindexing job and a user-facing query can use different values.

Why does adding a WHERE filter return fewer rows than the LIMIT?

An HNSW query with a WHERE clause can return fewer rows than the LIMIT requests because the index traversal collects ef_search candidates first and the filter is applied afterwards. If you ask for 10 chunks belonging to one tenant and that tenant owns 0.1% of the table, most of the 40 default candidates are discarded and two or three rows come back.

This bit us on a multi-tenant knowledge base, and it is the worst class of bug: no error, no slow query, just quietly thinner context arriving at the model. We reach for three fixes in order.

- Enable iterative scans. pgvector 0.8.0 added hnsw.iterative_scan, which keeps scanning the index until enough rows survive the filter. Use relaxed_order for throughput or strict_order when results must come back in exact distance order, and cap the work with hnsw.max_scan_tuples.

- Use a partial index when the filter is a small fixed set of values, for example WHERE deleted_at IS NULL. A partial HNSW index over live rows removes the problem instead of mitigating it.

- Raise ef_search as a blunt instrument when the filter is only mildly selective. It costs latency and does not scale to very selective filters.

What broke in production?

The dimension mismatch is the first error every team meets: ERROR: expected 1536 dimensions, not 768. A vector(1536) column is a hard constraint, so switching embedding models is a migration rather than a config change. Embeddings from different models are not comparable at all, which means a model swap requires a new column, a full backfill, and a cutover, never a partially re-embedded table.

The 2000-dimension index limit surprised us more. pgvector indexes vector columns up to 2,000 dimensions, so OpenAI's text-embedding-3-large at 3,072 dimensions cannot be indexed as a plain vector. There are two honest options: store it as halfvec(3072) and index with halfvec_cosine_ops, which pgvector indexes up to 4,000 dimensions and which also halves storage, or request fewer dimensions from the API using the dimensions parameter, since these models are trained so that truncated vectors remain useful.

Index builds were slower than expected on a few hundred thousand rows, because the default maintenance_work_mem is far too small for a graph index. Raising maintenance_work_mem so the index fits in memory, together with max_parallel_maintenance_workers, turned a build we were ready to run overnight into one we ran inside a deploy window.

Row width is the quiet problem. A vector(1536) value occupies roughly 6 KB, well past the point where Postgres moves the column out of line into TOAST storage, so every row fetch becomes an extra read. Keeping the chunk table narrow, with ids, body, embedding, and tsvector and nothing else, then joining to document metadata, was worth more than any query rewrite.

Finally, the latency we chased in SQL was not in SQL. Embedding the user's query is a network round trip to a model provider on every single search, and it dominated our P95 long before Postgres did. Caching embeddings for repeated queries, and issuing the embedding call concurrently with the rest of the request, was the actual win.

FAQ

Q: Do we need a dedicated vector database instead of pgvector?

A: Not for application-scale retrieval in the low millions of chunks. Keeping vectors in Postgres means one backup story, one connection pool, transactional consistency between documents and their embeddings, and the ability to join retrieval results against permissions and metadata in the same query, which a separate vector service cannot do without a second round trip.

Q: Which distance operator should we use with OpenAI embeddings?

A: Use cosine distance, the <=> operator, with an HNSW index created using vector_cosine_ops. The index operator class must match the operator in the ORDER BY clause, or Postgres silently falls back to a sequential scan.

Q: Does hybrid search require storing two embeddings per chunk?

A: No. Hybrid search needs one vector column for semantic similarity and one tsvector column for lexical matching. The tsvector is best written as a GENERATED ALWAYS AS ... STORED column so it can never drift from the body text.

Q: How many results should we retrieve before passing them to the model?

A: Retrieve around 50 candidates per arm, fuse with RRF, then pass the top 5 to 10 fused chunks to the model. Feeding more chunks than that usually lowers answer quality, because irrelevant context competes with relevant context inside the prompt.

Q: Is Reciprocal Rank Fusion better than weighted score blending?

A: RRF is the better default because it needs no tuning and is immune to the different score scales of cosine distance and ts_rank_cd. Weighted blending can beat it once labelled relevance data exists to tune against; without that data, tuning weights is guesswork.

Q: Should we add a reranking step on top of hybrid search?

A: Add a cross-encoder reranker only after hybrid search is in place and you can measure that the right chunk is retrieved but ranked too low. Reranking cannot recover a chunk that retrieval never returned, so fixing recall first is strictly the higher-leverage move.