PostgreSQL Full-Text Search Engine

1. What You'll Learn

Understand the core concepts of full-text search (document, lexeme, dictionary, configuration)
Use the tsvector and tsquery data types
Use to_tsvector/to_tsquery/plainto_tsquery/phraseto_tsquery/websearch_to_tsquery for conversion
Use the @@ operator to perform full-text search
Use ts_rank for ranking and ts_headline for highlighting
Create GIN indexes to accelerate full-text search
Configure multilingual full-text search (english/simple/zhpinyin)
Use ts_stat statistics and system catalog queries

2. The Story

Charlie's e-commerce platform has 500,000 product records, and users need to search products. He started with LIKE '%red shoes%', but searching "red shoes" only returned products whose description happened to contain the exact consecutive string "red shoes", and the query took 3 seconds. After switching to PostgreSQL full-text search:

"red shoes" matches products containing both "red" and "shoes" (in any order)
Stop words are automatically ignored (the/a/an)
Automatic stemming (shoes → shoe)
Accelerated by a GIN index, the query takes only 30 milliseconds—100x faster

3. Concept: Core Concepts of Full-Text Search

(1) Four Core Concepts

Concept	Description	PostgreSQL equivalent
Document	The text content to be searched	`tsvector`
Query	The user's search terms	`tsquery`
Dictionary	Defines how words are processed (stop words, stemming)	`pg_ts_dict` system catalog
Configuration	Combines a parser and dictionaries	`pg_ts_config` system catalog

(2) Full-Text Search Processing Pipeline

flowchart LR
    A[Raw Text] --> B[Parser<br/>Tokenize]
    B --> C[Dictionary<br/>Stemming + Stop words]
    C --> D[tsvector<br/>Sorted lexemes]
    D --> E[GIN Index<br/>Fast lookup]
    F[User Query] --> G[to_tsquery<br/>Parse & normalize]
    G --> H[tsquery<br/>Lexemes + operators]
    E --> I["@@ operator<br/>Match"]
    H --> I
    I --> J[ts_rank<br/>Scoring]
    J --> K[ts_headline<br/>Highlighting]
    K --> L[Results]

▶ Example: See How Text Is Processed

SQL

SELECT * FROM ts_debug('english', 'The red shoes are beautiful');

TEXT

 alias | descriptor | token  |  dictionaries  | dictionary | lexemes 
-------+------------+--------+----------------+------------+---------
 ascii | word       | The    | {english_stem}  | english_stem| {}
 ascii | word       | red    | {english_stem}  | english_stem| {red}
 ascii | word       | shoes  | {english_stem}  | english_stem| {shoe}
 ascii | word       | are    | {english_stem}  | english_stem| {}
 ascii | word       | beautiful | {english_stem}| english_stem| {beauti}

4. Concept: tsvector and tsquery

(1) tsvector—The Preprocessed Result of a Document

tsvector is a sorted, de-duplicated list of lexemes, each with positional information.

▶ Example: Convert Text to tsvector

SQL

SELECT to_tsvector('english', 'The red shoes are beautiful shoes');

TEXT

'beauti':5 'red':2 'shoe':3,6

Step	Input	Output
Tokenize	The red shoes are beautiful shoes	[The, red, shoes, are, beautiful, shoes]
Remove stop words	[The, red, shoes, are, beautiful, shoes]	[red, shoes, beautiful, shoes]
Stem	[red, shoes, beautiful, shoes]	[red, shoe, beauti, shoe]
De-dup + position	[red, shoe, beauti, shoe]	'beauti':5 'red':2 'shoe':3,6

(2) tsquery—The Preprocessed Result of a Query

tsquery is a combination of lexemes and boolean operators.

Operator	Meaning	Example
`&`	AND	`red & shoe`
`\|`	OR	`red \| blue`
`!`	NOT	`!broken`
`<->`	FOLLOWED BY (adjacent)	`red <-> shoe`
`<N>`	Distance N	`red <2> shoe`

▶ Example: Build a tsquery

SQL

SELECT to_tsquery('english', 'red & shoe');
-- 'red' & 'shoe'

SELECT to_tsquery('english', 'red | blue');
-- 'red' | 'blue'

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Concept: Full-Text Search Query Functions

(1) Five Query-Building Functions

Function	PG version	Input format	Description
`to_tsquery(config, text)`	all	`red & shoe`	You write operators manually
`plainto_tsquery(config, text)`	all	`red shoes`	Plain text, auto-joined with &
`phraseto_tsquery(config, text)`	9.6+	`red shoes`	Plain text, auto-joined with <->
`websearch_to_tsquery(config, text)`	11+	`"red shoes" -broken`	Web search engine syntax
`tsvector @@ tsquery`	all	—	Match operator

▶ Example: plainto_tsquery—Plain Text Auto-AND

SQL

SELECT plainto_tsquery('english', 'red shoes');
-- 'red' & 'shoe'  (matches docs with BOTH terms)

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: phraseto_tsquery—Phrase Search

SQL

SELECT phraseto_tsquery('english', 'red shoes');
-- 'red' <-> 'shoe'  (matches docs where 'red' is immediately before 'shoe')

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: websearch_to_tsquery—Search Engine Syntax

SQL

SELECT websearch_to_tsquery('english', '"red shoes" -broken OR new');
-- 'red' <-> 'shoe' & !'broken' | 'new'

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

websearch syntax	Meaning
`"exact phrase"`	Phrase match (<->)
`-word`	Exclude (!)
`word1 OR word2`	OR (\|)
`word1 word2`	AND (&)

▶ Example: Search with the @@ Operator

SQL

CREATE TABLE catalog (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  search_vector tsvector
);

INSERT INTO catalog (name, description, search_vector) VALUES
  ('Red Running Shoes', 'Lightweight red shoes for running', 
   to_tsvector('english', 'Lightweight red shoes for running')),
  ('Blue Casual Shoes', 'Comfortable blue shoes for daily wear',
   to_tsvector('english', 'Comfortable blue shoes for daily wear')),
  ('Red Dress', 'Elegant red dress for special occasions',
   to_tsvector('english', 'Elegant red dress for special occasions'));

-- Search for "red shoes"
SELECT name, description
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

TEXT

INSERT 0 1

6. Concept: Ranking and Highlighting

(1) ts_rank Ranking

ts_rank computes the relevance of a query to a document, returning a float from 0 to 1—the higher the value, the more relevant.

▶ Example: Sort by Relevance

SQL

SELECT name,
       ts_rank(search_vector, plainto_tsquery('english', 'red shoes')) AS rank
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Ranking function	Description	Use case
`ts_rank(vector, query)`	Frequency-based rank	General ranking
`ts_rank_cd(vector, query)`	Cover density rank	More precise ranking for short docs
`ts_rank(vector, query, weights)`	Weighted rank	Title weighted higher than body

▶ Example: Weighted Ranking (title weighted above description)

SQL

ALTER TABLE catalog ADD COLUMN title_vector tsvector;

UPDATE catalog SET title_vector = to_tsvector('english', name);

-- Set weights: title=D(default), body=A(highest)
SELECT name,
       ts_rank(
         setweight(title_vector, 'A') || setweight(search_vector, 'B'),
         plainto_tsquery('english', 'red shoes')
       ) AS rank
FROM catalog
WHERE setweight(title_vector, 'A') || setweight(search_vector, 'B')
      @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

TEXT

UPDATE 3

Weight letter	Default weight	Typical use
A	1.0	Title
B	0.4	Body
C	0.2	Summary
D	0.1	Supplementary info

(2) ts_headline Highlighting

ts_headline marks matched lexemes in the original text.

▶ Example: Highlight Search Results

SQL

SELECT name,
       ts_headline('english', description, plainto_tsquery('english', 'red shoes')) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

TEXT

 name              | highlighted
-------------------+-------------------------------------------------
 Red Running Shoes | Lightweight <b>red</b> <b>shoes</b> for running

▶ Example: Custom Highlight Tags

SQL

SELECT ts_headline(
  'english',
  description,
  plainto_tsquery('english', 'red shoes'),
  'StartSel=<em>,StopSel=</em>,MaxWords=10,MinWords=5'
) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Highlight parameter	Description	Default
`StartSel`	Match start tag	`<b>`
`StopSel`	Match end tag	`</b>`
`MaxWords`	Max words in highlight fragment	35
`MinWords`	Min words in highlight fragment	15
`ShortWord`	Min word length to ignore	3

7. Concept: Accelerating with GIN Indexes

(1) Three GIN Index Approaches

Approach	Syntax	Update overhead	Best for
Column-based	`gin(to_tsvector(config, column))`	Rebuilt on update	Best when column values don't change
Stored-column-based	`gin(search_vector)`	Lower update overhead	High-frequency updates, needs trigger sync
FASTUPDATE	On by default	Deferred merge	Write-heavy, read-heavy scenarios

▶ Example: Function-Based GIN Index

SQL

CREATE INDEX idx_catalog_desc_fts
ON catalog USING gin (to_tsvector('english', description));

-- Query must use same config
SELECT name FROM catalog
WHERE to_tsvector('english', description) @@ plainto_tsquery('english', 'red shoes');

TEXT

CREATE TABLE

▶ Example: GIN Index on a Stored tsvector Column

SQL

CREATE INDEX idx_catalog_search_fts ON catalog USING gin (search_vector);

-- Query uses stored column
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

TEXT

CREATE TABLE

▶ Example: Trigger to Auto-Sync the tsvector Column

SQL

CREATE FUNCTION catalog_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_catalog_search
BEFORE INSERT OR UPDATE OF name, description ON catalog
FOR EACH ROW EXECUTE FUNCTION catalog_search_vector_update();

TEXT

INSERT 0 1

Approach	Query style	Index used	Maintenance cost
Function index	`to_tsvector('english', col) @@ query`	Yes	Zero (automatic)
Stored column + index	`stored_col @@ query`	Yes	Needs trigger sync
No index	`to_tsvector('english', col) @@ query`	Full table scan	Zero

8. Concept: Multilingual Configuration

(1) PostgreSQL Built-in Configurations

Config	Language	Description
`english`	English	Default; supports stemming and stop words
`simple`	No language processing	Tokenize only; no stop words, no stemming
`zhpinyin`	Chinese pinyin	PG extension; Chinese-to-pinyin search

▶ Example: simple Config Doesn't Filter Stop Words

SQL

SELECT to_tsvector('simple', 'The red shoes');
-- 'red':2 'shoes':3 'the':1  (keeps 'the', no stemming)

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Compare Search Behavior Across Configurations

Config	to_tsvector('the red shoes are nice')	Characteristic
english	'nice':5 'red':2 'shoe':3	Drop stop words + stemming
simple	'are':4 'nice':5 'red':2 'shoes':3 'the':1	Lowercase + tokenize only
zhpinyin	Depends on extension implementation	Chinese to pinyin

(2) Chinese Full-Text Search Options

PostgreSQL doesn't ship a native Chinese tokenizer. Common approaches:

Approach	Extension	Tokenization	Install complexity
zhparser	PostgreSQL extension	Based on SimpleChineseSeg	Medium
pg_jieba	PostgreSQL extension	Based on jieba tokenizer	Medium
zhpinyin	PostgreSQL extension	Pinyin search	Low
Application-layer tokenization	None	App generates tsvector	Low

▶ Example: Application-Layer Tokenization (no extension needed)

SQL

-- Pre-segment Chinese text with spaces at application layer
INSERT INTO catalog (name, description, search_vector)
VALUES (
  'Red Running Shoes',
  'light red running shoes for jogging',
  to_tsvector('simple', 'light red running shoes for jogging')
);

-- Search with segmented query
SELECT name FROM catalog
WHERE search_vector @@ to_tsquery('simple', 'red & shoes');

TEXT

INSERT 0 1

▶ Example: Mixed Chinese-English Search

SQL

CREATE TABLE articles (
  article_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  fts_en tsvector,
  fts_zh tsvector
);

-- English config for English content, simple for Chinese
UPDATE articles SET
  fts_en = to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(content, '')),
  fts_zh = to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(content, ''));

-- Search English
SELECT title FROM articles
WHERE fts_en @@ plainto_tsquery('english', 'database optimization');

-- Search Chinese (application-segmented)
SELECT title FROM articles
WHERE fts_zh @@ to_tsquery('simple', 'data & optimization');

TEXT

UPDATE 3

9. Concept: Statistics and System Catalogs

(1) ts_stat for Word Frequency

ts_stat helps analyze the lexeme distribution in a tsvector—commonly used to tune search configuration.

▶ Example: View the Most Common Lexemes

SQL

SELECT word, ndoc, nentry
FROM ts_stat(
  'SELECT search_vector FROM catalog'
)
ORDER BY nentry DESC
LIMIT 10;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Column	Description
`word`	Lexeme
`ndoc`	In how many documents it appears
`nentry`	Total number of occurrences

(2) System Catalogs

▶ Example: View Available Configurations

SQL

SELECT cfgname FROM pg_ts_config;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: View Available Dictionaries

SQL

SELECT dictname, dictnamespace FROM pg_ts_dict;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: LIKE vs Full-Text Search Performance Comparison

SQL

-- Create test data
INSERT INTO catalog (name, description, search_vector)
SELECT
  'Product ' || i,
  'High quality product number ' || i || ' with great features and amazing design',
  to_tsvector('english', 'High quality product number ' || i || ' with great features and amazing design')
FROM generate_series(1, 100000) AS i;

-- LIKE query (slow, no index support for leading wildcard)
EXPLAIN ANALYZE
SELECT name FROM catalog WHERE description LIKE '%great features%';

-- Full-text search with GIN index (fast)
CREATE INDEX idx_catalog_fts ON catalog USING gin (search_vector);
EXPLAIN ANALYZE
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'great features');

TEXT

INSERT 0 1

Query method	Time	Index support	Capability
`LIKE '%keyword%'`	~3000ms	None	Exact substring match
`LIKE 'keyword%'`	~5ms	B-tree	Prefix match
`@@ full-text search + GIN`	~30ms	GIN	Lexeme match + stemming + ranking

10. Hands-on: E-commerce Product Search Engine

Charlie needs to implement a complete product search for the e-commerce platform, including multi-field search, ranking, highlighting, and pagination.

SQL

-- Step 1: Create products table with pre-computed tsvector
CREATE TABLE shop_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  description TEXT,
  price NUMERIC(10,2) NOT NULL,
  search_doc tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(category, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(description, '')), 'C')
  ) STORED
);

-- Step 2: Insert sample data
INSERT INTO shop_products (name, category, description, price) VALUES
  ('Red Running Shoes', 'Shoes', 'Lightweight red running shoes with cushioned sole', 89.99),
  ('Blue Casual Shoes', 'Shoes', 'Comfortable blue shoes for everyday casual wear', 59.99),
  ('Red Leather Dress Shoes', 'Shoes', 'Premium red leather dress shoes for formal events', 149.99),
  ('Red Silk Dress', 'Dresses', 'Elegant red silk dress for special occasions', 199.99),
  ('Running Watch', 'Accessories', 'GPS running watch with heart rate monitor', 249.99),
  ('Running Shorts', 'Clothing', 'Breathable running shorts with pockets', 39.99);

-- Step 3: Create GIN index on generated column
CREATE INDEX idx_shop_products_search ON shop_products USING gin (search_doc);

-- Step 4: Search function with ranking, highlighting and pagination
CREATE FUNCTION search_products(
  p_query TEXT,
  p_limit INT DEFAULT 20,
  p_offset INT DEFAULT 0
) RETURNS TABLE (
  product_id INT,
  name TEXT,
  category TEXT,
  price NUMERIC,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.product_id,
    s.name,
    s.category,
    s.price,
    ts_rank(s.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(s.name, '') || '. ' || COALESCE(s.description, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>,MaxWords=20,MinWords=5'
    ) AS headline
  FROM shop_products s
  WHERE s.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC
  LIMIT p_limit
  OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

-- Step 5: Test search
SELECT * FROM search_products('red shoes');
SELECT * FROM search_products('"running shoes"');
SELECT * FROM search_products('running -shoes');

-- Step 6: Search with category filter
SELECT name, price,
       ts_rank(search_doc, plainto_tsquery('english', 'red')) AS rank
FROM shop_products
WHERE search_doc @@ plainto_tsquery('english', 'red')
  AND category = 'Shoes'
ORDER BY rank DESC;

❓ FAQ

Q What's the essential difference between full-text search and LIKE?

A LIKE is an exact substring match—it doesn't understand semantics. Full-text search tokenizes, removes stop words, and stems, matching by lexeme with boolean combinations and ranking. Full-text search has GIN index support; LIKE with a leading wildcard can't use an index.

Q Which should I use, to_tsquery or plainto_tsquery?

A If users type search terms directly, use plainto_tsquery or websearch_to_tsquery (PG 11+), which auto-convert plain text into a query. to_tsquery needs you to write & | ! operators manually—better for programmatically generated queries.

Q Why must I use the same config for the index and the query?

A Different configs tokenize and stem differently. If the index uses the english config but the query uses simple, the lexemes won't match and search returns nothing. They must stay consistent.

Q Which is better, GENERATED ALWAYS AS ... STORED columns or triggers?

A GENERATED columns (PG 12+) are computed automatically—no trigger needed—and are recommended. If you're on PG below 12 or need to combine tsvectors across tables, use a trigger.

Q What's the position information in tsvector for?

A Position info is used for phrase search (phraseto_tsquery uses the <-> operator to match adjacent lexemes) and proximity search (the `<N>` distance operator). If you don't need phrase search, you can drop positions in to_tsvector.

Q Does a GIN index affect write performance?

A Yes. GIN index updates are slower than B-tree because inverted lists must be merged. PostgreSQL enables FASTUPDATE by default, buffering updates then merging in batches—trading a little query real-time latency for better write performance.

Q How do I do Chinese full-text search?

A PostgreSQL doesn't natively support Chinese tokenization. Recommended: install the zhparser/pg_jieba extension (native tokenizer), or pre-segment text with a tokenizer at the application layer and store it with the simple config in a tsvector. The application-layer approach is simplest—no extension install needed.

📖 Summary

Full-text search converts text into tsvector via tokenization, stop-word removal, and stemming; queries become tsquery
plainto_tsquery suits user input; websearch_to_tsquery (PG 11+) supports search-engine syntax
The @@ operator performs matching; ts_rank ranks; ts_headline highlights
GIN indexes are key to full-text search performance; function indexes and stored-column indexes each have their use
Weights (setweight A/B/C/D) make title matches rank above body
Multilingual uses different configs; Chinese needs the zhparser/pg_jieba extension or application-layer tokenization
ts_stat analyzes word frequency; the pg_ts_config/pg_ts_dict system catalogs list configurations

📝 Exercises

⭐ Create a blog_posts table with title (TEXT) and body (TEXT), insert 3 rows, and use to_tsvector + plainto_tsquery to search for articles containing "database".
⭐⭐ Add a search_vector column (tsvector) to blog_posts, create a GIN index, and write a trigger that auto-updates search_vector on INSERT/UPDATE (title weight A, body weight B). Then search "performance optimization" with ts_rank + ts_headline and sort by rank.
⭐⭐⭐ Design a knowledge_articles table supporting mixed Chinese-English search: English content with the english config, Chinese content with the simple config (application-layer pre-segmentation), with two tsvector columns and corresponding GIN indexes. Write a search function that picks the right column based on the input query's language, highlights results with ts_headline, sorts by ts_rank, and supports pagination.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Full-Text Search Engine

1. What You'll Learn

2. The Story

3. Concept: Core Concepts of Full-Text Search

(1) Four Core Concepts

(2) Full-Text Search Processing Pipeline

▶ Example: See How Text Is Processed

4. Concept: tsvector and tsquery

(1) tsvector—The Preprocessed Result of a Document

▶ Example: Convert Text to tsvector

(2) tsquery—The Preprocessed Result of a Query

▶ Example: Build a tsquery

5. Concept: Full-Text Search Query Functions

(1) Five Query-Building Functions

▶ Example: plainto_tsquery—Plain Text Auto-AND

▶ Example: phraseto_tsquery—Phrase Search

▶ Example: websearch_to_tsquery—Search Engine Syntax

▶ Example: Search with the @@ Operator

6. Concept: Ranking and Highlighting

(1) ts_rank Ranking

▶ Example: Sort by Relevance

▶ Example: Weighted Ranking (title weighted above description)

(2) ts_headline Highlighting

▶ Example: Highlight Search Results

▶ Example: Custom Highlight Tags

7. Concept: Accelerating with GIN Indexes

(1) Three GIN Index Approaches

▶ Example: Function-Based GIN Index

▶ Example: GIN Index on a Stored tsvector Column

▶ Example: Trigger to Auto-Sync the tsvector Column

8. Concept: Multilingual Configuration

(1) PostgreSQL Built-in Configurations

▶ Example: simple Config Doesn't Filter Stop Words

▶ Example: Compare Search Behavior Across Configurations

(2) Chinese Full-Text Search Options

▶ Example: Application-Layer Tokenization (no extension needed)

▶ Example: Mixed Chinese-English Search

9. Concept: Statistics and System Catalogs

(1) ts_stat for Word Frequency

▶ Example: View the Most Common Lexemes

(2) System Catalogs

▶ Example: View Available Configurations

▶ Example: View Available Dictionaries

▶ Example: LIKE vs Full-Text Search Performance Comparison

10. Hands-on: E-commerce Product Search Engine

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好