pg_textsearch Documentation

The memtable cache uses a three-tier memory management system to prevent excessive memory consumption:

1. Per-index soft cap: Limits memory usage for a specific index.
2. Global soft cap: Limits total memory usage across all indices. When this limit is reached, the system uses evict_largest (protected by global_eviction_mutex) to remove items, while ensuring the caller's own index is not evicted.
3. Global hard cap: A strict limit on total memory usage.

Memtable v2 uses an on-disk paged design. When the memtable reaches a threshold, a 'spill' occurs to move data into L0 segments.

Spill Workflow:

1. A chain source is constructed under an LW_EXCLUSIVE lock.
2. Sorted TermInfo[] and a TpDocMapBuilder are extracted.
3. A new L0 segment is built via tp_write_segment.
4. tp_spill_finalize: Updates the metapage (root, doc counts, lengths) and links the new segment to the old chain head.
5. tp_memtable_mark_chain_dead: Marks the old chain pages as DEAD using the spill transaction's FullTransactionId as the recycle horizon.

Crash Safety: The process is ordered to finalize the new segment before marking old pages as dead. If a crash occurs between these steps, orphaned pages may leak until a REINDEX is performed, but data corruption is avoided.

The Memtable cache is designed to restore v1 query performance on top of v2 storage. It uses a TpMemtable structure to cache data, utilizing dshash (inverted index) and DSA (dynamic storage allocation) patterns.

Key characteristics:

• Data Structures: Uses string_hash_handle for term-to-entry mapping and doc_lengths_handle for ctid-to-doc-length mapping.
• Corpus Statistics: Total document counts (total_docs) and total lengths (total_len) are sourced from TpSharedIndexState to represent the whole-index totals (segments + memtable), rather than being tracked separately by the cache.
• Spill Detection: Uses a generation token (cursor_gen_spill_count) derived from TpSharedIndexState.spill_generation. This prevents ABA problems during spills where a physical block might be reused after a bm25_force_merge or tp_truncate_dead_pages operation.

To avoid confusion with historical implementation versions, use the following descriptive terms when working with the project:

Memtable v2 is an on-disk paged design introduced in version 1.3.0. It replaces the previous shared-memory dshash implementation to improve compatibility with PostgreSQL's single-page WAL-redo and streaming replication.

Instead of shared memory, the L0 layer is now a chain of pages stored directly within the index relation. These pages are mutated under standard buffer locks and logged via GenericXLog. This design ensures that standard PostgreSQL replay can reconstruct every page without requiring the pg_textsearch.so extension to be loaded during redo.

For very large documents in languages like Chinese, Japanese, or Korean, pg_textsearch might encounter chunking boundaries that split words incorrectly.

Workaround: Split the document into smaller pieces in your application layer and index a text[] column instead of a single text column. pg_textsearch indexes arrays element-by-element, and the BM25 scores will match a single concatenated text value while giving you control over chunk boundaries. Pair this with a language-specific parser like zhparser.

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

To extend the benchmark suite with a new dataset, follow these steps:

1. Create a new directory: datasets/[name]/.
2. Add a download.sh script to handle data preparation.
3. Add a load.sql script to load data and create the index.
4. Add a queries.sql script containing the query benchmarks.
5. Update the benchmarks/README.md to include the new dataset.

pg_textsearch supports parallel index builds to speed up indexing for large tables. PostgreSQL automatically uses parallel workers based on table size and configuration.

Requirements:

• maintenance_work_mem must be set to at least 64MB. If it is lower, the build will silently fall back to serial mode.
• You can configure the number of workers using max_parallel_maintenance_workers.

When a parallel build is active, you will see a notice: NOTICE: parallel index build: launched X of X requested workers.

-- Configure parallel workers
SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '256MB';  -- Must be >= 64MB

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

To use pg_textsearch, you must first load it via shared_preload_libraries in your postgresql.conf and restart the PostgreSQL server. After restarting, enable the extension in your specific database.

1. Update postgresql.conf:

shared_preload_libraries = 'pg_textsearch'

2. Restart PostgreSQL.
3. Run the following SQL command in your database:

CREATE EXTENSION pg_textsearch;

To support Chinese, pair pg_textsearch with the zhparser extension. You must create a custom TEXT SEARCH CONFIGURATION using the zhparser parser and map the appropriate token types (e.g., n, v, a, i, e, l) to the simple dictionary. Ensure the configuration is schema-qualified so the bm25 index can resolve it.

CREATE EXTENSION zhparser;

-- Map zhparser's content token types
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;

Appending to the memtable (tp_memtable_append) follows a strict lock order to prevent deadlocks:

1. per-index LWLock SHARED
2. tail buf EXCL
3. new buf EXCL (if extending)
4. metapage buf EXCL

Append Modes:

• Bootstrap: Used when the chain is empty. Initializes the first page and sets both head and tail in the metapage.
• Fast append: Appends to the current tail if the record fits.
• Extend: If the tail is full but the record fits in one page, a new page is allocated and linked.
• Fragment: For oversized records, a chain of continuation pages is created and then published by updating the metapage and the previous tail's next_block pointer.