Search engines: Elasticsearch, and why LIKE is not search

Type a few words into a box and get back the most relevant results, ranked, fast, even with a typo and even though you did not use the exact words in the document. That is search, and it is a genuinely different problem from the WHERE title LIKE '%...%' you have been writing. Elasticsearch (and OpenSearch, its open fork) is the dominant dedicated search engine; this lesson is why you eventually need one, and how far PostgreSQL gets you first.

Why `LIKE` is not search

LIKE '%monsoon%' looks like search and is not, in four ways that each break a real expectation.

It is a substring match, not a word match. LIKE '%cat%' matches "cat", "category" and "scatter". It has no idea what a word is.

It does not understand word forms. Searching "running" will not find "run"; "stories" will not find "story". Proven against the library database — ILIKE '%story%' finds nothing, because the book is titled "Breast Stories":

SELECT count(*) FROM books WHERE title ILIKE '%story%';    -- → 0

It cannot rank. Every match is equally "matching"; there is no notion of which result is more relevant. Search is largely about ranking, and LIKE has none.

It cannot use an index for a leading wildcard. LIKE '%monsoon%' scans every row (module 7), so it is slow at exactly the scale where you would want search.

Search engines solve all four. They tokenise text into words, reduce words to their root (stemming: "running"→"run"), build an inverted index (word → the documents containing it — the same idea as a book's back-of-book index and MongoDB's GIN), score each match for relevance, and return results ranked by that score.

PostgreSQL full-text search — the first step, and often enough

Before a separate search engine, PostgreSQL has real full-text search built in, and it fixes the first three problems immediately. The same "story" search that ILIKE missed:

SELECT title FROM books
WHERE to_tsvector('english', title) @@ plainto_tsquery('english', 'story');
--  → 'Breast Stories'

It works because both sides are stemmed to the same root:

SELECT to_tsvector('english', 'Breast Stories');   -- → 'breast':1 'stori':2
SELECT plainto_tsquery('english', 'story');          -- → 'stori'

"Stories" and "story" both become stori, so they match — something no LIKE can do. to_tsvector tokenises and stems; plainto_tsquery does the same to the query; @@ matches; and ts_rank scores for relevance so you can ORDER BY it. Back it with a GIN index on the tsvector (module 7) and it is fast.

For a large fraction of applications, this is all the search you need — site search over articles, products or documents, in the database you already run, with no new system. The pg_trgm extension adds trigram matching for fuzzy "did you mean" and leading-wildcard support. Reach for PostgreSQL full-text first, exactly as this course keeps advising.

When you outgrow it — what a dedicated engine adds

PostgreSQL full-text is good; a dedicated search engine is a different order of capability, and you move when you need what it adds:

Relevance tuning. Boost matches in the title over the body, weight recent documents, tune the scoring (BM25) — search quality as a first-class, controllable thing.
Typo tolerance (fuzzy matching). "monsson" finds "monsoon" via edit distance, out of the box.
Faceted search. The filter sidebar on any shopping site — "brand: 3 results, colour: 5" — computed as part of the search, across millions of documents, instantly.
Autocomplete and suggestions as the user types.
Analysis of many languages, synonyms, custom tokenisation.
Scale built for search — sharded across nodes, near-real-time indexing of huge document volumes.
Aggregations over search results — Elasticsearch doubles as an analytics engine, which is why it anchors the "ELK" logging stack (Elasticsearch, Logstash, Kibana) for searching and charting logs at volume.

The cost, and the crucial catch

A dedicated search engine is a serious commitment:

It is a second datastore to run, secure, scale and keep available — and Elasticsearch in particular is memory-hungry and operationally involved.
It is not your source of truth. This is the catch that causes real bugs. Your data lives in PostgreSQL; the search engine holds a derived copy, and you must keep it in sync — reindex on every change, via a pipeline that will occasionally lag or fail. Search results can therefore be stale or briefly inconsistent with the database, and reconciling them is ongoing work. It is the denormalisation-drift problem from module 8, across two systems.
It is eventually consistent by design — a newly indexed document is searchable after a short refresh, not instantly.

So the mental model matches Redis: PostgreSQL is the source of truth; the search engine is a fast, derived, eventually-consistent index of it. If it vanished, search would break but no real data would be lost — you would reindex from PostgreSQL.

How to recognise the need

Move from LIKE to full-text, and from full-text to a dedicated engine, on evidence:

LIKE → PostgreSQL full-text: the moment you want word-aware matching, stemming or ranking — i.e. almost as soon as you have a real search box.
Full-text → Elasticsearch: when you need typo tolerance, faceting, fine relevance tuning, autocomplete, multi-language analysis, or search across a document volume that strains the database — and when search quality is central enough to your product to justify a second system.

Do not start with Elasticsearch. Start with LIKE if it is truly just filtering, move to PostgreSQL full-text for real search, and adopt a dedicated engine only when you have hit its limits — measured, not assumed.

Check your work

Four ways LIKE fails as search. Substring not word match, no stemming, no ranking, and no index for a leading wildcard.

What a search engine does instead. Tokenises, stems, builds an inverted index, scores relevance, and returns ranked results.

What an inverted index is. Word → the documents containing it — the same idea as GIN.

How PostgreSQL full-text fixes the "story"/"stories" case. Both stem to stori via to_tsvector/plainto_tsquery, so they match.

The three functions and the operator. to_tsvector (stem the document), plainto_tsquery (stem the query), @@ (match), ts_rank (score) — with a GIN index for speed.

What pg_trgm adds. Trigram fuzzy matching and leading-wildcard support.

Six things a dedicated engine adds. Relevance tuning, typo tolerance, faceting, autocomplete, multi-language analysis, and search-scale plus aggregations.

The crucial catch with a search engine. It is a derived copy, not the source of truth; you must keep it in sync, and it is eventually consistent.

The mental model. PostgreSQL is the source of truth; the search engine is a fast, derived, eventually-consistent index — if it vanished, search breaks but no data is lost.

The recommended progression. LIKE (mere filtering) → PostgreSQL full-text (real search) → dedicated engine (when you hit its limits), each on evidence.

Practice

Run LIKE '%story%' against a titles table and confirm it misses "Stories". Explain why.
Show to_tsvector('english', 'Breast Stories') and plainto_tsquery('english', 'story') and point at the shared stem.
Run the full-text query and confirm it finds what LIKE missed.
Add ts_rank and ORDER BY it. Search a two-word query and see the ranking.
Create a GIN index on the tsvector and compare the plan with and without it.
Try a typo ("stroies") in full-text and confirm it fails. Note that this is where a dedicated engine's fuzzy matching would help.
Install pg_trgm and try a fuzzy/similarity match on the same typo.
List, for an application you know, which search features it needs and therefore which tier is right.
Describe the sync pipeline you would need to keep an Elasticsearch index current with PostgreSQL, and where it could go wrong.

Official documentation

PostgreSQL — Full text search — tsvector, tsquery, ranking and GIN indexing.
PostgreSQL — pg_trgm — Trigram similarity and fuzzy matching.
Elasticsearch — Documentation — The dedicated engine: analysis, relevance, aggregations.
Elasticsearch — Relevance scoring (BM25) — How ranking works when quality matters.
OpenSearch — The open-source fork, if licensing matters to you.

Next: time-series databases, and why time is special.