What you'll learn
Quick Answer
Elasticsearch builds an inverted index mapping words to the documents containing them, then ranks results by relevance. Use it when search quality matters; a database LIKE query is fine for simple filtering.
What LIKE cannot do
SELECT * FROM articles WHERE body LIKE '%database index%';Four separate problems with this.
It cannot use an index. A leading wildcard forces a full scan of every row — confirmed by EXPLAIN, which reports a scan rather than an index seek.
It matches literally. It finds "database index" but not "indexing databases", and not "database indexes".
There is no ranking. An article mentioning the phrase once in a footnote ranks identically to one about nothing else.
There is no tolerance. A user typing "databse" gets nothing.
For "find rows where status = active" a database is perfect. For "find the most relevant articles about database indexing", it is the wrong tool.
The inverted index
A normal index maps a row to its values. An inverted index maps each word to the documents containing it:
"database" -> [doc1, doc3, doc7]
"index" -> [doc1, doc4]
"query" -> [doc3, doc7, doc9]Searching for "database index" becomes a lookup of two lists and an intersection — fast regardless of how many documents exist, because you never examine documents that contain neither word.
The index also stores how often each word appears and where, which is what makes ranking possible. A document mentioning "database" fifteen times is probably more about databases than one mentioning it once.
Ranking uses term frequency weighed against how common the word is across the whole collection. A rare word matching is far more meaningful than a common one, which is why matching "the" contributes almost nothing.
Analysers: why 'running' matches 'ran'
Text is processed before indexing, and the same processing is applied to queries. That pipeline is the analyser, and it explains most search behaviour.
- Tokenising splits text into words.
- Lowercasing makes matching case-insensitive.
- Stop word removal drops words like "the" and "is" that carry little meaning.
- Stemming reduces words to a root, so "running", "runs" and "ran" all become the same token.
- Synonyms can map "laptop" and "notebook" to each other.
This is why search feels intelligent — it is not, it is careful preprocessing. It is also why the analyser must match between indexing and querying: change it and you must reindex, or queries will look for tokens that were never stored.
Fuzzy matching handles typos by allowing a small edit distance, which is how "databse" still finds results.
Using it, roughly
Elasticsearch is a service you talk to over HTTP with JSON. Documents go into an index:
PUT /articles/_doc/1
{ "title": "Database Indexing Explained",
"body": "An index lets the database find rows without scanning...",
"tags": ["sql", "performance"] }And queries come back ranked:
GET /articles/_search
{ "query": { "match": { "body": "database index" } } }Two distinctions worth knowing. A match query analyses your search text, so it stems and lowercases — this is what you want for text. A term query looks for the exact token, which is right for keywords, IDs and enums but usually surprising on prose.
Fields are typed by a mapping: text is analysed for search, keyword is stored whole for exact matching, sorting and aggregation. Getting this wrong is the most common beginner problem — sorting on an analysed text field does not work as expected because it was broken into tokens.
When it is worth the infrastructure
Elasticsearch is a separate service to run, monitor, secure and keep in sync with your database. That is a real cost, and it should buy something.
Worth it when search quality is a feature: a product catalogue where users search by description, a documentation site, log aggregation across many services, or anything needing faceted filtering and aggregations over large volumes.
Not worth it for a few thousand rows, for exact-match filtering, or when a database's built-in full-text search is enough. PostgreSQL's full-text search handles a surprising amount, requires no extra service, and stays transactionally consistent with your data.
That last point is the main operational headache: Elasticsearch is a copy of your data. Keeping it synchronised, and deciding what happens when indexing fails after a database write succeeds, is ongoing work — and it is never perfectly consistent, only eventually so.
Start with your database's own search. Move to a search engine when you can articulate which specific limitation is hurting you.