How do you optimize full-text search performance on a large Postgres table in Supabase?

Postgres's full-text search works by converting text into a tsvector — a normalized, stemmed representation of the words in a column — and comparing it against a tsquery built from the search terms. On a small table this works fine unindexed, but on a large table, computing that tsvector on every query row-by-row becomes the dominant cost.

The standard optimization is to add a generated tsvector column and index it with GIN rather than computing the vector inline on every search:

alter table articles add column fts tsvector
  generated always as (to_tsvector('english', title || ' ' || body)) stored;
create index articles_fts_idx on articles using gin(fts);

With this in place, a query like select * from articles where fts @@ websearch_to_tsquery('english', 'connection pooling') can use the GIN index directly instead of scanning and re-vectorizing every row, which is the difference between an index scan and a full table scan on large datasets.

Beyond indexing, further gains come from ranking only a reasonably sized candidate set (filter first with other WHERE conditions, then rank with ts_rank), and, for workloads that need to blend keyword relevance with semantic meaning, combining this indexed full-text score with a pgvector similarity score in a hybrid ranking query rather than relying on full-text search alone.