Postgres Full-Text & Fuzzy Search

When to use it: building search over text in Postgres — a search box, autocomplete,
typo tolerance, "did you mean", relevance ranking, or hybrid keyword+semantic search —
and deciding whether native FTS is enough or you need a dedicated engine. Companion to
Push Compute to the Datastore (§4 names FTS; this is the depth).

The thesis: an indexed search inside your transactional database beats shipping rows to
the app to substring-match, and beats standing up Elasticsearch — until it structurally
can't. Know where that wall is.

1. tsvector / tsquery + the dictionary pipeline

to_tsvector('english', text) runs parser → dictionary chain (lowercase, stem, drop stop
words). ::tsvector casting does not normalize — always use to_tsvector.¹

A text-search configuration = a parser + an ordered dictionary chain per token type. The
first dictionary to emit a lexeme wins, so Snowball stemmers must be last (they match
everything). Chain order matters:

• Stemming: snowball (default english) — fast, no compound-word splitting.
• Ispell/Hunspell: real morphology + compound splitting (German/Norwegian/Dutch). Put
  before the stemmer.
• Synonym/thesaurus: normalize domain vocab (postgres → pgsql); before the stemmer.
• unaccent: a filtering dictionary — passes output on instead of terminating, so
  chain it before the stemmer. Gotcha: unaccent() is STABLE, not IMMUTABLE, so it
  can't go directly in a functional index — wrap it in your own IMMUTABLE SQL function.¹

2. Choose the query function by trust level — this is the one that bites

Do websearch_to_tsquery for untrusted search-box input, not to_tsquery — the latter
throws your endpoint a 500 on a bare &, while websearch gives Google-style power (phrases,
OR, exclusion) and silently discards junk. (Stop words are stripped even inside quotes.)¹

Operators: <-> = followed-by, <N> = distance ≤16384, :* = prefix. Precedence tightest→
loosest: ! > <-> > & > |.

3. Ranking: ts_rank_cd + setweight

• ts_rank_cd (cover density — rewards matched terms appearing close together) over
  ts_rank (frequency only) for multi-term boxes. ts_rank_cd needs positions — if
  you strip() the tsvector it silently returns 0.¹
• setweight(vec,'A') to boost title over body. Default weight multipliers are
  {D:0.1, C:0.4, B:0.4, A:1.0} — wait, exactly {0.1,0.2,0.4,1.0} for D,C,B,A.
• Normalization bitmask (3rd arg): 2 = divide by doc length (penalize long docs,
  changes ordering); 32 = squash into [0,1) for display only (cosmetic, no reorder).

UPDATE docs SET tsv =
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body ), 'B');
SELECT id, ts_rank_cd(tsv, q) AS rank
FROM docs, websearch_to_tsquery('english', $1) q
WHERE tsv @@ q ORDER BY rank DESC LIMIT 20;

4. Indexing: GIN, then GiST, then RUM

• GIN is the preferred tsvector index (docs say so): inverted, non-lossy for plain
  matches. Weight/position-qualified queries still need a heap recheck (GIN stores neither).
• GiST: lossy signature index; needs a recheck always. Pick it only when build speed/size
  is the binding constraint.
• RUM (3rd-party, postgrespro/rum — not in contrib, not on RDS/Cloud SQL):
  stores positions in the index, so phrase queries and ORDER BY tsv <=> tsquery ranked
  retrieval need no heap recheck/sort. Cost: ~2.5× GIN size, ~4× write amplification. Reach
  for it only when ranked/phrase latency is a proven hot-path bottleneck.¹
• Generated column vs expression index: same write cost (both compute to_tsvector per
  write). Prefer a GENERATED ... STORED column (PG12+) over the legacy
  tsvector_update_trigger. GIN fastupdate (on by default) batches writes into a pending
  list — turn it off when consistent search latency matters more than write throughput.

ALTER TABLE docs ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'')||' '||coalesce(body,''))) STORED;
CREATE INDEX docs_tsv ON docs USING GIN (tsv);

5. pg_trgm — making LIKE '%x%', ILIKE, and regex indexable

Trigram indexes extract 3-char grams regardless of position, so unlike a B-tree the pattern
need not be left-anchored — this is the fix for "LIKE '%foo%' won't use an index".²

• gin_trgm_ops (default, faster reads) vs gist_trgm_ops. GiST is required for
  ORDER BY col <-> 'term' LIMIT n (KNN similarity) — GIN structurally can't do it.
• similarity() (whole-string) / word_similarity() (best substring extent) /
  strict_word_similarity() (whole-word boundary). Operators % / <% / <<% with GUC
  thresholds 0.3 / 0.6 / 0.5.
• Trigrams pad short words with spaces, so 1–2 char queries produce unstable gram sets — fall
  back to ILIKE 'prefix%' (B-tree, left-anchored) below ~3 chars.

CREATE INDEX articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
SELECT * FROM articles WHERE title ILIKE '%postgre%';   -- now a Bitmap Index Scan

6. Fuzzy / phonetic: fuzzystrmatch

levenshtein(a,b[,ins,del,sub]) and levenshtein_less_equal(a,b,max) (early-exit) for edit
distance/typo budgets on short strings, any script. metaphone/dmetaphone for "sounds like"
name matching (dmetaphone when pronunciation is ambiguous); soundex is legacy English-only.
These don't work well with UTF-8 multibyte — use daitch_mokotoff() there.³ Combine
with unaccent(lower(...)) (via an IMMUTABLE wrapper) so diacritics don't break matches.

7. Hybrid search (keyword FTS + pgvector) with RRF

Keyword search nails exact terms/IDs/rare tokens; vector search captures paraphrase/synonymy.
Hybrid beats either alone. Combine with Reciprocal Rank Fusion — it consumes rank order,
not raw scores (which are on incomparable scales), so no normalization needed:⁴

RRF_score(d) = Σ_lists  1 / (k + rank_in_list(d))          -- k = 60 (paper) or 50 (Supabase); implementations diverge

Each leg ranks independently (row_number() OVER (ORDER BY ts_rank_cd(...) DESC) and
... ORDER BY embedding <#> $q), FULL OUTER JOIN on id, sum coalesce(1.0/(k+rank),0) per
leg (optionally weighted). Prefer RRF over weighted-score fusion by default (weighted fusion is
outlier-sensitive and needs normalization); use weighting only when eval data says one signal
should dominate.

8. When to leave Postgres — the structural wall

Native FTS (tsvector+GIN, ts_rank_cd, pg_trgm) is enough through roughly tens of
thousands to low millions of rows, moderate QPS, one/few Snowball languages, no hard faceting.
It breaks specifically at:⁵

1. Top-N relevance-ranked queries at multi-million rows — ts_rank/ts_rank_cd are not
   indexable, so top-N-by-relevance must score every matching row. Benchmark (Neon, 10M
   rows): native ranked query 38.8s vs pg_search 81ms. This wall breaks first, not raw
   row count.
2. Faceted/aggregation UIs — no BM25 corpus stats, no doc-values store for fast buckets.
3. CJK / non-Snowball languages — no word-boundary-free tokenization.
4. Typo tolerance at scale with zero tuning (Elasticsearch fuzziness:AUTO, Typesense
   default-on) vs hand-wired pg_trgm thresholds.

Intermediate step before a new cluster: ParadeDB pg_search (BM25 via Tantivy, a native
index AM — writes immediately visible, no ES refresh lag). AGPL-3.0; self-host only (managed is
waitlist); PG15+. It fixes the "ts_rank isn't indexable" wall inside Postgres.⁶

If you do leave: Typesense/Meilisearch for instant typo-tolerant product search with low ops
(single binary, no JVM); Elasticsearch/OpenSearch for deep aggregation/log-analytics at
scale (3-node minimum, JVM tuning — a real specialty). Always run the engine as a secondary,
always-rederivable-from-Postgres index, never the system of record (GitLab's pattern), with a
pg_trgm fallback if the index breaks.

See also

• Push Compute to the Datastore — the hub and the ladder.
• Postgres Query Planning and EXPLAIN — verify FTS indexes are actually used.