pgvector Hybrid Search: Combine Full-Text and Vector Search in PostgreSQL
How to build pgvector hybrid search in PostgreSQL: a generated tsvector, HNSW, reciprocal rank fusion, the tokenizer trap with SKUs and error codes, and filtered iterative scans, with lab results.

A customer types WMS-245 into your support assistant. Another asks whether a jacket they wore once can still go back after three weeks. A warehouse lead pastes trl6764 blk s because that is what the label printer shows. A retrieval system for a store, a SaaS help centre or an internal ops wiki has to answer all three, and no single search method handles them equally well.
Vector search is good at the second question and unreliable at the first and third. Keyword search is the reverse. pgvector hybrid search runs both inside PostgreSQL and merges the rankings, so one query can match meaning and exact identifiers. This guide shows the schema, the SQL, a tokenizer problem that quietly breaks keyword matching on product codes, and the numbers from a small lab we ran on PostgreSQL 18.7 with pgvector 0.8.6.
Why vector-only retrieval misses the answers that matter
Embedding models turn text into points where similar meaning sits close together. That is exactly what you want for “the box came crushed, what should the buyer do?”, which shares almost no words with a policy titled “Damaged on arrival”. It is not what you want for identifiers. To an embedding model, TRL-6764-BLK-S and TRL-6764-BLK-M are nearly the same string, and the product pages behind them are nearly the same text. The model has no reason to rank the exact variant first.
Commerce and SaaS data is full of these strings: SKUs, order numbers, error codes, invoice IDs, tariff codes, plan names, API field names. When a retrieval-augmented generation (RAG) pipeline hands the LLM the wrong variant, the answer is fluent and wrong, which is worse than no answer. In our lab, vector-only search put the correct document in the top five for 90% of exact identifier queries, but its mean reciprocal rank on those queries was 0.77: the right page was often second or third behind a sibling variant.
Keyword search fixes identifiers but fails on paraphrase. PostgreSQL full-text search requires the query words (after stemming) to appear in the document, so “item got cheaper a week after I bought it” finds nothing in a policy that says “drops in price within 14 days”. Each method covers the other's blind spot, which is the whole argument for hybrid retrieval.
What hybrid search means in PostgreSQL
Hybrid search runs two or more retrievers over the same rows and fuses their ranked lists. In PostgreSQL the parts are all standard:
- Semantic list: a
vectorcolumn from pgvector with an HNSW index, queried by cosine distance (<=>). - Lexical list: a
tsvectorcolumn with a GIN index, queried withwebsearch_to_tsqueryand ranked withts_rank_cd. - Identifier list (optional, recommended): a normalized
text[]of codes found in each chunk, matched exactly with a GIN index. - Fusion: reciprocal rank fusion (RRF), which scores each row as the sum of
1 / (k + rank)across the lists it appears in.
The pgvector README itself points to full-text search plus RRF or a cross-encoder for hybrid search. The advantage of doing it in Postgres is that the business row, its embedding and its search index live in one transaction. When a product is deleted or a policy is edited, there is no second system to keep in sync, and tenant filters and row-level security apply to every list.

Schema: one table, three indexes
Here is the table we used. The embedding dimension (384) matches the small open model in our lab; use whatever your model outputs.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE kb_chunks (
id bigint PRIMARY KEY,
tenant_id bigint NOT NULL,
kind text NOT NULL, -- product, policy, error, sop ...
title text NOT NULL,
content text NOT NULL,
idents text[] NOT NULL DEFAULT '{}',
embedding vector(384) NOT NULL,
tsv tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', content), 'B')
) STORED
);
CREATE INDEX kb_chunks_embedding_idx ON kb_chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX kb_chunks_tsv_idx ON kb_chunks USING gin (tsv);
CREATE INDEX kb_chunks_idents_idx ON kb_chunks USING gin (idents);
CREATE INDEX kb_chunks_tenant_idx ON kb_chunks (tenant_id);
Why a generated column for the tsvector
A stored generated column recomputes the tsvector whenever the title or content changes, so the lexical index can never drift from the text. The embedding cannot be generated in SQL, so it is written by your ingestion worker. Store a content hash next to it and re-embed only when the hash changes; that keeps embedding costs tied to real edits rather than to every sync run.
Pin the pgvector version
pgvector 0.8.7 was released on 1 October 2026 to fix a buffer overflow in IVFFlat index builds (CVE-2026-103484). Our lab used 0.8.6 from conda-forge with HNSW only, but for any real deployment upgrade to 0.8.7 and check what your managed provider ships before relying on features such as iterative index scans, which arrived in 0.8.0.
The hybrid query with reciprocal rank fusion
RRF ignores raw scores. A cosine distance of 0.21 and a ts_rank_cd of 0.4 are not comparable, and weighted sums of them break whenever the corpus or the model changes. Ranks are comparable. The constant k = 60 comes from the original RRF paper by Cormack, Clarke and Büttcher (SIGIR 2009) and is still the usual default; it stops a single first-place result from dominating.
WITH semantic AS (
SELECT id, row_number() OVER (ORDER BY dist) AS r
FROM (
SELECT id, embedding <=> $1::vector AS dist
FROM kb_chunks
WHERE tenant_id = $3
ORDER BY dist -- inner ORDER BY + LIMIT lets HNSW drive the scan
LIMIT 50
) s
),
lexical AS (
SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, q) DESC, id) AS r
FROM kb_chunks, websearch_to_tsquery('english', $2) q
WHERE tenant_id = $3 AND tsv @@ q
LIMIT 50
),
ident AS (
SELECT id, row_number() OVER (ORDER BY id) AS r
FROM kb_chunks
WHERE tenant_id = $3 AND idents && $4::text[]
LIMIT 50
)
SELECT id,
coalesce(1.0 / (60 + semantic.r), 0)
+ coalesce(1.0 / (60 + lexical.r), 0)
+ coalesce(1.0 / (60 + ident.r), 0) AS score
FROM semantic
FULL JOIN lexical USING (id)
FULL JOIN ident USING (id)
ORDER BY score DESC, id
LIMIT 10;
Three details matter. First, keep the candidate limit per list (50 here) at or below what the HNSW scan can actually return; more on that in the filtering section. Second, apply the tenant filter in every list, not only after the join, otherwise another tenant's rows can win a slot and then disappear, leaving you with fewer than ten results. Third, use a FULL JOIN: a row found by only one retriever must still be eligible.
The tokenizer trap: SKUs and error codes in PostgreSQL full-text search
This is the part most hybrid search tutorials skip. PostgreSQL's default text search parser was designed for prose, and it splits codes in ways you might not expect. Running ts_debug on our data showed:
SELECT alias, token, lexemes
FROM ts_debug('english', 'SKU TRL-6764-BLK-S error WMS-245');
asciiword | TRL | {trl}
int | -6764 | {-6764}
asciihword | BLK-S | {blk-s}
hword_asciipart | BLK | {blk}
hword_asciipart | S | {} -- "s" is an English stop word
asciiword | WMS | {wms}
int | -245 | {-245} -- parsed as a negative integer
When the user types the code exactly as stored, the query is tokenized the same way and the match works. In our lab, full-text search found 100% of exact identifier queries. But people rarely type codes exactly. WMS 245 produces the lexeme 245, not -245, and wms245 is a single unknown word. Across 20 realistically mistyped identifiers, full-text search matched zero.
The second surprise is that websearch_to_tsquery combines unquoted words with AND. That is right for a search box, but a conversational question such as “how to fix WMS-245” becomes 'fix' & 'wms' <-> '-245', and if the document never says “fix”, there is no match. Full-text search returned nothing for all 15 natural-language questions and all 13 mixed queries in our set. In that situation, the hybrid result is simply the vector result.
Add a normalized identifier list
The fix that worked best was not a smarter tokenizer but a separate, boring list. At ingestion, extract anything that looks like one of your identifier formats, strip separators, upper-case it and store every meaningful prefix. Run the same function on the query.
import re
IDENT = re.compile(
r"\b([A-Za-z]{3})[\s-]?(\d{3,4})(?:[\s-]?([A-Za-z]{3}))?(?:[\s-]?(XL|S|M|L))?\b",
re.I,
)
def idents(text: str) -> list[str]:
out = set()
for m in IDENT.finditer(text):
parts = [p.upper() for p in m.groups() if p]
for k in range(2, len(parts) + 1): # WMS245, TRL6764, TRL6764BLK, TRL6764BLKS
out.add("".join(parts[:k]))
return sorted(out)
idents("trl6764 blk s") # ['TRL6764', 'TRL6764BLK', 'TRL6764BLKS']
The regular expression has to match your formats, not ours. Shopify SKUs are free text set by the merchant, so look at real catalogue data before writing the pattern. Order names like #1042, carrier tracking numbers and HS codes each need their own rule. Keep the function in one shared module so ingestion and query time cannot diverge.
What our lab measured
We built a synthetic knowledge base for an outdoor-gear store: 160 product variant pages (8 products × 5 colours × 4 sizes, each with its own SKU), 12 integration error codes with deliberately similar wording, 12 customer policies and 6 warehouse procedures. Embeddings came from BAAI/bge-small-en-v1.5 (384 dimensions) via fastembed. We wrote 68 labelled queries in four groups and measured whether the correct document appeared in the top five (recall@5).
- Exact identifiers (20): vector 90%, full-text 100%, vector + full-text RRF 100%, RRF + identifier list 100%.
- Mistyped identifiers (20): vector 85%, full-text 0%, vector + full-text RRF 85%, RRF + identifier list 100%.
- Identifier plus words (13), e.g. “care instructions for PCK-…”: vector 92%, full-text 0%, vector + full-text RRF 92%, RRF + identifier list 100%.
- Plain-language questions (15): vector 100%, full-text 0%, vector + full-text RRF 100%, RRF + identifier list 100%.
Mean reciprocal rank over all 68 queries went from 0.77 (vector only) to 0.83 (vector + full-text) to 0.95 with the identifier list. We also tried rewriting the lexical query as OR terms instead of AND. That rescued some mistyped codes but pulled noisy matches into plain-language questions, where recall@5 dropped to 73%.

Treat these numbers as a direction, not a benchmark. The corpus is small and synthetic, and we wrote the queries ourselves. The point is the pattern: hybrid search only helps where the lexical retriever can match, and on identifier-heavy commerce data that depends on how you tokenize codes. The scripts and raw results are kept with the article's source files, and the method is easy to repeat on your own data.
Filters, tenants and the ef_search ceiling
pgvector's HNSW index applies WHERE filters after it collects candidates, and by default it collects at most hnsw.ef_search = 40 of them. In a multi-tenant table, a filter that matches 1% of rows will often return far fewer than the ten you asked for. We tested this on 100,000 random 384-dimension vectors spread across 100 tenants (971 rows for the tenant we queried):
-- default settings
SELECT count(*) FROM (
SELECT id FROM tenant_chunks WHERE tenant_id = 7
ORDER BY embedding <=> $1 LIMIT 10) s; -- returned 1 row
SET hnsw.iterative_scan = relaxed_order; -- pgvector 0.8.0+
-- same query -- returned 10 rows
Iterative index scans keep walking the graph until enough rows pass the filter or hnsw.max_scan_tuples (default 20,000) is reached. relaxed_order can return results slightly out of distance order, which does not matter here because RRF re-ranks them anyway. Set it per transaction with SET LOCAL in the code path that runs retrieval, so it does not leak into unrelated queries through a pooled connection. If you have a small number of large tenants, partial HNSW indexes per tenant are another option; if you have thousands of small tenants, an exact scan over the tenant's rows using the B-tree index can be faster than the ANN index at all.
Random vectors are a poor test of recall quality, so we only report row counts here. Measure recall on your own embeddings against an exact (sequential scan) baseline before you tune ef_search.
Where this fits in real products
- Store support assistants. Answers that mention a SKU, order number or return policy need the exact record, and the policy needs semantic matching. One hybrid query covers both.
- B2B catalogue lookup. Buyers paste part numbers from purchase orders with inconsistent separators. The identifier list handles formatting, and the vector list handles “the 22 litre pack with a laptop sleeve”.
- SaaS help centres and internal runbooks. Error codes, API field names and plan names sit next to prose explanations. This is the same retrieval layer described in our guide to RAG for business knowledge bases.
- Laravel and Django apps already on PostgreSQL. Laravel 13 added native vector queries for PostgreSQL with pgvector to its query builder, and Django projects can run the SQL above directly. Either way, no extra database is required.
For multi-tenant SaaS, the retrieval layer should inherit the same isolation rules as the rest of the app. Our article on multi-tenant SaaS architecture covers row-level security and tenant-scoped indexes in more depth.
Common mistakes
- Adding scores instead of ranks. Cosine distance and
ts_rank_cdlive on different scales. Use RRF or a trained reranker. - Assuming full-text search handles codes. Check your identifiers with
ts_debug. Hyphens, digits and stop words change how codes are indexed. - Filtering after the fusion. Tenant and permission filters belong inside each retriever, or results vanish and access rules depend on luck.
- Leaving ef_search at 40 with selective filters. Enable iterative scans or restructure the index.
- Indexing NULL or zero vectors. pgvector does not index NULL vectors, or zero vectors for cosine distance. Rows waiting for embeddings silently drop out of the semantic list, so track them.
- Judging retrieval by eye. A dozen spot checks will not show a 10-point recall drop after a model or chunking change. Keep a labelled query set; and pair it with
EXPLAIN ANALYZEchecks on the retrieval SQL.
Recommendations
- Start with one table holding text, a generated
tsvector, an embedding and a normalized identifier array, all indexed. - Fuse with RRF at
k = 60and 40 to 50 candidates per list. Tune only after you have measurements. - Write the identifier extractor from real data, share it between ingestion and query, and test it on deliberately messy input.
- Use
SET LOCAL hnsw.iterative_scan = relaxed_orderfor filtered retrieval, and keep tenant filters in every branch. - Build a labelled query set of at least 50 queries across identifier, mixed and plain-language types, and run it in CI whenever the model, chunking or SQL changes.
- Add a cross-encoder reranker only if the evaluation shows the right chunk is retrieved but ranked too low.
FAQ
Do I need a dedicated vector database for hybrid search?
Not for most business workloads. PostgreSQL with pgvector handles semantic and lexical retrieval in one query, with transactional consistency and existing backups and access control. A dedicated engine starts to make sense at very large vector counts or when you need features Postgres does not offer, and that decision should follow measurements.
What value of k should I use for reciprocal rank fusion?
60 is the standard default from the original paper and works well in practice. Smaller values give more weight to the top few ranks of each list. Change it only when a labelled evaluation shows a clear improvement.
Is ts_rank_cd good enough, or do I need BM25?
ts_rank_cd lacks BM25's document-length normalization and corpus-wide term statistics, but in a fused setup only the order of the lexical list matters. Extensions that add BM25 to Postgres exist; try them if keyword ranking is clearly the weak link in your evaluation.
Does hybrid search make queries slower?
It runs two or three index scans instead of one. On our 190-row lab table the single-statement hybrid query had a median time under half a millisecond, which tells you nothing about production scale. In most RAG systems, the embedding and LLM calls cost far more time than retrieval.
How do I pass retrieved chunks to the model safely?
Send chunk IDs with the text and ask for citations, then validate the response against a schema. Our guide to LLM structured outputs shows how to enforce that shape.
Conclusion
pgvector hybrid search is not a new database or a new framework. It is a few columns, three indexes and a fusion query on the PostgreSQL you probably already run. The real gains come from the less glamorous details: knowing how Postgres tokenizes your SKUs and error codes, giving identifiers their own normalized list, keeping filters inside each retriever, and lifting the ef_search ceiling when tenants are involved. Measure each change against a labelled query set, and the retrieval layer under your assistant becomes something you can reason about instead of something you hope works.
If you are planning a support assistant, catalogue search or internal knowledge tool on Shopify, Laravel, Django or a custom SaaS stack, CodeSapient designs and builds these retrieval pipelines end to end. You can look at our services or get in touch to talk through your data and constraints.
