pgx-bm25 1.0: BM25 ranked full-text search as a native PostgreSQL index

PGX Inc. has released version 1.0 of pgx-bm25, an extension that adds Okapi BM25 ranked full-text search to PostgreSQL as a native index access method. It supports PostgreSQL 17 and 18, and installs as bm25_native.

CREATE EXTENSION bm25_native;
CREATE INDEX docs_bm25 ON docs USING bm25_native (body);

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

@@@ selects the matching rows and &@@ orders them by relevance. The query runs as an ordered index scan; there is no Sort node.

It is a PostgreSQL index

The whole index lives in the index relation's own pages, so it gets WAL logging, crash recovery, and physical replication from core. VACUUM maintains it. There is no external search engine and no separate runtime to operate. The extension is written in C against the stock server headers and builds with PGXS; a C compiler and pg_config are the whole toolchain.

Text is analyzed with PostgreSQL's own Snowball dictionaries, with the language set per index, so a search for negligent finds negligence. Ranked top-N queries use block-max WAND, so a typical LIMIT 10 search does not have to score every matching document.

Query features

• Multi-column indexes with BM25F scoring: per-field boosts and per-field length normalization. k1, b, and the boosts can be changed with ALTER INDEX ... SET and take effect on the next scan, without a REINDEX.
• Exact phrases, and proximity search by token distance, ordered or unordered.
• Boolean queries (must, should, must_not), nested as needed.
• Prefix wildcards (judg*).
• Query-time boosts on any clause.
• Highlighted snippets with bm25_snippet(), HTML-escaped by default.

Simple searches can be written as a text string. Everything else is composed from builder functions that produce a jsonb query tree, so user input always arrives as a value and never as query syntax:

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 @@@ 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;

License

pgx-bm25 is released under the PostgreSQL License, the same terms as PostgreSQL itself. It is a standalone project: it does not depend on, or connect to, any other product or service, and there is no separate commercial edition. The source, the regression and TAP test suites, the benchmark scripts, and the design record (an architecture guide and a log of design decisions) are all in the repository.

Upgrades and testing

The on-disk format carries a compatibility contract: additive format changes do not require a REINDEX, and where a breaking change allows it, bm25_upgrade() migrates an index in place. CI builds and tests against PostgreSQL 17 and 18, including assert-enabled, UBSan, and AddressSanitizer runs, plus TAP tests for crash recovery and replica equality. PostgreSQL 19 is tested against the current beta but is not yet a supported major version.

Getting it

make    
sudo make install

Source, documentation, and issue tracker: https://github.com/pgexperts/pgx-bm25