PostgreSQL Full-Text Search in Practice: Build Effective Search Without ES

Whenever search comes up, many teams instinctively reach for Elasticsearch. Then, just to support one search box, they spin up another JVM cluster, maintain another data synchronization pipeline to feed PG data into ES through Debezium or Logstash, and worry about another set of replica and disk configurations. Search quality barely improves, while operational costs double.

The reality is that most business systems never reach a search scale where ES is essential. Millions of documents, millisecond response times, and basic relevance ranking are all within PostgreSQL's capabilities. This article explains the complete PG full-text search pipeline, from tokenization and indexing to ranking and Chinese-language processing, so you can put it into production directly.

1. When PG Full-Text Search Is Enough

Start by defining the use case clearly so you do not choose the wrong tool.

Scenarios suited to PG:

• The data already lives in PG, and you do not want to build another synchronization pipeline
• Document volume ranges from millions to around ten million, with moderate query QPS
• Search is a supporting feature rather than the core product, such as admin filtering or on-site article search
• You need transactional consistency, so new data is searchable immediately rather than merely "eventually consistent"
• The team does not have the capacity to maintain another ES system

Scenarios where you should use ES:

• Document volume exceeds 100 million and requires distributed sharding
• Search is a core revenue-generating feature that needs complex scoring, aggregation, and recall tuning
• You have a dedicated search team willing to invest in relevance metrics

In short: do not use a cannon to kill a mosquito, but do not try to crack a safe with a toothpick either. PG full-text search occupies an extremely cost-effective middle ground and deserves to be the default option.

2. Three Core Concepts: tsvector, tsquery, and @@

The entire PG full-text search model consists of three things. Once you understand them, everything else is configuration.

• tsvector - A structured representation of a tokenized document: a deduplicated set of lexemes with positions
• tsquery - A query expression supporting & (AND), | (OR), ! (NOT), and <-> (adjacent)
• @@ - The match operator, with a tsvector on the left and a tsquery on the right, returning a Boolean value

Here is a minimal runnable example:

-- Convert text into a tsvector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- -> 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2'

-- Convert user input into a tsquery
SELECT to_tsquery('english', 'fox & dog');
-- -> 'fox' & 'dog'

-- Match
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'fox & cat');
-- -> false

Notice several details: the stop word the is discarded; jumps is reduced to the stem jump; and lazy becomes lazi. This is the "text search configuration" discussed below at work. Full-text search matches lexemes, not raw strings. That is the fundamental difference between it and LIKE: the former understands language, while the latter recognizes only characters.

3. Tokenization and Configuration: The Dictionaries Behind to_tsvector

The first argument to to_tsvector is the configuration name (regconfig). It determines how text is tokenized, which stop words are removed, and how stems are extracted. PG includes configurations for dozens of languages. Common options include:

• simple - Splits on whitespace, converts everything to lowercase, performs no stemming, and removes no stop words. It suits IDs, tags, and other cases that do not require linguistic analysis
• english - English processing with stop-word removal and Porter stemming
• pg_catalog.french / german / ... - Configurations for other languages

-- The same sentence produces dramatically different results under different configurations
SELECT to_tsvector('simple',  'Running runners ran quickly');  -- No stemming
SELECT to_tsvector('english', 'Running runners ran quickly');  -- 'quick':4 'ran':3 'run':1,2

Pitfall: Many people do not realize that the shorthand to_tsvector('body'), which omits the configuration, uses the session parameter default_text_search_config. In production, always specify the configuration explicitly. Otherwise, moving to another machine or changing one parameter can alter search results.

Want custom stop words or dictionaries? Modify the .stop and .syn files under SHAREDIR/tsearch_data/, then run ALTER TEXT SEARCH DICTIONARY. For example, to treat 公司 (company) and 企业 (enterprise) as synonyms so that searching for either matches the other:

-- Contents of the synonym file my_syn.syn:
# One pair of synonyms per line, separated by a space
公司 企业
CREATE TEXT SEARCH DICTIONARY my_syn (TEMPLATE = synonym, SYNONYMS = my_syn);
ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR word WITH my_syn, english_stem;

Be aware of the side effects of stemming: it reduces running/runs/ran to run, improving recall, but it also prevents precise distinctions between tenses. For legal text, contract clauses, and other cases that require exact wording, the simple configuration may be more appropriate. Do not use one configuration for everything.

4. GIN Indexes: The Lifeline of Full-Text Search

Full-text search without an index is disastrous: PG computes a tsvector and matches it row by row, and one million rows can saturate the CPU. A GIN index is a prerequisite for production full-text search.

The most direct form is:

CREATE INDEX idx_articles_body_fts ON articles
  USING gin(to_tsvector('english', body));

Note that the configuration in the index must exactly match the configuration in the query. If both are not 'english', the optimizer will not use this index. This is another reason to pin the configuration explicitly.

A more production-oriented approach uses a generated column to materialize the tsvector. Both the index and queries then reference the same column, avoiding repeated computation and configuration drift:

ALTER TABLE articles
  ADD COLUMN body_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || body)) STORED;

CREATE INDEX idx_articles_body_tsv ON articles USING gin(body_tsv);

-- Query this column directly so the index is used
SELECT id, title FROM articles WHERE body_tsv @@ plainto_tsquery('english', 'fox dog');

Combining the title with the body lets title matches receive greater weight when used with setweight below, while also avoiding a separate index. plainto_tsquery is friendlier than manually writing & because it parses user input as an ordinary phrase.

5. Ranking: ts_rank and ts_rank_cd

@@ only tells you whether something matches, not which result is more relevant. Use ts_rank to sort results:

SELECT id, title,
       ts_rank(body_tsv, plainto_tsquery('english', 'fox dog')) AS rank
FROM articles
WHERE body_tsv @@ plainto_tsquery('english', 'fox dog')
ORDER BY rank DESC
LIMIT 20;

ts_rank calculates a score from the frequency and weight of matched terms. ts_rank_cd is similar but also considers cover density, meaning how closely matched terms are clustered in the document. It is generally more effective for short documents.

To give titles more weight, use setweight to assign A/B/C/D weights to tsvector lexemes, with A being the highest:

-- Change the generated column to a weighted version
SELECT setweight(to_tsvector('english', title), 'A')
     || setweight(to_tsvector('english', body),  'D');

In practice, do not put blind faith in pure ts_rank ordering because it does not understand your business. A common approach is to use a blended score: include ts_rank, then combine it with recency decay, like counts, and pinned status to produce an intuitive final order:

SELECT id, title,
       (ts_rank(body_tsv, q)
          * 0.4                              -- Keyword relevance
        + ln(pinned + likes + 1) * 0.3       -- Business popularity (log-compressed to prevent one major account from dominating)
        + exp(-age_days / 30.0) * 0.3)       -- 30-day half-life
       AS final_score
FROM articles, plainto_tsquery('chinese', '并发') q
WHERE body_tsv @@ q
ORDER BY final_score DESC LIMIT 20;

The three coefficients, 0.4/0.3/0.3, are tuning parameters. There is no standard answer; you can only iterate against real click logs. PG full-text search gives you the ingredients, but you still have to cook the dish yourself.

6. Chinese Tokenization: zhparser and pg_jieba

This is the most criticized limitation of PG full-text search: it does not support Chinese tokenization by default. to_tsvector('english', '今天天气真好') treats the entire sentence as one token, making it effectively useless.

The solution is to install an extension. There are two mainstream choices:

• zhparser - Based on SCWS; established, stable, and fast
• pg_jieba - Based on jieba; offers a more modern dictionary and better recognition of new words

Using zhparser as an example after installing its package on RHEL or Debian:

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese
  ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- The chinese configuration can now tokenize text
SELECT to_tsvector('chinese', '今天天气真好适合跑步');
-- -> '今天':1 '天气':2 '真好':3 '适合':4 '跑步':5

ADD MAPPING FOR n,v,a,... determines which parts of speech are included in the tsvector. Filtering out u (particles), r (pronouns), and c (conjunctions) can cut the index size in half while improving search precision. Terms such as 的, 了, 和, and 或者 should not participate in matching in the first place.

Compare how the same sentence, 我想查询订单的状态, is processed under simple and chinese:

SELECT to_tsvector('simple',  '我想查询订单的状态');   -- One token for the entire sentence; impossible to search effectively
SELECT to_tsvector('chinese', '我想查询订单的状态');   -- '查询':3 '订单':4 '状态':6

Only the latter performs genuine tokenization. Installing a Chinese-language extension determines whether PG full-text search is viable for Chinese content. Without this step, everything discussed earlier is merely theoretical.

Pitfall: A custom zhparser dictionary belongs in SCWS's etc directory and requires a restart or reload after changes. pg_jieba requires reloading the extension after modifying dict.txt. In production, do not edit dictionaries manually inside containers. Mount them as configuration files and keep them under version control.

7. Highlighting Matches with ts_headline

Search results must show users why each item matched. ts_headline wraps matched terms in tags within the original text, producing output the frontend can use directly:

SELECT id,
       ts_headline('chinese', body,
                   plainto_tsquery('chinese', '天气'),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20')
FROM articles
WHERE body_tsv @@ plainto_tsquery('chinese', '天气');
-- -> 今天<mark>天气</mark>真好,适合出门跑步……

Optional parameters control excerpt length, ellipses, and minimum or maximum word counts. Note that ts_headline must fetch the original text from the table and tokenize it again. Do not use it indiscriminately on huge result sets; apply LIMIT first, then generate headlines.

8. Combining It with pgvector for Hybrid Search

By 2026, keyword search alone is no longer enough. If a user searches for "how to handle concurrency," you should also retrieve documents discussing "locking mechanisms" or "mutual exclusion." Keyword matching cannot do that; semantic search is required.

This is where PG's advantage becomes clear: the same table can contain both keyword and vector indexes, so hybrid search requires no cross-system JOIN:

-- Two indexes on the same table
CREATE INDEX idx_articles_body_tsv ON articles USING gin(body_tsv);
CREATE INDEX idx_articles_embedding ON articles USING hnsw (embedding vector_cosine_ops);

-- Hybrid search: keyword filtering + semantic recall, fused inside the database
SELECT id, title,
       ts_rank(body_tsv, q)                       AS lex_rank,
       1 - (embedding <=> emb)                    AS sem_rank,
       -- Weighted fusion: BM25 approach + semantic score
       ts_rank(body_tsv, q) * 0.6
         + (1 - (embedding <=> emb)) * 0.4        AS hybrid_rank
FROM articles,
     plainto_tsquery('chinese', '并发处理') q,
     (SELECT embedding AS emb FROM articles WHERE id = $1) e
WHERE body_tsv @@ q OR embedding <=> emb < 0.5
ORDER BY hybrid_rank DESC
LIMIT 20;

On the left, ts_rank follows the BM25 approach of term frequency plus weighting; on the right, cosine distance provides semantic similarity. Fusing and ranking the two scores preserves precise keyword matches while adding broader semantic recall. The weights, 0.6/0.4 here, are tuning parameters that must be repeatedly adjusted using real query logs.

Hybrid search goes much deeper than this. Recall fusion can use RRF, linear weighting, reranking, and other strategies, while choosing between HNSW and IVFFlat involves its own tradeoffs. A separate article covers this topic in detail. The key point here is: PG is one of the few databases that can combine both retrieval methods in a single SQL query, making it one of the simplest choices for hybrid search at small and medium scale.

Summary

• PG full-text search = tsvector (document) + tsquery (query) + @@ (match). Understand these three elements, and everything else is configuration
• GIN indexes are the lifeline. Full-text search without an index burns CPU; materialize the tsvector with a generated column to prevent configuration drift
• Use ts_rank / ts_rank_cd for ranking, but do not rely on pure relevance scores. Blending in business signals is the practical engineering approach
• Chinese requires an extension: zhparser (SCWS) or pg_jieba (jieba). Part-of-speech mappings can cut the index size in half
• Combine it with pgvector for hybrid search, fusing the BM25 approach and semantic recall in one SQL query. This is PG's unique cost advantage in the AI era

One final sentence: Elasticsearch is not the only way to build search. When PostgreSQL can solve the problem, do not burden yourself with another cluster. One fewer component means one fewer reason to be woken up in the middle of the night.