Postgres Full-Text Search: Good Enough Before You Reach for Elasticsearch

The first search box in most apps is WHERE title ILIKE '%' || $1 || '%'. It works on 500 rows. On 500,000 it scans the whole table on every keystroke, can't match "running" to "run," and returns results in no useful order. Before adding Elasticsearch, OpenSearch, Meilisearch, or Typesense — another service to run, sync, and secure — know that PostgreSQL has capable full-text search built in.

The core pieces

tsvector — a document processed into normalised lexemes (word stems), with positions.
tsquery — a search query in the same normalised form, with operators.
@@ — the match operator.

SELECT to_tsvector('english', 'The runners were running quickly');
-- 'quick':5 'run':4 'runner':2

SELECT to_tsvector('english', 'The runners were running quickly')
       @@ websearch_to_tsquery('english', 'run');
-- true

The english configuration handles stemming and drops stop words ("the," "were"). PostgreSQL ships configurations for many languages; use simple for no stemming (codes, names).

Set it up properly: a generated column plus a GIN index

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

CREATE INDEX articles_search_idx ON articles USING GIN (search);

The generated column keeps the vector in sync automatically on insert and update — no triggers, no application code.
Weights (A–D) let title matches rank above body matches.
The GIN index makes @@ fast.

(Adding a stored generated column rewrites the table; on a large, busy table, plan it like any heavy migration. Postgres Migrations on Large Tables.)

Querying

SELECT id, title,
       ts_rank_cd(search, q) AS rank
FROM articles,
     websearch_to_tsquery('english', $1) AS q
WHERE search @@ q
ORDER BY rank DESC, published_at DESC
LIMIT 20;

websearch_to_tsquery accepts the syntax users already know and never throws on odd input:

User types	Means
`postgres backup`	both words
`"point in time"`	phrase
`backup or restore`	either
`backup -mysql`	exclude

Avoid to_tsquery with raw user input — it has strict syntax and errors on malformed queries.

Pass the search text as a parameter, as always. (SQL Injection Explained.)

Ranking

ts_rank — based on frequency of matching lexemes.
ts_rank_cd — "cover density," also rewarding matches that appear close together.

Both honour weights. Ranking must compute for every matching row, so on very broad queries over large tables, it's the expensive part. Mitigate by combining with filters (tenant, date range, status) and a LIMIT, or by ranking only the top candidates.

Highlighting

SELECT ts_headline('english', body, websearch_to_tsquery('english', $1),
                   'MaxFragments=2, MinWords=5, MaxWords=15')
FROM articles WHERE id = $2;

ts_headline re-parses the original text, so it's relatively slow: run it only on the page of results you're displaying, not the whole match set. And escape its output before rendering — it returns text with markup for the highlights. (What Is XSS?.)

Prefix search and autocomplete

to_tsquery('english', 'postg:*') matches lexemes starting with postg. For search-as-you-type, append :* to the last term — sanitise the input so only word characters reach the query.

Typos and fuzzy matching: pg_trgm

Full-text search matches words; it doesn't tolerate typos ("postgers"). The pg_trgm extension compares strings by three-character chunks:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm_idx ON articles USING GIN (title gin_trgm_ops);

SELECT title, similarity(title, $1) AS score
FROM articles
WHERE title % $1                 -- similarity above threshold
ORDER BY score DESC
LIMIT 10;

A trigram GIN index also makes ILIKE '%term%' fast, which is a quick win for short fields like names, SKUs, and emails. A common pattern: full-text search for content, trigram matching for titles and names, and fall back to trigram suggestions when full-text returns nothing.

Multi-tenant search

Filter by tenant in the same query, and consider a composite approach: a B-tree index on tenant_id combined with the GIN index, or GIN with the btree_gin extension to include tenant_id in the same index. Row-level security applies to search queries too. (Multi-Tenant Postgres Patterns.)

Where Postgres search runs out

Reach for a dedicated engine when you need several of:

Typo tolerance and relevance tuning as a core product feature (e-commerce search, docs search).
Faceted navigation with counts across many dimensions at scale.
Very large corpora with high query volume and heavy ranking.
Synonyms, per-field boosting, and analytics managed by non-engineers.
Semantic/vector search — though PostgreSQL's pgvector extension covers many embedding-search use cases, and hybrid (keyword + vector) search is feasible in Postgres too.

The cost of a separate engine is a sync pipeline — every insert, update, and delete must reach the index, with retries — plus another service to secure and monitor. Many products never need it.

Checklist

tsvector as a stored generated column with weights
GIN index on it; verify with EXPLAIN ANALYZE
websearch_to_tsquery for user input, passed as a parameter
ts_rank_cd + filters + LIMIT; ts_headline only on displayed results
pg_trgm for names, short fields, and typo suggestions
Tenant filter in the query; tested with realistic data volume

EasySpawn provisions managed Postgres for every project, so Claude Code can add extensions like pg_trgm, build indexes, and benchmark search queries against realistic data in the same workspace as your app. See how it works or join the waitlist.