Implementing Search — From Basic SQL to Elasticsearch

Stage 1 — SQL LIKE

SELECT * FROM products WHERE name LIKE '%widget%'

Simple. Works for small datasets. Problems: cannot use indexes (leading wildcard), no relevance ranking, no typo tolerance. Use only for prototypes or very small datasets.

Stage 2 — Native full-text search

PostgreSQL: tsvector + tsquery with GIN indexes. MySQL: FULLTEXT indexes with MATCH ... AGAINST. Much faster than LIKE, with relevance ranking. No typo tolerance or semantic understanding.

-- PostgreSQL full-text
SELECT *, ts_rank(to_tsvector(name), query) AS rank
FROM products, to_tsquery('widget & blue') query
WHERE to_tsvector(name) @@ query
ORDER BY rank DESC;

Stage 3 — Elasticsearch / OpenSearch

When you need: fuzzy matching, autocomplete, faceted search, multi-language support, or search across millions of documents. Sync data from your primary DB to Elasticsearch via CDC or scheduled reindexing.

Stage 4 — Semantic / vector search

Embed both documents and queries with a text model. Search by cosine similarity instead of keyword overlap. Finds conceptually related results even when no keywords match. Combine with keyword search (hybrid retrieval) for best results.