blog/hybrid-search-postgres-tsvector-pgvector-rrf-hero
← Back to blog

Hybrid Search in Postgres: tsvector, pgvector and Why ts_rank Is Not BM25

Technology•16 November 2026•TREEO Indonesia•5 min read

If you already run Postgres, you can build a hybrid search for a RAG system without adding a search cluster or a separate vector database. The pattern is well documented in practitioner writing. It also has one trap that appears in almost every tutorial: Postgres full-text ranking looks like BM25 and is not. This guide covers the design, the trap, and what the evidence says about when to add more.

The evidence here is mostly practitioner blogs and extension documentation, with the original reciprocal rank fusion paper and one preprint that criticizes it. There is no source in the research that tests chunk size, overlap, or Indonesian-language retrieval, and this guide says so where it matters.

The hybrid pattern at a glance

1 table

one row per chunk, with a generated tsvector column and a vector column

60

the standard smoothing constant k in reciprocal rank fusion

0.016393

score of a chunk found at rank 1 by only one branch, at k = 60 (1/61)

The baseline: one table, two indexes, one query

A common design stores one row per chunk. A generated `tsvector` column is built from the text, so the keyword index cannot drift out of step with the content, and it gets a GIN index. A vector column holds the embedding and gets an HNSW index. The embedding dimension must match the model exactly (1024, 1536, 768 and so on) or inserts fail.

At query time, two small queries run side by side: a lexical one against the tsvector and a semantic one against the vectors. Each returns a short ranked candidate list. The lists are then fused. Field weighting is one simple option: `setweight` can give title matches weight A and body matches weight B so a title hit outranks the same word in the body.

Fusing the lists with reciprocal rank fusion

Reciprocal rank fusion (RRF) sums `1 / (k + rank)` for each chunk across the lists. Because it uses ranks only, it needs no normalization of scores that live on different scales. The original RRF paper reported, on TREC Robust, a mean average precision of .3686 for RRF against .3586 for the best single system, and the practitioner default of k = 60 comes from it.

RRF is not undisputed. Bruch, Gai and Ingber find it sensitive to its parameter and report that a convex combination of lexical and semantic scores performed better in and out of domain, while needing only a small training set to tune one parameter. A raw weighted sum is risky because the score scales differ. The practical resolution in the sources is to start with RRF, build an evaluation set, and tune fusion only once you have one.

Two housekeeping points from a worked example: keep one chunk identity through the whole pipeline (a chunk ID tied to a document version), and deduplicate within each branch, or duplicates will eat the context budget and get inconsistent credit.

ts_rank is not BM25

BM25 is a probabilistic ranking function with inverse document frequency (IDF) weighting and document-length normalization. Postgres's `ts_rank` is a simpler term-frequency score. `ts_rank_cd` adds cover density, meaning how close together the matched terms are. Neither has IDF, so a match on a rare, high-signal term counts the same as a match on a common word, and calling `ts_rank_cd` "BM25-style" hides a real difference.

OptionWhat it providesCost
`ts_rank` / `ts_rank_cd`Built in; term frequency and proximityNo IDF or length normalization
pg_textsearchA transactional BM25 index kept up to date automatically; defaults k1 = 1.2, b = 0.75; text configurations for 29+ languagesAn extension to install, pin and upgrade; described at launch as a preview release that keeps indexes in memory up to a default 64MB
ParadeDB pg_searchA real BM25 index over the textAnother extension to carry
VectorChord-BM25BM25 scoring with field boosting and phrase handlingAnother extension to carry

Is the difference worth fixing? One practitioner argument says usually not for the lexical arm of a hybrid system: RRF only uses rank order, and the same set of documents matches whichever function ranks them, so the native scorer mostly decides order inside a list that fusion reshuffles anyway. The advice that follows is to ship native full-text search with RRF, measure retrieval on a real evaluation set, and adopt a BM25 extension only when the numbers show the lexical side is the bottleneck. Adding one on day one because a benchmark blog said so is how a team inherits a dependency it did not need. This is practitioner opinion, not a controlled comparison, and no published equivalence test between the BM25 extensions was found.

Details that bite

- Strict queries. `plainto_tsquery` joins terms with AND, which can be too restrictive for a long natural-language question. Identifiers such as error codes or case numbers are better matched exactly through a separate normalized field, with an explicit policy for case, hyphens and leading zeros. If a query looks like ABC-123 or a path, leaning toward exact and phrase matching often works better, and a simple routing rule beats an elaborate classifier. - Filtered vector search. Approximate vector indexes can return fewer matches when a selective filter is applied after the index scan. When neighbours go missing, compare against exact search. - Filter order. One source advises pre-filtering for selective filters such as tenant or category, and post-filtering for score thresholds. The sources give no method for choosing a score cutoff.

What the evidence says about chunking and query rewriting

Less than hoped. A practitioner argues that embedding a two- or three-sentence summary can retrieve better than embedding a long raw text, because a long passage is diluted into one fixed-size vector. On a financial text-and-table benchmark reported in a preprint, hybrid retrieval followed by neural reranking reached Recall@5 of 0.816 and MRR@3 of 0.605, ahead of single-stage methods. The same work found query expansion with HyDE or multi-query, and adaptive retrieval, gave limited benefit on precise numerical queries, while contextual retrieval gave consistent gains.

The research found no reliable source for chunk size or overlap, small-to-big parent retrieval, or score-threshold calibration, and none for Indonesian or other cross-lingual retrieval. Treat any specific number you read for those as untested until you try it on your own documents.

When not to do this

Three honest exclusions come from the sources. If the corpus is small and every query is a natural-language question with no codes or names, pure vector search is simpler and fine. If users only ever search exact tokens, full-text search alone is enough. And if a dedicated vector store and search cluster are already working well, do not merge them into Postgres for its own sake. Hybrid in Postgres earns its place by removing infrastructure.

For more on running your own stack, see open-source AI tools for geospatial work and building an AI server we own.

Frequently Asked Questions

No. `ts_rank` and `ts_rank_cd` have no IDF weighting and no BM25-style length normalization. BM25 in Postgres comes from extensions such as ParadeDB's pg_search, VectorChord-BM25 or pg_textsearch.

Turn climate goals into a verified portfolio

TREEO connects the full carbon cycle — eligibility, simulation, real-time monitoring and registry-ready reporting — combining expert consulting with dMRV technology so the evidence exists before anyone asks for it.

Related Articles