Data Locality
Every network hop costs time and money. Move the computation to the data, not the data to the computation.
The story
A dashboard page loads in about 300 ms. Then, for data-residency reasons, the team moves the database from a US region to an EU one. The app servers stay in the US for now, since "it's only one hop".
The next day the same page takes 3.5 seconds.
Nothing about the code changed. But the page runs 40 queries one after another: load the account, then its projects, then for each project its latest run, then for each run its owner… Each query used to be a round trip of about 0.5 ms inside one data centre. Now each one crosses the Atlantic at about 80 ms.
| Per query | × 40 sequential queries | |
|---|---|---|
| Same data centre | 0.5 ms | 20 ms |
| Across the Atlantic | ~80 ms | 3,200 ms |
The query count didn't change. The distance did, and distance is physics: light in fibre covers roughly 200 km per millisecond, and no amount of tuning makes it faster.
Data locality is the habit of keeping data and the code that uses it close together, and when you can't, crossing the distance as few times as possible.
The idea: three distances
Each step out is roughly 1,000× slower and, between zones and regions, also costs money: cloud providers charge for data transfer that crosses zones or regions, so a chatty cross-zone service shows up on the bill as well as in the latency.
There are four ways to keep the hops down.
1 · Put compute and data in the same place
App servers and their primary database go in the same region, ideally the same zone for the hottest path. If users are global, put read replicas or caches in each region and accept that writes go to one home region.
2 · Cross the distance fewer times
The story's page wasn't slow because of one query. It was slow because of forty sequential ones. Batch them:
Same data, 2 round trips instead of 41. At 80 ms each, that's 160 ms instead of 3.3 seconds. The same fix works between services: one batch endpoint instead of a loop of single calls.
3 · Keep related data on the same shard
When you shard, choose a key that keeps things queried together on the same shard. A SaaS app that shards by tenant_id answers almost every request from one shard. Shard the same app by row_id and every dashboard becomes a scatter-gather across all of them.
Scatter-gather is as slow as the slowest shard, and its chance of hitting a slow one grows with every shard you add.
4 · Send the question, not the data
If you need the total revenue per country, don't pull 10 million order rows into the app and add them up there. Send a GROUP BY to the database and get back 200 rows.
The same idea at a bigger scale is why Hadoop and Spark schedule tasks on the machines that already hold the data blocks, and why CDNs run code at the edge, next to the user.
The trade-offs
- Locality fights availability. Everything in one zone is fast, and everything in one zone fails together. The usual compromise is the primary and app servers in one zone for speed, with a synchronous standby in another zone for failover.
- Locality fights flexibility. Sharding by tenant is fast for per-tenant queries and slow for "all tenants, last 7 days". Send those to a separate analytics store fed by change data capture (CDC).
- Pushing compute into the database puts load on the hardest part of the system to scale. Aggregations are fine. Heavy business logic in stored procedures usually isn't.
- Residency rules can override all of this. EU data may have to live in the EU. Then move the compute to where the data must live, not the other way round, which is what the team in the story did in the end.
Cheat sheet
| Symptom | Locality fix |
|---|---|
| Page fast locally, slow in production | Count round trips × latency per hop. It's probably distance. |
| N+1 queries / a loop of service calls | Batch with IN (…), a JOIN or a bulk endpoint |
| App and DB in different regions | Move them together. Use regional replicas or caches for global reads. |
| Every request touches every shard | Re-shard by the entity queries group on (tenant, user) |
| App pulls millions of rows to aggregate | GROUP BY in the DB, or a materialised view |
| Cross-zone data transfer bill growing | Find the chatty pair of services and put them in the same zone |
| Global users, single write region | Reads local (replica/cache/CDN), writes to the home region |
Numbers to remember: 0.5 ms in a data centre · ~80 ms across the Atlantic · ~200 km per ms in fibre.
Say it in the interview
"I'd keep the app tier and the primary in the same region and zone, and batch anything that would be a loop of calls, because 40 sequential round trips at 80 ms is over three seconds no matter how fast each query is. I'd shard by tenant_id so a tenant's requests hit one shard, and push aggregations down to the database instead of pulling rows up."
Go deeper
- Back-of-the-Envelope Estimation: the latency ladder these numbers come from.
- SD 2 · Chapter 1 — The Speed of Physics: why distance sets a floor no code can beat.
- SD 2 · Chapter 7 — Sharding: choosing a key that keeps related data together.