How to Use PostgreSQL Full-Text Search

PostgreSQL full-text search is one of the most useful features that many backend teams underuse.

A lot of applications start search with:

• LIKE
• ILIKE
• wildcard patterns
• or application-side filtering after fetching too much text

That can work for tiny datasets. It becomes painful quickly once you want:

• better relevance
• stemming
• phrase search
• ranked results
• and search that stays reasonably fast as content grows

That is where PostgreSQL full-text search helps.

It gives you:

• tokenized search documents with tsvector
• search queries with tsquery
• built-in language-aware parsing
• ranking functions
• highlighted excerpts
• and GIN indexing for real performance

This guide shows how to use it properly in a real application.

What PostgreSQL Full-Text Search Actually Is

PostgreSQL full-text search is not the same thing as substring search.

A simple query like:

where title ilike '%postgres%'

looks for raw text patterns.

Full-text search is different. It works by:

1. parsing text into normalized searchable terms
2. storing those terms in a tsvector
3. converting user search input into a tsquery
4. matching them with the @@ operator

That means PostgreSQL can do more intelligent things like:

• stem words
• ignore some stop words
• support phrase-style search
• and rank matches more usefully

Core Concepts You Need to Know

tsvector

A tsvector is the searchable form of a document.

It stores normalized lexemes, and often position information too.

Example:

select to_tsvector('english', 'PostgreSQL makes search much better');

This produces a processed search document rather than raw text.

tsquery

A tsquery is the searchable form of the user’s query.

Example:

select plainto_tsquery('english', 'better search');

@@

The @@ operator checks whether a tsvector matches a tsquery.

Example:

select
  to_tsvector('english', 'PostgreSQL makes search much better')
  @@ plainto_tsquery('english', 'better search');

Why Full-Text Search Beats LIKE for Search

LIKE and ILIKE are still useful for:

• prefix matching
• tiny lookup tables
• exact-ish string matching
• admin filters

But they are weak as a real content-search solution.

PostgreSQL full-text search is better when you need:

• language-aware matching
• ranked results
• phrase handling
• efficient multi-word search
• large text fields
• search over combined fields like title plus body

A query like:

where body ilike '%postgres%'

does not understand:

• whether postgres should match postgresql
• whether “search engine” is a phrase
• how to rank the best result first
• or how to search efficiently across several text columns

Full-text search does.

The Simplest Working Example

Let’s start with a simple content table:

create table articles (
  id bigint generated always as identity primary key,
  title text not null,
  body text not null
);

You can search it without storing a tsvector column yet:

select id, title
from articles
where to_tsvector('english', title || ' ' || body)
      @@ plainto_tsquery('english', 'postgres search');

This works, but it is not the best production pattern because PostgreSQL has to build the search vector on the fly for each row unless you persist or index it properly.

The Best Practical Pattern: Store a Search Vector

For real applications, a very common pattern is to store a dedicated search column.

Example:

alter table articles
add column search_vector tsvector;

Then populate it:

update articles
set search_vector =
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

Then query it:

select id, title
from articles
where search_vector @@ plainto_tsquery('english', 'postgres search');

This is much more index-friendly and production-friendly.

Add a GIN Index

This is the step that makes full-text search fast enough to matter.

create index idx_articles_search_vector
on articles using gin (search_vector);

For most applications, this is the best default index for PostgreSQL full-text search.

Once that index exists, the search path becomes much more realistic for larger datasets.

A Better Production Variant: Generated Search Vector

If you want PostgreSQL to maintain the vector for you, a very practical pattern is a generated stored column.

Example:

create table articles (
  id bigint generated always as identity primary key,
  title text not null,
  body text not null,
  search_vector tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) stored
);

Then index it:

create index idx_articles_search_vector
on articles using gin (search_vector);

This is a strong pattern because:

• the vector stays in sync
• ranking can use weighting
• and queries stay simple

Use setweight to Make Title Matter More Than Body

Not all text fields are equally important.

Usually:

• title matches should matter more
• tags may matter a lot
• summaries may matter moderately
• body text may matter, but often less than title

That is why weighting is so useful.

Example:

setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
setweight(to_tsvector('english', coalesce(body, '')), 'C')

A-weighted text is treated as more important than lower-weighted text during ranking.

That gives you much more realistic search results.

The Main Query Builders You Should Know

PostgreSQL gives you several ways to turn input into a tsquery.

They are not interchangeable.

plainto_tsquery

This is the safest default for simple plain-text input.

Example:

select plainto_tsquery('english', 'fast postgres search');

This is usually a strong choice when:

• the user types simple words
• you do not want them to use search operators directly
• you want a predictable plain-input experience

phraseto_tsquery

This is useful for phrase-oriented search.

Example:

select phraseto_tsquery('english', 'full text search');

Use it when you want:

• phrase-sensitive behavior
• better matching for word order
• less broad multi-term matching

websearch_to_tsquery

This is often the best user-facing search-box option.

Example:

select websearch_to_tsquery('english', '"full text search" or postgres -mysql');

This is useful because it is more forgiving for normal users and understands web-style ideas like:

• quoted phrases
• or
• minus for exclusion

For many search boxes, this is the most ergonomic input parser.

to_tsquery

This is the most powerful and least forgiving.

Example:

select to_tsquery('english', 'postgres & search');

Use it when:

• you want explicit operators
• the caller knows the syntax
• you are building more advanced internal search behavior

For public search boxes, it is often not the first choice because it is less forgiving.

Ranking Results With ts_rank

Search without ranking is usually disappointing.

If everything matches, users still need the best matches first.

Example:

select
  id,
  title,
  ts_rank(search_vector, plainto_tsquery('english', 'postgres search')) as rank
from articles
where search_vector @@ plainto_tsquery('english', 'postgres search')
order by rank desc;

This is the basic ranking pattern.

When to use ts_rank_cd

If proximity matters more strongly in your use case, ts_rank_cd can be useful too.

In practice:

• ts_rank is a great starting point
• ts_rank_cd is worth testing if you care a lot about close term grouping

Practical tip

Always order by rank for real search experiences. Otherwise your search may technically work but feel poor.

Highlight Matching Text With ts_headline

A good search result usually shows a useful excerpt, not only a title.

PostgreSQL supports that with ts_headline.

Example:

select
  id,
  title,
  ts_headline(
    'english',
    body,
    websearch_to_tsquery('english', 'postgres search')
  ) as snippet
from articles
where search_vector @@ websearch_to_tsquery('english', 'postgres search');

This is useful for:

• search result snippets
• matched keyword previews
• content exploration experiences

It makes PostgreSQL search feel much more like a real product feature.

Search Across Multiple Fields

A strong practical pattern is to combine several fields into one vector.

Example:

create table docs (
  id bigint generated always as identity primary key,
  title text not null,
  tags text,
  summary text,
  body text not null,
  search_vector tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(tags, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'C')
  ) stored
);

This works well because:

• titles can rank highest
• tags can carry high relevance
• summaries contribute meaningful context
• body text fills in the long-tail matching

This is usually much better than separate independent searches unless your product really needs separate search scopes.

PostgreSQL Full-Text Search With JSONB

If your content or metadata lives inside JSONB, PostgreSQL can still help.

PostgreSQL supports turning JSON and JSONB into searchable vectors with to_tsvector, and more selective control is available through json_to_tsvector and jsonb_to_tsvector.

That means you can search structured documents too.

Example:

select
  to_tsvector('english', '{"title":"PostgreSQL search","body":"Fast built-in search"}'::jsonb);

For more deliberate indexing of JSONB content, it is usually better to decide which parts of the document actually matter to search, rather than indexing every bit of metadata blindly.

Choose the Right Text Search Configuration

The english configuration is common, but it is not automatically correct for every app.

Configuration matters because it affects:

• stemming
• stop words
• dictionaries
• parsing behavior

Examples

Use:

• english for English-heavy content
• other language configs when content is clearly language-specific
• custom configs when domain-specific search needs more control

If your search results feel “wrong,” the configuration may be part of the problem, not only the query.

When GIN Is Best and When GiST Might Appear

For most PostgreSQL full-text search workloads, GIN is the correct starting point.

That is the safest practical default.

GiST is still supported, but GIN is generally preferred for text search because it is the more natural fit for this indexing pattern.

Good default rule

• use GIN unless you have a specific reason not to

That keeps the decision simple for most teams.

Common Production Pattern

Here is a strong end-to-end pattern for an app search table:

create table articles (
  id bigint generated always as identity primary key,
  title text not null,
  summary text,
  body text not null,
  published_at timestamptz not null default now(),
  search_vector tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'C')
  ) stored
);

create index idx_articles_search_vector
on articles using gin (search_vector);

Then query it like this:

select
  id,
  title,
  published_at,
  ts_rank(search_vector, websearch_to_tsquery('english', $1)) as rank,
  ts_headline('english', body, websearch_to_tsquery('english', $1)) as snippet
from articles
where search_vector @@ websearch_to_tsquery('english', $1)
order by rank desc, published_at desc
limit 20;

That is a strong practical baseline for many real applications.

Common Mistakes to Avoid

1. Using LIKE when you really want search

Substring matching is not the same as search relevance.

2. Building to_tsvector(...) on the fly without indexing

This is fine for demos, not great for production search over growing tables.

3. Not weighting fields

If title and body matter differently, the vector should reflect that.

4. Using the wrong query builder

A public search box usually wants websearch_to_tsquery or plainto_tsquery, not raw to_tsquery.

5. Ignoring ranking

Matching without ranking often feels low quality to users.

6. Throwing too much noisy text into the vector

Not every field deserves to affect search equally.

7. Expecting PostgreSQL full-text search to be a complete replacement for every search system

It is powerful, but not always the right tool for every advanced search product.

When PostgreSQL Full-Text Search Is a Great Fit

Use PostgreSQL FTS when:

• search lives inside your main app
• the dataset is moderate to large but still manageable in PostgreSQL
• you want relevance better than LIKE
• you do not need a separate search stack yet
• you want fewer moving parts
• your search problem is mostly text matching, ranking, and snippets

This is a great fit for:

• blog search
• documentation search
• knowledge bases
• product catalog text search
• internal app search
• comment or note search
• admin search tools

When You May Need More Than PostgreSQL Full-Text Search

A dedicated search engine may be better when you need:

• typo tolerance at a stronger level
• very advanced linguistic analysis
• large-scale faceting
• distributed search architecture
• autocomplete at heavy scale
• highly customized search relevance logic across huge corpora

That does not make PostgreSQL FTS weak. It just means it has a sweet spot.

And for many applications, that sweet spot is more than enough.

FAQ

What is PostgreSQL full-text search best for?

PostgreSQL full-text search is best for application-level search over articles, posts, product text, documentation, notes, comments, or other medium-scale search needs where you want relevance ranking and stemming without adding a separate search engine immediately.

Should I use GIN or GiST for PostgreSQL full-text search?

GIN is usually the best default and is PostgreSQL’s preferred index type for text search. GiST is supported too, but GIN is usually the better starting point for most applications.

What is the difference between plainto_tsquery and websearch_to_tsquery?

plainto_tsquery is great for simple plain input and treats terms as an AND-style query, while websearch_to_tsquery is more forgiving for user-facing search boxes because it understands quoted phrases, OR, and dash-style exclusion syntax.

Can PostgreSQL full-text search replace Elasticsearch?

Sometimes, yes for simpler in-app search. But if you need advanced typo tolerance, large-scale faceting, or more specialized distributed search features, a dedicated search engine may still be the better fit.

Conclusion

PostgreSQL full-text search is one of the best ways to build real search into an application without introducing a separate search stack too early.

The most practical pattern is usually:

• build a tsvector
• parse user input into a tsquery
• index the vector with GIN
• rank with ts_rank
• highlight with ts_headline
• and weight the important fields properly

That gives you a search system that is:

• much better than LIKE
• much easier to keep inside your main database
• and often strong enough for real product search without extra infrastructure

For a lot of teams, that is exactly the right place to start.