timescale/pg_textsearch

pg_textsearch

Modern ranked text search for Postgres.

• Simple syntax: ORDER BY content <@> 'search terms'
• BM25 ranking with configurable parameters (k1, b)
• Works with Postgres text search configurations (english, french, german, etc.)
• Expression indexes for JSONB fields, multi-column search, and text transformations
• Partial indexes for scoped search and multilingual tables
• Fast top-k queries via Block-Max WAND optimization
• Parallel index builds for large tables
• Supports partitioned tables
• Best in class performance and scalability

🚀 Status: v1.4.0-dev - Production ready.

Historical note

The original name of the project was Tapir - Textual Analysis for Postgres Information Retrieval. We still use the tapir as our mascot and the name occurs in various places in the source code.

PostgreSQL Version Compatibility

pg_textsearch supports PostgreSQL 17 and 18.

Installation

Pre-built Binaries

Download pre-built binaries from the Releases page. Available for Linux and macOS (amd64 and arm64), PostgreSQL 17 and 18.

Build from Source

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

Getting Started

pg_textsearch must be loaded via shared_preload_libraries. Add the following to postgresql.conf and restart the server:

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

Then enable the extension (once per database):

CREATE EXTENSION pg_textsearch;

Create a table with text content

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

Note: <@> returns the negative BM25 score since Postgres only supports ASC order index scans on operators. Lower scores indicate better matches.

The index is automatically detected from the column. For explicit index specification:

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

Verifying Index Usage

Check query plan with EXPLAIN:

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

For small datasets, PostgreSQL may prefer sequential scans. Force index usage:

SET enable_seqscan = off;

Note: Even if EXPLAIN shows a sequential scan, <@> and to_bm25query always use the index for corpus statistics (document counts, average length) required for BM25 scoring.

Filtering with WHERE Clauses

There are two ways filtering interacts with BM25 index scans:

Pre-filtering uses a separate index (B-tree, etc.) to reduce rows before scoring:

-- Create index on filter column
CREATE INDEX ON documents (category_id);

-- Query filters first, then scores matching rows
SELECT * FROM documents
WHERE category_id = 123
ORDER BY content <@> 'search terms'
LIMIT 10;

Post-filtering applies the BM25 index scan first, then filters results. Columns without their own index are filtered after the BM25 scan:

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

Performance considerations:

• Pre-filtering tradeoff: If the filter matches many rows (e.g., 100K+), scoring all of them can be expensive. The BM25 index is most efficient when it can use top-k optimization (ORDER BY + LIMIT) to avoid scoring every matching document.

• Post-filtering tradeoff: The index returns top-k results before filtering. If your WHERE clause eliminates most results, you may get fewer rows than requested. Increase LIMIT to compensate, then re-limit in application code.

• Best case: Pre-filter with a selective condition (matches <10% of rows), then let BM25 score the reduced set with ORDER BY + LIMIT.

This is similar to the filtering behavior in pgvector, where approximate indexes also apply filtering after the index scan.

Indexing

Create a BM25 index on your text columns:

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

Index Options

• text_config - PostgreSQL text search configuration to use (required)
• k1 - term frequency saturation parameter (1.2 by default)
• b - length normalization parameter (0.75 by default)

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

Also supports different text search configurations:

-- English documents with stemming
CREATE INDEX docs_en_idx ON documents USING bm25(content) WITH (text_config='english');

-- Simple text processing without stemming
CREATE INDEX docs_simple_idx ON documents USING bm25(content) WITH (text_config='simple');

-- Language-specific configurations
CREATE INDEX docs_fr_idx ON french_docs USING bm25(content) WITH (text_config='french');
CREATE INDEX docs_de_idx ON german_docs USING bm25(content) WITH (text_config='german');

Expression Indexes

Index expressions instead of plain columns — useful for JSONB fields, multi-column concatenation, and text transformations:

-- JSONB field extraction
CREATE INDEX 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;

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

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

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

Partial Indexes

Index a subset of rows by adding a WHERE clause. Partial indexes are smaller and faster when queries always target a specific subset:

CREATE INDEX ON docs USING bm25 (content)
    WITH (text_config='english')
    WHERE status = 'published';

SELECT * FROM docs
WHERE status = 'published'
ORDER BY content <@> to_bm25query('search terms', 'docs_content_idx')
LIMIT 10;

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

Expression and partial indexes can be combined:

CREATE INDEX ON events USING bm25 ((data->>'message'))
    WITH (text_config='english')
    WHERE (data->>'severity') = 'error';

Multilingual Tables

For tables with documents in multiple languages, create one partial index per language, each with the appropriate text search configuration:

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

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

Each index applies language-appropriate stemming and stop words. Query with the matching predicate and index name:

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

Data Types

bm25query

The bm25query type represents queries for BM25 scoring with optional index context:

-- Create a bm25query with index name (required for WHERE clause and standalone scoring)
SELECT to_bm25query('search query text', 'docs_idx');
-- Returns: docs_idx:search query text

-- Embedded index name syntax (alternative form using cast)
SELECT 'docs_idx:search query text'::bm25query;
-- Returns: docs_idx:search query text

-- Create a bm25query without index name (only works in ORDER BY with index scan)
SELECT to_bm25query('search query text');
-- Returns: search query text

Note: In PostgreSQL 18, the embedded index name syntax using single colon (:) allows the query planner to determine the index name even when evaluating SELECT clause expressions early. This ensures compatibility across different query evaluation strategies.

bm25query Functions

Performance

pg_textsearch indexes use an on-disk paged memtable (the L0 of an LSM) for efficient writes. The memtable is mutated under standard buffer locks and WAL-logged via GenericXLog. Like other index types, it is faster to create an index after loading your data.

-- Load data first
INSERT INTO documents (content) VALUES (...);

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

Parallel Index Builds

pg_textsearch supports parallel index builds for faster indexing of large tables. Postgres automatically uses parallel workers based on table size and configuration.

-- Configure parallel workers (optional, uses server defaults otherwise)
SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '256MB';  -- At least 64MB required for parallel builds

-- Create index (parallel workers used automatically for large tables)
CREATE INDEX docs_idx ON documents USING bm25(content) WITH (text_config='english');

Note: The planner requires maintenance_work_mem >= 64MB to enable parallel index builds. With insufficient memory, builds fall back to serial mode silently.

You'll see a notice when parallel build is used:

NOTICE:  parallel index build: launched 4 of 4 requested workers

For partitioned tables, each partition builds its index independently with parallel workers if the partition is large enough. This allows efficient indexing of very large partitioned datasets.

Performance Tuning

Force-merging segments

The index stores data in multiple segments across levels (similar to an LSM tree). After bulk loads or sustained incremental inserts, multiple segments may accumulate; consolidating them into one improves query speed by reducing the number of segments scanned:

SELECT bm25_force_merge('docs_idx');

This is analogous to Lucene's forceMerge(1). It rewrites all segments into a single segment and reclaims the freed pages. Best used after large batch inserts, not during ongoing write traffic.

Use LIMIT with ORDER BY

Top-k queries (ORDER BY ... LIMIT n) enable Block-Max WAND optimization, which skips blocks of postings that cannot contribute to the top results. Without a LIMIT clause, the index falls back to scoring all matching documents up to pg_textsearch.default_limit.

-- Fast: BMW skips non-competitive blocks
SELECT * FROM documents ORDER BY content <@> 'search terms' LIMIT 10;

-- Slower: scores up to default_limit documents
SELECT * FROM documents ORDER BY content <@> 'search terms';

Segment compression

Compression is on by default and generally improves both index size and query performance (fewer pages to read). Disable only if you observe that decompression overhead is a bottleneck for your workload:

SET pg_textsearch.compress_segments = off;

Postgres settings that affect index builds

pg_textsearch GUCs

Memtable architecture

Starting in 1.3.0, the L0 memtable lives in the index relation itself as a chain of doc-record pages, mutated under standard buffer locks and WAL-logged via GenericXLog. There is no shared-memory memtable, no custom WAL resource manager, and no docid-page recovery scaffold. PostgreSQL's stock WAL replay (including the single-page reconstruction helper used by online-page-fix tooling) reconstructs every page without needing to load pg_textsearch.so. See docs/memtable_v2.md for the spec.

Auto-spill is governed by two complementary triggers:

• memtable_pages_threshold — fires after each insert when the chain has grown past the configured page count. Default 64 pages (~512 KB at 8 KB blocks) keeps query latency bounded since the chain stays small.
• bulk_load_threshold — fires at COMMIT when a single transaction accumulates many terms in the memtable; useful for COPY / bulk INSERT to bound chain-page growth.

-- Manual spill (forces the current chain to a new L0 segment)
SELECT bm25_spill_index('docs_idx');

VACUUM (including autovacuum's insert-threshold path) also spills the memtable when it runs, so the amount of un-spilled state between CREATE INDEX and the next server restart stays bounded.

Crash recovery: The on-disk memtable chain is itself the durable record. After a crash, stock PostgreSQL replay restores every page; no rebuild is needed at first backend open.

Streaming replication: All page mutations are replicated via the standard WAL stream. Standbys reconstruct every page natively.

Monitoring

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelid::regclass::text ~ 'pg_textsearch';

Examples

Basic Search

CREATE TABLE articles (id serial PRIMARY KEY, title text, content text);
CREATE INDEX articles_idx ON articles USING bm25(content) WITH (text_config='english');

INSERT INTO articles (title, content) VALUES
    ('Database Systems', 'PostgreSQL is a powerful relational database system'),
    ('Search Technology', 'Full text search enables finding relevant documents quickly'),
    ('Information Retrieval', 'BM25 is a ranking function used in search engines');

-- Find relevant documents
SELECT title, content <@> 'database search' as score
FROM articles
ORDER BY score;

Also supports different languages and custom parameters:

-- Different languages
CREATE INDEX fr_idx ON french_articles USING bm25(content) WITH (text_config='french');
CREATE INDEX de_idx ON german_articles USING bm25(content) WITH (text_config='german');

-- Custom parameters
CREATE INDEX custom_idx ON documents USING bm25(content)
    WITH (text_config='english', k1=2.0, b=0.9);

Limitations

No Phrase Queries

The BM25 index stores term frequencies but not term positions, so it cannot natively evaluate phrase queries like "database system". You can emulate phrase matching by combining BM25 ranking with a post-filter:

-- BM25 ranks candidates; subquery over-fetches to account for
-- post-filter eliminating non-phrase matches
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;

Because the post-filter eliminates some results, the inner LIMIT should be larger than the desired result count.

No Built-in Faceted Search

pg_textsearch does not provide dedicated faceting operators, but standard Postgres query machinery handles common faceting patterns:

-- Filter by category (assumes a B-tree index on category)
SELECT * FROM documents
WHERE category = 'engineering'
ORDER BY content <@> 'search terms'
LIMIT 10;

-- Compute facet counts over top search results
SELECT category, count(*)
FROM (
    SELECT category FROM documents
    ORDER BY content <@> 'search terms'
    LIMIT 100
) matches
GROUP BY category;

Insert/Update Performance

The memtable architecture is designed to support efficient writes, but sustained write-heavy workloads are not yet fully optimized. For initial data loading, creating the index after loading data is faster than incremental inserts. This is an active area of development.

No Background Compaction

Segment compaction currently runs synchronously during memtable spill operations. Write-heavy workloads may observe compaction latency during spills. Background compaction is planned for a future release.

Partitioned Tables

BM25 indexes on partitioned tables use partition-local statistics. Each partition maintains its own:

• Document count (total_docs)
• Average document length (avg_doc_len)
• Per-term document frequencies for IDF calculation

This means:

• Queries targeting a single partition compute accurate BM25 scores using that partition's statistics
• Queries spanning multiple partitions return scores computed independently per partition, which may not be directly comparable across partitions

Example: If partition A has 1000 documents and partition B has 10 documents, the term "database" would have different IDF values in each partition. Results from both partitions would have scores on different scales.

Recommendations:

• For time-partitioned data, query individual partitions when score comparability matters
• Use partitioning schemes where queries naturally target single partitions
• Consider this behavior when designing partition strategies for search workloads

-- Query single partition (scores are accurate within partition)
SELECT * FROM docs
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
ORDER BY content <@> 'search terms'
LIMIT 10;

-- Cross-partition query (scores computed per-partition)
SELECT * FROM docs
ORDER BY content <@> 'search terms'
LIMIT 10;

Word Length Limit

pg_textsearch inherits PostgreSQL's tsvector word length limit of 2047 characters. Words exceeding this limit are ignored during tokenization (with an INFO message). This is defined by MAXSTRLEN in PostgreSQL's text search implementation.

For typical natural language text, this limit is never encountered. It may affect documents containing very long tokens such as base64-encoded data, long URLs, or concatenated identifiers.

This behavior is similar to other search engines:

• Elasticsearch: Truncates tokens (configurable via truncate filter, default 10 chars)
• Tantivy: Truncates to 255 bytes by default

Large Documents and Chunked Tokenization

pg_textsearch calls Postgres's to_tsvector to tokenize document text. Postgres caps a single tsvector's lexeme dictionary at 1 MB (MAXSTRPOS). Documents whose unique-token volume would exceed that cap are split into chunks (currently 256 KB) before tokenization, then the per-chunk term frequencies are merged.

Chunk boundaries are chosen at the last ASCII whitespace inside each window. This is correct for whitespace-delimited scripts (Latin, Cyrillic, Greek, Arabic, etc.). For non-whitespace-delimited scripts (CJK, Thai, Lao, Khmer), oversize documents are still indexed, but the chunk boundary may fall in the middle of what a language-aware tokenizer would treat as a word. In practice this is acceptable because Postgres's default text-search parser does not emit per-word tokens for those scripts anyway. If you use a custom text search configuration with a parser that produces word-level tokens for one of these scripts, very large documents may produce slightly different lexeme counts than a single-shot tokenization would.

Workaround for large CJK (or other non-whitespace-delimited) documents: split the document into smaller pieces in the application layer and index a text[] column instead of text. pg_textsearch indexes arrays element-by-element and BM25 scores match what you'd get from concatenating the elements into a single text value, so you keep ranking quality while controlling where chunk boundaries fall. Pair this with a CJK-aware text search configuration from an extension such as zhparser (Chinese) so that each chunk gets word-level tokenization:

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

CREATE TABLE docs (id bigserial PRIMARY KEY, content text[]);
CREATE INDEX docs_bm25 ON docs USING bm25(content)
    WITH (text_config='public.chinese_zh');

PL/pgSQL and Stored Procedures

The implicit text <@> 'query' syntax relies on planner hooks to automatically detect the BM25 index. These hooks don't run inside PL/pgSQL DO blocks, functions, or stored procedures.

Inside PL/pgSQL, use explicit index names with to_bm25query():

-- This won't work in PL/pgSQL:
-- SELECT * FROM docs ORDER BY content <@> 'search terms' LIMIT 10;

-- Use explicit index name instead:
SELECT * FROM docs
ORDER BY content <@> to_bm25query('search terms', 'docs_idx')
LIMIT 10;

Regular SQL queries (outside PL/pgSQL) support both forms.

Troubleshooting

-- List available text search configurations
SELECT cfgname FROM pg_ts_config;

-- List BM25 indexes
SELECT indexname FROM pg_indexes WHERE indexdef LIKE '%USING bm25%';

Installation Notes

If your machine has multiple Postgres installations, specify the path to pg_config:

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

If you get compilation errors, install Postgres development files:

# Ubuntu/Debian
sudo apt install postgresql-server-dev-17  # for PostgreSQL 17
sudo apt install postgresql-server-dev-18  # for PostgreSQL 18

Reference

Index Options

Text Search Configurations

Available configurations depend on your Postgres installation:

# SELECT cfgname FROM pg_ts_config;
  cfgname
------------
 simple
 arabic
 armenian
 basque
 catalan
 danish
 dutch
 english
 finnish
 french
 german
 greek
 hindi
 hungarian
 indonesian
 irish
 italian
 lithuanian
 nepali
 norwegian
 portuguese
 romanian
 russian
 serbian
 spanish
 swedish
 tamil
 turkish
 yiddish
(29 rows)

Further language support is available via extensions — see Chinese full-text search below.

Chinese full-text search

pg_textsearch tokenizes both documents and queries through the index's text_config (a standard PostgreSQL text search configuration), so Chinese support is a matter of choosing a Chinese-aware configuration; pg_textsearch itself needs no language-specific code. PostgreSQL's built-in parser does not split Chinese text into words, so pair pg_textsearch with a Chinese word segmenter such as zhparser, which builds on the SCWS segmentation library. On a managed platform the extension must be allow-listed (for example, zhparser is not on the Azure Database for PostgreSQL Flexible Server allow-list).

zhparser is packaged as a text search parser, so a configuration built on it plugs directly into a bm25 index. CREATE EXTENSION zhparser also registers the parser; map the token types you want to index and reference the configuration by a schema-qualified name.

CREATE EXTENSION zhparser;

-- Map zhparser's content token types (nouns, verbs, adjectives, idioms,
-- interjections, set phrases). Schema-qualify the configuration so the
-- bm25 index build can resolve it by name.
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 docs (id bigserial PRIMARY KEY, content text);
CREATE INDEX docs_bm25 ON docs USING bm25 (content)
    WITH (text_config='public.chinese');

-- The query string is tokenized with the same configuration:
SELECT id FROM docs
ORDER BY content <@> to_bm25query('机器学习', 'docs_bm25')
LIMIT 10;

An end-to-end regression test for this setup lives in test/sql/chinese.sql and runs via make test-chinese (see the Chinese CI workflow). For large Chinese documents, the large-document workaround above applies the same n,v,a,i,e,l zhparser mapping to a chunked text[] column.

Development Functions

These functions are for debugging and development use only. Their interface may change in future releases without notice. Functions marked with † require superuser privileges.

Additional file-writing debug functions (bm25_dump_index(text, text) and bm25_debug_pageviz) are available in debug builds only (compile with -DDEBUG_DUMP_INDEX).

-- Merge all segments into one (best after bulk loads)
SELECT bm25_force_merge('docs_idx');

-- Force spill to disk (returns number of entries spilled)
SELECT bm25_spill_index('docs_idx');

-- Quick overview of index statistics
SELECT bm25_summarize_index('docs_idx');

-- Detailed dump for debugging (truncated output)
SELECT bm25_dump_index('docs_idx');

Extension Compatibility

pg_textsearch uses fixed LWLock tranche IDs 1001-1008 to support large numbers of indexes (e.g., partitioned tables with hundreds of partitions). If you use another Postgres extension that also registers fixed tranche IDs in this range, wait event names in pg_stat_activity may be incorrect. Core Postgres tranches use IDs below 100. If you encounter a conflict, please open an issue.

Contributing

See CONTRIBUTING.md for development setup, code style, and how to submit pull requests.

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