Section 5: Everything About a Question Lives Together - Data Modeling
Executive Summary: List the access patterns first, and nearly every hot one is keyed by question. So content lives in PostgreSQL, sharded by question_id: a question's answers, comments, votes and revisions share its shard, making a page a single-shard read and a vote a single-shard transaction. We shard for restore time and blast radius, not throughput. IDs carry their logical shard, comment trees use an adjacency list plus a materialized path, and views keyed by user or tag come from derived stores.
Section 4 promised IDs that know where they live. Now say where that is. Don't open with "Postgres" or "MongoDB": a store is an answer, and the questions are the access patterns.
Access Patterns First
You: "Here's every way the product touches its data, with Section 3's peak rates."
| Access pattern | Peak rate (Section 3) | Keyed by | Served by |
|---|---|---|---|
| Rebuild a question page bundle | 80/s (≈4 queries each) | Question | One shard |
| Scores for a rendered page | ≈1,600/s | Question | One shard, replicas |
| My votes on this page (viewer-state) | 2,100/s | Question + user | One shard, replicas |
| Cast or change a vote | ≈350/s | Post + user | One shard, primary |
| Continue a comment thread | Small | Post + path | One shard |
| Revision history | Small | Post | One shard |
| Questions by tag (hot / new / unanswered) | Small | Tag | Redis hot sets, search index |
| User profile: "my answers" | Small | User | Derived user_posts |
Six of eight patterns, including all four hot ones, start from a question. That decides the shard key before we draw a table.
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