[PGX Inc.](https://pgexperts.com/) has released version 1.0 of
[pgx-bm25](https://github.com/pgexperts/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](https://www.postgresql.org/about/licence/), 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
