Question 1

Billions of chat messages, about 500k writes per second, append heavy, always queried as "messages in conversation X by time", no joins. Which store fits best?

Accepted Answer

Wide column store partitioned by conversation and time bucket Wide column stores (Cassandra, ScyllaDB, Bigtable) handle huge append rates and serve one partition sorted by time, which is exactly the access pattern, as Discord does. One PostgreSQL instance will not hold billions of rows at this write rate. A graph database optimizes traversals you do not need. Elasticsearch is a search index, not a durable primary for this.

Question 2

The hot query is: WHERE user_id = ? ORDER BY created_at DESC LIMIT 20. Which index serves it best?

Accepted Answer

A composite index on (user_id, created_at) Equality column first, then the sort column, lets the database seek to the user and read 20 rows already in order. created_at alone scans recent rows for all users. Two separate indexes need a merge and a sort. (created_at, user_id) puts the range column first, so it cannot seek by user.

Question 3

A browser dashboard shows live prices. Data flows only server to client, it must work over ordinary HTTP, and you want built in reconnection. Which transport fits?

Accepted Answer

Server-sent events SSE is a one way stream over plain HTTP with automatic reconnect and Last-Event-ID built into EventSource. Short polling at 100 ms wastes requests. WebSockets work but are bidirectional and need your own reconnect logic. Long polling reconnects after every message, adding latency and overhead.

Question 4

You need to send /api traffic to one service pool and /static to another, based on the URL path. Which load balancer?

Accepted Answer

Layer 7 (HTTP) load balancer Only an L7 balancer parses HTTP and can route on path. L4 sees ports and IPs, not URLs. DNS round robin picks a host before any request is sent. Anycast routes to the nearest site by network, not by path.

Question 5

Product images are served worldwide from one origin, and users far away see slow loads. Images rarely change. What is the fix?

Accepted Answer

Serve them through a CDN with long cache lifetimes and versioned URLs A CDN puts copies near users, and versioned file names let you cache for a year and still ship changes instantly. A bigger origin does not shorten the distance. Database replicas have nothing to do with image bytes. App server memory is still in one region.

Question 6

Chat users hold WebSockets to 50 gateway servers. User A on gateway 3 messages user B on gateway 41. How does the message reach B?

Accepted Answer

A connection registry or pub/sub channel keyed by user routes it to B's gateway You need to know or subscribe to where B is connected: a registry (user to gateway) or a per user pub/sub topic. Sticky sessions keep A on one gateway but do nothing to find B. Broadcasting to all gateways multiplies traffic by 50. Polling defeats the point of WebSockets.

Question 7

Ad clicks must be counted per minute by when the click happened. Up to 2% of events arrive as late as 2 minutes. What windowing works?

Accepted Answer

Event time tumbling windows with a watermark allowing about 2 minutes of lateness Event time assigns clicks to the minute they happened, and the watermark keeps windows open long enough for late data. Processing time counts arrival minute, misplacing late clicks. Dropping late events loses 2% of billable clicks. A nightly batch is accurate but fails the per minute need.

Question 8

A users table is sharded by user_id. Login needs lookup by email at 100k QPS. What do you add?

Accepted Answer

A separate index (email to user_id) partitioned by email A global index partitioned by the lookup key turns the email lookup into one read plus one read by user_id. Scatter gather to every shard at 100k QPS multiplies load by the shard count. A table can only be partitioned one way. Caching the whole table everywhere does not scale and goes stale.

Storage, real time and streaming system design questions

More system design topics

Practise these on your commute