LESSON 5.3
Modeling for access patterns
Start from the queries, choose partition keys that spread load, and denormalize on purpose.
6 min read
Relational modeling starts from the data: what entities exist and how they relate. At scale, and especially with NoSQL stores, you start from the other end: what queries will the system run, how often, and how fast must they be? Then you shape the data so each important query is cheap.
List the access patterns first
Before choosing tables or keys, write down the queries. For a simple social app:
| Access pattern | Frequency | Latency need |
|---|---|---|
| Get a user's profile by id | very high | fast |
| Get a user's latest 50 posts | high | fast |
| Get the home feed: recent posts from followed users | very high | fast |
| Create a post | moderate | fast |
| Count likes on a post | very high | fast, approximate is fine |
Each row in that table should map to an index, a key or a precomputed structure. If a frequent query needs a scan or a big join, the model isn't done.
Partition keys
A partitioned database (DynamoDB, Cassandra, a sharded SQL cluster) splits data across nodes by a partition key. Queries that include the key go to one node and are fast; queries that don't must ask every node.
A good partition key:
- Has many distinct values (user ids, not countries),
- Spreads traffic evenly, avoiding a celebrity or a hot date that gets most requests,
- Matches the main query, so common reads touch one partition.
Inside a partition, a sort key orders items, so "latest 50 posts by user 7" is a single, ordered range read: partition by user_id, sort by created_at descending.
Denormalize on purpose
Shaping data for reads usually means copying it.
Compute on read
- Writes are simple
- Always exact
- Slow when the read does lots of work
Pick it when: Rare queries, or cheap ones.
Precompute on write
- Reads are one lookup
- Writes do more
- Copies can drift if an update fails
Pick it when: Hot reads that would otherwise be expensive.
The classic example is a news feed. Fan-out on read fetches recent posts from every followed user at read time, which is slow when you follow hundreds. Fan-out on write pushes each new post's id into every follower's feed list when it is posted, so a read is one lookup. Real systems mix both: precompute for normal users, and merge in posts from celebrities with millions of followers at read time.
Counters and aggregates
Counting rows at read time doesn't scale for popular items. Keep counters as columns, update them in the same transaction as the event, or, for very hot counters, accumulate increments in memory or a queue and flush them in batches. Many counters, like views and likes, can be approximate; say so and pick the cheaper design.
Putting it together
A good data model answer names the main access patterns, the key and sort order that serve each one, and any denormalized copies with how they stay in sync. It also says what is the source of truth: when copies disagree, which one wins and how the others are rebuilt.
Try it
Real-time Leaderboard HLD
A design that lives or dies by its data structure choice.