Full-Text Search

ELI5 — The Vibe Check

Full-text search lets you search through text like Google does, not just exact matches. It understands that 'running' and 'ran' are related, ignores words like 'the' and 'a', and ranks results by relevance. It's the difference between a librarian and someone who can only find books by their exact title.

Real Talk

Full-text search (FTS) creates inverted indexes that map words to documents, enabling natural language queries with stemming, stop word removal, and relevance ranking. PostgreSQL's built-in tsvector/tsquery system supports weighted searches, phrase matching, and custom dictionaries. Dedicated solutions like Elasticsearch offer more advanced features.

Show Me The Code

ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || body);
CREATE INDEX idx_search ON articles USING GIN(search_vector);

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC;

When You'll Hear This

"Postgres full-text search is good enough for most apps, you don't always need Elasticsearch." / "We added full-text search to our product catalog."