Currently Available: Need a skilled Software Developer for your next project?
Categories
PostgreSQL

How to Get BM25 Relevance Ranking in PostgreSQL

When you search text stored in PostgreSQL, the database can match documents and sort them by relevance with built-in functions such as ts_rank and ts_rank_cd. Those functions are useful, but they do not implement BM25, a ranking method that also considers how common each search term is across all indexed documents.

Use PostgreSQL’s built-in ranking when its scoring controls fit your needs. For BM25-style scoring, use an extension such as ParadeDB’s pg_search. The extension adds its own index and search operators, so you create that index and use those operators to search your data.

What PostgreSQL’s built-in ranking scores

PostgreSQL stores searchable text in a tsvector, which holds normalized words and can keep their positions and field labels. A tsquery holds the search terms and conditions. A GIN index, one type of PostgreSQL index, speeds up matching. A ranking function then orders the matching rows. PostgreSQL’s text search documentation describes these building blocks.

ts_rank scores matches based on how often query terms occur in a document. ts_rank_cd adds cover-density ranking, which considers how close matching terms appear to one another. Cover-density ranking needs word positions, so ts_rank_cd ignores stripped lexemes and returns zero when no unstripped lexemes remain.

Both functions let you assign weights to fields and adjust scores for document length. For example, a search vector can give a title more weight than a body. A normalization flag adjusts scores based on document length. PostgreSQL states that its built-in ranking functions use no information about how often a term appears across all documents. The ranking documentation lists the available weights and normalization flags.

Here is a built-in full-text search setup that favors title matches and uses proximity-aware ranking:

ALTER TABLE documents
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'D')
) STORED;

CREATE INDEX documents_search_vector_idx
ON documents USING GIN (search_vector);

WITH q AS (
    SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT d.id,
       d.title,
       ts_rank_cd(d.search_vector, q.query, 1 | 32) AS rank
FROM documents AS d
CROSS JOIN q
WHERE d.search_vector @@ q.query
ORDER BY rank DESC, d.id
LIMIT 20;

The A and D labels assign more weight to the title than to the body. The 1 normalization flag adjusts for document length; 32 maps the score to rank / (rank + 1), changing its scale without changing result order. These are tuning choices, not universal improvements. PostgreSQL’s full-text search setup with tsvector and GIN provides more detail on indexing and matching.

Choose ts_rank when term frequency and straightforward ranking are enough. Choose ts_rank_cd when proximity between matching words should influence order. Neither function supplies BM25’s collection-wide term weighting.

What BM25 adds

BM25 scores each document using information from all documents in the index. It rewards matches, gives more weight to terms that appear in fewer documents, and adjusts the effect of repeated terms based on document length. The extension you use determines the formula’s default settings and how it handles fields and text analysis. The BM25 research overview describes the method and its probabilistic relevance framework.

BM25 does not rank every query better. Results depend on how the extension analyzes text, which fields it searches, the indexed documents, the queries, and what users consider relevant. PostgreSQL’s built-in functions are sufficient when their scoring controls fit the search task.

How ParadeDB adds BM25 scoring

ParadeDB’s pg_search extension provides BM25 scoring through its own index. You create that index, search with the extension’s operators, and use its score function. ParadeDB’s scoring documentation shows queries that order results by pdb.score(...).

A query using the documented style looks like this:

CREATE EXTENSION pg_search;

CREATE INDEX documents_search_idx
ON documents USING paradedb (id, title, body);

SELECT id,
       pdb.score(id) AS score
FROM documents
WHERE body ||| 'database indexing'
ORDER BY score DESC, id
LIMIT 20;

This example shows the query structure; it is not a universal installation script. Check the documentation for the extension release you plan to use. Review its index syntax and the PostgreSQL builds it supports. The project’s development README lists official PostgreSQL versions starting at 15 and current development targets from 15 through 18. Confirm that your hosting provider supports the extension and that its installation and licensing terms fit your deployment.

PGXN also lists a PostgreSQL extension named pg_bm25. Its PGXN release record documents a release dated October 17, 2023, under the AGPL-3 license. That record does not establish that every package with a similar name is current or equivalent to ParadeDB’s pg_search.

Check security behavior as well as ranking behavior. ParadeDB documents that BM25 statistics use rows in the index, including rows hidden from a role by row-level security. It also says dead rows can affect scores until vacuum cleanup. Those details matter when different roles search a shared, multi-tenant index.

How to choose and test a ranking method

Keep built-in PostgreSQL search when its ranking behavior and operational footprint fit the search task. ts_rank and ts_rank_cd require no BM25 extension, and ts_rank_cd adds proximity scoring. If you use RUM, treat it as a separate PostgreSQL full-text search index option: its documentation describes positional data and ranking features, but not BM25 scoring. RUM also trades faster ranking and phrase search in documented cases for slower index builds and inserts, as explained in the RUM documentation.

Choose a BM25 extension when you need term statistics from the full indexed collection and can meet its requirements. Check what index and query syntax it requires, then confirm you can operate it. Before switching, run the same representative queries with both methods. Ask reviewers to judge which results are relevant, then compare a ranking measure such as NDCG, which rewards relevant results near the top, or MRR, which measures the position of the first relevant result. Also compare indexing and query performance on your data. A vendor benchmark against another search product does not establish a performance or relevance advantage over ts_rank.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *