--- title: Optimize full text search with BM25 | Tiger Data Docs description: Install and tune pg_textsearch (v1.0.0 GA) for BM25 full-text search on Tiger Cloud and self-hosted deployments --- Note **pg\_textsearch v1.0.0** (March 2026) is **production-ready** on Tiger Cloud. See the [v1.0.0 release notes](https://github.com/timescale/pg_textsearch/releases/tag/v1.0.0). `pg_textsearch` supports **PostgreSQL 17 and 18**. For self-hosted installs, confirm versions against the [upstream compatibility table](https://github.com/timescale/pg_textsearch#postgresql-version-compatibility). PostgreSQL full-text search at scale consistently hits a wall where performance degrades catastrophically. Tiger Data’s [pg\_textsearch](https://github.com/timescale/pg_textsearch) brings modern [BM25](https://en.wikipedia.org/wiki/Okapi_BM25)-based full-text search directly into PostgreSQL, with a memtable architecture for efficient indexing and ranking. `pg_textsearch` integrates seamlessly with SQL and provides better search quality and performance than the PostgreSQL built-in full-text search. With Block-Max WAND optimization, `pg_textsearch` delivers up to **4x faster top-k queries** compared to native BM25 implementations. Parallel index builds reduce indexing times by **4x or more** for large tables. Advanced compression using delta encoding and bitpacking reduces index sizes by **41%** while improving query performance by 10-20% for shorter queries. BM25 scores in `pg_textsearch` are returned as negative values, where lower (more negative) numbers indicate better matches. `pg_textsearch` implements the following: - **Corpus-aware ranking**: BM25 uses inverse document frequency to weight rare terms higher - **Term frequency saturation**: prevents documents with excessive term repetition from dominating results - **Length normalization**: adjusts scores based on document length relative to corpus average - **Relative ranking**: focuses on rank order rather than absolute score values This page shows you how to install `pg_textsearch`, configure BM25 indexes, and optimize your search capabilities using the following best practices: - **Parallel indexing**: enable parallel workers for faster index creation on large tables - **Language configuration**: choose appropriate text search configurations for your data language - **Hybrid search**: combine with pgvector or pgvectorscale for applications requiring both semantic and keyword search - **Query optimization**: use score thresholds to filter low-relevance results - **Index monitoring**: regularly check index usage and memory consumption ## Prerequisites To follow the steps on this page: - Create a target [Tiger Cloud service](/docs/get-started/quickstart/create-service/index.md) with the Real-time analytics capability. You need [your connection details](/docs/integrate/find-connection-details/index.md). This procedure also works for [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md). ## Install pg\_textsearch To install this PostgreSQL extension: 1. **Connect to your Tiger Cloud service** In [Tiger Console](https://console.cloud.tigerdata.com/dashboard/services) open an [SQL editor](/docs/build/data-management/run-queries-from-tiger-console/index.md). You can also connect to your service using [psql](/docs/integrate/query-administration/psql#connect-to-your-service/index.md). 2. **Enable the extension on your Tiger Cloud service** - For new services, simply enable the extension: ``` CREATE EXTENSION pg_textsearch; ``` - For existing services, update your instance, then enable the extension: The extension may not be available until after your next scheduled maintenance window. To pick up the update immediately, manually pause and restart your service. 3. **Verify the installation** ``` SELECT * FROM pg_extension WHERE extname = 'pg_textsearch'; ``` You have installed `pg_textsearch` on Tiger Cloud. ## Self-hosted: load `pg_textsearch` before `CREATE EXTENSION` On self-managed PostgreSQL, the extension must be **preloaded** so the server loads it at startup: 1. Set `shared_preload_libraries` in `postgresql.conf` (append `pg_textsearch` to any existing list): ``` shared_preload_libraries = 'pg_textsearch' ``` 2. Restart PostgreSQL. 3. In each database where you need search, run `CREATE EXTENSION pg_textsearch;`. Tiger Cloud manages preloading for you; you typically only run `CREATE EXTENSION` in the SQL editor or client. ## Create BM25 indexes on your data BM25 indexes provide modern relevance ranking that outperforms PostgreSQL‘s built-in ts\_rank functions by using corpus statistics and better algorithmic design. To create a BM25 index with `pg_textsearch`: 1. **Create a table with text content** ``` CREATE TABLE products ( id serial PRIMARY KEY, name text, description text, category text, price numeric ); ``` 2. **Insert sample data** ``` INSERT INTO products (name, description, category, price) VALUES ('Mechanical Keyboard', 'Durable mechanical switches with RGB backlighting for gaming and productivity', 'Electronics', 149.99), ('Ergonomic Mouse', 'Wireless mouse with ergonomic design to reduce wrist strain during long work sessions', 'Electronics', 79.99), ('Standing Desk', 'Adjustable height desk for better posture and productivity throughout the workday', 'Furniture', 599.99); ``` 3. **Create a BM25 index** ``` CREATE INDEX products_search_idx ON products USING bm25(description) WITH (text_config='english'); ``` BM25 supports single-column indexes only. For optimal performance, load your data first, then create the index. You have created a BM25 index for full-text search. ## Enable parallel indexing for faster index creation Since [v0.5.0](https://github.com/timescale/pg_textsearch/releases/tag/v0.5.0) `pg_textsearch` supports parallel index builds that can significantly reduce indexing times for large tables. PostgreSQL automatically uses parallel workers based on table size and available resources. 1. **Configure parallel workers (optional)** PostgreSQL uses server defaults, but you can adjust settings for your workload: ``` -- Set number of parallel workers (uses CPU count by default) SET max_parallel_maintenance_workers = 4; -- Set memory for index builds (must be at least 64MB for parallel builds) SET maintenance_work_mem = '256MB'; ``` Note The planner requires `maintenance_work_mem >= 64MB` to enable parallel index builds. With insufficient memory, builds fall back to serial mode silently. 2. **Create index (parallel workers used automatically for large tables)** ``` CREATE INDEX products_search_idx ON products USING bm25(description) WITH (text_config='english'); ``` When parallel build is used, you see a notice: ``` NOTICE: parallel index build: launched 4 of 4 requested workers ``` 3. **Verify parallel execution in partitioned tables** 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. You have configured parallel index builds for faster indexing. ## Merge BM25 segments after bulk loads After large batch inserts or sustained incremental writes, multiple on-disk **segments** can accumulate. Merging them into a single segment improves query speed by reducing how many segments a query must scan. This is analogous to Lucene’s `forceMerge(1)`. Use `bm25_force_merge()` **after bulk loads**, not during steady high write traffic: ``` SELECT bm25_force_merge('products_search_idx'); ``` ## Optimize search queries for performance The `<@>` operator returns BM25-based scores as **negative** values: **lower (more negative) means a better match**. ### Ranked search with implicit query syntax (primary pattern) For typical top-k search, pass the query as a **string literal**. In `ORDER BY`, PostgreSQL can **automatically detect** the BM25 index from the indexed column: ``` SELECT name, description, description <@> 'ergonomic work' AS score FROM products ORDER BY score LIMIT 3; ``` You see something like: ``` name | description | score ----------------------------+-----------------------------------------------------------------------------------+--------------------- Ergonomic Mouse | Wireless mouse with ergonomic design to reduce wrist strain during long work sessions | -1.8132977485656738 Mechanical Keyboard | Durable mechanical switches with RGB backlighting for gaming and productivity | 0 Standing Desk | Adjustable height desk for better posture and productivity throughout the workday | 0 ``` ### The `bm25query` type and `to_bm25query()` Use an explicit **`bm25query`** value when you need the index name in the expression (for example in `WHERE`, or when the planner cannot infer the index from context): | Form | Use | | ------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------- | | `to_bm25query('query text')` | Query text only; use in **`ORDER BY`** with an index scan (implicit index detection from the column). | | `to_bm25query('query text', 'index_name')` | Query text **and** index name; required for **`WHERE`** filters and whenever you must name the index. | | `'index_name:query text'::bm25query` | Cast form (embedded index name). In PostgreSQL 18, the single-colon form helps the planner when expressions are evaluated early. | | `text <@> bm25query` → `double precision` | BM25 score (negative is better). | | `bm25query = bm25query` | Equality comparison. | ### Filter results by score threshold For `WHERE` clauses, use **`to_bm25query()`** with an explicit index name: ``` SELECT name, description <@> to_bm25query('wireless', 'products_search_idx') AS score FROM products WHERE description <@> to_bm25query('wireless', 'products_search_idx') < -0.5; ``` You see something like: ``` name | score ----------------+--------------------- Ergonomic Mouse | -0.9066488742828369 ``` ### Combine with standard SQL operations ``` SELECT category, name, description <@> to_bm25query('ergonomic', 'products_search_idx') AS score FROM products WHERE price < 500 AND description <@> to_bm25query('ergonomic', 'products_search_idx') < -0.5 ORDER BY score LIMIT 5; ``` You see something like: ``` category | name | score -------------+-----------------+--------------------- Electronics | Ergonomic Mouse | -0.9066488742828369 ``` ### Pre-filtering vs post-filtering How you combine BM25 with other predicates affects performance: - **Pre-filtering**: use another index (for example B-tree on `category_id`) so PostgreSQL restricts rows **before** BM25 scoring. Best when the filter is **selective** (for example matches a small fraction of rows); then `ORDER BY ... LIMIT` scores a smaller candidate set. - **Post-filtering**: the BM25 index scan produces top-k matches **first**; other `WHERE` conditions apply **after**. If the filter removes most of those rows, you can get **fewer rows than `LIMIT`**. Increase the inner `LIMIT` and re-limit in application code if needed. This mirrors tradeoffs familiar from approximate vector indexes: know whether your filter is shrinking the universe of rows before or after ranking. ### Verify plans with EXPLAIN (and sequential scans on small tables) ``` EXPLAIN SELECT * FROM products ORDER BY description <@> 'ergonomic' LIMIT 5; ``` On **small** tables, the planner may choose a **sequential scan** instead of the BM25 index. To force index use while testing, you can run: ``` SET enable_seqscan = off; ``` Note Even when `EXPLAIN` shows a sequential scan, `<@>` and `to_bm25query()` still use the BM25 index for **corpus statistics** (document counts, average length, and related metadata) required to score results. You have optimized your search queries for BM25 ranking. ## Build hybrid search with semantic and keyword search Combine `pg_textsearch` with `pgvector` or `pgvectorscale` to build powerful hybrid search systems that use both semantic vector search and keyword BM25 search. 1. **Enable the vectorscale extension on your Tiger Cloud service** ``` CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE; ``` 2. **Create a table with both text content and vector embeddings** ``` CREATE TABLE articles ( id serial PRIMARY KEY, title text, content text, embedding vector(3) -- Using 3 dimensions for this example; use 1536 for OpenAI ada-002 ); ``` 3. **Insert sample data** ``` INSERT INTO articles (title, content, embedding) VALUES ('Database Query Optimization', 'Learn how to optimize database query performance using indexes and query planning', '[0.1, 0.15, 0.2]'), ('Performance Tuning Guide', 'A comprehensive guide to performance tuning in distributed systems and databases', '[0.12, 0.18, 0.25]'), ('Introduction to Indexing', 'Understanding how database indexes improve query performance and data retrieval', '[0.09, 0.14, 0.19]'), ('Advanced SQL Techniques', 'Master advanced SQL techniques for complex data analysis and reporting', '[0.5, 0.6, 0.7]'), ('Data Warehousing Basics', 'Getting started with data warehousing and analytical query processing', '[0.8, 0.9, 0.85]'); ``` 4. **Create indexes for both search types** ``` -- Vector index for semantic search CREATE INDEX articles_embedding_idx ON articles USING hnsw (embedding vector_cosine_ops); -- Keyword index for BM25 search CREATE INDEX articles_content_idx ON articles USING bm25(content) WITH (text_config='english'); ``` 5. **Perform hybrid search using reciprocal rank fusion** ``` WITH vector_search AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector) AS rank FROM articles ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector LIMIT 20 ), keyword_search AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY content <@> 'query performance') AS rank FROM articles ORDER BY content <@> 'query performance' LIMIT 20 ) SELECT a.id, a.title, COALESCE(1.0 / (60 + v.rank), 0.0) + COALESCE(1.0 / (60 + k.rank), 0.0) AS combined_score FROM articles a LEFT JOIN vector_search v ON a.id = v.id LEFT JOIN keyword_search k ON a.id = k.id WHERE v.id IS NOT NULL OR k.id IS NOT NULL ORDER BY combined_score DESC LIMIT 10; ``` You see something like: ``` id | title | combined_score ----+----------------------------+-------------------- 3 | Introduction to Indexing | 0.0325224748810153 1 | Database Query Optimization| 0.0322664584959667 2 | Performance Tuning Guide | 0.0320020481310804 5 | Data Warehousing Basics | 0.0310096153846154 4 | Advanced SQL Techniques | 0.0310096153846154 ``` 6. **Adjust relative weights for different search types** ``` WITH vector_search AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector) AS rank FROM articles ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector LIMIT 20 ), keyword_search AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY content <@> 'query performance') AS rank FROM articles ORDER BY content <@> 'query performance' LIMIT 20 ) SELECT a.id, a.title, 0.7 * COALESCE(1.0 / (60 + v.rank), 0.0) + -- 70% weight to vectors 0.3 * COALESCE(1.0 / (60 + k.rank), 0.0) -- 30% weight to keywords AS combined_score FROM articles a LEFT JOIN vector_search v ON a.id = v.id LEFT JOIN keyword_search k ON a.id = k.id WHERE v.id IS NOT NULL OR k.id IS NOT NULL ORDER BY combined_score DESC LIMIT 10; ``` You see something like: ``` id | title | combined_score ----+----------------------------+-------------------- 3 | Introduction to Indexing | 0.0163141195134849 2 | Performance Tuning Guide | 0.0160522273425499 1 | Database Query Optimization| 0.0160291438979964 4 | Advanced SQL Techniques | 0.0155528846153846 5 | Data Warehousing Basics | 0.0154567307692308 ``` You have implemented hybrid search combining semantic and keyword search. ## Configuration options Customize `pg_textsearch` behavior for your specific use case and data characteristics. 1. **Configure memory and performance settings** To manage memory usage, you control when the in-memory index spills to disk segments. When the memtable reaches the threshold, it automatically flushes to a segment at transaction commit. Tips **Crash recovery:** the memtable is rebuilt from the heap on startup, so you do **not** lose indexed data if PostgreSQL crashes before a spill to disk completes. ``` -- Set memtable spill threshold (default 32000000 posting entries, ~1M docs/segment) SET pg_textsearch.memtable_spill_threshold = 32000000; -- Set bulk load spill threshold (default 100000 terms per transaction) SET pg_textsearch.bulk_load_threshold = 150000; -- Segments per level before automatic compaction (default 8, allowed range 2–64) SET pg_textsearch.segments_per_level = 8; -- Set default query limit when no LIMIT clause is present (default 1000) SET pg_textsearch.default_limit = 5000; -- Enable Block-Max WAND optimization for faster top-k queries (enabled by default) SET pg_textsearch.enable_bmw = true; -- Log block skip statistics for debugging query performance (disabled by default) SET pg_textsearch.log_bmw_stats = false; -- Log BM25 scores during scans (disabled by default) SET pg_textsearch.log_scores = false; ``` Since [v0.1.0](https://github.com/timescale/pg_textsearch/releases/tag/v0.1.0) ``` -- Enable segment compression using delta encoding and bitpacking (enabled by default) -- Reduces index size by ~41% with 10-20% query performance improvement for shorter queries SET pg_textsearch.compress_segments = on; ``` Since [v0.4.0](https://github.com/timescale/pg_textsearch/releases/tag/v0.4.0) 2. **Configure language-specific text processing** You can create multiple BM25 indexes on the same column with different language configurations: ``` -- Create an additional index with simple tokenization (no stemming) CREATE INDEX products_simple_idx ON products USING bm25(description) WITH (text_config='simple'); -- Example: French language configuration for a French products table -- CREATE INDEX products_fr_idx ON products_fr -- USING bm25(description) -- WITH (text_config='french'); ``` 3. **Tune BM25 parameters** ``` -- Adjust term frequency saturation (k1) and length normalization (b) CREATE INDEX products_custom_idx ON products USING bm25(description) WITH (text_config='english', k1=1.5, b=0.8); ``` 4. **Monitor index usage and memory consumption** - Check index usage statistics ``` SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelid::regclass::text ~ 'bm25'; ``` - View index summary with corpus statistics and memory usage (**superuser**) ``` SELECT bm25_summarize_index('products_search_idx'); ``` - View detailed index structure (output is truncated for display) (**superuser**) ``` SELECT bm25_dump_index('products_search_idx'); ``` Note The two-argument form `bm25_dump_index('index_name', '/path/to/file.txt')` exists only in **debug builds** compiled with `-DDEBUG_DUMP_INDEX`. Standard packages expose the single-argument form only. - Force memtable spill to disk (useful for testing or memory management) ``` SELECT bm25_spill_index('products_search_idx'); ``` You have configured `pg_textsearch` for optimal performance. For production applications, consider implementing result caching and pagination to improve user experience with large result sets. ## Current limitations - **No phrase search**: you cannot search for exact multi-word phrases (no positional index; emulate with BM25 plus a post-filter if needed). - **No compressed data support**: `pg_textsearch` does not work with **compressed** chunk or table data (for example Hypercore-compressed data where the extension applies). - **No expression indexing**: each BM25 index covers a **single text column**. You cannot index an expression such as `lower(title) || ' ' || content` directly; use a **generated column** that concatenates or normalizes text, then index that column. - **No built-in faceted search**: there is no dedicated faceting operator; use ordinary PostgreSQL patterns (`WHERE`, `GROUP BY`, counts) alongside BM25 filters. - **Insert and update throughput**: the memtable design supports incremental writes, but **sustained write-heavy** workloads are still a focus of ongoing performance work. Prefer **bulk load, then index** (or `bm25_force_merge` after large batches) when possible. - **No background compaction**: segment compaction runs **synchronously** during memtable spill; write-heavy workloads may see latency spikes during spills. - **Partitioned table statistics**: BM25 statistics are **per partition**. Scores are accurate within a partition but are **not directly comparable** across partitions when a query spans many partitions (different IDF and length stats). Prefer queries that target a single partition when rank comparability matters. - **Word length limit**: tokenization inherits PostgreSQL’s `tsvector` **2047 character** word limit; very long tokens (for example base64 blobs) may be ignored with an `INFO` message. - **PL/pgSQL and stored procedures**: the implicit form `text <@> 'query'` relies on planner hooks that **do not run** inside PL/pgSQL `DO` blocks, functions, or procedures. Use **`to_bm25query('query', 'index_name')`** explicitly in those contexts. For more detail and workarounds, see the [limitations section](https://github.com/timescale/pg_textsearch#limitations) in the upstream README.