The hardest habit to unlearn coming from SQL is normalising. In a relational database, storing a city name in forty thousand rows is a mistake you fix with a foreign key. In Elasticsearch it is usually the right answer.
This lesson is about why, and about the four ways to model a relationship when flattening is not enough.
Start with the question, not the data
Before anything else: what is one search result?
For StayHub the answer is a listing. A guest searches and gets back listing cards, so one document is one listing — not a booking, not a review, not a host. Everything else in the model is downstream of that one sentence.
This sounds obvious and it is where most bad models start. A team indexes their
orders table because it is the biggest one, then discovers that the search page shows
products. Or they index everything into one index with a type field, and every query
carries a filter that would not exist if the model were right.
An index holds one kind of thing. If a page shows two kinds of results, that is two indexes and one multi-index search, not one index with a discriminator.
Denormalise on purpose
Once you know what a result is, the document should contain everything that result needs. In Postgres, a StayHub listing's amenities live in a join table and its images in another. In the search document they are just there:
doc: dict[str, Any] = {
"public_id": str(prop.public_id),
"title": prop.title,
...
"city": prop.city,
"amenities": [a.slug for a in prop.amenities],
"price_per_night": float(prop.price_per_night),
"cover_image_url": prop.cover_image_url,
}The reason is not laziness. It is that a join at query time is exactly the work the index exists to avoid. A result card needs the cover image and the amenity list; fetching those separately for twenty results is twenty round trips, or a second database query that reintroduces the load you built the index to remove.
Elasticsearch has no joins across indexes at all, so the choice is not "denormalise or join" but "denormalise or make two queries from your application".
What denormalising costs
Documents get stale. The city name is copied into every listing in that city, so renaming the city means rewriting all of them. In a normalised store you update one row.
Documents get large. _source stores the whole JSON, so every copied
field costs disk on every document.
Updates fan out. This is the one that actually bites, and it is worth sizing before you commit. A host changing their display name, when the name is copied into every listing they own, is an update to a handful of documents — fine. The same field copied into every booking would be thousands.
So the test is not "is this duplicated" but "how many documents change when this changes, and how often does it change?" A city name copied 40,000 times is fine, because cities do not get renamed. A live availability count copied 40,000 times is not, because it changes every minute.
The four ways to model a relationship
1. Flatten it in
Copy the fields you need onto the parent document. This is the default and it should be your first instinct.
{ "title": "Cedar Cabin", "host_name": "Ana", "host_rating": 4.9 }Right when the related thing is small, changes rarely, and you only need a few of its fields.
2. An array of objects
{ "title": "Cedar Cabin", "images": [
{ "url": "/a.jpg", "caption": "The deck" },
{ "url": "/b.jpg", "caption": "Kitchen" }
] }Right when you need the objects returned but never need to query two of their fields together. Remember lesson 4: the arrays are flattened in the index, so "an image whose caption is 'Kitchen'" is not a question this can answer correctly.
3. nested
Same shape, mapped as nested, queried through a nested clause. Right
when you genuinely must query two fields of one array element together — "a review by bob
rated 5" — and only then. Each element becomes its own Lucene document, updating the parent
rewrites all of them, and aggregating requires a nested wrapper everywhere.
4. join fields (parent-child)
Elasticsearch does have a real parent-child relationship, within a single index. Children are separate documents that can be updated independently of the parent.
{ "mappings": { "properties": {
"listing_review": { "type": "join", "relations": { "listing": "review" } }
} } }It is almost always the wrong answer, and it is worth knowing exactly why so you can rule it out
quickly. Parent and child must live on the same shard, so the shard distribution is driven by the
relationship rather than by data volume. Queries across the relation
(has_child, has_parent) are substantially slower than the flat
equivalent. And the model is harder to reason about than either flattening or nesting.
The narrow case where it earns its place: a small number of parents, a very large number of children, and children that change constantly. Product with millions of price updates. If you are not in that shape, flatten or nest.
5. Two queries from your application
Frequently the best answer, and frequently forgotten because it feels like giving up. Search Elasticsearch for the ids, then fetch the full records from your database by primary key. That is one indexed lookup per result set, not per result, and your database is extremely good at it.
This keeps the search document small — only the fields you match, filter, sort and aggregate on — and it means the data a user sees is never stale, because it came from the source of truth. The cost is a second round trip and a page that cannot render until both return.
StayHub does not do this, deliberately: the result cards render straight from
_source, which is the whole reason the index is worth maintaining. But for a page where
correctness matters more than latency, fetch-by-id is the safer design.
The same data, three ways, side by side
Reviews on a listing, to make the difference concrete. As a flat array of objects:
{ "reviews": [ {"author": "ana", "rating": 5}, {"author": "bob", "rating": 1} ] }
// "any 5-star review?" -> correct
{ "term": { "reviews.rating": 5 } }
// "a 5-star review BY BOB?" -> WRONG, matches
{ "bool": { "must": [ {"term": {"reviews.author": "bob"}},
{"term": {"reviews.rating": 5}} ] } }As nested, the same question is scoped to one element and answers correctly:
{ "nested": { "path": "reviews", "query": { "bool": { "must": [
{ "term": { "reviews.author": "bob" } },
{ "term": { "reviews.rating": 5 } }
] } } } }And flattened, which is what you do when the query above is not one anyone asks:
{ "rating_average": 4.9, "rating_count": 12, "reviewer_slugs": ["ana", "bob"] }That third version is the one StayHub uses, and it is the one to reach for by default. It answers "how well rated is this" and "has ana reviewed it" in one term lookup each, costs three fields, and has none of nested's write amplification. The individual reviews are still in Postgres, where the listing page reads them — they were never search data in the first place.
That last point generalises. A field belongs in the search document if it is used to find or rank or display in a result card. Data that is only read once the user has clicked through belongs in your database.
How to choose, quickly
Ask three questions in order.
Do I need to query these fields together? If no, flatten or use an object array. If yes, keep going.
How many children per parent, and how often do they change? Tens, rarely →
nested. Millions, constantly → consider join. In between →
still nested.
Does the data need to be current to the second? If yes, do not put it in the index at all. Store the id and fetch it live.
One index or many?
The other structural decision, and the one where "it depends" is genuinely the answer.
One index, filtered. All tenants' data together, every query carrying
{"term": {"tenant_id": ...}}. Simple, efficient, and one forgotten filter is a data
leak. If you go this way, the filter belongs in one place in your code that every query must pass
through — never in the individual queries.
An index per tenant. Isolation by construction, and a per-tenant restore or delete becomes trivial. The cost is real: every index carries shard overhead and cluster-state entries, so a thousand small tenants is a thousand shards and a cluster that struggles to do anything.
An index per time period — logs-2026.08.22 — for
anything append-only that expires. Deleting old data becomes dropping an index, which is instant,
rather than a delete-by-query, which rewrites segments. This is what index lifecycle management
automates, and it is the correct model for logs, metrics and events.
A middle path worth knowing: routing. Keep one index but pass a routing value so all of a tenant's documents land on the same shard. Queries for that tenant then hit one shard instead of all of them. It is a real win at scale and it introduces a real hazard — a large tenant creates a hot shard that cannot be split.
What not to put in a document
Three things, each of which someone tries.
Anything that only lives here. The moment a field exists in Elasticsearch and nowhere else, you can no longer rebuild the index, and every operational option in lessons 15 and 17 narrows.
Counters you increment in place. Elasticsearch has no atomic increment worth relying on across concurrent writers, and every update rewrites the whole document. Keep counters in your database or in Redis and copy the value across when it is worth updating.
Large blobs you never search. A full document body, a base64 image. They inflate
_source, and _source is read for every hit. If you must keep them, use
"index": false, or better, store a URL.
Model for the queries, not the data
The relational instinct is to model the domain and then write queries against it. Here it goes the other way: write down the queries first, then build the document that answers them cheaply.
StayHub's search page needs five things — free-text matching over title, description and city; filters on price, guests, type and amenities; sorting by price, rating or distance; facet counts for the filter panel; and enough data to render a card. Every field in the mapping is there because one of those five needs it, and nothing is there because the database has it.
That is why status is in the mapping but never filtered on. Only published listings
are indexed at all — unpublishing removes the document rather than flagging it:
should_be_visible = prop.status == PropertyStatus.PUBLISHED and not prop.deleted
if not should_be_visible:
return remove_property(str(prop.public_id), es=client, raise_on_error=raise_on_error)Filtering at query time would work too. Removing is better: every query would otherwise pay for
the filter, and one forgotten .filter() leaks a draft listing into public results.
Absent is safer than filtered — and that is a modelling decision, not a query one.
The whole document, and where each field came from
StayHub's mapping is worth reading as a set of answers rather than a schema. Every entry is one of the five requirements above:
"properties": {
"public_id": {"type": "keyword"},
"title": {"type": "text", "analyzer": "stayhub_text"},
"description": {"type": "text", "analyzer": "stayhub_text"},
"city": {
"type": "text",
"analyzer": "stayhub_text",
"fields": {"raw": {"type": "keyword"}},
},
"state": {"type": "keyword"},
"country": {"type": "keyword"},
"property_type": {"type": "keyword"},
"room_type": {"type": "keyword"},
"status": {"type": "keyword"},
"amenities": {"type": "keyword"},
"price_per_night": {"type": "scaled_float", "scaling_factor": 100},
...
"max_guests": {"type": "integer"},
...
"rating_average": {"type": "scaled_float", "scaling_factor": 100},
"rating_count": {"type": "integer"},
"cover_image_url": {"type": "keyword", "index": False},
...
"location": {"type": "geo_point"},
"created_at": {"type": "date"},
},title and description are text because they are matched.
property_type and amenities are keyword because they come
from tick-boxes. city is both, because a guest types it and the filter panel
groups by it. cover_image_url is "index": false because it is only ever
rendered. location exists because of one query, and created_at exists
because of one sort.
What is not in there is as informative. No host name, because the card does not show one. No booking history. No availability — and that omission is a real decision worth looking at.
The field that deliberately is not there
A booking site obviously wants "available on these dates". StayHub's API accepts the parameters and does not filter on them:
"""Search published listings.
⚠️ `checkIn` / `checkOut` are accepted but do NOT filter results yet. Date availability lives
in Postgres (the bookings table), not in the index, so filtering on it here would mean either
denormalising every booking into the document or a second query per hit. The listing page
checks availability properly. Saying so out loud beats a filter that quietly does nothing.
"""Work through the options and the trade is clear. Denormalising every booking into the listing
document means every booking, cancellation and date change rewrites a listing document — the
volatile-field problem, at the highest write rate in the system. A date_range array of
blocked periods is better and still rewrites the document on every booking. A second query per hit
puts the database back in the search path.
The fourth option, and the one a mature system usually lands on, is a separate availability index keyed by listing and date, written by the booking service, joined in the application. That is real work, and the honest thing at StayHub's size is to say the filter is not implemented rather than ship one that silently does nothing.
Sizing the fan-out before you commit
The "how many documents change" question deserves an actual number, and the arithmetic is quick.
Take a field you are thinking of copying in. Multiply how many documents carry a copy by how
often the source value changes. StayHub's rating_average is copied into one document
per listing and changes when a review lands — say 12 listings and a handful of reviews a day,
which is nothing. Now imagine the same field on a booking document: 40,000 bookings, and a
single new review rewrites every booking for that listing.
The rule of thumb worth carrying: if one source change rewrites more than a few hundred documents, or if it happens more than a few times a minute, that field does not belong in the document. Either fetch it live, or accept a scheduled refresh and be explicit that the value is minutes old.
There is a middle option people forget. Copy a bucketed version instead of the exact one — a rating band rather than the average, a price tier rather than the price. The bucket changes far less often than the value, and it is usually all the filter panel needed.
Signs the model is wrong
Worth recognising early, because all four are cheap to fix in week one and expensive in month six.
Every query has the same filter in it. That filter is a modelling decision leaking into the query layer. Either it should be a separate index, or the documents that need filtering out should not be indexed.
You are making a second query to render results. Either denormalise the missing fields in, or commit to fetch-by-id properly and stop storing display fields.
Your aggregations are wrong in a way you cannot explain. Almost always an
array-of-objects that needed to be nested, or an aggregation on a
text field instead of its keyword sibling.
A single field change rewrites most of the index. Something volatile got denormalised. Move it out and fetch it live.
None of these is fatal on its own. What makes them worth watching is that every one of them gets more expensive with the size of the index, and the fix is always a reindex — so the cheapest day to notice is the first one.
With the model settled, the next question is how documents actually get in there — and what "indexed" does and does not mean.