SYSTEM DESIGN · DESIGN PROBLEMS · BUSINESS PLATFORMS · MEDIUM · 51 OF 51

Design a Q&A Platform like Stack Overflow: Votes, Reputation, Search and Hot Ranking

Design a question and answer site: questions, answers, comments and votes, reputation earned from votes, tags, full-text search and duplicate suggestions, a hot list that decays with age, and caching for traffic that is mostly anonymous visitors from search engines. Why one well-tuned SQL database goes a long way, and how to adapt it for many separate communities or private company instances.

Reported atPhonePe

Try it on the practice board Drag the blocks, wire them up, and check your design against the reference.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
  35. 35
  36. 36
  37. 37
  38. 38
  39. 39
  40. 40
  41. 41
  42. 42
  43. 43
  44. 44
  45. 45
  46. 46
  47. 47
  48. 48
  49. 49
  50. 50
  51. 51
1 / 18

Press play to hear the narration, or step through at your own pace.

Notes

Listen

Listen. Users ask questions with tags, answer them and comment on both. Anyone signed in can vote a post up or down, once. The asker accepts one answer. Votes earn reputation, and reputation unlocks privileges like editing or closing questions. People search by text and tags and browse hot, newest and per-tag lists. Posts keep a revision history, and authors are notified of new answers and comments.

The qualities. Question pages must load fast and be server-rendered with clean, stable URLs, because search engines send most of the traffic, and a slow or script-only page ranks lower. One vote per user per post, strictly. Reputation must be exact and explainable. A new answer must appear at once for its author, but anonymous readers can see a page up to a minute old. Reads must stay up even when writes have trouble.

Functional
  • ask a question with tags
  • answer; comment on both
  • vote up or down, once each
  • asker accepts one answer
  • reputation from votes;
  • privileges at thresholds
  • search by text and tags
  • lists: hot, newest, by tag
  • edits with revision history
  • notify on answers, comments
Non-functional
  • question page p95 ≤ 200 ms
  • pages server-rendered and
  • crawlable: search engines
  • bring most traffic
  • one vote per user per post
  • reputation exact, auditable
  • new answer visible to its
  • author at once, others ≤ 1 min
  • high availability for reads

Ask scale

Scale. A billion and a half page views a month is about six hundred a second, eighteen hundred at peak, and eighty percent are anonymous, so mostly cacheable. Writes are tiny: thousands of questions and answers a day, about three and a half votes a second. All posts are about two hundred gigabytes, four hundred with revisions, and a billion votes are twenty gigabytes. The whole working set fits in the memory of one large database server.

Back of the envelope
1.5B page views/month
  / 2.5M s ≈ 600/s, peak ≈ 1,800/s
  80% anonymous → CDN cacheable
writes/day: 10k questions,
  15k answers, 100k comments,
  300k votes ≈ 3.5 votes/s
posts: 65M × 3 KB ≈ 200 GB
  + revisions ≈ 400 GB
votes: 1B rows × 20 B ≈ 20 GB
→ fits in RAM of one big server

Define API

The API. Question pages live at an id plus a readable slug, and the id alone redirects to the canonical URL, which keeps search engines happy. Lists take a sort and a tag. Voting is a put of plus or minus one on a post, so sending it twice changes nothing, and delete removes the vote. Accept marks one answer. Search takes text and tags, and a similar-questions call powers duplicate suggestions while someone types a title.

API
GET  /questions/{id}/{slug}  (HTML)
GET  /questions?sort=hot|new&tag=
POST /questions { title, body, tags }
POST /questions/{id}/answers
PUT  /posts/{id}/vote { value: 1|-1 }
DELETE /posts/{id}/vote
POST /answers/{id}/accept
GET  /search?q=redis+ttl&tag=redis
GET  /questions/similar?title=…

Data model

One posts table holds both questions and answers, with a parent id linking answers to their question. Score, answer count and views are stored on the row, denormalized, so a page never counts votes on the fly. Votes have a primary key of post and user, which enforces one vote each. Reputation is an append-only table of events, plus a cached total on the user. Markdown is rendered to HTML once, on save, and kept.

Tables (SQL)
posts(id, type Q|A, parent_id,
  owner, title, body_md, body_html,
  score, answer_count, accepted_id,
  views, last_activity)
votes(post_id, user_id, value, at)
  PRIMARY KEY (post_id, user_id)
tags(id, name, count)
post_tags(post_id, tag_id)
rep_events(user, post, delta,
  reason, at)  append-only
revisions(post_id, rev, body, editor)

High-level design

The read path. An anonymous visitor's request for a question page is served from the CDN, cached for up to a minute. A miss, or a signed-in user, reaches stateless web servers, which render the page from Redis-cached fragments and, on a miss, from the database, mostly by primary key. At eighteen hundred views a second with most of them cached, the database sees a few hundred queries a second, easily handled by a primary and a couple of replicas.

The write path. A new answer is committed to SQL in one transaction that also bumps the question's answer count and activity time. An event goes to a queue, and consumers index it for search, notify the asker and watchers, and award badges. A ranking job keeps hot lists in Redis. All derived data can be rebuilt from the database, so the queue can lag without losing anything.

Deep dive: votes

Deep dive one: votes. In one transaction, read the user's existing vote, upsert the new one and adjust the post's score by the difference, so switching from down to up adds two, and repeating a vote adds nothing. The primary key guarantees a single vote per user. Then emit an event for reputation. Block votes on your own posts and cap votes per day. At three and a half votes a second, there is no need for sharded counters here.

Casting a vote
BEGIN
  old = SELECT value FROM votes
        WHERE post=:p AND user=:u
  UPSERT votes (p, u, +1)
  UPDATE posts
    SET score = score + (1 - old)
  WHERE id = :p
COMMIT
emit VoteChanged(p, u, old, +1)
no self-votes; daily vote cap

Deep dive: reputation

Reputation. Workers turn vote events into reputation events: plus ten for an upvoted answer, plus fifteen for an accepted one, minus two for a downvote, and a reversing event when a vote is removed. Apply a daily cap. The user's total is a cached sum of the ledger, so it can always be recomputed and explained. A nightly job looks for voting rings, like two accounts always upvoting each other, and reverses their events.

Reputation as events
  • answer upvoted +10
  • question upvoted +10
  • answer accepted +15
  • downvoted -2
  • vote removed: reverse event
  • daily cap on gains
  • total = SUM(rep_events),
  • cached on the user row
  • fraud job: voting rings →
  • reverse their events

Deep dive: hot list

Deep dive two: the hot list. A hot score grows with votes, answers and views, and is divided by a power of the question's age, so yesterday's popular question sinks. When a question gets a vote or an answer, recompute its score and update a Redis sorted set. Because age changes even without activity, a job recomputes the current candidates every few minutes. Keep one set overall and one per tag, each trimmed to the top thousand.

Hot score
hot = (score + 2·answers
       + log10(views))
      / (age_hours + 2) ^ 1.5

on any vote/answer: recompute
  that question, ZADD
every 5 min: recompute top
  candidates (age decays)
Redis sorted sets:
  hot:all, hot:tag:redis
  keep top 1,000 each

Deep dive: search

Deep dive three: search. Index each question with a boosted title, the body, tags, score and whether it has an accepted answer, fed by events within seconds. Queries combine text with tag filters and rank by relevance plus quality. While someone types a new title, search for similar questions and show them, which prevents many duplicates. When a duplicate is closed, keep its page and link it to the original, since its wording may match what other people search for.

Search and duplicates
  • index: title (boosted), body,
  • tags, score, accepted?
  • query: text + tag filters,
  • rank by relevance and score
  • while asking: search the
  • title → "similar questions"
  • fewer duplicates posted
  • close as duplicate → link,
  • keep the page for searchers
  • lag ≈ seconds, rebuildable

Deep dive: caching

Caching. Anonymous pages are cached whole at the CDN for a minute and purged when the question is edited. Signed-in users get the same cached HTML plus one small request for personal details: how they voted, their reputation, their inbox count. View counts are incremented in Redis, counting each visitor once per fifteen minutes, and flushed to SQL every minute. Right after posting, the author's reads go to the primary, so their own answer is never missing.

Caching layers
  • anonymous: whole page at CDN,
  • 60 s TTL, purge on edit
  • signed in: cached HTML +
  • small call for my votes,
  • my rep, my inbox count
  • views: INCR in Redis,
  • flush to SQL every minute,
  • 1 per visitor per 15 min
  • author: read from primary
  • for a few s after a write

Deep dive: scaling

Deep dive four: scaling the store. The honest answer is to scale up first: one primary with plenty of memory and two replicas, since everything fits in RAM and most reads are by key. Move heavy lists to Redis, and add covering indexes for queries like newest by tag and answers by score. When writes grow, move votes and reputation events to their own database. Shard by community or question id only when nothing else works.

Growing the database
  • today: one primary, large RAM,
  • 2 replicas, all reads by key
  • heavy lists → Redis, not SQL
  • tune: covering indexes on
  • (tag, last_activity),
  • (parent_id, score)
  • next: split votes and
  • rep_events to their own DB
  • last: shard by site or by
  • question id

Multi-tenant variant

The multi-tenant variant. If you host many public communities, give each its own database while sharing web servers, and route by hostname. For private company instances, every page needs a login, so CDN page caching is off, cached fragments include the tenant and permission groups in their keys, large tenants get their own search index, and smaller ones share one with a filter. Expect single sign-on, data residency and export requirements.

Multi-tenant Q&A
  • public communities: one DB
  • per site, shared servers
  • router: host → site → DB
  • private company Q&A:
  • login required, no CDN
  • page cache; cache key
  • includes tenant + groups
  • search: index per big tenant,
  • filtered alias for small
  • SSO, data residency, export

Trade-offs

Trade-offs. Denormalized scores make pages fast, and a periodic recount job fixes any drift. Anonymous readers may see a page a minute old, which is fine. If the primary fails, switch the site to read-only while a replica is promoted; readers barely notice. If the queue lags, search and reputation are late but nothing is lost. Rate limits scale with reputation, and flagged posts go to a moderation queue, see Design Problems fifty.

Trade-offs and failures
  • denormalized score: fast
  • pages, fixed by recount job
  • stale cache ≤ 60 s for
  • anonymous readers: fine
  • primary down → read-only mode,
  • replicas keep pages up
  • queue lag → search and rep
  • late, nothing lost
  • abuse: rate limits by rep,
  • flags → moderation queue

Interview summary

In the interview: six hundred views a second, mostly anonymous, and only about four hundred gigabytes of data, so one SQL primary with replicas, scaled up before sharding. Pages cached at the CDN with personal bits fetched separately. Votes enforced by a primary key, with the score adjusted in the same transaction. Reputation as a ledger of events. A hot score that decays with age, kept in Redis sorted sets per tag, and a search index fed by events.

Say it like this
  • 600 views/s, 80% anonymous
  • data ≈ 400 GB: one SQL primary
  • + replicas, scale up first
  • CDN pages, Redis fragments
  • vote: PK(post, user), score
  • delta in one transaction
  • reputation = event ledger
  • hot = activity / age^1.5,
  • Redis sorted sets per tag
  • search index fed by events

Interview questions

How do you ensure a user can vote only once on a post?

Store votes with a primary key of post id and user id and upsert them, adjusting the post score by the difference between the new and old value in the same transaction, so repeats change nothing and switching sides is handled correctly.

How would you compute reputation?

Turn vote, accept and removal events into append-only reputation events with fixed deltas and a daily cap, and keep the user total as a cached sum that can be recomputed and audited. Fraud jobs reverse events from voting rings.

How does a hot ranking work?

A score that grows with votes, answers and views and is divided by a power of the post age. Recompute it on activity and periodically for candidates, and store the top items in Redis sorted sets, globally and per tag.

Why might a single SQL database be enough for this system?

Writes are only a few per second and the total data is a few hundred gigabytes, which fits in memory on one large server. With page caching and read replicas, the primary handles the load, and sharding adds complexity without benefit.

How do you cache pages when users are logged in?

Serve the same cached HTML to everyone and fetch personal details, such as the user's own votes and inbox count, with a small separate request, so the expensive part of the page stays shared.

How do you reduce duplicate questions?

Search the index for similar titles while the user types and show matches before they post, and let the community close duplicates by linking them to the original while keeping the duplicate page for searchers.

How do you count page views without a write per view?

Increment counters in Redis, count each visitor at most once per time window, and flush aggregated counts to the database every minute.

How would you adapt the design for private company instances?

Require login and disable shared CDN page caching, include tenant and permission groups in cache keys, give large tenants their own database and search index, and support single sign-on and data residency.