# Postgres full-text search: tsvector, tsquery, and ranking

> Postgres full-text search without an external engine: to_tsvector and to_tsquery, a generated tsvector column, a GIN index, and ranking results with ts_rank.

Source: https://scribase.com/guides/postgres-full-text-search

Reading time: ~7 min.

Postgres has full-text search built in: to_tsvector turns a document into a searchable list of normalized lexemes (stemmed, stripped of stop words), to_tsquery turns a search string into the same form, and the @@ operator matches one against the other. For a table of any real size, store the tsvector in a generated column and back it with a GIN index — plain to_tsvector(...) @@ to_tsquery(...) with no index re-derives the vector and scans every row.

## Match a query against text directly

to_tsvector converts text to the searchable form, to_tsquery converts the search terms, and @@ tests the match. This form recomputes the tsvector on every row for every query and does not use an index — fine to try, not what you ship.

```sql
select title from articles
where to_tsvector('english', body) @@ to_tsquery('english', 'postgres & index');
```

## Add a generated tsvector column and a GIN index

A generated column keeps the tsvector automatically in sync with the source columns on every insert and update, so the query only has to read it, not recompute it — and a GIN index on that column turns the match into an index scan.

```sql
alter table articles
  add column search_vector tsvector
  generated always as (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) stored;

create index on articles using gin (search_vector);

select title from articles
where search_vector @@ to_tsquery('english', 'postgres & index');
```

## Multi-word queries and plainto_tsquery / websearch_to_tsquery

to_tsquery requires operators (& for AND, | for OR, <-> for "followed by") and errors on plain text with spaces. plainto_tsquery turns free text into an AND of its terms; websearch_to_tsquery (Postgres 11+) additionally understands quoted phrases and a leading minus for exclusion, which is the closer match to how users actually type a search box.

```sql
-- user types: rust async runtime
select title from articles
where search_vector @@ websearch_to_tsquery('english', 'rust async runtime');

-- user types: "exact phrase" -excluded
select title from articles
where search_vector @@ websearch_to_tsquery('english', '"exact phrase" -excluded');
```

## Rank results with ts_rank

A match is boolean; ts_rank scores how relevant a matching row is based on term frequency and (with ts_rank_cd) proximity, so results can be ordered best-first instead of in arbitrary row order.

```sql
select title, ts_rank(search_vector, query) as rank
from articles, websearch_to_tsquery('english', 'rust async runtime') as query
where search_vector @@ query
order by rank desc
limit 20;
```

## FAQ

**Do I need an external search engine for Postgres full-text search?**

Not for most apps. Postgres has to_tsvector/to_tsquery, GIN indexing, and ts_rank built in, which covers stemming, stop words, phrase search, and relevance ranking. Reach for a dedicated search engine only once you need features Postgres does not have, like typo-tolerant fuzzy matching at scale or faceted search across millions of rows.

**What is the difference between to_tsquery and plainto_tsquery?**

to_tsquery expects search-operator syntax (&, |, <->) and errors on plain text with spaces. plainto_tsquery accepts plain text and ANDs the terms together. websearch_to_tsquery goes further and also understands quoted phrases and a leading minus for exclusion, closer to a real search box.

**Why is my full-text search query slow?**

Usually because to_tsvector(...) is being recomputed on every row on every query with no index behind it. Store the tsvector in a generated column and add a GIN index on that column so the match becomes an index scan instead of a full-table recompute.

**How do I rank full-text search results by relevance?**

Pass the tsvector and tsquery to ts_rank (or ts_rank_cd, which also weighs how close together the matched terms appear) and order by the result descending. A plain @@ match alone only tells you yes/no, not how good a match it is.

## Related

- [Postgres JSONB query guide](https://scribase.com/guides/postgres-jsonb-query)
- [SQL subquery guide](https://scribase.com/guides/sql-subquery)
- [Postgres errors reference](https://scribase.com/postgres-errors)