PostgreSQL’s built-in full-text search — tsvector, tsquery, ts_rank — is lexeme-based, and ts_rank is a term-frequency score, not Okapi BM25. That gap is the usual reason teams run a second search engine next to Postgres, even when the corpus already lives in a table.

As of October 7, 2026, there is another option. PGX Inc. released pgx-bm25 1.0: BM25 full-text search as a native PostgreSQL index access method, USING bm25_native. It supports PostgreSQL 17 and 18, builds with PGXS and installs inside your existing database. The index lives in the index relation’s own pages, so it inherits WAL logging, crash recovery, physical replication and VACUUM maintenance from core — no external cluster, no separate runtime. Here is how to build it and run ranked queries.

What you need first

• PostgreSQL 17 or 18. PG16 is not supported: its planner cannot order-then-tiebreak an amcanorderbyop index scan, which is what ranked top-N relies on.
• Server development headers and a C toolchain — the dev package for your distro (postgresql-server-dev-18) plus make and a C compiler, or a source install whose pg_config is on PATH.
• Source, docs and issue tracker: github.com/pgexperts/pgx-bm25 (PostgreSQL License).

Step 1 — Build and install

make
sudo make install

# if pg_config for the target server is not on PATH:
make         PG_CONFIG=/usr/local/pgsql/bin/pg_config
make install PG_CONFIG=/usr/local/pgsql/bin/pg_config

Restart the server after installing a new release: backends that loaded the previous library keep running its old code until they exit.

Step 2 — Create the extension and your first index

CREATE EXTENSION bm25_native;

CREATE TABLE docs (id int PRIMARY KEY, body text);
INSERT INTO docs VALUES
  (1, 'the quick brown fox'),
  (2, 'quick foxes leap'),
  (3, 'lazy dogs sleep');

CREATE INDEX docs_bm25 ON docs USING bm25_native (body);

It is not relocatable: CREATE EXTENSION bm25_native SCHEMA x works, a later ALTER EXTENSION ... SET SCHEMA does not — the access method name is global to the cluster.

Step 3 — Match with @@@, rank with &@@

SELECT id, bm25_score(ctid) AS score
FROM   docs
WHERE  body @@@ 'quick fox'
ORDER  BY body &@@ 'quick fox'
LIMIT  10;

Keep both halves of that statement. WHERE is what lets the index answer the query; ORDER BY ... LIMIT becomes an ordered index scan driven by block-max WAND, so a top-10 search does not score every matching document. Confirm with EXPLAIN: an index scan, no Sort node. Text is analyzed with PostgreSQL’s own Snowball dictionaries, language per index, so searching negligent finds negligence.

Step 4 — Multi-column BM25F with your own key

CREATE INDEX docs_bm25 ON docs
  USING bm25_native (title, summary, body) INCLUDE (id)
  WITH (key_field = 'id');

SELECT id, bm25_score_key(id) AS score
FROM   docs
WHERE  title @@@ 'mandamus'
ORDER  BY title &@@ 'mandamus'
LIMIT  10;

One index can span several text columns, with per-field boosts and length normalization (BM25F). Anchor both operators on the first indexed column (title above); the query tree inside targets the fields its builders name. key_field via INCLUDE lets you score and return by your own key instead of ctid (int, bigint, uuid or text).

Step 5 — Boolean, phrase, proximity and wildcard queries

Simple text queries cover a lot: 'body:cat' scopes a term to a field, '"red car"' is an exact phrase, '"red car"~2' allows up to two intervening tokens in any order (~>2 for ordered), and judg* is a prefix wildcard. A colon naming no indexed field stays plain text ('10:30' works; 'titel:cat' errors).

Everything richer is composed as a jsonb query tree from builder functions rather than a query string, so user input lands as a value and can never be parsed as query syntax.

SELECT id, bm25_score_key(id) AS score
FROM   docs
WHERE  title @@@ bm25_boolean(
         must     => ARRAY[ bm25_term('body', 'tort') ],
         must_not => ARRAY[ bm25_term('body', 'battery') ])
ORDER  BY title &@@ bm25_boolean(
         must     => ARRAY[ bm25_term('body', 'tort') ],
         must_not => ARRAY[ bm25_term('body', 'battery') ])
LIMIT  10;

Builders: bm25_term, bm25_match_terms, bm25_phrase(field, phrase, slop, ordered), bm25_wildcard, bm25_boolean(must, should, must_not) and bm25_boost(weight, query) — so bm25_boost(3.0, bm25_term('title', 'mandamus')) makes a title hit count three times a body hit. All are IMMUTABLE and PARALLEL SAFE and carry a COMMENT for in-database help (\df+ bm25_*).

Step 6 — Highlighted snippets

SELECT id, bm25_snippet('body') AS excerpt
FROM   docs
WHERE  body @@@ 'quick fox'
ORDER  BY body &@@ 'quick fox'
LIMIT  10;

bm25_snippet(field, start_tag, end_tag, max_num_chars, escape) defaults to <mark> / </mark>, a 300-character window and HTML escaping on — a deliberate departure from ts_headline, which escapes nothing. Pass escape => false for CLI or JSON output. max_num_chars counts characters, not bytes.

Tuning and maintenance without a REINDEX

-- live knobs: they take effect on the next scan
ALTER INDEX docs_bm25 SET (k1 = 1.5, b = 0.6, boost_title = 3.0);

SELECT * FROM bm25_stats('docs_bm25');  -- docs, avg length, k1/b, segments, format
SELECT bm25_seal('docs_bm25');          -- drain the pending list now
SELECT bm25_merge('docs_bm25');         -- force a merge pass

Defaults are k1 = 1.2 and b = 0.75, overridable per field with k1_<col>, b_<col> and boost_<col>: because these change how an already-found document is scored rather than what a query can find, ALTER INDEX ... SET applies on the next scan with no REINDEX. The analyzer is different — language, stopwords and store_positions are fixed at CREATE INDEX and change with a rebuild.

Inserts land in a WAL-logged pending list, sealed into segments automatically at bm25_native.seal_threshold and during VACUUM, which also tombstones deleted rows. bm25_seal, bm25_merge and bm25_upgrade mutate the index and require ownership; bm25_stats needs SELECT on the table. All respect maintenance_work_mem — a small budget leaves more segments and slower scans, so raise it and run bm25_merge(). On a hot standby a ranked query can be cancelled with SQLSTATE 40001 (“segment reclaimed concurrently; retry”) if the primary reused a page the standby needed; the index is not damaged, so retry, and hot_standby_feedback = on makes it rare.

Should it replace ts_rank — or a search engine?

Benchmark first: measure ranked top-N latency and result quality on your own corpus against ts_rank (see our full-text search walkthrough). pgx-bm25 fits when the corpus already lives in Postgres and you would rather not run a second cluster for ranking. It is not a replacement for fuzzy matching, faceting or cross-cluster scale-out, and keyword relevance does not answer semantic queries — that is the vector side. If you need both, the hybrid-search pattern (pgvector plus reciprocal rank fusion) from Postgres Hybrid Search for RAG ports directly onto BM25 scoring as the lexical half. If you already run pgvector, note that 0.8.7 fixes a buffer overflow during IVFFlat index builds (CVE-2026-103484).

For Houston teams keeping one Postgres for the operational corpus — trading contracts, refinery and chemical procedures, lease and permit filings, claims notes, freight documentation — relevance ranking stops being a second system to secure, back up and fail over. Read the compatibility notes before production: additive format changes need no REINDEX, breaking changes ship with bm25_upgrade(regclass) for in-place migration, and the upgrade order is new library on every node, ALTER EXTENSION bm25_native UPDATE, then bm25_upgrade() on the primary only.