pg_tokenizer Documentation

• 1: Installation
• 2: Tokenizer Architecture & Usage
• 3: Text Analyzers & Filters
• 4: Tokenization Models
• 5: Multilingual Recipes & Limitations
• 6: SQL & TOML Reference

1 - Installation

Linux Packages via pig (Recommended)

On EL 8/9/10, Debian 12/13, and Ubuntu 22.04/24.04 (PostgreSQL 14–18), you can install pre-built native packages from the Pigsty repository (see package matrix on pgext.cloud):

curl -fsSL https://repo.pigsty.io/pig | bash
pig repo add pgsql -u
pig ext add pg_tokenizer

Docker (vchord-suite)

The tensorchord/vchord-suite Docker image comes pre-configured with the Vchord extension suite:

• pg_tokenizer — Standalone multilingual tokenizer (pgsty/pg_tokenizer)
• vchord_bm25 — Native BM25 ranking index (pgsty/vchord_bm25)
• vchord — Scalable, disk-friendly vector search (pgsty/vchord)
• pgvector — Standard PostgreSQL vector data types

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

Connect with psql and enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;

Configuration (shared_preload_libraries & search_path)

When installing via native packages or archives, add pg_tokenizer.so to shared_preload_libraries (required for model preloading) and include tokenizer_catalog in search_path:

psql -U postgres -c 'ALTER SYSTEM SET shared_preload_libraries = "pg_tokenizer.so"'
psql -U postgres -c 'ALTER SYSTEM SET search_path TO "$user", public, tokenizer_catalog'
sudo systemctl restart postgresql.service

Then enable the extension in your target database:

CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;

From Debian Package or ZIP Archive

Pre-built .deb and .zip release archives (GLIBC >= 2.35, x86_64 and aarch64) are available on the pgsty/pg_tokenizer Releases page:

# Debian / Ubuntu
sudo apt install ./postgresql-17-pg-tokenizer_*.deb

From Source (cargo-pgrx)

git clone https://github.com/pgsty/pg_tokenizer.git
cd pg_tokenizer
cargo pgrx install --sudo --release

Upgrading to 0.1.2

The 0.1.2 source changes are not yet a published release. After installing the matching library and SQL files, restart PostgreSQL to load the new preloaded library, then update each database as the extension owner/administrator:

ALTER EXTENSION pg_tokenizer UPDATE TO '0.1.2';

Both 0.1.0 and 0.1.1 have upgrade paths. The upgrade replaces trigger/helper definitions and grants catalog access without rebuilding custom vocabularies or changing existing token IDs. Six-argument helper dependencies remain valid. Standard cargo pgrx install/package and tools/package.sh include the migration scripts. Lindera’s separate dictionary-ID migration still requires re-tokenization as described in the model notes.

Database-owner permissions

Installation and upgrade grant pg_database_owner USAGE/CREATE on tokenizer_catalog, EXECUTE on its functions, SELECT/INSERT/UPDATE/DELETE on catalog and model tables, and USAGE/SELECT on their sequences. Future objects created in that schema by the installing role receive the same table/sequence/function defaults; newly created custom models also explicitly grant access. The grants follow a database ownership change.

A non-superuser database owner can create/drop its own custom models, analyzers, tokenizers, stopwords, and synonyms after an administrator installs the extension. Grants do not transfer table ownership: dropping a model created by a different role still needs that object’s owner or an administrator. Other application roles require explicit grants; the extension does not give PUBLIC schema access or catalog write privileges. Functions continue to execute with the caller’s privileges.

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

Native PostgreSQL full-text search (0.1.2, unreleased)

Version 0.1.2 adds two functions returning the native tsvector type. analyze_to_tsvector uses the text analyzer’s string tokens as lexemes, making it useful for Jieba and custom stemmer/filter pipelines. tokenize_to_tsvector instead uses the model’s token IDs as decimal lexemes, supporting built-in, custom, Hugging Face, and Lindera models. No model decoding or second PostgreSQL text normalization is performed.

SELECT create_text_analyzer('fts_words', $$
pre_tokenizer = "unicode_segmentation"
[[character_filters]]
to_lowercase = {}
$$);
SELECT analyze_to_tsvector('Cat dog cat', 'fts_words');
-- 'cat':1,3 'dog':2

CREATE TABLE fts_documents (id int PRIMARY KEY, passage text, terms tsvector);
INSERT INTO fts_documents VALUES
    (1, 'Cat dog cat', analyze_to_tsvector('Cat dog cat', 'fts_words'));
CREATE INDEX ON fts_documents USING gin (terms);
SELECT id FROM fts_documents WHERE terms @@ 'cat <-> dog'::tsquery;

SELECT create_tokenizer('fts_bert', 'model = "bert_base_uncased"');
SELECT tokenize_to_tsvector('hello hello', 'fts_bert');
-- '7592':1,2

Both functions preserve repeated output tokens and assign positions from the final output sequence (default weight D). Positions are not source-text offsets: filters can remove, expand, or reorder tokens, and model output can contain subwords. Quotes, backslashes, whitespace, and punctuation in lexemes are preserved. Empty tokens are omitted; an empty output produces an empty vector, and NULL input returns NULL.

PostgreSQL’s native limits apply: lexemes must be shorter than 2 KB, each lexeme stores at most 256 positions, and positions above 16,383 are clamped. Long input can therefore lose term-frequency/proximity detail. See PostgreSQL text search limits.

Use the same analyzer/model and lexeme representation for documents and queries. plainto_tsquery('english', ...) does not generate model token IDs or reproduce a custom Chinese analyzer. These functions do not add a custom text search parser or a tsquery builder. They are STABLE because definitions live in catalogs: store/update the resulting vector in a column and index that column with GIN/GiST. When an installed BM25 implementation accepts native tsvector, it can consume these values too; check that implementation’s SQL API before migrating an existing bm25vector index.

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

4 - 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.

For builds using Lindera 6.2, [segmenter] dictionary = "embedded://ipadic" is the native dictionary configuration. Existing [segmenter.dictionary] kind = "ipadic" configurations in the catalog and preload files remain supported, as do legacy dictionary and user-dictionary path tables. External compiled dictionaries must be rebuilt with Lindera 6.

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.

Custom model updates in 0.1.2 (unreleased)

Concurrent writes (0.1.2)

Vocabulary triggers deduplicate and sort new tokens and take a transaction-level advisory lock per model before writing. This serializes writers to the same vocabulary across all rows and statements in a transaction, preventing the opposite token-lock order that caused concurrent batch inserts to deadlock. Ordinary tokenization reads and writes to different models can proceed independently. Keep transactions short; when writing multiple models or locking application rows, use a consistent order and retain normal deadlock/serialization retry handling.

The embedding trigger refreshes its SPI snapshot after the vocabulary trigger, so at READ COMMITTED it can see token IDs committed by a writer it waited for. At REPEATABLE READ or SERIALIZABLE, a conflicting writer can receive SQLSTATE 40001 and must retry the whole transaction. Existing vocabulary IDs are preserved during upgrade; new IDs are internal mappings and are not guaranteed to match a separately created model.

Filtering a corpus (0.1.2)

Set filter in the custom model’s TOML configuration, or pass a seventh filter argument to the convenience helper:

SELECT create_custom_model('tenant_a', $$
table = 'public.documents'
column = 'passage'
text_analyzer = 'text_analyzer1'
filter = "tenant_id = 42 AND published IS TRUE"
$$);

-- Alternative: create the model, tokenizer, and embedding trigger together.
SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name => 'tenant_b_tok', model_name => 'tenant_b',
    text_analyzer_name => 'text_analyzer1', table_name => 'public.documents',
    source_column => 'passage', target_column => 'embedding',
    filter => 'tenant_id = 43 AND published IS TRUE'
);

The predicate uses source column names without NEW. or WHERE, and is evaluated with the caller’s privileges. Only TRUE matches; FALSE and NULL do not. Use a deterministic predicate of ordinary source columns, independent of the embedding target column; system columns such as ctid and volatile predicates are not supported. Provide a trusted SQL expression, with schema-qualified functions if needed; corpus selection does not replace row-level security. Empty or invalid expressions raise an error even for an empty table.

The filter applies to the initial vocabulary scan, embedding backfill, and future inserts/updates. Filtered models observe every UPDATE so changing only a predicate column can bring a row into the corpus. Nonmatching rows are left untouched, including existing embeddings; when a row leaves the corpus, its old embedding and vocabulary entries remain. Apply the same predicate when querying the corpus, or clear excluded embeddings in the application. Vocabulary is append-only and is not pruned on UPDATE/DELETE. A matching row with a NULL source has a NULL embedding.

Omitting filter (or passing SQL NULL to the helper) retains the whole-table behavior. Existing models and six-argument helper calls remain supported. Schema-qualified table names and quoted column names are supported; the stored table name is qualified when the model is created. Qualified names take precedence over a literal name containing a dot; use SQL double quotes (for example, '"corpus.docs"') to select the latter explicitly.

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

Recipe 4: Japanese Full-Text Search (lindera IPADIC)

When upgrading from Lindera 0.42 to 6.2, regenerate stored token arrays and rebuild their indexes because the IPADIC token IDs change. The dictionary configuration below remains supported; see the Lindera migration notes.

For Japanese text, create a Lindera IPADIC morphological model with iteration mark normalization, NFKC normalization, Japanese stop-tag filtering, and Katakana stemming (demonstrated below using passages from Natsume Sōseki’s I Am a Cat / 《吾輩は猫である》):

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

INSERT INTO jp_docs (passage) VALUES
('吾輩は猫である。名前はまだ無い。'),
('どこで生れたかとんと見当がつかぬ。何でも薄暗いじめじめした所でニャーニャー泣いていた事だけは記憶している。'),
('吾輩はここで始めて人間というものを見た。しかもあとで聞くとそれは書生という人間中で一番獰悪な種族であったそうだ。'),
('この書生というのは時々我々を捕えて煮て食うという話である。しかしその当時は何という考もなかったから別段恐しいとも思わなかった。'),
('ただ彼の掌に載せられてスーと持ち上げられた時何だかフワフワした感じがあったばかりである。'),
('掌の上で少し落ちついて書生の顔を見たのがいわゆる人間というものの見始であろう。この時妙なものだと思った感じが今でも残っている。'),
('第一毛をもって装飾されべきはずの顔がつるつるしてまるで薬缶だ。その後猫にもだいぶ逢ったがこんな片輪には一度も出会わした事がない。'),
('のみならず顔の真中があまりに突起している。そうしてその穴の中から時々ぷうぷうと煙を吹く。どうも咽せぽくて実に弱った。'),
('これが人間の飲む煙草というものである事はようやくこの頃知った。'),
('この書生の掌の裏でしばらくはよい心持に坐っておったが、しばらくすると非常な速力で運転し始めた。'),
('書生が動くのか自分だけが動くのか分らないが無暗に眼が廻る。胸が悪くなる。到底助からないと思っていると、どさりと音がして眼から火が出た。'),
('それまでは記憶しているがあとは何の事やらいくら考え出そうとしても分らない。'),
('ふと気が付いて見ると書生はいない。たくさんおった兄弟が一疋も見えぬ。肝心の母親さえ姿を隠してしまった。'),
('その上今までの所とは違って無暗に明るい。眼を明いていられぬくらいだ。果てな何でも容子がおかしいと、のそのそ這い出して見ると非常に痛い。'),
('吾輩は藁の上から急に笹原の中へ棄てられたのである。');

SELECT create_lindera_model('lindera_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('tokenizer1', $$
model = "lindera_ipadic"
$$);

UPDATE jp_docs SET embedding = tokenize(passage, 'tokenizer1');

CREATE INDEX jp_docs_passage_bm25 ON jp_docs USING bm25 (embedding bm25_ops);

SELECT id, passage,
       embedding <&> to_bm25query('jp_docs_passage_bm25', tokenize('人間 吾輩', 'tokenizer1')) AS rank
FROM jp_docs
ORDER BY rank
LIMIT 10;

Operational Notes & Known Limitations

1. Object Name Validation: All names for tokenizers, text analyzers, models, stopword dictionaries, and synonym dictionaries must contain only alphanumeric characters and underscores ([A-Za-z0-9_]), and must not start with a digit.
2. Transaction Rollback & Per-Connection Object Cache: Each PostgreSQL connection caches tokenizer, text_analyzer, model, stopwords, and synonym objects in backend memory (DashMap). Because this in-memory object pool is not tied to PostgreSQL’s transaction rollback callbacks, rolling back a transaction (ROLLBACK) reverts the tokenizer_catalog tables on disk, but the current connection’s in-memory cache still holds the created object. After a ROLLBACK, explicitly call the corresponding drop_* function (or reconnect) to clear the in-memory cache:

   BEGIN;
   SELECT create_text_analyzer('text_analyzer1', $$
   pre_tokenizer = "unicode_segmentation"
   $$);
   ROLLBACK;
   
   -- Clear the stale in-memory cache entry in the current session (or reconnect)
   SELECT drop_text_analyzer('text_analyzer1');
   
   -- Now you can recreate it cleanly
   SELECT create_text_analyzer('text_analyzer1', $$
   pre_tokenizer = "unicode_segmentation"
   $$);

3. Preload Models Across Replicas: add_preload_model writes the preload model list to the local $PGDATA/pg_tokenizer directory on the primary node. Because filesystem files outside PostgreSQL relations are not replicated via WAL, you should run add_preload_model on read replicas independently if you preload models there.
4. Memory Consumption of Built-in LLM Models: Models like llmlingua2 (~200 MB), gemma2b (~100 MB), and lindera consume significant memory. Always use add_preload_model in production so the model is loaded once in the postmaster process rather than duplicated per connection, or prefer a custom corpus model (create_custom_model) which uses only a few megabytes.

6 - SQL & TOML Reference

SQL Functions (tokenizer_catalog)

Text Analyzer & Dictionaries

Tokenization Models & Preloading

Tokenizer Execution

Native full-text search (0.1.2, unreleased)

These functions are STABLE. Store the result in a column for GIN/GiST indexing; use the same lexeme representation for documents and queries. See positions and native limits.

TOML Configuration Options

text_analyzer Options

tokenizer Options

custom_model Options