Full-Text Search

DB9 supports full-text search with language-specific tokenizers.

Available Tokenizers

Tokenizer	Aliases	Description
jieba	chinese, zhparser	Chinese word segmentation (jieba-rs)
chinese_ngram	zhparser_ngram	Chinese + bigram overlay for multi-char words
simple	-	Whitespace tokenizer for English/Latin. No stemming, no stopword removal
english	-	Whitespace tokenizer plus a minimal stopword list (a, an, is, the). No stemming
english_stem	-	Snowball stemming plus the full PostgreSQL English stopword list (~174 words)

The default configuration is simple (SHOW default_text_search_config). Any other configuration name — including french, german, and spanish — raises unknown text search configuration.

english does not stem — use english_stem for PostgreSQL-compatible behavior

In PostgreSQL, to_tsvector('english', ...) applies Snowball stemming. In DB9 the english configuration only strips a minimal stopword list; it does not stem. A query for running therefore will not match a document containing runs.

Use english_stem when you want PostgreSQL-compatible English search:

SQL

-- english: no stemming — 'running' does not match 'runs'
SELECT to_tsvector('english', 'he runs fast') @@ plainto_tsquery('english', 'running') AS english_match;

-- english_stem: stems both sides — 'running' matches 'runs'
SELECT to_tsvector('english_stem', 'he runs fast') @@ plainto_tsquery('english_stem', 'running') AS english_stem_match;

▶ Run

Stemming is algorithmic (Snowball), not dictionary-based, so irregular forms are not folded together — runs and running both stem to run, but ran stems to ran and will not match them.

Compare how each configuration tokenizes the same input:

SQL

SELECT
  to_tsvector('simple',       'the a is database running') AS simple,
  to_tsvector('english',      'the a is database running') AS english,
  to_tsvector('english_stem', 'the a is database running') AS english_stem;

▶ Run

GIN Indexes for Full-Text Search

GIN (Generalized Inverted Index) indexes are the standard way to accelerate full-text search queries. Create a GIN index on a tsvector column or expression to avoid sequential scans:

SQL

-- Index on a tsvector column
CREATE INDEX idx_tsv ON documents USING GIN (tsv);

-- Index on an expression
CREATE INDEX idx_content_fts ON documents
USING GIN (to_tsvector('simple', content));

The optimizer uses GIN indexes automatically when the @@ operator matches an indexed expression.

Chinese Text Search (jieba)

SQL

-- Create index
CREATE INDEX idx_content_fts ON documents
USING gin(to_tsvector('jieba', content));

-- Search
SELECT * FROM documents
WHERE to_tsvector('jieba', content) @@ plainto_tsquery('jieba', '关键词');

English Text Search

Use simple for exact-token matching, or english_stem when you want stemming and stopword removal (the closest match to PostgreSQL’s english configuration):

SQL

-- Exact tokens (no stemming)
CREATE INDEX idx_content_fts ON documents
USING gin(to_tsvector('simple', content));

SELECT * FROM documents
WHERE to_tsvector('simple', content) @@ to_tsquery('simple', 'keyword');

-- Stemmed search: a query for 'running' also matches 'runs'
CREATE INDEX idx_content_stem ON documents
USING gin(to_tsvector('english_stem', content));

SELECT * FROM documents
WHERE to_tsvector('english_stem', content) @@ plainto_tsquery('english_stem', 'running');

Use the same configuration on both sides of @@ and in the index expression. The configurations tokenize differently, so mixing them (for example indexing with simple but querying with english_stem) changes which rows match.

Ranking Results

SQL

SELECT
  content,
  ts_rank(to_tsvector('jieba', content), plainto_tsquery('jieba', '搜索词')) as rank
FROM (VALUES
        ('全文搜索词法分析很重要'),
        ('数据库支持中文检索'),
        ('这段文字没有关键内容')
     ) AS d(content)
WHERE to_tsvector('jieba', content) @@ plainto_tsquery('jieba', '搜索词')
ORDER BY rank DESC
LIMIT 10;

▶ Run

Query Types

Function	Description	Example
plainto_tsquery	Simple phrase	plainto_tsquery('jieba', '人工智能')
to_tsquery	Boolean operators	to_tsquery('simple', 'cat & dog')
phraseto_tsquery	Exact phrase	phraseto_tsquery('simple', 'hello world')
websearch_to_tsquery	Google-style	websearch_to_tsquery('simple', '"exact" -exclude')

Boolean Search

SQL

-- AND: both terms must match
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'database & performance');

-- OR: either term matches
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'postgres | mysql');

-- NOT: exclude term
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'database & !oracle');

-- Prefix matching
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'data:*');

Prefix matching (:*) is not supported

The :* prefix flag is silently dropped when parsing the query — to_tsquery('simple', 'data:*') renders as 'data' (an exact-match lexeme), not a prefix match. No error is raised, so a query like WHERE tsv @@ to_tsquery('simple', 'data:*') will not match documents containing database unless they also contain the exact lexeme data. Do not rely on prefix matching until this is supported.

Highlight Search Results

SQL

SELECT
  ts_headline('jieba', content, plainto_tsquery('jieba', '数据库'),
    'StartSel=<b>, StopSel=</b>, MaxWords=50'
  ) as highlighted
FROM (VALUES ('数据库支持中文全文检索')) AS d(content)
WHERE to_tsvector('jieba', content) @@ plainto_tsquery('jieba', '数据库');

▶ Run

ts_headline does not highlight Chinese terms — in any configuration

Run the example above and you get 数据库支持中文全文检索 back verbatim: the row matches, but no <b> (or other StartSel/StopSel) markup is inserted.

The deciding factor is the script of the matched term, not the tokenizer. Switching to simple does not help — holding the text constant and varying only the configuration, both fail:

SQL

SELECT ts_headline('simple', content, plainto_tsquery('simple', '支持'),
         'StartSel=<b>, StopSel=</b>') AS simple_cjk,
       to_tsvector('simple', content) @@ plainto_tsquery('simple', '支持') AS did_match
FROM (VALUES ('数据库 支持 中文')) AS d(content);

▶ Run

did_match is true while simple_cjk comes back unhighlighted — the term is found, the markup just is not applied.

Conversely, holding the tokenizer constant at jieba and varying only the text shows jieba highlighting perfectly well:

SQL

SELECT ts_headline('jieba', content, plainto_tsquery('jieba', 'engine'),
         'StartSel=<b>, StopSel=</b>') AS jieba_ascii_term
FROM (VALUES ('数据库 engine 检索')) AS d(content);

▶ Run

This returns 数据库 <b>engine</b> 检索. So jieba is not the problem and simple is not a workaround; Han-script terms are not highlighted by either. Highlight Chinese results in your application layer instead.

Length options only take effect in combination

ts_headline accepts MaxWords, MinWords and MaxFragments without error, but each one on its own does nothing — a 13-word input comes back with all 13 words under MaxWords=5, under MinWords=2, and under MaxFragments=1.

Truncation engages only when MaxFragments is combined with MaxWords:

Options	13-word input returns
MaxWords=5	all 13 words
MinWords=2	all 13 words
MaxFragments=1	all 13 words
MaxWords=5, MaxFragments=1	<b>one</b> two three — 3 words
MaxWords=5, MaxFragments=2	the same 3 words

Note that even then the count is not honoured literally — MaxWords=5 yielded three words, and raising MaxFragments changed nothing. Fragment truncation applies to Chinese text too, so it is not script-specific like the highlighting problem above. If you need a specific length, slice the text yourself rather than tuning these options.

Search with Weights

SQL

-- Prioritize title matches over body
ALTER TABLE articles ADD COLUMN tsv tsvector;
UPDATE articles SET tsv =
  setweight(to_tsvector('jieba', title), 'A') ||
  setweight(to_tsvector('jieba', body), 'B');

-- Search with weighted ranking
WITH q AS (SELECT plainto_tsquery('jieba', '搜索词') AS query)
SELECT a.title, ts_rank(a.tsv, q.query) AS rank
FROM articles a, q
WHERE a.tsv @@ q.query
ORDER BY rank DESC;

Do not put plainto_tsquery() in the FROM clause

PostgreSQL accepts FROM articles, plainto_tsquery('jieba', '搜索词') q, but in DB9 a scalar function in FROM position fails with ERROR: function plainto_tsquery(unknown, unknown) does not exist (42883) — the misleading part being that the function does exist, just not in that call position. Bind it in a CTE as above, or inline it in both ts_rank(...) and the @@ comparison. See call position.

Limits

Limit	Value
Max tsvector value size	1 MB
Max lexemes per tsvector	~264,000
Supported text search configurations	5 (jieba, chinese_ngram, simple, english, english_stem), plus the aliases chinese, zhparser, zhparser_ngram
GIN index build strategy	Sequential (full table scan)

See Limits and Quotas for the complete list.

Next Steps

• RAG with Built-in Embeddings — Combine FTS with vector search for hybrid retrieval
• Vector Search — Embedding and HNSW indexes for semantic search
• Extensions Overview — All 9 built-in extensions
• Limits and Quotas — All operational limits