Databases & System Design•Part 5: Real-Time Architecture, Replication & Scale (51 - 60)
Full-Text Search: Elasticsearch vs SQL LIKE vs tsvector:
In Simple Words:
LIKE '%pattern%' requires slow full-table scans. PostgreSQL tsvector with GIN index provides fast text search built-in. Elasticsearch is a dedicated distributed cluster for fuzzy matching and spell checking at scale.
sql
-- PostgreSQL built-in full-text search:
ALTER TABLE articles ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);
-- Sub-millisecond search:
SELECT title FROM articles WHERE tsv @@ to_tsquery('english', 'distributed & database');