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)
french	-	Snowball French stemming plus a French stopword list
ngram	-	Lowercased character bigrams, never spanning whitespace or punctuation — 数据库 indexes 数据 and 据库
ngram3	trigram	Lowercased character trigrams, never spanning whitespace or punctuation
unigram	-	Lowercased single characters

SQL

SELECT
  to_tsvector('french', 'les chats mangeaient les souris') AS french,
  to_tsvector('ngram',  'database')                        AS ngram;

▶ Run

This returns 'chat':2 'mang':3 'sour':5 for french — the stopword les is dropped and the other words are stemmed — and 'ab':4 'as':6 'at':2 'ba':5 'da':1 'se':7 'ta':3 for ngram.

The default configuration is simple (SHOW default_text_search_config). Configuration names are matched exactly as written. Any other name — german or spanish, a differently-cased name such as English, or a schema-qualified name such as pg_catalog.english — fails with ERROR: internal error (XX000) in to_tsvector, to_tsquery, plainto_tsquery, phraseto_tsquery, websearch_to_tsquery and ts_headline. The error does not name the configuration, so check the configuration argument first when you see it.

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 a GIN index when the @@ operator matches the indexed column or expression and the right-hand side is a to_tsquery / plainto_tsquery / websearch_to_tsquery call whose arguments are inlined literals. Two forms fall back to a Seq Scan over every row:

• a driver-bound parameter (@@ websearch_to_tsquery('english', $1)), which is how most ORMs issue the query;
• a tsquery literal — 'cat & dog'::tsquery, CAST('cat & dog' AS tsquery) and tsquery 'cat & dog' are not recognised as probes, even though they are inlined.

So holding a ready-made tsquery string and casting it is the one spelling that looks most direct and is never indexed. Results are identical either way, so nothing signals the fallback — check EXPLAIN ANALYZE before assuming the index is in play.

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 multi-character Chinese terms, except under unigram

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

What decides it is the length of the matched Chinese term, not the tokenizer: a multi-character term such as 支持 or 数据库 is not highlighted under jieba, simple, or any other word-level configuration, while a single-character term such as 数 is. 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; multi-character Chinese terms are not highlighted by either.

unigram is the exception because it splits the query into single characters ('数' & '据' & '库') and marks each one separately:

SQL

SELECT ts_headline('unigram', '数据库支持中文全文检索', plainto_tsquery('unigram', '数据库'),
         'StartSel=<b>, StopSel=</b>') AS unigram_cjk;

▶ Run

This returns <b>数</b><b>据</b><b>库</b>支持中文全文检索. Because it matches single characters, unigram also marks those characters wherever else they appear in the text, not only inside the matched term. It splits English query words into letters too, so on mixed-language text it marks every word sharing a letter with the query — 'database engine' searched for engine returns <b>database</b> <b>engine</b>. Use it for highlighting Chinese text if per-character markup is acceptable; otherwise highlight Chinese results in your application layer.

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. 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	jieba, chinese_ngram, simple, english, english_stem, french, ngram, ngram3, unigram, plus the aliases chinese, zhparser, zhparser_ngram, trigram
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 — every built-in extension
• Limits and Quotas — All operational limits