pg_textsearch

pg_textsearch provides BM25 ranked full-text search in Postgres. It is a separate access method from built-in tsvector / @@ search.

pg_textsearch is available only on PostgreSQL 17 and 18. Existing clusters must be refreshed to pick up the extension.

Enable it as the postgres role:

CREATE EXTENSION pg_textsearch;

CREATE EXTENSION requires superuser. The application role cannot create it. If pg_available_extensions does not list pg_textsearch, the cluster still needs a refresh (or scheduled maintenance) onto a current 17 or 18 image.

pg_textsearch must be listed in shared_preload_libraries. Current 17 and 18 images include it by default. If CREATE EXTENSION fails because it is not preloaded, shared_preload_libraries needs to be updated and Postgres must be restarted.

Info

Updating shared_preload_libraries incorrectly can prevent Postgres from starting. If you are not familiar with this process, please open a support ticket so we can assist you.

Once enabled, create a BM25 index and order by the <@> operator. Scores are negated (more relevant is more negative), so the default ORDER BY ascending order is correct:

CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text
);

INSERT INTO documents (content) VALUES
    ('PostgreSQL is a powerful database system'),
    ('BM25 is an effective ranking function'),
    ('Full text search with custom scoring');

CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');

SELECT * FROM documents
ORDER BY content <@> 'search terms'
LIMIT 5;

Documentation:

pg_textsearch