PolarDB:Full-text search

How full-text search works

Unlike keyword-based searches that match specific fields or tags, full-text search scans the entire content of documents to find relevant information. This makes it well suited for retrieving results from large, unstructured text collections.

A full-text search pipeline consists of four stages:

• Text preprocessing: tokenizes the text, removes stopwords, and applies stemming to improve retrieval accuracy.

• Index construction: builds an inverted index on the preprocessed text to track the position of each term across documents.

• Query processing: parses the query and converts it into a format that can be matched against the index.

• Result ranking: scores and sorts results by relevance so the most relevant documents appear first.

Use cases

• Document management: search internal documents, reports, and contracts to reduce retrieval time.

• Online search: quickly surface relevant results across large web or intranet content collections.

• Academic research: search academic databases and literature collections for relevant papers and data.

• E-commerce: let customers find products by keyword, description, or partial match to improve the shopping experience.

• Social media: search posts, comments, and user-generated content by keyword.

• Legal research: help legal professionals quickly locate relevant cases and statutes.

• Medical records: retrieve patient information and historical records from clinical reports.

• Customer support: surface relevant FAQ articles and support documents in self-service portals.

• Content management: index website or blog content so visitors can find relevant articles quickly.

• Library systems: let readers search book and article catalogs by title, author, or topic.

Features

Tokenization

Before a document can be searched, preprocesses it and stores an index for subsequent queries. Text preprocessing involves three steps:

• Parse the document into tokens — numbers, words, compound words, and email addresses — so each token type can be processed independently.

• Normalize tokens into lexemes by lowercasing and removing suffixes such as s or es. This collapses word variations into a single form, improving search accuracy.

• Store the preprocessed document as an ordered array of lexemes along with positional information. Position data is used for relevance ranking: a document where the query terms cluster together scores higher than one where they are scattered.

tsvector

The tsvector data type in stores a sorted list of lexemes along with their positions in the document. Storing processed text in a tsvector column — rather than computing it at query time — is the recommended approach for production workloads.

For example, add a generated tsvector column to a table and build a generalized inverted index (GIN) index on it so the index updates automatically on every write:

ALTER TABLE documents
ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX documents_search_idx ON documents USING GIN (search_vector);

pg_bigm

is an extension of that enables fuzzy text search using n-grams (character n-tuples). It is well suited for applications with large amounts of text data, such as search engines and content management systems, and is particularly effective for Asian-language text where word boundaries are not marked by spaces.

pg_trgm

pg_trgm is an extension of that indexes text using trigrams — contiguous sequences of three characters. It supports fuzzy matching, similarity search, auto-completion, and spelling correction. Like pg_bigm, pg_trgm supports wildcard queries efficiently.

Chinese text segmentation

PostgreSQL's default text search does not segment Chinese text correctly because Chinese does not use spaces to delimit words. addresses this with two extensions: pg_jieba and Zhparser.

pg_jieba

The extension integrates the Jieba word segmentation library into PolarDB. Jieba is a widely used Chinese text segmentation library that identifies word boundaries in Chinese sentences, enabling full-text search over Chinese text.

Zhparser

Simple Chinese Word Segmentation (SCWS) is an open source Chinese word segmentation engine based on word frequency dictionaries.

is built on SCWS and integrates with PostgreSQL's full-text search. It supports a range of configuration options and custom dictionaries for fine-tuned Chinese segmentation.

Indexing

provides two index types for full-text search: GIN and RUM.

GIN indexing

The generalized inverted index (GIN) is PostgreSQL's built-in index type for full-text search. GIN indexes are optimized for tsvector and tsquery data types, making them fast for complex text queries over large datasets. GIN also supports JSONB and other composite data types.

RUM indexing

is a PostgreSQL extension that provides a RUM index type for relevance-ranked full-text search queries.

Like GIN, RUM is an inverted index. RUM stores additional metadata — such as the positions of terms within a document — alongside the index entries. During a query, RUM uses this stored position data to compute relevance rankings without accessing the original document, making ranked queries faster than GIN.

Query processing

tsquery

tsquery is a data type for expressing full-text search conditions in . Use it together with tsvector and the @@ match operator to run full-text queries.

The database also provides to_tsquery and related functions to convert plain text into tsquery values:

tsquery supports the following operators to build compound conditions:

• @@ — matches a tsvector against a tsquery

• & — AND: both terms must match

• | — OR: either term must match

• ! — NOT: the term must not match

Relevance ranking

ts_rank

ts_rank is a PostgreSQL function that scores how well a tsvector document matches a tsquery. Use the score to sort results so the most relevant documents appear at the top:

SELECT title, ts_rank(search_vector, query) AS rank
FROM documents, to_tsquery('english', 'database & search') query
WHERE search_vector @@ query
ORDER BY rank DESC;