Platform Engineering · Lesson 165

Data Modeling and Schema Design: Entities, Access Patterns, Keys, and Migrations

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

Run it as:

When it breaks, I check

One thing to remember

Where the time goes

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.

Try it: size the history table

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.

480,000,000rows / month
1,440,000,000rows online
~432 GBtable size
~58 GBindex size
~19 GBone month of index

One month of index fits in memory: lookups stay fast

Check yourself

Three questions. No score is saved anywhere — this is just you against the material.

Basic to master

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.

Basic — Entities, relationships, and one fact in one place

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:

  • Entities: the nouns, each with a primary key that never changes meaning.
  • Relationships: one-to-many through a foreign key, many-to-many through a join table.
  • Attributes: columns with a type and a rule (not null, allowed values).
  • Natural keys versus surrogate keys: a tracking number from the label, or an id you generate.

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.

Intermediate — Access patterns drive keys and indexes

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:

  • Primary keys: the identity and, in many stores, the physical order of rows.
  • Composite and covering indexes: serve a filter plus a sort, or answer from the index alone.
  • Precomputed rows: current status stored ready, kept correct in the same transaction.
  • Lookup tables for secondary access: phone to parcel ids, maintained on write.

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.

Advanced — Partitioning, retention, and choosing the store

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:

  • Range partitioning by time for append-heavy data; hash partitioning or sharding by key for spread.
  • Retention tiers: hot partitions online, warm in cheaper storage, cold archived to object storage.
  • Single-table key-value design: partition key plus sort key, modelled directly from access patterns.
  • Hot keys: one parcel or one courier receiving a disproportionate share of traffic.

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.

Master — Changing a live schema without an outage

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:

  • Expand and contract for renames, type changes, and table splits.
  • Online backfills in small batches with a throttle, a checkpoint, and a verification query.
  • Online index builds that do not lock writes, and lock-timeout guards on every migration.
  • Data contracts between teams: a schema change in a table another team reads is an API change.

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.

Go deeper

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.

See the column-order rule in a real database

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.

One time it broke

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.

Timeline

  • Month 11The nightly delete takes five hours. Nobody notices; it finishes before 06:00.
  • Month 13It takes nine hours. Replication lag climbs to minutes during the delete, and read replicas serve stale statuses until mid-morning.
  • Month 14, 07:30The delete holds locks while couriers start scanning. Ingest slows, the queue backs up, and customers see "created" for parcels already out for delivery.
  • Month 14, 08:10The delete is killed. The rows it had not reached stay; the table keeps growing.

What changed after

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.

If you remember nothing else

Design exercise

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.

Clarifying questions to ask

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?

Rough sizing

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.

Shape of the answer

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.

Trade-offs to name

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.

Saved on this device, in this browser.
Next in this series · Lesson 166
Low-Level Design in Depth: Classes, Interfaces, SOLID, and Sequence Diagrams

The HLD said one component, the scan processor, owns the status rule. The low-level design says how: which classes exist, what each one is responsible for, which interfaces they talk through, what order the calls happen in, and…

Go to Lesson 166 →

Or browse the full course from the Lessons index.