Interview Walkthrough: Design the Data Layer

"Design the data layer for a social platform."

Databases rarely appear as a standalone prompt. They appear as the follow-up — "where does that data live?" — inside every other design. This walkthrough runs the decision properly, once, so you can compress it into two minutes when it comes up.

Step 0 — Scope it before you design

• How many users, and what's the read/write ratio?
• Which entities matter — users, posts, follows, feeds, messages?
• Which data needs transactions, and which can be eventually consistent?
• Is this single-region or global? Any data residency requirements?
• What are the query patterns — by ID, by relationship, by range, analytical?

Then commit:

"I'll assume 500 million users, 100 million daily active, read/write around 100:1. Entities: users, posts, follows, feeds, and direct messages. Global, with EU residency requirements. Let me size it, then choose storage per access pattern rather than picking one database for everything."

Step 1 — A quick BOTEC

DAU                = 100M
Posts/user/day     = 2         -> 200M posts/day
Feed reads/user/day = 50       -> 5B reads/day

Write QPS  = 200M / 100,000    = ~2,000/sec
Read QPS   = 5B / 100,000      = ~50,000/sec   (peak ~150,000)

Post size (text + metadata) = ~1 KB
Storage/day = 200M * 1 KB      = 200 GB/day
Per year, 3x replicated        = ~220 TB/year

"2,000 writes/sec is modest — a well-tuned primary handles that. 150,000 peak reads is the real number, and it says cache plus read replicas before anything exotic. 220 TB/year says posts outgrow a single node within the first year, so posts get partitioned."

Step 2 — Enumerate the access patterns

This is the step that produces the design. For each entity: how is it read, how often, and what guarantees does it need?

Step 3 — Replication

"Accounts get semi-sync because losing a password change is a security incident. Feeds get plain async because if we lose a feed entry we can rebuild it from posts — the data is derived, so durability doesn't have to be paid for twice."

Step 4 — Partitioning

The decision that cannot be cheaply reversed. Choose the key from the dominant query, then check it for skew.

Step 5 — Secondary indexes and routing

Search users by name — an attribute that is not the partition key.

"Reads dominate, so a global index partitioned by term rather than local indexes: a name lookup hits one index shard instead of scatter-gathering across all of them. I'd accept that it's updated asynchronously, so a brand-new account is briefly unsearchable. Routing goes through a consensus-backed map — ZooKeeper or equivalent — so two clients never disagree about which node owns a partition."

Step 6 — The mature picture

Deep Dives & Follow-up Questions

"A user posts and doesn't see it in their own feed. Why, and what's the fix?"

Replication lag — their read went to a follower or a cache that hasn't caught up. The fix is read-your-writes, and I'd do it with a version watermark rather than routing all self-reads to the leader: the write returns a version, the client echoes it, and any follower caught up to that version can serve the read. Routing every self-read to the primary works but loads the one node that's already the write bottleneck.

"Why not put everything in Postgres?"

For a long time you should — and I'd genuinely check whether we can. One node handles billions of rows and tens of thousands of transactions per second, and staying relational keeps ACID and referential integrity for free. What breaks it here isn't row count, it's the access patterns: the social graph is a traversal problem where a three-hop query becomes a self-join that explodes, and analytics is a columnar scan that a row store answers by reading every byte of every row. Those two justify separate stores. Sessions and feeds go to a cache for latency, not because Postgres can't hold them.

"You're sharding posts by user_id. How do you get a user's feed?"

The feed is a fan-out of many other users' posts, so it can't be answered from one shard — which is exactly why feeds are precomputed rather than queried. On write, the post is fanned out to followers' feed entries in the cache; on read, the feed is a single key-value lookup by user ID. The alternative, querying every followed user's shard at read time, is a scatter-gather across hundreds of shards and its latency is the slowest one. For celebrities I'd invert it — pull their posts at read time instead of fanning out to 100 million feeds.

"A shard is running hot. What do you do?"

First establish why, because the fixes differ. If it's data volume, split the partition. If it's a single hot key — a celebrity — splitting doesn't help because the key can't divide; that needs caching in front, or splitting the key artificially into sub-keys the application recombines. If it's a query pattern that turned out to be shard-local, I'd revisit the partition key, which is a migration and the reason I'd have stress-tested key distribution before committing.

"How do you add a node without a huge migration?"

Never let the mapping depend on hash mod n — adding a node changes the modulus and remaps almost every key. Consistent hashing with virtual nodes, or a fixed large partition count as a stable indirection layer, so adding a node moves whole partitions rather than recomputing keys. And I'd throttle the rebalance: moving terabytes while serving traffic can cascade into an outage if it isn't rate-limited.

"EU residency. How does that change things?"

Region becomes part of the partitioning decision rather than something bolted on. EU users' data is homed in EU shards and never replicated outside; the routing map records the home region per user. That interacts with the global secondary index, since an index by term would otherwise span regions — so I'd keep it region-scoped and search within region. It's a good example of a compliance requirement changing a data-layer decision rather than just an access-control one.

"How do analytics stay current without hurting production?"

Never query production for analytics — one scan can evict the working set from cache and hurt every user. I'd stream changes out via change data capture into a columnar warehouse, which is also logical replication doing double duty: because it captures row-level logical changes rather than engine bytes, it can feed a completely different system. Analytics then runs on the warehouse and is minutes stale, which for aggregate reporting is fine.

Now do it live

The next section drills these as standalone probes.