vchord_bm25 & pg_tokenizer

• 1: Getting Started
  • 1.1: Overview
  • 1.2: Installation
  • 1.3: Quick Start
• 2: Usage & Hybrid Search
  • 2.1: Indexing & Maintenance
  • 2.2: Querying & Scoring
  • 2.3: Filtered Search & Prefiltering
  • 2.4: Hybrid Search (BM25 + VectorChord)
  • 2.5: BM25 & Block-WeakAnd Internals
• 3: Tokenizers (pg_tokenizer)
  • 3.1: Tokenizer Architecture & Usage
  • 3.2: Text Analyzers & Filters
  • 3.3: Tokenization Models
  • 3.4: Multilingual Recipes & Limitations
• 4: Reference & Benchmarks
  • 4.1: SQL Reference
  • 4.2: GUC & Index Parameters
  • 4.3: Benchmarks
  • 4.4: Release History & API Evolution

1 - Getting Started

Get started with vchord_bm25 in PostgreSQL:

1.1 - Overview

PostgreSQL includes built-in full-text search via tsvector, tsquery, ts_rank, and GIN indexes. While effective for boolean keyword matching (@@), PostgreSQL’s built-in ranking has two structural limitations for modern search and Retrieval-Augmented Generation (RAG) workloads:

1. Suboptimal Ranking Formula (ts_rank): PostgreSQL’s ts_rank and ts_rank_cd do not track corpus-wide Inverse Document Frequency (IDF) or Average Document Length ($\text{avgdl}$) across the index. Common words are weighted the same as rare, high-signal terms, and term frequency is not properly saturated.
2. Exhaustive Index Scans on ORDER BY ... LIMIT K: A PostgreSQL GIN index only accelerates boolean matching (WHERE tsv @@ tsquery). To return the Top-10 highest-ranked documents, PostgreSQL must fetch every matching row from the heap, compute ts_rank for all of them, and sort the entire result set.

How VectorChord-BM25 Solves Both Problems

VectorChord-BM25 (vchord_bm25) introduces a dedicated PostgreSQL index access method (USING bm25) and sparse BM25 scoring operator (<&>) specifically engineered for Top-K lexical retrieval:

Architecture Overview

flowchart LR
    subgraph Tokenization["1. Tokenization Layer"]
        T1["Raw Text"] --> T2["pg_tokenizer (v0.1–v0.3)\\nor to_tsvector (main)"]
        T2 --> T3["bm25vector / tsvector\\n(Token IDs + Term Frequencies)"]
    end
    subgraph Index["2. BM25 Index (USING bm25)"]
        I1["Growing Segment\\n(Append-only pages)"] --> I2["Sealed Segment\\n(Delta-Bitpacked 128-doc blocks)"]
        I2 --> I3["Term Statistics (DF)\\n+ 1-Byte Fieldnorm Array"]
    end
    subgraph Query["3. Top-K Query (ORDER BY vec <&> bm25query LIMIT k)"]
        Q1["Block-Max Score Upper Bounds"] --> Q2["Block-WeakAnd Pivot Pruning"]
        Q2 --> Q3["Top-K Heap (-BM25 Score)"]
    end
    T3 --> Index
    Index --> Query

Two API Generations

When working with vchord_bm25, be aware of the two API generations across releases:

1. Stable Releases (v0.1.0 – v0.3.0, schema bm25_catalog):

   • Uses custom data types bm25vector (sparse token_id:frequency map, e.g., {1:1, 2:2}) and bm25query.
   • Pairs with the pg_tokenizer extension (tokenizer_catalog), which converts text into INT[] token ID arrays that cast implicitly to bm25vector.
   • Index creation: CREATE INDEX idx ON documents USING bm25 (embedding bm25_ops);
   • Query construction: to_bm25query('idx', tokenize('query text', 'tokenizer_name'))

2. Next-Gen Native tsvector Architecture (main branch):

   • Operates directly on PostgreSQL’s built-in tsvector data type (CREATE OPERATOR CLASS bm25_ops FOR TYPE tsvector USING bm25), removing the requirement for a separate bm25vector column.
   • Index creation: CREATE INDEX idx ON documents USING bm25 (vector bm25_ops) WITH (options = $$ k1 = 1.2, b = 0.75 $$);
   • Query construction: to_bm25query(to_tsvector('english', 'query text'), 'idx')

1.2 - Installation

vchord_bm25 supports PostgreSQL 14, 15, 16, 17, and 18 on Linux (x86_64 and aarch64) and macOS.

Method 1: Pigsty / Native OS Packages (Recommended)

Pigsty provides pre-built native RPM and DEB packages for vchord, vchord_bm25, and pg_tokenizer across EL 8/9/10, Debian 12/13, and Ubuntu 22.04/24.04 for PostgreSQL 14–18.

# Install via Pigsty CLI (automatically resolves PG version and OS distro)
pig ext install vchord_bm25
pig ext install pg_tokenizer

Or install directly via apt / dnf once the Pigsty repository is configured:

# Debian / Ubuntu (replace 17 with your PostgreSQL major version)
sudo apt-get install postgresql-17-vchord-bm25 postgresql-17-pg-tokenizer

# RHEL / Rocky / AlmaLinux (replace 17 with your PostgreSQL major version)
sudo dnf install vchord_bm25_17 pg_tokenizer_17

Method 2: Docker (tensorchord/vchord-suite)

The official tensorchord/vchord-suite Docker image bundles PostgreSQL, VectorChord (vchord), VectorChord-BM25 (vchord_bm25), and pg_tokenizer out of the box:

docker run \\
  --name vchord-suite \\
  -e POSTGRES_PASSWORD=postgres \\
  -p 5432:5432 \\
  -d tensorchord/vchord-suite:pg18-latest

Connect with psql:

psql -h localhost -p 5432 -U postgres

Method 3: GitHub Release Packages (.deb & RHEL .zip)

Download prebuilt .deb or RHEL 9+ .zip packages for your PostgreSQL major version (14–18) and CPU architecture (amd64 / x86_64 or arm64 / aarch64) from the GitHub Releases pages of pgsty/vchord_bm25 (upstream supervc-stack/VectorChord-bm25) and pgsty/pg_tokenizer (upstream supervc-stack/pg_tokenizer.rs):

Debian / Ubuntu (.deb)

# Install vchord_bm25 (example for PostgreSQL 16 on x86_64)
curl -LO https://github.com/supervc-stack/VectorChord-bm25/releases/download/0.3.0/postgresql-16-vchord-bm25_0.3.0-1_amd64.deb
sudo apt-get install ./postgresql-16-vchord-bm25_0.3.0-1_amd64.deb

# Install pg_tokenizer (example for PostgreSQL 16 on x86_64)
curl -LO https://github.com/supervc-stack/pg_tokenizer.rs/releases/download/0.1.1/postgresql-16-pg-tokenizer_0.1.1-1_amd64.deb
sudo apt-get install ./postgresql-16-pg-tokenizer_0.1.1-1_amd64.deb

RHEL 9+ / Enterprise Linux (.zip Archive)

For RHEL 9+ systems when installing manually from the release .zip archive (requires glibc >= 2.34):

# 1. Locate PostgreSQL library and extension directories
pg_config --pkglibdir
# Example: /usr/pgsql-16/lib
pg_config --sharedir
# Example: /usr/pgsql-16/share

# 2. Download and extract the release archive
wget https://github.com/supervc-stack/pg_tokenizer.rs/releases/download/0.1.1/pg16-pg_tokenizer-0.1.1-x86_64-unknown-linux-gnu.zip
unzip pg16-pg_tokenizer-0.1.1-x86_64-unknown-linux-gnu.zip

# 3. Copy the shared library (.so) and extension control/SQL files
sudo cp ./usr/lib/postgresql/16/lib/pg_tokenizer.so $(pg_config --pkglibdir)
sudo cp ./usr/share/postgresql/16/extension/* $(pg_config --sharedir)/extension

Method 4: Build from Source (cargo-pgrx)

To compile pgsty/vchord_bm25 and pgsty/pg_tokenizer from source:

# 1. Install Rust toolchain and system dependencies (clang, libclang, pkg-config, PostgreSQL dev headers)
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# 2. Build and install vchord_bm25
git clone https://github.com/pgsty/vchord_bm25.git
cd vchord_bm25
cargo install cargo-pgrx --version $(grep -o 'pgrx = "=[^"]*' Cargo.toml | cut -d = -f 3)
cargo pgrx init --pg17=$(which pg_config)
cargo pgrx install --release --pg-config $(which pg_config)

# 3. Build and install pg_tokenizer
git clone https://github.com/pgsty/pg_tokenizer.git
cd pg_tokenizer
cargo pgrx install --release --pg-config $(which pg_config)

cargo pgrx install --release --pg-config $(which pg_config) \
  --features "lindera-ipadic lindera-ipadic-neologd lindera-unidic lindera-ko-dic lindera-cc-cedict"

Development & Debugging (cargo-pgrx)

When developing or debugging vchord_bm25 or pg_tokenizer locally:

# Initialize pgrx managed PostgreSQL instances (PG 14–18)
cargo pgrx init

# Use the `dev-opt` profile to enable optimizations alongside debug symbols
RUST_BACKTRACE=1 cargo pgrx run --profile dev-opt

# Inside the interactive psql session, inspect the backend PID to attach gdb if needed:
SELECT pg_backend_pid();

Configure PostgreSQL & Enable Extensions

1. Shared Preload Libraries (Optional / Recommended for pg_tokenizer)

If you use pg_tokenizer and want preloaded vocabulary models initialized at server startup, add pg_tokenizer (and vchord if used) to shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'vchord.so, pg_tokenizer.so'

Restart PostgreSQL after modifying shared_preload_libraries. (vchord_bm25 itself does not require shared_preload_libraries and can be loaded dynamically per session via CREATE EXTENSION.)

2. Create Extensions and Set search_path

Connect to your target database and enable the extensions:

CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

In v0.1.0–v0.3.0, vchord_bm25 installs its objects into the bm25_catalog schema and pg_tokenizer installs into tokenizer_catalog. Add both schemas to your search_path:

-- Per-session
SET search_path TO "$user", public, bm25_catalog, tokenizer_catalog;

-- Or persist for your database / role
ALTER DATABASE postgres SET search_path TO "$user", public, bm25_catalog, tokenizer_catalog;

Verify the installed version:

SELECT extname, extversion FROM pg_extension WHERE extname IN ('vchord_bm25', 'pg_tokenizer');

1.3 - Quick Start

This guide walks through creating a table, tokenizing documents, building a bm25 index, and executing Top-K BM25 queries.

Workflow A: bm25vector + pg_tokenizer (v0.1 – v0.3)

In released versions v0.1.0 through v0.3.0, vchord_bm25 pairs with pg_tokenizer to tokenize text into a bm25vector column.

1. Enable Extensions and Configure search_path

CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

SET search_path TO "$user", public, bm25_catalog, tokenizer_catalog;

2. Create a Tokenizer

Use create_tokenizer with the built-in llmlingua2 (or bert_base_uncased) model:

SELECT create_tokenizer('my_tokenizer', $$
model = "llmlingua2"
$$);

3. Create a Table and Populate Documents

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    passage TEXT NOT NULL,
    embedding bm25vector
);

INSERT INTO documents (passage) VALUES
  ('PostgreSQL is a powerful, open-source object-relational database system. It has over 35 years of active development.'),
  ('Full-text search is a technique for searching a single computer-stored document or a collection in a full-text database.'),
  ('BM25 is a bag-of-words retrieval function that ranks a set of documents based on the query terms appearing in each document.'),
  ('VectorChord-bm25 is a PostgreSQL extension that provides native BM25 ranking index using the Block-WeakAnd algorithm.');

4. Tokenize Documents into bm25vector

tokenize(text, tokenizer_name) returns an INT[] array of token IDs, which implicitly casts to bm25vector (aggregating duplicate token IDs into term frequencies):

UPDATE documents
SET embedding = tokenize(passage, 'my_tokenizer');

-- Inspect the sparse bm25vector representation ({token_id:term_frequency, ...})
SELECT id, embedding FROM documents LIMIT 2;

5. Build the bm25 Index

CREATE INDEX documents_embedding_bm25
ON documents
USING bm25 (embedding bm25_ops);

6. Run Top-K BM25 Queries

Use the <&> operator with to_bm25query(index_name, query_vector) in an ascending ORDER BY clause:

SELECT
    id,
    passage,
    -(embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL BM25 index', 'my_tokenizer'))) AS bm25_score
FROM documents
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL BM25 index', 'my_tokenizer'))
LIMIT 10;

Workflow B: Native tsvector API (main Branch)

In the next-generation main branch rewrite, vchord_bm25 indexes PostgreSQL’s native tsvector type directly—no external tokenizer extension is required for standard languages.

1. Create Extension and Table

CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    passage TEXT NOT NULL,
    vector tsvector GENERATED ALWAYS AS (to_tsvector('english', passage)) STORED
);

INSERT INTO documents (passage) VALUES
  ('PostgreSQL is a powerful, open-source object-relational database system.'),
  ('Full-text search is a technique for searching documents in a full-text database.'),
  ('BM25 is a bag-of-words retrieval function that ranks documents based on query terms.'),
  ('VectorChord-bm25 provides a native BM25 ranking index in PostgreSQL.');

2. Build the bm25 Index on tsvector

CREATE INDEX documents_bm25_idx
ON documents
USING bm25 (vector bm25_ops)
WITH (options = $$
k1 = 1.2
b = 0.75
$$);

3. Query with to_bm25query(tsvector, regclass)

SELECT
    id,
    passage,
    -(vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL BM25 index'), 'documents_bm25_idx')) AS bm25_score
FROM documents
ORDER BY vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL BM25 index'), 'documents_bm25_idx')
LIMIT 10;

2 - Usage & Hybrid Search

Learn how to build, tune, and query vchord_bm25 indexes in production:

2.1 - Indexing & Maintenance

vchord_bm25 provides the bm25 index access method (CREATE ACCESS METHOD bm25 TYPE INDEX HANDLER _bm25_amhandler) and the bm25_ops operator class.

Creating a bm25 Index

Stable Releases (v0.1 – v0.3, bm25vector)

CREATE INDEX documents_embedding_bm25
ON documents
USING bm25 (embedding bm25_ops);

Next-Gen main Branch (tsvector)

In the main branch, bm25_ops operates on tsvector and supports index-level storage parameters (options, limit, and prefilter):

CREATE INDEX documents_vector_bm25
ON documents
USING bm25 (vector bm25_ops)
WITH (
    options = $$
    k1 = 1.2
    b = 0.75
    $$,
    limit = 100,
    prefilter = false
);

You can dynamically update limit or prefilter on an existing index with ALTER INDEX:

ALTER INDEX documents_vector_bm25 SET (limit = 200, prefilter = true);
ALTER INDEX documents_vector_bm25 RESET (limit, prefilter);

Automatic Tokenization Triggers (v0.1 – v0.3)

When using pg_tokenizer, you can keep your bm25vector column automatically synchronized on INSERT and UPDATE:

Using a Custom Model Trigger (One-Step Helper)

create_custom_model_tokenizer_and_trigger creates a custom model, a tokenizer, backfills existing rows, and attaches an automatic BEFORE INSERT OR UPDATE trigger:

SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name => 'my_tokenizer',
    model_name     => 'my_model',
    text_analyzer_name => 'my_analyzer',
    table_name     => 'documents',
    source_column  => 'passage',
    target_column  => 'embedding'
);

Using a Standard PL/pgSQL Trigger with Pretrained Models

If you use a pretrained model (such as llmlingua2 or bert_base_uncased), create a standard trigger function:

CREATE OR REPLACE FUNCTION documents_tokenize_trigger()
RETURNS trigger AS $$
BEGIN
    NEW.embedding := tokenize(NEW.passage, 'my_tokenizer');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_documents_tokenize
BEFORE INSERT OR UPDATE OF passage ON documents
FOR EACH ROW
EXECUTE FUNCTION documents_tokenize_trigger();

Segment Architecture: Growing vs. Sealed Segments (v0.1 – v0.3)

Internally, vchord_bm25 organizes index tuples into two segment states:

1. Growing Segment: Newly inserted tuples (INSERT / UPDATE) are appended to the growing segment for fast write throughput without re-compressing entire posting lists.
2. Sealed Segment: When the growing segment exceeds bm25_catalog.segment_growing_max_page_size pages (default 4096 pages, ~32 MB), it is sealed into an immutable, read-only segment with delta-bitpacked 128-document blocks, precomputed block-max upper bounds, and term document frequency statistics.

-- Adjust the growing segment seal threshold (in 8 KB pages, range 1..1000000)
SET bm25_catalog.segment_growing_max_page_size = 4096;

Parallel Index Build & Progress Monitoring

Index creation supports PostgreSQL parallel maintenance workers and reports live build progress via pg_stat_progress_create_index across three phases:

1. initializing
2. scanning table
3. writing index structures

-- Speed up large index builds with parallel workers
SET max_parallel_maintenance_workers = 8;
SET maintenance_work_mem = '2GB';

-- Monitor build progress from another session
SELECT
    pid,
    phase,
    tuples_total,
    tuples_done,
    round(100.0 * tuples_done / nullif(tuples_total, 0), 2) AS pct_done
FROM pg_stat_progress_create_index;

Inspecting Index Pages (v0.3.0)

Version 0.3.0 provides bm25_page_inspect(index regclass, blkno int) to inspect the internal metadata and page headers of a bm25 index:

-- Inspect the metapage (block 0) of a bm25 index
SELECT bm25_page_inspect('documents_embedding_bm25', 0);

Index Constraints & Limitations

• Single-Column Only: A bm25 index can only be created on a single bm25vector (or tsvector) column. Multicolumn bm25 indexes are not supported.
• No Unlogged Tables: UNLOGGED tables are not supported by the bm25 access method (ambuildempty raises Unlogged indexes are not supported).
• Forward Scan Direction Only: bm25 index scans only support forward (ASC) scan direction on <&>.

2.2 - Querying & Scoring

Basic Top-K Query Syntax

To run an index-accelerated Top-K BM25 search, use the <&> operator in the ORDER BY clause together with to_bm25query:

-- v0.1 – v0.3 (bm25vector + pg_tokenizer)
SELECT
    id,
    passage,
    embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL vector search', 'my_tokenizer')) AS raw_score,
    -(embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL vector search', 'my_tokenizer'))) AS bm25_score
FROM documents
ORDER BY raw_score ASC
LIMIT 10;

-- main branch (native tsvector)
SELECT
    id,
    passage,
    -(vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL vector search'), 'documents_bm25_idx')) AS bm25_score
FROM documents
ORDER BY vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL vector search'), 'documents_bm25_idx') ASC
LIMIT 10;

Why to_bm25query Requires the Index Name

Unlike vector distance operators (where the distance between two vectors depends only on the two vectors themselves), the BM25 score of a document depends on corpus-wide statistics stored inside the BM25 index:

• Total number of documents in the corpus ($N$)
• Average document length across the corpus ($\text{avgdl}$)
• Document frequency of each query token ($n(q_i)$)

Passing the index ('documents_embedding_bm25'::regclass) into to_bm25query(...) allows the standalone scoring function (search_bm25query / _bm25_evaluate) to read corpus statistics from the index even when PostgreSQL evaluates <&> in the SELECT projection list or during a brute-force sequential scan.

Understanding Negative BM25 Scores

By design, <&> returns negative BM25 scores:

• PostgreSQL’s index access method API (amcanorderbyop) only supports ORDER BY <operator> ASC for index-ordered scans.
• Because higher BM25 scores mean better relevance, returning $-\text{BM25}(D, Q)$ places the most relevant documents first when sorted in default ascending (ASC) order.
• Never use ORDER BY embedding <&> to_bm25query(...) DESC—PostgreSQL cannot use the forward bm25 index scan for DESC ordering and will either fall back to a full scan or raise a scan direction error.

Tuning bm25_limit vs SQL LIMIT

Because Block-WeakAnd maintains a bounded Top-K min-heap during the index scan to aggressively prune non-competitive posting blocks, the index scanner needs to know $K$ (the maximum number of candidates to collect) when the scan begins.

In v0.1.0 – v0.3.0: bm25_catalog.bm25_limit

The GUC bm25_catalog.bm25_limit (default 100, range -1 to 65535) controls the maximum number of Top-K documents returned by the bm25 index scan:

-- Default is 100. Increase it if your SQL query requests LIMIT 500:
SET bm25_catalog.bm25_limit = 500;

SELECT id, passage
FROM documents
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('database', 'my_tokenizer'))
LIMIT 500;

Special values of bm25_catalog.bm25_limit:

In main: bm25.limit and Index limit Reloption

In the main branch, the effective limit is determined in priority order:

1. Session GUC bm25.limit (if > 0)
2. Index storage parameter WITH (limit = ...) (if > 0)

SET bm25.limit = 100;

Verifying the Query Plan with EXPLAIN

Use EXPLAIN (ANALYZE, BUFFERS) to confirm that PostgreSQL is using Index Scan using ... on documents:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM documents
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL', 'my_tokenizer'))
LIMIT 10;

To force a sequential scan + exact brute-force evaluation (useful for verifying recall or debugging), disable the index scan temporarily:

-- v0.1 – v0.3
SET bm25_catalog.enable_index = false;

-- main branch
SET bm25.enable_scan = false;

2.3 - Filtered Search & Prefiltering

Real-world applications frequently combine full-text BM25 ranking with structured relational filters (such as category_id = 5, tenant_id = 'acme', or date ranges). vchord_bm25 supports three strategies for filtered BM25 search.

1. Post-Filtering vs. Pre-Filtering

When you execute a query with a WHERE clause and ORDER BY embedding <&> to_bm25query(...) LIMIT K:

SELECT id, passage
FROM documents
WHERE category = 'database'
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL BM25', 'my_tokenizer'))
LIMIT 10;

There are two ways vchord_bm25 can evaluate the WHERE predicate:

Post-Filtering (enable_prefilter = false)

1. The bm25 index runs Block-WeakAnd over the entire corpus and returns the global Top-bm25_limit candidates (e.g., Top 100).
2. PostgreSQL fetches those 100 heap tuples and applies WHERE category = 'database'.
3. Caveat: If only 3 of the global Top-100 documents have category = 'database', the query returns only 3 rows even if hundreds of other 'database' documents exist with slightly lower BM25 scores!

Pre-Filtering (enable_prefilter = true)

When pre-filtering is enabled, vchord_bm25 checks heap visibility and evaluates filter acceptability before pushing candidate documents into the Block-WeakAnd Top-K heap:

1. Non-matching documents never enter the Top-K heap and never raise the Block-WeakAnd pruning threshold $\theta$.
2. The index scan continues until it finds $K$ documents that both match the WHERE filter and have the highest BM25 scores among all matching rows.

Enabling Pre-Filtering

In v0.2.2 – v0.3.0:

-- Enabled by default in v0.3.0; can be toggled per session:
SET bm25_catalog.enable_prefilter = true;

In main branch:

-- Enable per session:
SET bm25.prefilter = true;

-- Or enable by default on the index:
ALTER INDEX documents_bm25_idx SET (prefilter = true);

2. Partial BM25 Indexes for High-Selectivity Partition Keys

If you frequently filter by a small set of known partition keys (for example, status = 'published' or specific tenant_id values), you can create partial bm25 indexes:

CREATE INDEX documents_published_bm25
ON documents
USING bm25 (embedding bm25_ops)
WHERE status = 'published';

When querying a partial index, reference the partial index’s name in to_bm25query and include the matching WHERE condition:

SELECT id, passage
FROM documents
WHERE status = 'published'
ORDER BY embedding <&> to_bm25query('documents_published_bm25', tokenize('PostgreSQL BM25', 'my_tokenizer'))
LIMIT 10;

Choosing the Right Filtering Strategy

2.4 - Hybrid Search (BM25 + VectorChord)

Modern retrieval pipelines combine dense vector search (vchord, which captures semantic similarity) with sparse lexical BM25 search (vchord_bm25, which captures exact keyword, identifier, and rare-term matches). Because both extensions run natively inside PostgreSQL, you can execute end-to-end hybrid search in a single SQL query with zero external orchestration.

Schema Setup for Hybrid Search

CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

SET search_path TO "$user", public, bm25_catalog, tokenizer_catalog;

-- 1. Create a BM25 tokenizer
SELECT create_tokenizer('bert', $$
model = "bert_base_uncased"
$$);

-- 2. Create a table storing both dense vectors and sparse bm25vectors
CREATE TABLE chunks (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    dense_emb vector(768),
    bm25_emb bm25vector
);

-- 3. Create VectorChord dense index (vchordrq) and BM25 lexical index (bm25)
CREATE INDEX chunks_dense_idx ON chunks USING vchordrq (dense_emb vector_l2_ops);
CREATE INDEX chunks_bm25_idx  ON chunks USING bm25 (bm25_emb bm25_ops);

Method 1: Reciprocal Rank Fusion (RRF) in Pure SQL

Reciprocal Rank Fusion (RRF) combines rankings from multiple retrieval systems without requiring score calibration between L2/cosine distance and BM25 scores:

where $k$ is a smoothing constant (typically $k = 60$).

WITH dense_results AS (
    SELECT
        id,
        RANK() OVER (ORDER BY dense_emb <-> '[0.012, -0.034, ...]'::vector) AS dense_rank
    FROM chunks
    ORDER BY dense_emb <-> '[0.012, -0.034, ...]'::vector
    LIMIT 100
),
bm25_results AS (
    SELECT
        id,
        RANK() OVER (
            ORDER BY bm25_emb <&> to_bm25query('chunks_bm25_idx', tokenize('PostgreSQL BM25 hybrid search', 'bert'))
        ) AS bm25_rank
    FROM chunks
    ORDER BY bm25_emb <&> to_bm25query('chunks_bm25_idx', tokenize('PostgreSQL BM25 hybrid search', 'bert'))
    LIMIT 100
),
fused AS (
    SELECT
        COALESCE(d.id, b.id) AS id,
        d.dense_rank,
        b.bm25_rank,
        COALESCE(1.0 / (60 + d.dense_rank), 0.0) +
        COALESCE(1.0 / (60 + b.bm25_rank), 0.0) AS rrf_score
    FROM dense_results d
    FULL OUTER JOIN bm25_results b ON d.id = b.id
    ORDER BY rrf_score DESC
    LIMIT 10
)
SELECT
    c.id,
    c.content,
    f.dense_rank,
    f.bm25_rank,
    round(f.rrf_score::numeric, 6) AS rrf_score
FROM fused f
JOIN chunks c ON c.id = f.id
ORDER BY f.rrf_score DESC;

Method 2: Hybrid Retrieval + ColBERT Late-Interaction Reranking

For even higher accuracy, you can retrieve candidate documents using vchordrq (dense) + vchord_bm25 (lexical) and rerank the union of candidates in PostgreSQL using VectorChord’s @# (max_sim) multivector ColBERT operator:

WITH dense_candidates AS (
    SELECT id FROM chunks
    ORDER BY dense_emb <-> $1::vector
    LIMIT 50
),
bm25_candidates AS (
    SELECT id FROM chunks
    ORDER BY bm25_emb <&> to_bm25query('chunks_bm25_idx', tokenize($2, 'bert'))
    LIMIT 50
),
candidate_ids AS (
    SELECT id FROM dense_candidates
    UNION
    SELECT id FROM bm25_candidates
)
SELECT
    c.id,
    c.content,
    -(c.colbert_emb @# $3::vector[]) AS colbert_maxsim_score
FROM candidate_ids u
JOIN chunks c ON c.id = u.id
ORDER BY c.colbert_emb @# $3::vector[] ASC
LIMIT 10;

2.5 - BM25 & Block-WeakAnd Internals

This page details the exact scoring equations and data structures implemented in vchord_bm25 (crates/bm25/src/bm25.rs and src/algorithm/block_wand.rs).

1. Okapi BM25 Scoring Formula

Given a document $D$ and a query $Q = \\{q_1, q_2, \\dots, q_m\\}$ with query term frequencies $c(q_i, Q)$ and document term frequencies $f(q_i, D)$, vchord_bm25 computes:

Inverse Document Frequency ($\text{IDF}$)

where:

• $N$ is the total number of indexed documents in the table.
• $n(q_i)$ is the number of documents containing token $q_i$ (document frequency).

Saturated Term Frequency ($\text{TF}$)

where:

• $f(q_i, D)$ is the term frequency of token $q_i$ in document $D$.
• $\\hat{|D|}$ is the quantized document length (total token count in $D$) decoded from its 1-byte fieldnorm ID.
• $\\text{avgdl}$ is the average document length across all indexed documents.
• $k_1 \\in [1.2, 2.0]$ (default 1.2) controls term frequency saturation.
• $b \\in [0.0, 1.0]$ (default 0.75) controls document length normalization.

2. 1-Byte Fieldnorm Quantization (FIELDNORM_TO_LENGTH)

Storing a 32-bit float or integer length for every document and computing floating-point division for every scored posting would consume excessive memory bandwidth and CPU cycles. Instead, vchord_bm25 quantizes document length $|D|$ into a single byte (u8, 0..=255) using the 256-entry FIELDNORM_TO_LENGTH lookup table:

• Exact for short documents (0 – 40 tokens): Values 0..=40 map 1:1 to exact token counts.
• Logarithmically stepped for longer documents (41 – 255): Steps widen progressively (e.g., 42, 44, 46, ... up to 2,013,265,944 tokens).
• 256-Entry Precomputed Score Cache: Because fieldnorm has only 256 possible values, vchord_bm25 precomputes the denominator term for all 256 possible document lengths once per query term:

Scoring a candidate posting during scan execution reduces to a single table lookup and one division:

3. Posting List Compression & Block-WeakAnd (Block-Max WAND)

Fixed 128-Document Blocks with Delta-Bitpacking

For each term in a sealed segment, the inverted list of (doc_id, term_frequency) pairs is partitioned into fixed blocks of 128 documents and compressed with Delta-Bitpacking:

1. Document IDs within a block are sorted in ascending order and stored as deltas ($\Delta_i = \text{doc}_i - \text{doc}_{i-1}$), bitpacked using the minimum bit width required by the largest delta in the block.
2. Each block header stores the last document ID in the block (for fast shallow seeking) and the Block-Max TF / Fieldnorm pair $(\text{tf}{\max}, \text{fn}{\min})$ that achieves the highest possible BM25 contribution inside that 128-document block.

Block-WeakAnd Top-K Pruning Loop

During an ORDER BY vec <&> to_bm25query(...) LIMIT K scan:

flowchart TD
    A["Sort query term cursors by current doc_id"] --> B["Accumulate upper-bound max_score across cursors\\nuntil sum > current Top-K threshold θ (Pivot Document)"]
    B --> C{"Sum of Block-Max scores\\nat Pivot > θ ?"}
    C -- "No (Prune Block!)" --> D["Skip entire 128-doc block\\nwithout decoding postings"]
    C -- "Yes" --> E{"All leading cursors\\naligned at Pivot doc_id?"}
    E -- "No" --> F["Advance leading cursor to Pivot doc_id"]
    E -- "Yes" --> G["Decode block & evaluate exact BM25 score\\nUpdate Top-K min-heap and raise θ"]
    D --> A
    F --> A
    G --> A

1. Pivot Selection: Query term cursors are ordered by their current doc_id. Starting from the first cursor, vchord_bm25 sums each term’s global upper-bound max_score until the cumulative sum exceeds the current $K$-th best score threshold $\theta$ in the Top-K min-heap. The cursor where the sum crosses $\theta$ defines the pivot document.
2. Block-Max Refinement: Without decompressing any posting block, vchord_bm25 shallow-seeks each cursor up to the pivot’s 128-document block and sums the tighter per-block upper bounds (block_max_score).
3. Fast Block Skipping: If the sum of block_max_score values is $\le \theta$, no document in the current block intersection can possibly enter the Top-K results—the scanner immediately jumps past the block without decoding document IDs or frequencies.
4. Exact Evaluation: Only when the block upper bound exceeds $\theta$ and cursors align on pivot_doc does vchord_bm25 decode the block, verify visibility/deletion bitmap (and optional prefilter), compute the exact BM25 score, and update the Top-K heap.

3 - Tokenizers (pg_tokenizer)

pg_tokenizer (maintained by Pigsty at pgsty/pg_tokenizer) is the companion PostgreSQL extension for vchord_bm25 (pgsty/vchord_bm25) that provides full-text analyzers and vocabulary models to convert raw text into integer token ID arrays (INT[]) and sparse bm25vector embeddings.

3.1 - Tokenizer Architecture & Usage

Two-Stage Tokenization Pipeline

A tokenizer in pg_tokenizer consists of up to two stages that transform a raw TEXT string into an INT[] array of integer token IDs:

flowchart LR
    A["Raw Text\\n(TEXT)"] --> B["Stage 1: Text Analyzer\\n1. character_filters\\n2. pre_tokenizer\\n3. token_filters"]
    B --> C["Normalized Tokens\\n(TEXT[])"]
    C --> D["Stage 2: Model\\nVocabulary Mapping / Subword BPE"]
    D --> E["Token ID Array\\n(INT[] → bm25vector)"]

1. Text Analyzer (text_analyzer): Splits raw text into a TEXT[] array of normalized string tokens by applying a sequential pipeline of:
   • Character Filters (character_filters): Transform the raw string before splitting (e.g., to_lowercase, unicode_normalization).
   • Pre-tokenizer (pre_tokenizer): Splits the string into initial substrings (unicode_segmentation, regex, or Chinese jieba).
   • Token Filters (token_filters): Filter or transform individual tokens (skip_non_alphanumeric, stemmer, stopwords, synonym, pg_dict, ngram).
2. Model (model): Maps the string tokens (or raw text, for standalone subword models like llmlingua2, bert_base_uncased, gemma2b, HuggingFace, or lindera) into integer token IDs (INT[]).

Creating and Using a Tokenizer

All configuration in pg_tokenizer uses TOML format passed as a dollar-quoted string ($$ ... $$).

Option 1: Single-Stage Tokenizer (Built-in Model)

SELECT create_tokenizer('llm_tok', $$
model = "llmlingua2"
$$);

SELECT tokenize('PostgreSQL is an object-relational database system.', 'llm_tok');

Option 2: Inline Text Analyzer + Model

You can define the text_analyzer pipeline directly inside create_tokenizer:

SELECT create_tokenizer('wiki_eng', $$
model = "wiki_tocken"

[text_analyzer.character_filters.to_lowercase]
[text_analyzer.pre_tokenizer.unicode_segmentation]
[[text_analyzer.token_filters.stopwords]]
words = "nltk_english"
[[text_analyzer.token_filters.stemmer]]
kind = "english_porter2"
$$);

SELECT tokenize('PostgreSQL is an object-relational database system.', 'wiki_eng');

Option 3: Reusable Named Text Analyzer + Custom Model

You can create a standalone text_analyzer once with create_text_analyzer and reference it across multiple custom models and tokenizers:

-- 1. Create a reusable text analyzer
SELECT create_text_analyzer('english_analyzer', $$
[character_filters.to_lowercase]
[pre_tokenizer.unicode_segmentation]
[[token_filters.skip_non_alphanumeric]]
[[token_filters.stopwords]]
words = "nltk_english"
[[token_filters.stemmer]]
kind = "english_porter2"
$$);

-- Preview how the text analyzer splits text into TEXT[] tokens:
SELECT apply_text_analyzer('PostgreSQL is a powerful database system!', 'english_analyzer');
-- Output: {postgresql,power,databas,system}

Managing Tokenizers

-- List configured tokenizers
SELECT name FROM tokenizer_catalog.tokenizer;

-- Drop a tokenizer
SELECT drop_tokenizer('wiki_eng');

3.2 - Text Analyzers & Filters

A Text Analyzer processes raw text through three sequential stages:

1. Character Filters (character_filters) — Zero or more character-level transformations on the full text string.
2. Pre-tokenizer (pre_tokenizer) — Exactly one splitter that breaks the string into initial tokens.
3. Token Filters (token_filters) — Zero or more token-level transformations, expansions, or filters.

SELECT create_text_analyzer('analyzer1', $$
character_filters = [
    "to_lowercase",
    { unicode_normalization = "nfkd" },
]
pre_tokenizer = "unicode_segmentation"
token_filters = [
    "skip_non_alphanumeric",
    { stopwords = "nltk_english" },
    { stemmer = "english_porter2" },
]
$$);

1. Character Filters (character_filters)

to_lowercase

Converts all characters in the input text to lowercase.

character_filters = ["to_lowercase"]
# or table syntax:
[character_filters.to_lowercase]

unicode_normalization

Applies Unicode Normalization Forms (UAX #15) to standardize composed/decomposed characters, accents, and full-width/half-width variants.

Supported modes:

• "nfc" — Normalization Form C (Canonical Decomposition followed by Canonical Composition)
• "nfd" — Normalization Form D (Canonical Decomposition)
• "nfkc" — Normalization Form KC (Compatibility Decomposition followed by Canonical Composition)
• "nfkd" — Normalization Form KD (Compatibility Decomposition)

[[character_filters]]
unicode_normalization = "nfkd"

2. Pre-Tokenizers (pre_tokenizer)

A text analyzer may specify at most one pre_tokenizer.

unicode_segmentation

Splits text on Unicode word boundaries according to the Unicode Standard Annex #29 (UAX #29) specification (via the Rust unicode-segmentation crate). Ideal for space-delimited and Western languages.

pre_tokenizer = "unicode_segmentation"
# or:
[pre_tokenizer.unicode_segmentation]

regex

Extracts tokens matching a custom regular expression (powered by the Rust regex crate):

[pre_tokenizer]
regex = '''\\w+'''

jieba (Chinese Word Segmentation)

Segments Chinese text into words using the jieba-rs dictionary and Hidden Markov Model (HMM):

[pre_tokenizer.jieba]
mode = "search"
enable_hmm = true

3. Token Filters (token_filters)

Token filters run in the exact order listed in your TOML array.

skip_non_alphanumeric

Drops any token that contains no alphanumeric characters (such as standalone punctuation marks ,, ., ! left behind by word segmentation).

[[token_filters]]
skip_non_alphanumeric = {}
# or in array shorthand:
token_filters = ["skip_non_alphanumeric"]

stemmer (Snowball Stemming — 30 Languages)

Reduces inflected words to their root stem using the Snowball stemming algorithms.

[[token_filters]]
stemmer = "english_porter2"

Supported stemmer values:

stopwords

Removes common stopwords that carry little lexical signal.

• Built-in dictionaries:
  • "lucene_english" — Apache Lucene standard English stopword list
  • "nltk_english" — NLTK standard English stopword list
  • "iso_english" — Stopwords ISO English stopword list
• Custom stopword dictionary: Create your own with create_stopwords(name, text) (one word per line) and reference it by name.

-- Create a custom stopword dictionary (one word per line)
SELECT create_stopwords('my_stopwords', $$
it
is
a
an
the
$$);

SELECT create_text_analyzer('text_analyzer1', $$
pre_tokenizer = "unicode_segmentation"
[[token_filters]]
stopwords = "my_stopwords"
$$);

SELECT apply_text_analyzer('it is a cat', 'text_analyzer1');
-- Output: {cat}

synonym

Normalizes synonymous terms to a single canonical token. Create a synonym dictionary with create_synonym(name, text) where each line contains space-separated synonyms (all words on a line are replaced by the first word on that line):

SELECT create_synonym('my_synonym', $$
postgres pgsql postgresql
index indices
$$);

SELECT create_text_analyzer('text_analyzer2', $$
pre_tokenizer = "unicode_segmentation"
[[token_filters]]
synonym = "my_synonym"
$$);

SELECT apply_text_analyzer('pgsql and postgresql have many indices', 'text_analyzer2');
-- Output: {postgres,and,postgres,have,many,index}

pg_dict (PostgreSQL Full-Text Dictionaries)

Delegates token filtering to any existing PostgreSQL Full-Text Search Dictionary (pg_ts_dict, including Simple, Ispell, Snowball, and Synonym dictionaries; note that Thesaurus dictionaries are not supported because they operate on multi-word phrases rather than single tokens):

[[token_filters]]
pg_dict = "english_stem"

ngram

Splits each token into character n-grams (useful for substring, prefix, CJK without dictionaries, or code/identifier search):

[[token_filters.ngram]]
min_gram = 2
max_gram = 3
preserve_original = true

3.3 - Tokenization Models

In pg_tokenizer, a Model maps string tokens (or raw text) to integer token IDs (INT[]). You can choose from four built-in pretrained models, train a custom vocabulary model directly on your PostgreSQL table, import any HuggingFace tokenizer.json, or use a Lindera morphological model.

1. Built-in Pretrained Models

Example: Using bert_base_uncased or llmlingua2

SELECT create_tokenizer('bert_tok', $$
model = "bert_base_uncased"
$$);

SELECT tokenize('VectorChord-BM25 brings Block-WeakAnd to PostgreSQL.', 'bert_tok');

2. Custom Corpus Models (create_custom_model)

For maximum BM25 accuracy and minimal memory footprint, you can build a custom vocabulary model directly from your own table column. Unlike subword LLM tokenizers (which split rare domain words into multiple sub-tokens), a custom model assigns a dedicated 1:1 integer ID to every unique normalized token produced by your text_analyzer—and weighs only a few megabytes in memory.

Option A: One-Step Helper (create_custom_model_tokenizer_and_trigger)

This convenience function creates the custom model, creates the tokenizer, populates the target bm25vector column for all existing rows, and installs a BEFORE INSERT OR UPDATE trigger that automatically learns new vocabulary tokens and updates the bm25vector column when rows change:

-- 1. Define how text should be analyzed
SELECT create_text_analyzer('my_analyzer', $$
character_filters = ["to_lowercase"]
pre_tokenizer = "unicode_segmentation"
token_filters = [
    "skip_non_alphanumeric",
    { stopwords = "nltk_english" },
    { stemmer = "english_porter2" },
]
$$);

-- 2. Create model, tokenizer, backfill embeddings, and attach trigger
SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name     => 'my_tokenizer',
    model_name         => 'my_model',
    text_analyzer_name => 'my_analyzer',
    table_name         => 'documents',
    source_column      => 'passage',
    target_column      => 'embedding'
);

Option B: Step-by-Step (create_custom_model Without Trigger)

If you prefer not to attach an automatic trigger (or want to control when embedding is populated):

-- 1. Build the custom model from the existing table column
SELECT create_custom_model('my_model', $$
table = "documents"
column = "passage"
text_analyzer = "my_analyzer"
$$);

-- 2. Create the tokenizer referencing the analyzer and custom model
SELECT create_tokenizer('my_tokenizer', $$
text_analyzer = "my_analyzer"
model = "my_model"
$$);

-- 3. Populate or refresh embeddings manually
UPDATE documents SET embedding = tokenize(passage, 'my_tokenizer');

3. Importing HuggingFace Tokenizers (create_huggingface_model)

You can import any HuggingFace tokenizers JSON specification (tokenizer.json) into PostgreSQL using create_huggingface_model.

In psql, you can fetch tokenizer.json directly from HuggingFace Hub into a variable using \set and pass it to create_huggingface_model:

\set content `wget -q -O - https://huggingface.co/google-bert/bert-base-uncased/resolve/main/tokenizer.json`
SELECT create_huggingface_model('bert_import', :'content');

SELECT create_tokenizer('bert_import_tok', $$
model = "bert_import"
$$);

Or pass the contents of a tokenizer.json file directly as a dollar-quoted string:

SELECT create_huggingface_model('bge_m3_model', $$
{
  "version": "1.0",
  ...
}
$$);

SELECT create_tokenizer('bge_m3_tok', $$
model = "bge_m3_model"
$$);

4. Lindera Morphological Models (create_lindera_model)

For East Asian languages such as Japanese, Korean, and Chinese that benefit from morphological dictionary analysis, pg_tokenizer integrates lindera.

cargo pgrx install --release --features "lindera-ipadic lindera-ipadic-neologd lindera-unidic lindera-ko-dic lindera-cc-cedict"

Example configuration using ipadic with iteration mark normalization, NFKC Unicode normalization, character mapping, compound word merging, number normalization, Japanese part-of-speech stop-tags, Katakana stemming, and diacritical mark removal:

SELECT create_lindera_model('japanese_ipadic', $$
[segmenter]
mode = "normal"
  [segmenter.dictionary]
  kind = "ipadic"

[[character_filters]]
kind = "japanese_iteration_mark"
  [character_filters.args]
  normalize_kanji = true
  normalize_kana = true

[[character_filters]]
kind = "unicode_normalize"
  [character_filters.args]
  kind = "nfkc"

[[character_filters]]
kind = "mapping"
  [character_filters.args.mapping]
  "リンデラ" = "Lindera"

[[token_filters]]
kind = "japanese_compound_word"
  [token_filters.args]
  kind = "ipadic"
  tags = [
    "名詞,数",
    "名詞,接尾,助数詞",
  ]
  new_tag = "名詞,数"

[[token_filters]]
kind = "japanese_number"
  [token_filters.args]
  tags = ["名詞,数"]

[[token_filters]]
kind = "japanese_stop_tags"
  [token_filters.args]
  tags = [
    "接続詞",
    "助詞",
    "助詞,格助詞",
    "助詞,格助詞,一般",
    "助詞,格助詞,引用",
    "助詞,格助詞,連語",
    "助詞,係助詞",
    "助詞,副助詞",
    "助詞,間投助詞",
    "助詞,並立助詞",
    "助詞,終助詞",
    "助詞,副助詞／並立助詞／終助詞",
    "助詞,連体化",
    "助詞,副詞化",
    "助詞,特殊",
    "助動詞",
    "記号",
    "記号,一般",
    "記号,読点",
    "記号,句点",
    "記号,空白",
    "記号,括弧閉",
    "その他,間投",
    "フィラー",
    "非言語音",
  ]

[[token_filters]]
kind = "japanese_katakana_stem"
  [token_filters.args]
  min = 3

[[token_filters]]
kind = "remove_diacritical_mark"
  [token_filters.args]
  japanese = false
$$);

SELECT create_tokenizer('jp_tok', $$
model = "japanese_ipadic"
$$);

5. Model Preloading (add_preload_model)

Large models (llmlingua2 at ~200 MB, gemma2b at ~100 MB, japanese_ipadic) take several seconds and significant RAM to load on first query in each new PostgreSQL backend process. By preloading models during shared_preload_libraries startup, PostgreSQL loads the model once in the postmaster process and shares it across all backend connections via copy-on-write memory (llmlingua2 is preloaded by default):

-- Add a model to the preload list (persisted in $PGDATA/pg_tokenizer/preload_models)
SELECT add_preload_model('my_model');

-- List currently configured preload models (default: {llmlingua2})
SELECT list_preload_models();

-- Remove a model from the preload list (e.g., to save ~200 MB if llmlingua2 is unused)
SELECT remove_preload_model('llmlingua2');

Restart PostgreSQL after calling add_preload_model(...) (with shared_preload_libraries = 'pg_tokenizer.so' set in postgresql.conf) for preloading to take effect across all backends.

3.4 - Multilingual Recipes & Limitations

This page provides production-ready tokenization recipes for English, Chinese, and Japanese full-text BM25 search, followed by important operational limitations to keep in mind.

Recipe 1: English with Stemming & Stopwords (wiki_tocken)

Combines Unicode word segmentation, NLTK stopwords removal, and Porter2 stemming with the built-in Wikipedia vocabulary (wiki_tocken):

SELECT create_tokenizer('eng_wiki', $$
model = "wiki_tocken"

[text_analyzer.character_filters.to_lowercase]
[text_analyzer.pre_tokenizer.unicode_segmentation]
[[text_analyzer.token_filters.skip_non_alphanumeric]]
[[text_analyzer.token_filters.stopwords]]
words = "nltk_english"
[[text_analyzer.token_filters.stemmer]]
kind = "english_porter2"
$$);

SELECT tokenize('PostgreSQL databases are running analytical and vector workloads.', 'eng_wiki');

Recipe 2: English Custom Corpus Model with Trigger

From the upstream pg_tokenizer examples, this workflow builds a domain-specific English vocabulary from your documents table and keeps embeddings updated automatically via trigger:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    passage TEXT,
    embedding bm25vector
);

SELECT create_text_analyzer('text_analyzer1', $$
pre_tokenizer = "unicode_segmentation"
[[character_filters]]
to_lowercase = {}
[[character_filters]]
unicode_normalization = "nfkd"
[[token_filters]]
skip_non_alphanumeric = {}
[[token_filters]]
stopwords = "nltk_english"
[[token_filters]]
stemmer = "english_porter2"
$$);

SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name => 'tokenizer1',
    model_name => 'model1',
    text_analyzer_name => 'text_analyzer1',
    table_name => 'documents',
    source_column => 'passage',
    target_column => 'embedding'
);

INSERT INTO documents (passage) VALUES
('PostgreSQL is a powerful, open-source object-relational database system. It has over 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, feature robustness, and performance.'),
('Full-text search is a technique for searching a computer-stored document or a collection in a full-text database. Full-text search is distinguished from searches based on metadata or on parts of the original texts represented in databases (such as titles, abstracts, selected sections, or bibliographical references).'),
('BM25 is a ranking function used by search engines to estimate the relevance of documents to a given search query. It is based on the probabilistic retrieval framework developed in the 1970s and 1980s by Stephen E. Robertson, Karen Spärck Jones, and others.'),
('PostgreSQL supports advanced data types and performance optimization features, only available in the expensive commercial databases, such as Oracle and SQL Server. It is known for its resilience, integrity, and correctness.'),
('The BM25 algorithm computes a score for each document in a collection based on the query terms appearing in the document, regardless of their proximity within the document. It is a bag-of-words retrieval function that ranks a set of documents based on the query terms appearing in each document.'),
('PostgreSQL has a wide range of extensions and tools that make it suitable for various use cases, from small single-machine applications to large internet-facing applications with many concurrent users.'),
('Full-text search in PostgreSQL allows users to perform complex queries on text data, including phrase searching, ranking of results, and support for multiple languages through configurations and dictionaries.'),
('VectorChord-BM25 is a postgresql extension designed for bm25 ranking algorithm. Block-WeakAnd algorithm is implemented in it to accelerate the ranking process.'),
('pg_tokenizer is a postgresql extension designed for full-text search. It provides a set of functions to preprocess text data and generate tokens for indexing and searching.'),
('Look for organizations which use PostgreSQL for mission-critical or scaled applications. Look for organizations that have used PostgreSQL for a significant length of time.');

CREATE INDEX documents_passage_bm25 ON documents USING bm25 (embedding bm25_ops);

SELECT id, passage, embedding <&> to_bm25query('documents_passage_bm25', tokenize('PostgreSQL BM25', 'tokenizer1')) AS rank
FROM documents
ORDER BY rank
LIMIT 10;

Recipe 3: Chinese Full-Text Search (jieba + Custom Model)

For Chinese text, combine the jieba pre-tokenizer (mode = "search", enable_hmm = true) with a custom corpus model trained on your table (demonstrated below using passages from Qian Zhongshu’s Fortress Besieged / 《围城》):

CREATE TABLE zh_docs (
    id SERIAL PRIMARY KEY,
    passage TEXT,
    embedding bm25vector
);

-- 1. Create Chinese text analyzer with NFKD normalization and Jieba search mode
SELECT create_text_analyzer('text_analyzer1', $$
[character_filters.to_lowercase]
[character_filters.unicode_normalization]
kind = "nfkd"
[pre_tokenizer.jieba]
mode = "search"
enable_hmm = true
[[token_filters.skip_non_alphanumeric]]
$$);

-- 2. Create custom vocabulary model, tokenizer, and auto-update trigger
SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name     => 'tokenizer1',
    model_name         => 'model1',
    text_analyzer_name => 'text_analyzer1',
    table_name         => 'zh_docs',
    source_column      => 'passage',
    target_column      => 'embedding'
);

-- 3. Insert Chinese corpus (trigger automatically populates embedding)
INSERT INTO zh_docs (passage) VALUES
('红海早过了，船在印度洋面上开驶着，但是太阳依然不饶人地迟落早起，侵占去大部分的夜。'),
('夜仿佛纸浸了油，变成半透明体；它给太阳拥抱住了，分不出身来，也许是给太阳陶醉了，所以夕照霞隐褪后的夜色也带着酡红。'),
('到红消醉醒，船舱里的睡人也一身腻汗地醒来，洗了澡赶到甲板上吹海风，又是一天开始。'),
('这是七月下旬，合中国旧历的三伏，一年最热的时候。在中国热得更比常年利害，事后大家都说是兵戈之象，因为这就是民国二十六年【一九三七年】。'),
('这条法国邮船白康蒂号（VicomtedeBragelonne）正开向中国来。早晨八点多钟，冲洗过的三等舱甲板湿意未干，但已坐立满了人，法国人、德国流亡出来的犹太人、印度人、安南人，不用说还有中国人。'),
('海风里早含着燥热，胖人身体给风吹干了，蒙上一层汗结的盐霜，仿佛刚在巴勒斯坦的死海里洗过澡。'),
('毕竟是清晨，人的兴致还不没给太阳晒萎，烘懒，说话做事都很起劲。那几个新派到安南或中国租界当警察的法国人，正围了那年轻善撒娇的犹太女人在调情。'),
('俾斯麦曾说过，法国公使大使的特点，就是一句外国话不会讲；这几位警察并不懂德文，居然传情达意，引得犹太女人格格地笑，比他们的外交官强多了。'),
('这女人的漂亮丈夫，在旁顾而乐之，因为几天来，香烟、啤酒、柠檬水全靠这几个警察孝敬。船上买东西用法国钱，这犹太人只有一点儿沿着那位丹麦王子汉姆烈德（Hamlet）路线逃出来的德国马克。'),
('当时德国跟日本虽在谈恋爱，尚未结婚，到中国来的犹太人还是德国公民，有他们本国的保护。这船上的犹太人有过路到香港的，有到上海居留的，聚在一起在讨论生计，声音活像小洋磁盆里托的石子。'),
('几个年轻的安南人比手画脚，像猴子跳电线一样迅捷地交换着法语，华侨在旁羡慕地听着。这船上都是回家乡的中国人，像报纸上的标题，电影的字幕，标语，籍贯姓名年纪一张表上那几格，他们老早填满了，此番回来，不过把这些公式平滑无波地重演一次。'),
('只有鼻梁上架着一副黑眼镜的苏小姐，心算着她离国这几年，家里有什么变化，她父亲是否老了，她母亲新添了什么病痛，她弟弟结婚了没有。'),
('苏小姐在里昂研究法国文学，做了一篇《中国十八世纪的法国文学里的中国》的论文，刚得了博士。'),
('方鸿渐也到甲板上来了，在苏小姐身旁走过，向她脱帽招呼。苏小姐一壁应着，一壁想他真算厚脸皮，昨天向自己借了照相机，今天见了面，提都不提。');

-- 4. Create BM25 index and search
CREATE INDEX zh_docs_passage_bm25 ON zh_docs USING bm25 (embedding bm25_ops);

SELECT id, passage,
       embedding <&> to_bm25query('zh_docs_passage_bm25', tokenize('苏小姐 法国', 'tokenizer1')) AS rank
FROM zh_docs
ORDER BY rank
LIMIT 10;