timescale/pg_textsearch

README

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

Linux and Mac

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

Windows

Ensure you have a working MSVC environment on your path (cl.exe and nmake).

set "PGROOT=C:\Program Files\PostgreSQL\18"
cd %TEMP%
git clone https://github.com/timescale/pg_textsearch
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

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

Upgrading

For upgrade steps and version-specific index rebuild requirements, see UPGRADING.md.

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.

Combining Boolean filtering with BM25 ranking is supported, but is not yet optimized as a single index scan. PostgreSQL currently evaluates the filter, calculates standalone scores for the matching rows, and then sorts them. This is most effective when the Boolean filter matches relatively few rows.

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 or matches are exhausted.

Indexing

Create a BM25 index on a text column:

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

Index Options

Option	Default	Description
`text_config`	required	PostgreSQL text search configuration
`k1`	1.2	Term frequency saturation (0.1-10.0)
`b`	0.75	Length normalization (0.0-1.0)
`compaction`	inline	Spill-time compaction: `inline`, `background`, or `manual`; see Background Compaction
`compaction_schedule`	`pg_textsearch.background_compaction_schedule`	Optional cron schedule captured when the index enters background mode

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');

-- Array expression
CREATE INDEX ON articles USING bm25 ((ARRAY[title, body]))
    WITH (text_config='english');

The expression must evaluate to a text-like type or array (text, varchar, char, or arrays thereof) 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

Function	Description
to_bm25query(text) → bm25query	Create bm25query without explicit index context
to_bm25query(text, text) → bm25query	Create bm25query with query text and index name
text <@> bm25query → double precision	BM25 scoring operator (returns negative scores)
bm25query = bm25query → boolean	Equality comparison

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

maintenance_work_mem sets the total batch budget shared by parallel workers. Lower it to spill batches to disk earlier. Leave memory headroom beyond this budget and shared_buffers for other server activity; pg_textsearch.memory_limit controls the memtable cache, not index builds.

Partitioned tables build each partition separately. See build-memory benchmarking for details.

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:

REINDEX INDEX docs_idx;

Compaction

Compaction consolidates comparable-sized segments across levels and rewrites segments with at least 50% dead documents, even below the segment-count threshold. With the default inline policy, spills and serial VACUUM provide an opportunity for one synchronous pass. Readers and other memtable writers can continue while merged output is built, because the long build holds no per-index LWLock. This is reader non-blocking, not foreground-writer non-blocking: the invoking writer still spends the time required to build and publish the merge. Compaction is skipped, and left to the next spill, when another session is reindexing, vacuuming, or compacting the index.

These functions provide manual and scheduled control. They wait when another session holds index maintenance:

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

These report compaction state without waiting for maintenance:

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.
bm25_needs_compaction() reports whether bm25_compact_step() would run a pass, so either can drive a maintenance loop.
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. If a standby disconnects while an old snapshot remains active, stock PostgreSQL recovery-conflict WAL cancels that snapshot before reclaimed segment pages can be reused on replay.

Settings

Setting	Default	Description
`pg_textsearch.default_limit`	1000	Initial scoring batch when no SQL LIMIT is available
`pg_textsearch.compress_segments`	on	Compress posting blocks in new segments
`pg_textsearch.segments_per_level`	8	Count-triggered compaction threshold and maximum inputs per opportunistic merge (2-64)
`pg_textsearch.max_segment_size`	4095MB	Conservative size budget for newly merged multi-source segments (1-4095MB)
`pg_textsearch.background_compaction_schedule`	`/5 * * *`	Default cron schedule captured by indexes entering managed background mode
`pg_textsearch.bulk_load_threshold`	100000	Terms per transaction before auto-spill (0 = disable)
`pg_textsearch.memtable_pages_threshold`	64	Chain pages before auto-spill (0 = disable)
`pg_textsearch.allow_rls`	on	Allow BM25 indexes on RLS-protected tables; superuser-only
`pg_textsearch.memtable_cache_enabled`	on	Cache memtable data in shared memory for faster queries
`pg_textsearch.memory_limit`	2GB	Approximate shared-memory budget for the memtable cache across all indexes; changes take effect after a configuration reload without a restart (0 = no limit)

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.

Background Compaction

The default inline policy compacts during memtable spills, skipping the pass when another session is using or maintaining the index. 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.

pg_durable must be installed in the same database as the BM25 index. Use manual compaction for indexes outside pg_durable.database. This restriction is expected to be lifted in a future version of pg_durable.

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;

Two-Phase Commit

PREPARE TRANSACTION is not supported after creating a BM25 index or while BM25 index-drop cleanup is pending. Commit or roll back that DDL first.

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.

Function	Description
bm25_level_counts(index) → int4[]	Segments held at each of the eight LSM levels
bm25_needs_compaction(index) → bool	Whether a compaction pass is eligible (advisory)
bm25_compact(index) → void	Run eligible compaction passes
bm25_compact_step(index) → bool	Run at most one pass

Development Functions

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

Function	Description
bm25_force_merge(index_name) → void	Run one bounded best-effort merge pass
bm25_spill_index(index_name) → int4	Force memtable spill to disk segment
bm25_pending_free_pages(index_name) † → int8	Count pages awaiting standby-safe reclaim
bm25_dump_index(index_name) † → text	Dump internal index structure (truncated)
bm25_summarize_index(index_name) † → text	Show index statistics without content

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

Version	Highlights
`v1.5.0`	Background compaction, Boolean and phrase queries, and faster builds and scans
`v1.4.0`	Faster filtered top-k queries, Chinese search, and large-corpus improvements
`v1.3.1`	Standby-safe page reclaim and concurrency, VACUUM, and parallel-build fixes
`v1.3.0`	On-disk memtable with a shared-memory read cache and stateless WAL replay
`v1.2.0`	Physical replication and update-heavy workload correctness
`v1.1.0`	Concurrent writes, alive-bitset VACUUM, memory controls, arrays, expression indexes, and partial indexes
`v1.0.0`	First generally available release
`v0.6.1`	Transaction-abort, VACUUM, large-posting, and BMW stability
`v0.6.0`	Large-scale performance, lower memory use, and required shared preloading
`v0.5.1`	Concurrent index builds, iterative scans, progress reporting, and scan statistics
`v0.5.0`	Parallel index builds and improved hypertable support
`v0.4.2`	Hypertable scoring fix
`v0.4.1`	Hypertable scan locking fix
`v0.4.0`	Compressed posting lists, ordered-scan planner support, and partitioned-table fixes
`v0.3.0`	Block-Max WAND, bounded memtable growth, and expanded performance testing
`v0.2.0`	Public benchmarks, block-based segment storage, and a dynamic index registry
`v0.1.0`	First open-source release, simpler query syntax, and partitioned tables
`v0.0.5`	Pre-release
`v0.0.4`	BM25 indexing, PostgreSQL 17/18 support, crash recovery, and memory budgets
`v0.0.3`	PostgreSQL 18 support
`v0.0.2`	Schema insertion fixes and an inheritance safety check
`v0.0.1`	Initial release

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