The HLD says the primary database is the system of record. It does not say what is in it. The data model is the part of a design that outlives every other part: services get rewritten, queues get swapped, but the tables and their keys stay for years, and changing them later means migrating billions of rows while the service keeps running. This lesson follows one request for a parcel's scan history down through the query, the index, and the partition, and shows how each design choice was decided by an access pattern written down first. By the end you can model a service from its queries outward, choose keys and indexes on purpose, and change a schema in production without an outage.
Difficulty: Intermediate · Know this first: Lesson 164, Lesson 28, Lesson 30
Each hop owns a slice of one history query. The bar is a worked example, not a measurement, but the shape is the lesson: when the model fits the access pattern, the index and partition hops are a few milliseconds; when it does not, one of them becomes the whole bar.
Move the sliders. The question is whether one month of the index fits in the database's memory. The partition hop in the bar above follows your answer.
rows/month = parcels/day × scans × 30 · table size = rows online × 300 bytes · index size = rows online × 40 bytes
one month of index = rows/month × 40 bytes, compared with 32 GB of database memory set aside for indexes.
One month of index fits in memory: lookups stay fast
Three questions. No score is saved anywhere — this is just you against the material.
Data modelling grows in four levels: first entities and relationships, then keys and indexes driven by access patterns, then partitioning, retention, and the relational-versus-key-value choice at scale, and finally changing a live schema without anyone noticing.
At the basic level you name the things the service stores and how they relate. Parcels, scans, recipients, couriers, depots. A parcel has many scans; a scan belongs to one parcel and one courier. Each fact lives in exactly one place: the recipient's address is stored once, on the parcel, not copied onto every scan.
Types:
Uses: the first sketch of any service's storage, and the shared vocabulary for the team.
Where it breaks: a model drawn from entities alone looks clean and serves no query well. The next level fixes that by starting from the queries.
Try it: Basic
Where should the recipient's delivery address live?
Now do it: Sketch the entities for a service you know and mark each fact you see stored in more than one place.
At the intermediate level the access patterns decide the physical design. Each pattern names its filter, its sort, its rate, and its latency target, and each one gets a key or an index that serves it as a direct read. A composite index puts equality columns first and range or sort columns last.
Types:
Uses: every service with a latency target. The habit is running EXPLAIN on every listed pattern before approval.
Scale and failure: every index speeds a read and slows every write, because each insert updates each index. Five indexes on a table that takes 2,000 inserts a second is 10,000 index updates a second. Index the listed patterns, not every column someone might filter on.
Try it: Intermediate
Support needs "parcels for this phone number" five times a second. What does the model add?
Now do it: List the access patterns of one table you own and run EXPLAIN on each. Note any sequential scan or explicit sort.
At the advanced level you design for the size the data will be. Time-series data like scans is partitioned by time so the working set stays small and retention is a partition drop. Data too large for one machine is sharded by a key that spreads load and keeps related rows together. And you choose the store by its access patterns: relational when you need joins, constraints, and transactions; key-value or wide-column when every query is by key at very high rates.
Types:
Uses: event histories, audit logs, metrics, and anything growing by hundreds of millions of rows a month.
Scale and failure: the failure at 10x is the index that no longer fits in memory; at 100x it is the single primary taking every write. The cost is operational: partitions to create ahead of time, shards to rebalance, cross-shard queries that become expensive. The trade is between query flexibility, which relational gives, and predictable performance at extreme scale, which key-value gives.
Try it: Advanced
Scan history will hold 1.4 billion rows online. Most reads are for parcels from the last week. What partitioning fits?
Now do it: Use the slider tool above with your own volumes. Find the retention at which one month of index stops fitting in memory.
At the master level the model is never finished, and the skill is changing it while the service runs. Every change goes through expand and contract: add the new shape alongside the old, write to both, backfill, move reads, stop writing the old, and only then remove it. Each step is deployable and reversible on its own, so a bad step is a rollback, not an incident.
Types:
Uses: every system with real traffic. The master-level review question for any migration is "what does the old code do at each step, and what is the rollback?"
Trade-off: expand and contract turns one change into five deploys over days or weeks. That is slower than an ALTER, and it is the price of never taking the service down to change its shape.
Try it: Master
A backfill must copy 1.4 billion rows into a new column while the service runs. What makes it safe?
Now do it: Write the expand-and-contract steps for one column rename in a table you own, with the rollback for each step.
The model is easiest to review as two artefacts side by side: the access-pattern table, and the schema that serves it. A reviewer reads the first, then checks that every row is served by something in the second.
-- AP1 current status by parcel 5,800/s p99 20 ms -> parcel_status PK -- AP2 history by parcel, newest 200/s p99 50 ms -> scan_history (parcel_id, scanned_at DESC) -- AP3 scans by courier, one day 50/s p99 200 ms -> idx (courier_id, scanned_at) -- AP4 parcels by phone (support) 5/s p99 500 ms -> recipient_lookup PK CREATE TABLE parcel_status ( parcel_id text PRIMARY KEY, status smallint NOT NULL, -- 0 created .. 3 delivered, only moves forward updated_at timestamptz NOT NULL ); CREATE TABLE scan_history ( parcel_id text NOT NULL, scan_id uuid NOT NULL, status smallint NOT NULL, depot_id int, courier_id int NOT NULL, scanned_at timestamptz NOT NULL, PRIMARY KEY (parcel_id, scanned_at, scan_id), UNIQUE (scan_id, scanned_at) -- dedupe; must include the partition key ) PARTITION BY RANGE (scanned_at); CREATE INDEX ON scan_history (courier_id, scanned_at); CREATE TABLE recipient_lookup (phone_hash bytea, parcel_id text, PRIMARY KEY (phone_hash, parcel_id));
Every table and index has a comment line above it naming the access pattern it serves. An index with no line is a write cost with no reader.
SQLite ships with Python, so this runs anywhere. It builds the same table twice, once with the index columns in each order, and prints the plan for the history query.
# python3 index_order.py
import sqlite3
for cols in ["parcel_id, scanned_at DESC", "scanned_at DESC, parcel_id"]:
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE scan_history (parcel_id TEXT, scanned_at TEXT, status INT)")
db.execute(f"CREATE INDEX ix ON scan_history ({cols})")
plan = db.execute("EXPLAIN QUERY PLAN SELECT status FROM scan_history "
"WHERE parcel_id = ? ORDER BY scanned_at DESC LIMIT 20", ("P123",)).fetchall()
print(f"({cols}):", " | ".join(row[-1] for row in plan))
Expected: with parcel_id first, the plan is a SEARCH using the index, with no separate sort. With scanned_at first, it can no longer search by parcel_id: the plan walks the whole index or scans the table, and may add a step for the ORDER BY. Same columns, different order, a different query.
A delivery platform I worked with kept every scan in one unpartitioned table for "simplicity". For a year it was fine. Then the retention job, a nightly DELETE of rows older than 90 days, started running into the morning, and the morning was when couriers started scanning.
The fix took three weeks of expand and contract: a new table partitioned by month, dual writes, a throttled backfill of 90 days, a read switch, and the old table archived. After it, retention was a scheduled partition detach that took under a second. The model review checklist gained a line: every append-heavy table states its retention and how rows leave.
Model the storage for a URL click-analytics service: every click on a short link is recorded, link owners see clicks per day for the last 90 days, and a fraud team searches clicks by IP address.
How many clicks a day at peak? How far back must per-day counts go, and must raw clicks be kept that long? Do owners need per-hour or per-country breakdowns? How fast must a new click show up in the counts? Is IP address personal data with its own retention rule?
Say 500 million clicks a day: about 5,800 writes a second on average and 30,000 at peak. Raw clicks at 120 bytes are 60 GB a day, 5.4 TB for 90 days. Daily counts per link are tiny by comparison: ten million links × 90 days.
Raw clicks in a table partitioned by day, kept 30 days; a click_counts_daily table keyed by (link_id, day) updated by a stream job, which is the precomputed answer for owners; an ip_lookup keyed by (ip_hash, day) for the fraud pattern. Access-pattern table first, then the schema, with an EXPLAIN for each pattern.
Counting at query time is always exact and costs a scan per dashboard load; precomputed counts are instant and can lag a minute. Keeping raw clicks 90 days helps fraud work and costs 5 TB; 30 days with aggregates beyond is cheaper. Hashing IPs protects privacy and makes range searches by subnet impossible.