Hybrid Search in PostgreSQL in Practice: BM25 + Vector Retrieval in One Go

In app search, the most common dilemma is this: the articles are right there in the database, yet search results always seem to fall a bit short. When a user searches for "animals that make their own light," keyword matching may return nothing, because the articles say "bioluminescence." Conversely, semantic search may turn "Apollo 13" into some other space mission. Each retrieval method has its blind spots, and in a recent freeCodeCamp course, Beau Carnes demonstrates a pragmatic path: instead of introducing a second search system, combine keyword retrieval (BM25) and vector retrieval directly inside PostgreSQL, then merge the two result sets with Reciprocal Rank Fusion (RRF).

Why Keep Search Inside the Database

The course opens with a real scenario: the app's articles already live in Postgres, but the team brought in a separate search service for better ranking. From then on, every article change had to be replicated to a second system, and replica lag, lost delete events, and users seeing stale versions during reindexing all became new sources of failure.

The author's choice is to keep the search index and the articles in the same database. The benefit is that the data and its index share a single transaction boundary, and search can be freely combined with existing relational queries. This isn't to say dedicated search engines have no value, but rather a reminder: when your data scale is manageable within a single database, an extra system is just extra consistency cost.

According to the course, the sample project WikiSearch's keyword index covers about 242,000 Simple English Wikipedia articles, while semantic search covers only about 25,000, to keep embedding computation manageable. The interface displays both counts, and this difference matters when comparing results. The stack is pgTextSearch (providing BM25 ranking) plus pgVector and pgVectorScale (semantic retrieval), runnable on Tiger Cloud or deployed locally per the repo instructions. pgTextSearch itself is an open-source extension under the PostgreSQL license.

BM25 is a classic ranking function in information retrieval and a type of probabilistic model. It computes relevance based on three core factors: term frequency (TF, how often a term appears in a document), inverse document frequency (IDF, how rare the term is across the corpus), and document length normalization. IDF lowers the weight of common words (like "the" or "is") and raises the weight of rare ones; length normalization prevents long documents from getting an unfair advantage simply because they contain more words. BM25 has two tunable parameters: k1 controls how quickly term frequency saturates (default 1.2), and b controls the strength of length normalization (default 0.75). Unlike PostgreSQL's built-in ts_rank, BM25 requires global statistics (document frequency, average document length), which is why it typically needs a dedicated index structure.

Matching and Ranking Are Two Different Things

The course repeatedly stresses a distinction that's easy to overlook: matching determines which documents qualify for the results, and ranking determines which ones come first.

PostgreSQL's built-in full-text search illustrates the point. The author builds queries with to_tsquery, where & means all terms must appear. Searching for "volcano eruption ash cloud aviation" returns zero results, because no article contains all five terms. Switching to | (OR) immediately produces results, which are then ordered by ts_rank. Who's allowed in and who's shown first are two independently adjustable knobs.

The built-in approach's ceiling lies in ranking. ts_rank lacks corpus-wide document frequency statistics, so it can't distinguish a word that's everywhere from one that points to a narrow topic. That's exactly what BM25 fixes: the fewer documents a term appears in, the stronger the evidence; repetition helps but with diminishing returns (the k1 parameter controls saturation speed, default 1.2); document length must be normalized (the b parameter, default 0.75). Only all three together let a "rare-term hit" genuinely boost the score.

Putting BM25 to Work: Negative Scores and Index Scans

In pgTextSearch, building a BM25 index takes a single DDL statement specifying the table, column, and text configuration. The query syntax is worth noting: the operator is <@> rather than @@, and the extension returns negative BM25 scores. So the strongest match is the most negative number, and you sort ascending—the exact opposite of the descending logic for ts_rank above. The author specifically warns to double-check sort direction when switching between the two methods.

Comparing execution plans better illustrates the architectural difference. The built-in retrieval plan has a separate Sort step—find matches, then sort them; the BM25 index scan returns rows directly in relevance order with no extra sort. The course's timing shows roughly 0.3 ms for the native query and about 9 ms for BM25, but the author explicitly states this doesn't mean one is faster—the former returns zero rows and the latter returns 10, so they're doing different work. This cautious attitude toward benchmark numbers runs throughout the video.

Internally, the index uses an inverted structure with block-max WAND optimization, which can skip inverted blocks that can't improve the current results. This is also why the combination of "ordered scores + LIMIT" matters so much: we're asking the index for a small set of strong candidates, not computing all matches and then truncating.

Semantic Retrieval: Vector Columns and Approximate Indexes

The first step in semantic retrieval is turning text into numbers. The course uses a local model to generate 384-dimensional vectors for each article, taking only the title plus the first 1,500 characters of the body—meaning it retrieves "the meaning expressed at the start of the article," and concepts buried deep at the end of a long piece may be missed. This is a design tradeoff, not a flaw.

Accordingly, the schema adds a vector column to the articles table, and pgVectorScale builds a vector index. Queries compare distances, where smaller distances rank higher. Because it uses approximate search, the index may miss some true nearest neighbors; and being semantically "close" doesn't mean "correct"—searching for one space mission may return another. Embedding jobs are written in batches, so a failed rerun resumes from the breakpoint, and the script also verifies file checksums to keep the course reproducible.

Vector retrieval typically uses approximate nearest neighbor (ANN) algorithms for speed, the common ones being HNSW (Hierarchical Navigable Small World graphs) and IVFFlat (inverted file with flat quantization). These indexes trade a small amount of recall for a large gain in query speed, so they may miss some true nearest neighbors. pgVector is PostgreSQL's vector extension, supporting vector storage, distance computation (such as cosine distance and Euclidean distance), and ANN indexes. pgVectorScale adds extra extension features on top of pgVector, such as tools for batch embedding and index management. In semantic retrieval, text is converted into dense vectors by an embedding model, and similarity is usually measured by cosine distance or inner product; smaller distance means closer in meaning. Because embedding models capture only semantic information in the text and don't do keyword matching, they may return results that are semantically related but don't match literally (e.g., matching "Apollo 13" to other space missions).

Fusion: RRF Doesn't Add Scores

The two result sets use different score systems, and BM25 scores and vector distances can't be added directly. The course uses Reciprocal Rank Fusion (RRF): it looks only at each article's position in each list, scoring with 1/(60+rank). An article that appears in both lists gets two contributions.

In practice, each method takes 20 candidates, which are merged, aggregated by article ID, scores summed, and the top 10 taken. The course's execution plan shows: 20 keyword rows, 20 vector rows, and 40 records after aggregation deduplicated to 32 articles—meaning 8 were found by both methods. These double-hit articles naturally have an edge, which is exactly RRF's intent: consensus is more trustworthy than a high rank from a single path.

The app uses a semantic_weight parameter to adjust the two paths' weights: 0 means pure keyword, 1 means pure semantic, and values in between allocate proportionally. The course recommends changing only one variable at a time and repeating the same set of queries to observe which results rise or fall.

Reciprocal Rank Fusion (RRF) is a parameter-free fusion method often used to merge results from multiple retrieval systems. Its core idea: rather than directly comparing scores across systems (whose scales and distributions may be completely different), it uses only each document's rank in its respective result list. For each document, the RRF score is the sum of 1/(k + rank) across all lists, where k is a smoothing constant (usually 60) and rank is the document's position in the list (starting at 1). The k value weakens the absolute advantage of top-ranked documents, making score changes smoother. RRF's advantages are that it's simple, requires no training, is robust to anomalous scores, and naturally rewards documents that appear in multiple lists (i.e., "consensus"). In hybrid retrieval, RRF effectively balances the contributions of keyword and semantic retrieval, preventing one path from dominating the final ranking because its score scale is larger.

Real Tests: The True Differences Between Three Modes

The course sets three fixed test queries: a specific "Apollo 13," a descriptive one that doesn't use the original words ("animals that make their own light"), and a multi-word combination ("volcano eruption ash cloud aviation").

In the Apollo 13 example, keyword mode puts it straight at number one, semantic mode scatters related topics across other space missions, and in hybrid mode Apollo 13 rises while some semantically related surrounding results are retained. The author's judgment is that the hybrid results are best overall, but candidly admits a hard flaw in the comparison: keyword covers the whole corpus, while semantic covers only the 25,000 articles that were embedded—articles without vectors aren't even eligible to participate in fusion. A fair comparison would require embedding the entire collection first.

The insert demo also validates this flow: a new article's vector is first generated by the app, then written to the database in the same transaction as the text. The transaction guarantees all-or-nothing, so there are no half-finished records with text but no vector. After insertion, all three modes can find it.

Boundaries and Known Limitations

The course ends with several clear boundaries. Typo tolerance isn't part of the current solution and needs extra handling; to expand semantic coverage, you can only increase the number of embedded articles; chunking long documents before embedding lets retrieval reach beyond the opening; and further still, a re-ranker could reorder the top results.

On performance, the author cites a vendor benchmark from Tiger Data using about 138 million passages, comparing pgTextSearch 1.0 with ParadeDB 0.21.6: with data already cached, pgTextSearch is faster on short queries and handles higher concurrency, while ParadeDB builds indexes about twice as fast. The original explicitly notes this is the vendor's own testing and measured an older version, so it can't be directly applied to the 1.5.1 used in the course—it's only background reference.

The core argument of the whole approach is actually quite simple: when your data already lives in PostgreSQL, search can live there too. Do matching, ranking, and fusion in one SQL statement, and use transactions to keep data and index consistent, at the cost of giving up some advanced capabilities of dedicated search engines. Whether it's worth it depends on where your users actually get stuck—typos, or simply not knowing the right word.

One-line Summary

Fuse BM25 and vector retrieval inside PostgreSQL, merge results with RRF, and achieve transactionally consistent hybrid search.

Paragraph Summary

This article introduces a pragmatic approach to building a hybrid search system directly in PostgreSQL. Addressing the shortcomings of keyword and semantic retrieval, the author uses pgTextSearch for BM25 ranking, pgVector and pgVectorScale for vector retrieval, and merges the two result sets with Reciprocal Rank Fusion (RRF). The article emphasizes the separation of matching and ranking, BM25's negative-score sorting, semantic retrieval's coverage limits, and RRF's advantage of weighting by rank rather than score. Through real tests, it shows how hybrid mode performs on specific queries and discusses the approach's boundaries and performance considerations, with the core argument being that when data already resides in PostgreSQL, keeping search in the database reduces consistency cost.