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.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.