Full-text search in Postgres, without another service

The usual progression is: ILIKE '%term%' works fine on 500 rows, gets slow at 50,000, and then someone proposes a separate search engine. That's a big jump, and it adds a second copy of your data to keep in sync, secure and back up. Postgres sits neatly in between, and for a lot of products it's the last stop.

Why ILIKE isn't search

Three problems, not just speed. A leading wildcard can't use a B-tree index, so every row is read. It has no concept of word boundaries or stemming, so searching running misses run. And it has no ranking, so you get matches in whatever order the table returns them. Real search needs the text broken into normalised lexemes, which is exactly what tsvector is.

Step 1: a searchable column

Rather than converting text on every query, store the converted form and let Postgres keep it current with a generated column:

alter table articles
  add column search_doc 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;

setweight marks where each piece of text came from, so a hit in the title can outrank a hit buried in the body. coalesce matters: concatenating a NULL would wipe the whole vector.

Note the expression must be immutable, which is why the text search configuration is written out as 'english' rather than relying on the session default.

Step 2: index it

create index articles_search_idx on articles using gin (search_doc);

GIN is the right default for search: fast lookups, slower to build and update than GiST but far better for read-heavy workloads. On a live table, add concurrently.

Step 3: query it safely

Never feed raw user input to to_tsquery — it expects operator syntax and raises an error on anything malformed. Use websearch_to_tsquery, which accepts what people actually type:

select id, title
from articles
where search_doc @@ websearch_to_tsquery('english', $1)
order by ts_rank(search_doc, websearch_to_tsquery('english', $1)) desc
limit 20;

It understands "exact phrase" in quotes, or, and a leading - to exclude a term, and it won't throw on odd punctuation. plainto_tsquery is the simpler alternative: it ANDs all the words together and ignores operators entirely.

Ranking and snippets

ts_rank scores by term frequency and weight; ts_rank_cd also considers how close the terms are to each other, which suits longer documents. You can supply your own weight array — here title hits count for much more than body hits:

ts_rank('{0.1, 0.2, 0.4, 1.0}'::float4[], search_doc, query) desc

The array is ordered D, C, B, A. And for the highlighted extract users expect in a result list:

ts_headline('english', body, websearch_to_tsquery('english', $1),
            'MaxWords=30, MinWords=15, StartSel=<mark>, StopSel=</mark>')

ts_headline works on the original text, not the vector, and it's expensive — apply it only to the page of results you're about to return, never across the whole match set.

Typos, substrings and short queries: trigrams

Full-text search matches whole words. It won't find merino inside supermerino, and it won't forgive merion. Trigrams handle both:

create extension if not exists pg_trgm;
create index products_name_trgm on products using gin (name gin_trgm_ops);

select name, similarity(name, $1) as score
from products
where name % $1                -- similarity above the threshold
order by score desc
limit 10;

Adjust sensitivity with set pg_trgm.similarity_threshold = 0.3;. The same index also makes ILIKE '%term%' indexable, which is often the cheapest single fix for an existing slow search page.

A pragmatic pattern: run the full-text query first; if it returns nothing, fall back to a trigram similarity search for a "did you mean" result set.

Accents and Australian spelling

The unaccent extension folds café to cafe. It's usually applied inside the tsvector expression, but because unaccent() is not immutable you can't use it directly in a generated column — the standard workaround is a small IMMUTABLE wrapper function that pins the dictionary, which you then call in the expression.

On spelling, one thing to know before an Australian customer reports it as a bug: the english configuration is a stemmer, not a synonym dictionary, and it does not reconcile British and American spelling. The two forms stem differently, so a search for one won't match the other:

select to_tsvector('english', 'organise'), to_tsvector('english', 'organize');
--  'organis':1              'organ':1

Same for tyre and tire. If both forms must match, that's a synonym or thesaurus dictionary in a custom text search configuration, or a synonym table you expand the query against before you search.

When Postgres search isn't enough

Be honest about the ceiling. Move to a dedicated engine when you need heavy relevance tuning and A/B tested scoring, large-scale faceting and aggregations across many fields, typo-tolerance as a first-class ranking signal rather than a fallback, per-language analysers you'd otherwise be hand-rolling, or a search index too large to keep alongside your primary data.

Until then, one system is a real advantage: your search results are transactionally consistent with your data, they inherit the same permissions, and they're covered by the same backups. On WattleDB that also means one less service holding a copy of your customers' data — the index lives inside the same Australian-hosted database, under the same row-level security policies, and it's included in point-in-time recovery automatically.