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

Denormalizing Data for Elasticsearch

On this page
  1. Why joins don't scale in search engines
  2. Design search documents around queries
  3. Keeping denormalized copies in sync
  4. Denormalization trade-offs to accept
  5. Check yourself

Why joins don't scale in search engines

In a database, an article row stores author_id, and a join fetches the author's name at query time. Elasticsearch has no general joins: searching "articles by authors in London about Kubernetes" needs the author's city inside each article document. Denormalization, copying related data into the document you search, is the normal way to model search data, and denormalizing data well is most of the design work.

Design search documents around queries

Start from what users search, filter, sort and facet on, then build one document per thing they're looking for:

JSON
{
  "id": "article-482",
  "title": "Running Postgres on Kubernetes",
  "body": "...",
  "published": "2026-09-20",
  "author": { "id": 42, "name": "Cy Lee", "city": "London" },
  "tags": ["postgres", "kubernetes"],
  "comment_count": 17
}
  • Copy only what's searched or displayed in results; link to the database for the rest.
  • Precompute values that would otherwise need joins or aggregation at query time (comment_count, a product's average_rating).
  • Flatten where you can; use nested only where conditions must apply within one sub-object (Day 17).

Keeping denormalized copies in sync

When an author changes their name, every article document holding the old name is stale. Options, from simplest to most reliable:

1. Update by query for occasional changes:

HTTP
POST /articles/_update_by_query?refresh=true
{ "query": { "term": { "author": "ana" } },
  "script": { "source": "ctx._source.author = params.name", "params": { "name": "ana.k" } } }

On the course's lab index: updated 2 took_ms 62. Fine for rare changes affecting thousands of documents; costly if it touches millions or runs constantly. Run it as a task (wait_for_completion=false) for large updates.

2. Application dual writes: the code updates the database and then the index. Simple, but a crash between the two leaves them out of sync, and there's no record of what was missed.

3. Change data capture (CDC): read the database's change log (Debezium for PostgreSQL and MySQL, DynamoDB Streams, MongoDB change streams), publish changes to Kafka, and have an indexer service rebuild the affected search documents. Reliable and replayable; the standard approach at scale (see the Kafka course).

4. Periodic full rebuilds into a new index, swapped in with an alias (Day 19): a safety net that repairs any drift.

Data from several database tables is combined into one search document per searchable entity; changes flow from the database through change data capture and an indexer to update the affected documents.
Data from several database tables is combined into one search document per searchable entity; changes flow from the database through change data capture and an indexer to update the affected documents.

Denormalization trade-offs to accept

  • Eventual consistency: search results can lag the database by seconds. Show search results for discovery, and load authoritative details from the database.
  • Write amplification: one author change may update 10,000 article documents.
  • Bigger indices: repeated data costs storage, usually a reasonable price for fast queries.
  • Two sources of truth risk: the database is always the source of truth; the index can be rebuilt from it at any time. Design so that it can.

Check yourself

Elasticsearch can't join documents at query time, so filtering articles by author city requires the city to be in each article itself. The cost is updating those articles when an author moves, which is rare compared with searches.

A failure between writing the database and the index, such as a crash or timeout, leaves them inconsistent with no record of the missed update. Change data capture or an outbox makes the index update follow reliably from the database commit.

Rebuilding from the database into a new index repairs any drift and handles mapping changes, swapped in with an alias.

An author changing their city means updating every article they wrote. Accept it when such changes are rare compared with searches.

Elasticsearch can only filter and score fields in the same document, so each document should hold everything its searches need.