Full-Text Search

Try searching “running shoes under $100” in a regular SQL database with LIKE '%running shoes%'. It’ll be slow, won’t handle typos, won’t rank results by relevance, and won’t understand that “running” and “run” are related. That’s why search engines exist.

Why SQL Search Sucks

SQL’s LIKE and even FULL TEXT SEARCH have serious limitations:

No relevance scoring (which result is MOST relevant?)
Poor handling of typos and misspellings
Can’t handle synonyms (“laptop” vs “notebook”)
Gets painfully slow at scale
No faceted search (filter by category, price range, brand)

How Search Engines Work

Elasticsearch (and similar tools like Meilisearch, Typesense) use an inverted index, instead of storing documents and scanning them, they store words and point to which documents contain them.

Think of it like a book’s index at the back:

"database": appears in pages 12, 45, 89
"caching" : appears in pages 23, 45, 67

When you search “database caching”, it instantly finds pages 12, 23, 45, 67, 89, and ranks page 45 highest because it contains BOTH terms.

When to Use a Search Engine

When to Use

Product search in e-commerce
Article/blog search
Log analysis (searching through millions of log lines)
Autocomplete and suggestions
Filtering + sorting across many dimensions
Any time users type free-form text and expect smart results

The Architecture

Your primary database (PostgreSQL) remains the source of truth. Elasticsearch is a secondary index, you sync data to it.

User creates a product → save to PostgreSQL → push to Elasticsearch
User searches → query Elasticsearch → return results
User views product detail → query PostgreSQL (source of truth)

This means you have two copies of data. That’s intentional, each is optimized for its job. PostgreSQL for reliable writes, Elasticsearch for fast reads and complex queries.

Key Concepts

Analyzers, break text into searchable tokens. “Running Shoes” becomes [“running”, “shoes”]. Different languages need different analyzers.

Fuzzy matching, finds results even with typos. “sheos” still matches “shoes.”

Boosting, some fields matter more. Title matches rank higher than description matches.

Facets, “Show me running shoes, filtered by brand: Nike, price: $50-$100, rating: 4+.”

Wrapping Up

SQL is bad at free-text search
Search engines use inverted indexes for fast, relevant results
Keep your primary DB as source of truth, search engine as secondary index
Use search engines for any user-facing search functionality
Popular options: Elasticsearch, Meilisearch, Typesense, Algolia

Day 13 of 95 | Backend Engineering Series