pg_textsearch

Overview

bm25 am conflicts with pg_search; must be preloaded via shared_preload_libraries.

Version

Build

You can build the RPM / DEB packages for pg_textsearch using pig build:

pig build pkg pg_textsearch         # build RPM / DEB packages

Install

You can install pg_textsearch directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_textsearch;          # Install for current active PG version

pig ext install -y pg_textsearch -v 18  # PG 18
pig ext install -y pg_textsearch -v 17  # PG 17

dnf install -y pg_textsearch_18       # PG 18
dnf install -y pg_textsearch_17       # PG 17

apt install -y postgresql-18-textsearch   # PG 18
apt install -y postgresql-17-textsearch   # PG 17

Preload:

shared_preload_libraries = 'pg_textsearch';

Create Extension:

CREATE EXTENSION pg_textsearch;

Usage

Sources: README, release notes, Timescale changelog

pg_textsearch provides BM25-ranked full-text search for PostgreSQL with a bm25 access method and the <@> scoring operator. Upstream marks v1.0.0 as production ready.

Enable the extension

shared_preload_libraries = 'pg_textsearch'

CREATE EXTENSION pg_textsearch;

Build a BM25 index and query it

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

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

SELECT *
FROM documents
ORDER BY content <@> 'database system'
LIMIT 5;

The README notes that <@> returns the negative BM25 score, so lower values are better matches.

Explicit queries and index options

SELECT *
FROM documents
ORDER BY content <@> to_bm25query('database system', 'docs_idx')
LIMIT 5;

CREATE INDEX ON documents USING bm25(content)
WITH (text_config = 'english', k1 = 1.5, b = 0.8);

The README also documents expression indexes, partial indexes, and multilingual partial indexes.

Useful functions and GUCs

SELECT bm25_force_merge('docs_idx');

Documented GUCs in current upstream docs include:

• pg_textsearch.default_limit
• pg_textsearch.segments_per_level
• pg_textsearch.memory_limit
• pg_textsearch.log_scores

Caveats

• pg_textsearch currently supports PostgreSQL 17 and 18 upstream.
• Inside PL/pgSQL and stored procedures, the implicit text <@> 'query' form does not use planner hooks; upstream says to use to_bm25query() with an explicit index name there.
• v1.0.0 adds the production-ready status, bm25_force_merge(), and newer GUCs documented in the official changelog and README.