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

TL;DR

Add a tsvector column (or a generated one) combining the searchable fields with appropriate weights: title weight A, body weight B. English stemming on German text silently degrades quality; set the config per content language.

Steps

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.

When to use

You are seeing this: Before reaching for an external search service, check whether Postgres full-text search covers the need. Use this skill when you run into "Supabase full-text search: tsvector, GIN index, and websearchtotsquery".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.