Catalog My learning Blog
Elasticsearch Tutorial: How Full-Text Search Works · Phase 1 · Lesson 1 · 3 min read

Search Engine vs Database: Why Search Is Different

On this page
  1. Why SQL LIKE queries fail at search
  2. Search engine vs database: what's different
  3. The same search in Elasticsearch, ranked by relevance
  4. When to use a search engine alongside a database
  5. About this course: Elasticsearch 9.5 and OpenSearch
  6. Check yourself

An article site adds search with SQL:

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 querySearch engine query
QuestionWhich rows match exactly?Which documents are most relevant?
Text handlingCharacters as storedText broken into normalized terms (lowercase, stems, synonyms)
ResultA set, in any orderA ranked list with scores
IndexB-tree on whole valuesInverted index from terms to documents (Day 3)
Typos, word order, synonymsNot handledHandled by analysis and fuzzy matching
ConsistencyTransactional, immediately visibleNear 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:

HTTP
GET /articles/_search
{ "query": { "match": { "body": "database performance" } } }
Code
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.

A database scans or seeks exact values and returns an unordered set; a search engine looks up analyzed terms in an inverted index and returns documents ranked by relevance.
A database scans or seeks exact values and returns an unordered set; a search engine looks up analyzed terms in an inverted index and returns documents ranked by relevance.

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.