Search Engine vs Database: Why Search Is Different
On this page
Why SQL LIKE queries fail at search
An article site adds search with SQL:
SELECT id, title FROM articles WHERE body LIKE '%database performance%';It's slow, because a leading wildcard can't use a normal index, so every row is scanned. And it's wrong: it misses "performance of the database", "Databases" with an s, and anything with a typo. The results come back in no useful order. Users expect the best match first.
Search engine vs database: what's different
| Database query | Search engine query | |
|---|---|---|
| Question | Which rows match exactly? | Which documents are most relevant? |
| Text handling | Characters as stored | Text broken into normalized terms (lowercase, stems, synonyms) |
| Result | A set, in any order | A ranked list with scores |
| Index | B-tree on whole values | Inverted index from terms to documents (Day 3) |
| Typos, word order, synonyms | Not handled | Handled by analysis and fuzzy matching |
| Consistency | Transactional, immediately visible | Near real-time (visible within about a second) |
The same search in Elasticsearch, ranked by relevance
Seven articles indexed into a local Elasticsearch 9.5 node, searched for database performance:
GET /articles/_search
{ "query": { "match": { "body": "database performance" } } }total 4
5 1.498 Why Our Search Was Slow
1 1.438 Tuning Databases for Performance
3 1.356 Database Performance Myths
7 0.609 Network Performance Basics"Why Our Search Was Slow" ranks first even though its body says "performance of the database", in the opposite order. Articles mentioning both words rank above "Network Performance Basics", which only mentions one. Every result has a score, so the best matches come first. Day 9 explains how those scores are computed.
When to use a search engine alongside a database
Most systems use both:
- The database is the source of truth: transactions, constraints, joins, exact lookups.
- The search engine is a read-optimized copy for full-text search, filtering on many fields at once, faceted navigation, autocomplete, log analytics and vector similarity.
- Data flows from the database to the search index through application writes, change data capture (Debezium, Kafka) or periodic reindexing (Day 18).
Databases have grown search features (PostgreSQL full-text search, pg_trgm), which are fine for modest needs. A dedicated engine like Elasticsearch or OpenSearch earns its place with large volumes, complex relevance, aggregations at scale, or log and observability workloads.
About this course: Elasticsearch 9.5 and OpenSearch
Every query and command in this course was run against a real Elasticsearch 9.5 node. Elasticsearch is open source again under the AGPL option since 2024, alongside its Elastic License; OpenSearch is the Apache-licensed fork from 2021. Most of what you learn applies to both.
Check yourself
It can't use a normal index for a leading wildcard, so it scans every row, and it matches exact characters only, missing word variations, word order and typos.
A relevance score for each result, so the best matches come first, plus features built around it: stemming, typo tolerance, highlighting and facets. A database WHERE clause only says whether each row matches.
No. The database stays the source of truth, with transactions and constraints; the search index is a copy optimized for searching and analytics. If the index is lost or its mapping must change, you rebuild it from the database.
With change data capture or an outbox feeding an indexing worker, plus the ability to rebuild the index from the database when needed.
It's a fork of Elasticsearch 7.10 with the same core concepts and largely similar APIs, but the two have diverged in features, licensing and newer APIs.