Designing a Full-Text Search Feature in Supabase with Plain Postgres
Search is one of those features that tempts teams into adding a whole new service before checking what the database already does. Postgres has had full-text search for years, and Supabase exposes it directly, which covers a surprising amount of ground for a blog or content site.
TL;DR / Written for skimmers
A tsvector is a processed, searchable representation of text: lowercased, stripped of common stop words, reduced to word stems. A tsquery is the same kind of processing applied to the search term, so the two can be compared meaningfully. A generated column that combines title, excerpt and content into one tsvector, computed automatically by Postgres on insert and update, keeps the search index in sync without extra application code. A plain match is binary: a row either matches or it does not. ts_rank or ts_rank_cd score how well a row matches, which lets the query order results by relevance instead of by an arbitrary column like creation date. Full-text search is excellent at literal and near-literal matches. It struggles when a reader searches for a concept using different words than the post itself uses, since it has no notion of meaning, only of text.
tsvector and tsquery in plain language
A tsvector is a processed, searchable representation of text: lowercased, stripped of common stop words, reduced to word stems. A tsquery is the same kind of processing applied to the search term, so the two can be compared meaningfully.
Postgres compares a tsvector against a tsquery with the @@ operator, which returns true when the document matches the query. That single operator is the foundation the rest of the feature builds on.
Adding a generated search column without slowing down writes
A generated column that combines title, excerpt and content into one tsvector, computed automatically by Postgres on insert and update, keeps the search index in sync without extra application code.
Indexing that generated column with a GIN index is what keeps search fast as the table grows, turning a sequential scan into an index lookup.
Ranking results so the best match shows up first
A plain match is binary: a row either matches or it does not. ts_rank or ts_rank_cd score how well a row matches, which lets the query order results by relevance instead of by an arbitrary column like creation date.
Weighting the title higher than the body, using setweight when building the tsvector, pushes title matches above body-only matches in the ranked results, which usually matches what a reader expects.
When to graduate from full-text to vector search
Full-text search is excellent at literal and near-literal matches. It struggles when a reader searches for a concept using different words than the post itself uses, since it has no notion of meaning, only of text.
That gap is exactly what embeddings and vector search close. The two are not competitors: a lot of production search features run full-text first for speed and fall back to vector search for queries that do not return a confident match.