Algolia looks like magic until you see the bill: $1200/mo for 1M documents, $3000+ for real-time relevance tuning. Most indie SaaS and internal dashboards don't need it. Postgres tsvector — the native full-text search in PostgreSQL — handles 90% of search problems for free, ships with Supabase, and scales to millions of rows without extra infrastructure. If you've searched the Velocity X docs or filtered the dashboard, you've used it. The difference between Algolia and tsvector isn't capability — it's that Algolia's team has spent years on relevance tuning and typo tolerance. For product search, typo tolerance matters. For logs, docs, and internal tools, exact-ish matches and ranking by relevance are enough. Here's the architecture: a generated `tsvector` column, a GIN index for speed, `websearch_to_tsquery` for user input parsing, `ts_rank` for relevance ordering, and production patterns that actually work.
What's tsvector, and Why It Beats LIKE / ILIKE
A tsvector is Postgres' compressed representation of a searchable text document. It tokenizes words, removes stop words (the, a, and), and stores weights (is this word in the title or body?). When you query it with `@@` (the match operator), Postgres uses the GIN index to find matching rows in milliseconds, not seconds.
Compare three approaches to search:
-- LIKE: slow, unranked, no word boundaries
select * from documents where title ilike '%search%' or body ilike '%search%';
-- 500ms on 100k rows. Finds "searching" + "research" + "searches" + substring matches.
-- JSON/JSONB FTS: hand-rolled, slow
select * from documents where to_tsvector(body) @@ plainto_tsquery('english', 'search');
-- 2s on first query (no index), 50ms after.
-- Indexed tsvector: indexed, ranked, production-ready
select * from documents
where search_index @@ websearch_to_tsquery('english', 'search')
order by ts_rank(search_index, websearch_to_tsquery('english', 'search')) desc;
-- 20ms, ranked by relevance, precomputed at insert time.LIKE scans the entire table and does substring matching (slow, wrong results). Indexed tsvector uses the GIN to jump straight to matching rows, and the rank function orders by relevance. This is the move.
Schema: Generated Column + GIN Index
Start with a table. Add a `search_index` tsvector column that's automatically computed from the text you want searchable.
-- Example table
create table documents (
id uuid primary key default gen_random_uuid(),
title text not null,
body text not null,
category text,
created_at timestamp default now(),
search_index tsvector generated always as (
setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', body), 'B') ||
setweight(to_tsvector('english', coalesce(category, '')), 'C')
) stored
);
-- Index for speed
create index idx_documents_search on documents using gin(search_index);The `setweight` function gives words in the title (A) higher ranking than body (B), and body higher than category (C). When you rank by `ts_rank`, these weights bubble high-relevance matches to the top. The `generated always ... stored` means Postgres computes the tsvector at insert/update time, not at query time. The GIN index means Postgres can find matching rows in microseconds.
For multi-language search, replace `'english'` with `'french'`, `'spanish'`, or any supported language. For simple ASCII search without stemming, use `'simple'`.
Query Pattern: websearch_to_tsquery + ts_rank
Two functions do the heavy lifting. `websearch_to_tsquery` parses user input like Google: "exact phrase" matches, hyphens mean NOT, `OR` is explicit.
-- User types: "postgres search -algolia"
-- websearch_to_tsquery parses it into:
-- 'postgres' & 'search' & !'algolia'
const searchTerm = 'postgres search -algolia';
const query = await supabase
.from('documents')
.select('id, title, body, rank')
.rpc('search_documents', {
query_text: searchTerm
})
.order('rank', { ascending: false });And the corresponding Postgres function:
create or replace function search_documents(query_text text)
returns table (
id uuid,
title text,
body text,
rank float
) as $$
select
documents.id,
documents.title,
documents.body,
ts_rank(
documents.search_index,
websearch_to_tsquery('english', query_text)
) as rank
from documents
where documents.search_index @@ websearch_to_tsquery('english', query_text)
order by rank desc;
$$ language sql stable;
-- Grant execute to authenticated users
grant execute on function search_documents(text) to authenticated;The `@@` operator is the match check: "does this tsvector contain this query?" Postgres uses the GIN index to answer in O(log n) time. `ts_rank` scores each row by how many times the query terms appear and how much their weights (A/B/C) matter. This is ranking — exactly what you want for relevance.
Ranking + Highlighting
Ranking is built in (see above). For highlighting — showing users which words matched — use `ts_headline`:
create or replace function search_documents_with_highlight(query_text text)
returns table (
id uuid,
title text,
body text,
body_highlight text,
rank float
) as $$
select
documents.id,
documents.title,
documents.body,
ts_headline(
'english',
documents.body,
websearch_to_tsquery('english', query_text),
'StartSel=, StopSel=, MaxWords=30, MinWords=15'
) as body_highlight,
ts_rank(
documents.search_index,
websearch_to_tsquery('english', query_text)
) as rank
from documents
where documents.search_index @@ websearch_to_tsquery('english', query_text)
order by rank desc;
$$ language sql stable;
grant execute on function search_documents_with_highlight(text) to authenticated;`ts_headline` returns a snippet of the matching text with query terms wrapped in `` tags. This is what Velocity X does in dashboard search: show the user exactly where their search term matched in the document.
When tsvector Isn't Enough
tsvector excels at documents where word order doesn't matter much: titles, descriptions, logs, and tagged content. It falls apart on:
Typo tolerance. Search for "postres" and tsvector returns nothing. Algolia fuzzy-matches to "postgres". If typos matter (user-facing product search), you need trigram similarity (`pg_trgm`) or a separate typo layer.
-- Trigram similarity for typo handling
create extension pg_trgm;
create index idx_documents_title_trgm on documents using gist(title gist_trgm_ops);
-- Query with similarity
select * from documents
where similarity(title, 'postres') > 0.3
order by similarity(title, 'postres') desc;Phrase search. tsvector tokenizes, so you lose word order. `"machine learning"` is the same as `"learning machine"` in tsvector. If exact phrase matching is critical, combine tsvector ranking with a second CHECK for substring position.
Nested/relational search. If you search across related tables (docs + comments, products + reviews), tsvector is single-table. You'd use application code to combine results or a materialized view that denormalizes related text.
Real-time freshness. tsvector is precomputed. If you insert 10k rows per second, you're recomputing 10k tsvectors per second. At that scale (and budget), use a dedicated search engine (Typesense, Meilisearch, Algolia).
For everything else — docs, dashboards, internal tools, blogs, product catalogs under 10M documents — tsvector is the right tool.
Six FAQs
How big can my search index get before it breaks?
Postgres handles billion-row tables fine. The GIN index adds overhead (roughly 30% of table size on disk), but a billion rows with a GIN index fits in a reasonably sized Supabase plan. Velocity X indexes ~50M documents with tsvector + GIN and query time stays under 50ms. The real limit is Postgres disk budget on your Supabase plan, not the search algorithm.
Can I search across multiple languages in one table?
Mostly. tsvector is language-specific: `to_tsvector('english', text)` vs `to_tsvector('spanish', text)` will tokenize differently and remove different stop words. Store a `language` column, then query with the right language: `to_tsvector(documents.language, documents.body)`. This loses some optimization (you can't index across languages), but it works. For production multi-language apps, the cleanest move is separate tables or a language-specific index.
Should I use gin or gist for the index?
GIN (Generalized Inverted Index) for tsvector search. It's faster for exact matches. GIST (Generalized Search Tree) is better for geometric data and trigram similarity (`pg_trgm`). Stick with GIN for `@@` queries.
How do I update the search index when a document changes?
It's automatic. The `generated always ... stored` column recomputes on every INSERT, UPDATE, or DELETE. You don't need a trigger or manual refresh. If you're not using `generated`, add a trigger:
create trigger update_search_index before insert or update on documents
for each row execute function documents_search_index_update();Can I search JSON columns with tsvector?
Yes. Use `jsonb_to_tsvector` (available in Postgres 13+) or convert the JSON to text first: `to_tsvector('english', jsonb_col::text)`. The latter is simpler and doesn't require a newer Postgres version.
How do I handle prefix search (autocomplete)?
tsvector alone doesn't support it. You need trigrams or a dedicated autocomplete library. For autocomplete on titles, use `pg_trgm` with `%` operator (similarity), or index a separate autocomplete column with trigrams. Or just accept that you're searching on word boundaries and recommend Meilisearch for a polished typeahead.
The Bottom Line
Postgres full-text search is a 10x win for cost and simplicity. Generated tsvector columns auto-compute at insert time, GIN indexes keep queries fast, `websearch_to_tsquery` parses real user input, and `ts_rank` orders by relevance. You get ranking, multi-language stemming, and highlighting out of the box — no extra vendor. If your search needs are straightforward (docs, dashboards, internal tools, product catalog), ship tsvector and save the $1000/mo. For user-facing product search with typo tolerance and AI-powered relevance, Algolia is worth it. But for 90% of apps? Postgres is the move. Ready to audit your search layer? Check Aidxn Design pricing for backend reviews. For more on Postgres security and scale, see Supabase RLS in production.