Section 5: One Bike, Four Shapes - Data Modeling
Executive Summary: Maya's $450 Trek exists in four places at once: a row in Postgres, six sets of photo files in object storage, a document in Elasticsearch, and one or more conversations in Cassandra. We place each shape by its access pattern, choose a time-ordered ID over UUIDv4, keep search a disposable copy, and fix the listing lifecycle every later section relies on.
The interviewer has seen your endpoints: "What sits behind these on disk?" Don't start with tables. List the reads and writes first; the stores fall out of them.
Access Patterns First
| Access pattern | Rate (peak) | What it needs | Store |
|---|---|---|---|
| Listing by id (detail page) | ~21,000 QPS | Point lookup, authoritative price and status | Redis, then Postgres replicas |
| Create / edit / mark sold | ~300 writes/s | Transaction with its outbox row | Postgres primary |
| Seller's own listings | Low | Range by seller_id, newest first |
Postgres |
| Expiry sweep | ~23/s | Active listings past expires_at |
Postgres (partial index) |
| Search near me | ~10,500 QPS | Geo + text + filters + ranking | Elasticsearch |
| Messages in a conversation | ~1,400 sends/s | Append, read by time | Cassandra |
| User inbox | High, per open app | Conversations by recency | Cassandra |
| Photo bytes | ~270K requests/s | Immutable blobs at the edge | Object store + CDN |
Interviewer: "Four storage technologies for a classifieds site? Why not just Postgres?"
You: "The listing belongs in Postgres: ~300 writes a second, read-heavy, and I need one transaction that writes the listing and its outbox event together. Search is a different job: 10,500 geo-plus-text queries a second with ranking. PostGIS would do under ~1M active listings, not here. Messages are a third: 7.3 TB a year, append-only, nothing to join. One database would put Sunday's search peak and chat volume in posting's failure domain."
Sign in to continue reading
The rest of this lesson is available with a free account. Signing in with Google or Microsoft is free.
Sign in to read the full lesson