Supabase full-text search: tsvector, GIN index, and websearch_to_tsquery

Full-text search without leaving Postgres

Before reaching for an external search service, check whether Postgres full-text search covers the need. For titles, descriptions, and document bodies up to moderate scale, it does, with ranking and stemming built in.

Checkable procedure

1. Add a tsvector column (or a generated one) combining the searchable fields with appropriate weights: title weight A, body weight B. Generated columns keep it in sync automatically.
2. Index it with GIN. Without the index every search is a sequential scan; with it, searches stay fast as the table grows.
3. Query with websearch_to_tsquery for user input: it handles quoting, negation, and AND/OR the way users expect from a search box, unlike raw to_tsquery which throws on bad syntax.
4. Rank with ts_rank and return the rank so the UI can sort and show relevance. Unranked full-text results feel broken even when the matching is right.
5. Pick the right language configuration for stemming. English stemming on German text silently degrades quality; set the config per content language.

Ordering constraints

Column and index before the search UI. Backfill the tsvector for existing rows in the migration, or old content is unsearchable.

Verification

Search for a stemmed variant ("running" finding "run"), a quoted phrase, and a common word. Confirm ranking puts the best match first and latency stays flat as the table grows.

Verified against live Supabase docs 2026-09-26: https://supabase.com/docs/guides/database/full-text-search