GitHub - timescale/pg_textsearch: PostgreSQL extension for BM25 relevance-ranked full-text search. Postgres OSS licensed.

Modern ranked text search for Postgres.

• Simple syntax: ORDER BY content <@> 'search terms'
• BM25 ranking with configurable k1 and b
• PostgreSQL text search configurations
• Expression, partial, and partitioned indexes
• Fast top-k queries with Block-Max WAND
• Parallel index builds for large tables

PostgreSQL Version Compatibility

pg_textsearch supports PostgreSQL 17 and 18. PostgreSQL 19 (beta) is supported on a best-effort basis while it is in beta; its CI is allowed to fail and prebuilt binaries are not published for it yet.

Installation

pg_textsearch supports PostgreSQL 17 and 18.

Pre-built Packages

Download pre-built binaries from the Releases page. Available for Linux on amd64 and arm64.

Build from Source

cd /tmp
git clone https://github.com/timescale/pg_textsearch
cd pg_textsearch
make
make install # may need sudo

Getting Started

Add pg_textsearch to shared_preload_libraries in postgresql.conf, then restart the server:

shared_preload_libraries = 'pg_textsearch'  # add to existing list if needed

Enable the extension in each database:

CREATE EXTENSION pg_textsearch;

Create a table with text content

CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text,
    category_id integer
);
INSERT INTO documents (content, category_id) VALUES
    ('PostgreSQL is a powerful database system', 1),
    ('BM25 is an effective ranking function', 1),
    ('Full text search with custom scoring', 2);

Create a pg_textsearch index on the text column

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

Querying

Get the most relevant documents using the <@> operator

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

<@> returns negative BM25 scores for ascending index scans, so lower scores rank first.

<@> can also score rows outside a BM25 index scan. This is standalone scoring; it still uses corpus statistics from the selected BM25 index.

The index is detected from the column. Specify it explicitly when needed:

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

Supported operations:

• text <@> 'query' - Score text against a query (index auto-detected)
• text <@> bm25query - Score text with explicit index specification

Boolean Filtering

Use PostgreSQL's @@ operator and tsquery syntax to filter through a BM25 index:

SELECT * FROM documents
WHERE content @@ to_tsquery('english', 'postgres & (search | database) & !mysql');

Supported tsquery features include & (AND), | (OR), ! (NOT), phrase operators such as <->, prefix matching with :*, and weight restrictions. Phrase and weight checks may be rechecked against the table row after the index finds candidates.

The default_text_search_config used to parse the left-hand text value must match the index configuration:

SET default_text_search_config = 'english';

If that setting changes after a Boolean prepared statement has switched to a generic plan, DEALLOCATE and prepare the statement again. A newly planned query can choose the correct sequential fallback, while the cached plan is rejected to avoid incorrect index results.

Boolean filtering and BM25 ranking are separate scan modes. A query combining WHERE content @@ ... with ORDER BY content <@> ... cannot use one BM25 index scan for both operations.

Verifying Index Usage

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

PostgreSQL may prefer standalone scoring with a sequential scan for small tables. To test the index plan:

SET enable_seqscan = off;

Pre-filtering and Post-filtering

PostgreSQL can use a separate index to pre-filter rows before standalone scoring:

CREATE INDEX ON documents (category_id);

SELECT * FROM documents
WHERE category_id = 1
ORDER BY content <@> 'search terms'
LIMIT 10;

When PostgreSQL chooses the ordered BM25 index scan, other conditions are post-filters applied after BM25 scoring:

SELECT * FROM documents
WHERE length(content) > 100
ORDER BY content <@> 'search terms'
LIMIT 10;

Post-filtered scans automatically grow their internal scoring batch until the LIMIT is filled, matches are exhausted, or the 100,000-result scan cap is reached.

Indexing

Create a BM25 index on a text column:

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

Index Options

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

Expression Indexes

Index expressions for JSONB fields, multiple columns, or text transformations:

-- JSONB field extraction
CREATE INDEX events_expr_idx ON events USING bm25 ((data->>'description'))
    WITH (text_config='english');

SELECT * FROM events
ORDER BY (data->>'description') <@> to_bm25query('network error', 'events_expr_idx')
LIMIT 10;

-- Text transformation
CREATE INDEX ON documents USING bm25 ((lower(content)))
    WITH (text_config='simple');

-- Multi-column search
CREATE INDEX ON articles USING bm25 ((coalesce(title, '') || ' ' || coalesce(body, '')))
    WITH (text_config='english');

The expression must evaluate to text and use only IMMUTABLE functions. Queries must repeat the same expression in the ORDER BY clause.

Partial Indexes

Add a WHERE clause to index a subset of rows:

CREATE INDEX documents_category_bm25_idx ON documents USING bm25 (content)
    WITH (text_config='english')
    WHERE category_id = 1;

SELECT * FROM documents
WHERE category_id = 1
ORDER BY content <@> to_bm25query('search terms', 'documents_category_bm25_idx')
LIMIT 10;

Partial indexes require explicit index naming via to_bm25query() — the implicit text <@> 'query' syntax skips them.

Multilingual Tables

Create one partial index per language:

ALTER TABLE documents ADD COLUMN lang CHAR(2) NOT NULL DEFAULT 'en';

CREATE INDEX docs_en_idx ON documents USING bm25 (content)
    WITH (text_config='english') WHERE lang = 'en';
CREATE INDEX docs_de_idx ON documents USING bm25 (content)
    WITH (text_config='german')  WHERE lang = 'de';
CREATE INDEX docs_fr_idx ON documents USING bm25 (content)
    WITH (text_config='french')  WHERE lang = 'fr';

Query with the matching predicate and index name:

SELECT * FROM documents
WHERE lang = 'en'
ORDER BY content <@> to_bm25query('databases', 'docs_en_idx')
LIMIT 10;

Chinese Full-Text Search

Use a Chinese-aware PostgreSQL text search configuration such as zhparser. The same text_config tokenizes documents and queries.

CREATE EXTENSION zhparser;

CREATE TEXT SEARCH CONFIGURATION public.chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION public.chinese
    ADD MAPPING FOR n, v, a, i, e, l WITH simple;

CREATE TABLE chinese_documents (id bigserial PRIMARY KEY, content text);
INSERT INTO chinese_documents (content) VALUES ('机器学习');

CREATE INDEX chinese_documents_bm25 ON chinese_documents USING bm25 (content)
    WITH (text_config='public.chinese');

SELECT id FROM chinese_documents
ORDER BY content <@> to_bm25query('机器学习', 'chinese_documents_bm25')
LIMIT 10;

Explicit Queries

bm25query can carry an explicit index name:

SELECT to_bm25query('search query text', 'docs_idx');

SELECT 'docs_idx:search query text'::bm25query;

Explicit index names are required when the planner cannot infer an index, including partial indexes, PL/pgSQL, prepared or dynamic query text, and ambiguous expressions. Standalone scoring requires SELECT on the indexed table or columns.

Functions

Performance

For initial loads, create the index after loading data.

Parallel Index Builds

PostgreSQL uses parallel workers automatically for sufficiently large tables.

SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '256MB';

Each parallel worker receives at least a 64MB internal build budget, so size memory for the worker count. Partitioned tables build each partition separately.

Query Performance

When PostgreSQL chooses a BM25 index scan for ORDER BY, scoring uses Block-Max WAND. LIMIT n requests the top-k result count. For filtered queries, selectivity seeding may start with a deeper internal scoring batch. Without a pushed-down SQL LIMIT, pg_textsearch.default_limit sets the initial scoring batch, which can grow as more rows are requested.

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

Segment compression is enabled by default. Disable it only when decompression is a measured bottleneck:

SET pg_textsearch.compress_segments = off;

Update-heavy workloads can fragment index pages. Use REINDEX during a low-traffic window if cold-cache latency degrades:

Compaction

With the default inline policy, compaction of levels that reach the configured threshold occurs as part of the write transaction that triggers the spill. These functions provide manual and scheduled control:

SELECT bm25_force_merge('docs_idx');
SELECT bm25_compact('docs_idx'::regclass);
SELECT bm25_compact_step('docs_idx'::regclass);
SELECT bm25_needs_compaction('docs_idx'::regclass);
SELECT bm25_level_counts('docs_idx'::regclass);

bm25_force_merge() runs one bounded best-effort pass over eligible adjacent segments; oversized or otherwise uncombinable segments may remain. bm25_compact() processes all eligible levels, while bm25_compact_step() processes at most one pass.

• Long merge work checks for cancellation, but published replacements remain physical and are not undone by ROLLBACK.
• Drive maintenance loops from bm25_compact_step()'s return value, not bm25_needs_compaction(), which is advisory.
• Mutating functions require index ownership and do not operate on partitioned parent indexes or during recovery.

See ARCHITECTURE.md for sizing, publication, locking, and page-reclaim details.

Hot standbys serving queries must set hot_standby_feedback = on so active snapshots delay physical page reuse on the primary.

Settings

Memtable Architecture

The L0 memtable is stored in the index as a WAL-logged chain of pages. It is the durable source of truth and can be restored by PostgreSQL without loading pg_textsearch.so. See ARCHITECTURE.md.

Queries use a shared-memory cache when enabled. An index falls back to the chain when the next record's estimated growth would cross memory_limit / 8; global pressure triggers best-effort eviction at memory_limit / 2. The global memory_limit is an approximate admission threshold: incremental catch-up and cold builds fall back when the entry-time estimate is already at the limit, but admitted or concurrent work may take estimated usage above it. The cache is rebuilt from the chain when missing or stale, while standbys always read the chain directly.

Memtables spill automatically based on memtable_pages_threshold and bulk_load_threshold, and during VACUUM.

-- Manual spill
SELECT bm25_spill_index('docs_idx');

Monitoring

-- Check index usage
SELECT s.schemaname, s.relname AS tablename,
       s.indexrelname AS indexname,
       s.idx_scan, s.idx_tup_read, s.idx_tup_fetch
FROM pg_stat_user_indexes AS s
JOIN pg_class AS i ON i.oid = s.indexrelid
JOIN pg_am AS am ON am.oid = i.relam
WHERE am.amname = 'bm25';

Limitations

Row-Level Security

BM25 corpus statistics include all indexed rows, including rows hidden by RLS. A user who already knows a term can infer frequency information affected by inaccessible rows, though the index does not reveal unknown terms. This is analogous to Elastic's security limitation.

pg_textsearch.allow_rls defaults to on. Set it to off as a superuser to reject creating or rebuilding BM25 indexes on RLS-protected tables and enabling RLS where BM25 indexes already exist. This does not disable combinations that already exist when the setting is changed.

Phrase Queries

The BM25 index stores term frequencies but not term positions, so it cannot evaluate phrases directly. Over-fetch ranked candidates and apply a post-filter:

SELECT * FROM (
    SELECT *, content <@> 'database system' AS score
    FROM documents
    ORDER BY score
    LIMIT 100  -- over-fetch
) sub
WHERE content ILIKE '%database system%'
ORDER BY score
LIMIT 10;

Background Compaction

The default inline policy compacts synchronously during memtable spills. Managed background mode uses pg_durable 0.2.8 or newer rather than a built-in worker. pg_durable must be preloaded, initialized in the current database, and granted to the index owner. The owner must have LOGIN; a superuser owner also requires pg_durable.enable_superuser_instances = on.

Each physical index has one managed workflow scoped to its captured owner. The index owner, or a role PostgreSQL permits to act as that owner, may enable background mode. A separate insert-only writer may later trigger a spill, but pg_textsearch submits the workflow and calls df.signal under the index owner's identity. The compaction SQL nodes reached through either a spill signal or the cron backstop execute in pg_durable connections authenticated as the index owner, not as the DML writer; pg_durable's worker role provides only the orchestration infrastructure.

CREATE INDEX documents_bm25 ON documents USING bm25(content)
WITH (
    text_config = 'english',
    compaction = 'background'
);

Change modes with ALTER INDEX. Resetting compaction_schedule uses the current pg_textsearch.background_compaction_schedule default. Set the per-index option only when the default schedule is unsuitable.

ALTER INDEX documents_bm25 SET (compaction = 'background');
ALTER INDEX documents_bm25 RESET (compaction_schedule);
ALTER INDEX documents_bm25 SET (compaction = 'manual');

Use manual with an external scheduler when pg_durable is unavailable or not desired and foreground compaction causes unacceptable write transaction stalls. The legacy off value remains accepted as an alias for manual. Temporary indexes do not support background mode.

See ARCHITECTURE.md for workflow lifecycle and safety details.

Partitioned Tables

BM25 statistics are local to each partition. Scores are comparable within a partition but may use different IDF scales across partitions. Query individual partitions when cross-row score comparability matters.

Token and Document Limits

PostgreSQL ignores lexemes longer than 2047 bytes. This mainly affects base64 data, long URLs, and concatenated identifiers.

pg_textsearch splits raw inputs larger than 256KB at whitespace or multibyte-character boundaries before tokenization, then merges the term frequencies. Text arrays are flattened with spaces before this processing and do not preserve element boundaries.

PL/pgSQL and Stored Procedures

Planner hooks do not resolve the implicit query syntax inside PL/pgSQL. Use an explicit index name:

SELECT * FROM documents
ORDER BY content <@> to_bm25query('search terms', 'docs_idx')
LIMIT 10;

Troubleshooting

List installed text search configurations:

SELECT cfgname FROM pg_ts_config;

List BM25 indexes:

SELECT indexname FROM pg_indexes WHERE indexdef LIKE '%USING bm25%';

For multiple PostgreSQL installations, set PG_CONFIG before building:

export PG_CONFIG=/Library/PostgreSQL/18/bin/pg_config  # or 17
make clean && make && make install

Compilation requires PostgreSQL development files:

sudo apt install postgresql-server-dev-18  # use 17 for PostgreSQL 17

Reference

Compaction Functions

These functions take an index regclass. Mutating functions require index ownership. See Compaction before scripting them.

Development Functions

These interfaces may change without notice. Functions marked with † require superuser privileges.

Extension Compatibility

pg_textsearch uses LWLock tranche IDs 1001-1012. Another extension using the same IDs can cause incorrect wait-event names in pg_stat_activity. If you encounter a conflict, open an issue.

Version History

See GitHub Releases for complete release notes.

Contributing

See CONTRIBUTING.md for development setup and contribution guidelines. See ARCHITECTURE.md for implementation details and storage invariants.

• Bug Reports: Create an issue
• Feature Requests: Request a feature
• General Discussion: Start a discussion