Hybrid Search in Postgres with pgvector: Field Notes on HNSW, tsvector, and Why Pure Vector Search Missed Exact Matches

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 I 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.

My 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 the notes from moving that pipeline to hybrid search on Postgres 17 with pgvector 0.8, kept in the same database as the application data.

Key takeaways

• pgvector 0.8.0 adds four vector types (vector, halfvec, bit, sparsevec) and two ANN 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 combines both retrieval arms without normalising scores. RRF scores each row as the sum of 1 / (k + rank) across arms, with k = 60 as the common default.
• HNSW is the default index and IVFFlat is the exception. HNSW builds on an empty table; IVFFlat 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 worth memorising, because choosing the wrong one silently degrades ranking instead of throwing: <=> is cosine distance, <-> is L2 distance, <#> is negative inner product, and <+> is L1 distance. OpenAI's text-embedding-3-small and most hosted models return normalised vectors, so I 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 ordering on <=>, and Postgres falls back to a sequential scan without complaining. My first "pgvector is slow" investigation was exactly that mismatch, visible in one EXPLAIN ANALYZE.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  body        text NOT NULL,
  embedding   vector(1536) NOT NULL,
  tsv         tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);

CREATE INDEX chunks_embedding_hnsw
  ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

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.

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 "module", "import", and "error" in general — near enough to rank a generic troubleshooting page above the one paragraph containing the code verbatim.

Three query shapes broke consistently for me: exact error codes, product SKUs and order numbers, and rare proper nouns such as a customer's company name. All three share a 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 it worse. A 1,500-token chunk produces one vector representing the average of everything in it, so a single decisive sentence barely moves the vector. Cutting chunks to roughly 200–400 tokens with a small overlap improved my retrieval more than any embedding-model change I tried.

Postgres full-text search has the opposite failure mode. websearch_to_tsquery('english', 'how do I stop being billed') will not match a document titled "Subscription termination", because no shared stem exists. That complementary failure is the entire argument for hybrid search.

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

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

Reciprocal Rank Fusion assigns each row a score of 1 / (k + rank) in every arm it appears in, then sums those scores. k = 60 is the value from the original RRF paper and the one I have never needed to change.

WITH semantic AS (
  SELECT id, RANK() OVER (ORDER BY embedding <=> $1::vector) AS rank
  FROM chunks
  ORDER BY embedding <=> $1::vector
  LIMIT 50
),
keyword AS (
  SELECT c.id, RANK() OVER (ORDER BY ts_rank_cd(c.tsv, q) DESC) AS rank
  FROM chunks c, websearch_to_tsquery('english', $2) q
  WHERE c.tsv @@ q
  ORDER BY ts_rank_cd(c.tsv, q) DESC
  LIMIT 50
)
SELECT c.id, c.body,
       COALESCE(1.0 / (60 + s.rank), 0.0)
     + COALESCE(1.0 / (60 + k.rank), 0.0) AS score
FROM chunks c
LEFT JOIN semantic s ON s.id = c.id
LEFT JOIN keyword  k ON k.id = c.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY score DESC
LIMIT 10;

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

I retrieve 50 per arm and return 10. Retrieving 5 per arm produces two disjoint lists and fusion has nothing to fuse.

HNSW or IVFFlat: which pgvector index should I build?

Build HNSW unless index build time or memory is the binding constraint.

Property	HNSW	IVFFlat
Build on empty table	Yes	No — needs representative rows
Build parameters	m, ef_construction	lists
Query-time knob	hnsw.ef_search (default 40)	ivfflat.probes (default 1)
Build time	Slower	Faster
Index size / memory	Larger	Smaller
Recall per unit latency	Better	Lower at equal latency
Heavy inserts	Handled incrementally	Degrades; needs rebuilds

The knob that actually changes results is hnsw.ef_search, which controls how many candidates the graph traversal keeps. Set it per transaction so a background reindexing job and a user-facing query can use different values.

BEGIN;
SET LOCAL hnsw.ef_search = 100;
-- hybrid query here
COMMIT;

Why does adding a WHERE filter return fewer rows than my 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. Ask for 10 chunks belonging to one tenant that owns 0.1% of the table, and most of the 40 default candidates get discarded.

This bit me 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.

• Enable iterative scans. pgvector 0.8.0 added hnsw.iterative_scan, which keeps scanning until enough rows survive the filter. Use relaxed_order for throughput, strict_order when exact distance order matters, and cap work with hnsw.max_scan_tuples.
• Use a partial index when the filter is a small fixed set of values, e.g. WHERE deleted_at IS NULL.
• Raise ef_search as a blunt instrument when the filter is mildly selective.

SET LOCAL hnsw.iterative_scan = 'relaxed_order';
SET LOCAL hnsw.max_scan_tuples = 20000;

What broke in production?

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

The 2000-dimension index limit surprised me more. pgvector indexes vector columns up to 2,000 dimensions, so text-embedding-3-large at 3,072 dimensions cannot be indexed as a plain vector. 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.

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 and max_parallel_maintenance_workers turned an overnight build into a deploy-window build.

Row width is the quiet one. 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 and joining to document metadata was worth more than any query rewrite.

Finally, the latency I chased in SQL was not in SQL. Embedding the user's query is a network round trip on every search, and it dominated my P95 long before Postgres did.

FAQ

Q: Do I 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 embeddings, and the ability to join retrieval results against permissions in the same query.

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

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

Q: Does hybrid search require two embeddings per chunk?

A: No. It needs one vector column for semantic similarity and one tsvector column for lexical matching. Write the tsvector as a GENERATED ALWAYS AS ... STORED column so it can never drift from the body text.

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

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

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

A: 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 the higher-leverage move.

---

Originally published on devya.dev. Also on eng-ahmed.com. Built by Devya Solutions.