Full text search

PostgreSQL has built-in full text search. It converts text into searchable tsvector values and uses tsquery for keyword matching and ranking.

Full text search is suitable for articles, products, tickets, and knowledge bases. For semantic retrieval, combine it with pgvector.

Basic query

You can use to_tsvector and plainto_tsquery directly in a query.

select id, title
from public.articles
where to_tsvector('simple', title || ' ' || content)
  @@ plainto_tsquery('simple', 'cloudbase database');

The simple configuration does not perform advanced language processing. It is suitable for English, identifiers, or pre-tokenized text. Chinese search usually needs application-side tokenization or another search solution.

Use a generated column

For frequent searches, store the search vector in a generated column and create a GIN index.

create table public.articles (
  id bigint generated by default as identity primary key,
  title text not null,
  content text not null,
  search_vector tsvector generated always as (
    to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(content, ''))
  ) stored
);

create index articles_search_vector_idx
on public.articles
using gin (search_vector);

Query the search_vector field directly.

select id, title
from public.articles
where search_vector @@ plainto_tsquery('simple', 'cloudbase');

Search ranking

Use ts_rank to sort search results by relevance.

select
  id,
  title,
  ts_rank(search_vector, query) as rank
from public.articles, plainto_tsquery('simple', 'cloudbase database') query
where search_vector @@ query
order by rank desc
limit 20;

Search highlights

ts_headline can generate highlighted snippets.

select
  id,
  title,
  ts_headline('simple', content, plainto_tsquery('simple', 'database')) as snippet
from public.articles
where search_vector @@ plainto_tsquery('simple', 'database');

Full text, fuzzy, or vector search

Capability	Use case
Full text search	Keyword search, ranking, highlights
pg_trgm	Fuzzy search and similar spelling on short text
pgvector	Semantic similarity and RAG retrieval

You can combine them in real applications. For example, filter by tenant and status first, run full text search, then sort by relevance.