Stack Overflow looks like a CRUD app. Post a question, others post answers, everyone votes, one answer gets accepted, and a number next to your name goes up. Say that in an interview and the follow-ups arrive fast. “The question page is read a thousand times for every time it is written, and it must show a live-ish score, the accepted answer pinned first, and the rest sorted by score - all in under 200ms.” “Reputation is a running total over every vote you have ever received; you cannot re-sum a billion vote rows on every profile load.” “Search is not a LIKE '%...%' query - a user typing python list comprehension slow must get the canonical answer ranked above ten thousand near-duplicates, and that ranking blends text relevance with votes, recency, and acceptance.” “There are 100M questions and 1B answers; that does not fit on one box.”

The system is dominated by one asymmetry: content is written rarely but read enormously, and almost every read requires assembling a ranked, reputation-annotated, search-discoverable view of that content. Every hard part - the reputation total you cannot recompute per read, the search index that must rank by relevance and not just match, the vote and acceptance state that reorders answers, the question page you must assemble cheaply - falls out of that. Let me do it properly.

Functional Requirements (FR)

In scope:

  • Questions. A user posts a question (title, body in markdown, and up to five tags). Questions are viewable by anyone, listable per-tag and on a home feed, and editable.
  • Answers. Any user posts one or more answers to a question. Answers are ordered by score, with the accepted answer pinned to the top.
  • Voting. Any user casts one upvote or one downvote per question and per answer, can change it, and can remove it. Net score drives answer ordering and feeds reputation.
  • Accepted answer. The question’s author (and only the author) marks exactly one answer as accepted. Acceptance can be moved to a different answer later. Accepting grants reputation to the answerer.
  • Reputation. Every user has a reputation score that changes on defined events: your answer/question is upvoted (+10), downvoted (-2), your answer is accepted (+15), you accept an answer (+2), and so on. Reputation gates privileges (vote, comment, edit).
  • Search. Full-text search over questions and answers with relevance ranking - text match blended with votes, acceptance, recency, and tags. This is the hard part and the primary discovery path.
  • Tags. Questions carry tags; tags are a browse and filter dimension and a search signal.

Explicitly out of scope (say this so you control the scope):

  • Comments under posts (they exist and are votable, but they are a smaller version of the answer problem; I will note where they plug in and not re-derive them).
  • Real-time notifications, the reputation-privilege bureaucracy (badges, review queues, moderation tooling), and chat.
  • Rich media storage/transcoding. Bodies carry image URLs to a separate blob/CDN system I am not designing.
  • The recommendation/personalization home feed and the duplicate-question detection ML pipeline - I will note where they attach to the same index and event stream.

The one decision that drives everything: this is an extremely read-heavy content system whose every read is a ranked, denormalized assembly (answers by score, reputation by running total, questions by relevance), so the design is about precomputing and caching those rankings, not about the writes. That is the whole problem.

Non-Functional Requirements (NFR)

  • Scale: 100M questions, 1B answers accumulated. Assume ~10M DAU, heavily read-skewed - the classic “99% of traffic comes from Google landing on one question page” pattern. Reads dwarf writes by roughly 100:1.
  • Latency: question page load p99 under 200ms (this is what a Google visitor feels). Search p99 under 300ms - search is allowed to be a touch slower because it does real ranking work, but it is still an interactive path. Posting an answer or casting a vote should feel instant (optimistic UI, ack under 150ms).
  • Availability: 99.99% on the read path (question pages and search). A question page that fails to load is the most visible failure because most traffic is anonymous search-engine referral. Writes can tolerate marginally lower availability behind optimistic UI.
  • Consistency: eventual for scores, reputation, and search rank. It is fine if a vote takes a couple of seconds to move a score, if reputation lags by seconds, or if a new question takes a few seconds to become searchable. The exceptions that must be exact: one-user-one-vote (idempotent per user/item), and exactly-one-accepted-answer per question (a question can never show two accepted answers). Those are correctness constraints, not eventual.
  • Durability: a posted question or answer must never be lost. Raw votes and reputation-changing events must be durable - they are the audit trail we replay to rebuild a reputation total or a score. A cached score, a reputation counter, and a search index entry are all rebuildable; the source events are not.
  • Freshness: a vote should move the visible score within a few seconds. A new question should be searchable within seconds to low tens of seconds (near-real-time indexing, not synchronous).

Back-of-the-Envelope Estimation (BoE)

Real numbers with the arithmetic, because the architecture is dictated by them.

Reads (the dominant load):

10M DAU plus a large anonymous/search-referral population.
Assume effective ~50M unique daily readers (logged-out included),
each viewing ~10 pages/day (a search-engine visitor often reads one
question and leaves; power users read many).
50M * 10 = 500M page reads/day
= 500M / 86,400 s ≈ 5,800 reads/sec average
Peak (3x average) ≈ 17,000 reads/sec
Search is ~15% of reads → ~2,600 searches/sec peak.

Writes (rare relative to reads):

Questions: assume ~50K new questions/day (mature platform, growth flattened).
   50K / 86,400 ≈ 0.6 questions/sec  (peak ~2/sec)
Answers:   assume ~150K new answers/day.
   150K / 86,400 ≈ 1.7 answers/sec   (peak ~5/sec)
Votes:     the biggest write stream. Assume ~5M votes/day.
   5M / 86,400 ≈ 58 votes/sec         (peak ~180/sec)

Content writes are trivially small - single-digit per second. Even votes peak at only a couple hundred per second. This system is not write-bound. The entire challenge is serving 17K ranked reads/sec and 2.6K relevance-ranked searches/sec cheaply, and keeping reputation totals and search indexes current without recomputing them per read.

Storage:

Per question row:

question_id   8 bytes  (Snowflake-style 64-bit, time-sortable)
author_id     8 bytes
title         ~150 bytes
body          ~3 KB  (markdown, code blocks; questions are long)
tags          ~50 bytes (up to 5 tag ids)
created_at    8 bytes
metadata      ~200 bytes (score, view count, answer count, accepted_id)
------------------------------------------------
≈ 3.5 KB/question

100M questions * 3.5 KB ≈ 350 GB of question bodies.
1B answers * ~2 KB each ≈ 2 TB of answer bodies.
Total content ≈ 2.4 TB.  Modest for a sharded cluster.

The raw vote and reputation-event logs matter more over time:

Raw vote record: (user_id 8, item_id 8, value 1, ts 8, weight 4) ≈ 30 bytes.
Assume ~3B votes accumulated * 30 B ≈ 90 GB of vote state (latest per user/item),
plus an append-only reputation-event log of similar order that ages to cold storage.

Search index size:

The inverted index is the interesting store. Index questions + answers.
~1.1B documents (100M Q + 1B A). Assume ~150 unique terms/document
after tokenization and stemming, postings ~12 bytes each (doc_id +
positions + field). Rough index size:
1.1B docs * 150 terms * 12 B ≈ 2 TB of postings,
compressed to well under 1 TB with delta + varint encoding.
Sharded across an Elasticsearch/OpenSearch cluster of tens of nodes.

Cache size (the hot working set):

Reads are extremely skewed - a small fraction of questions get the vast
majority of views (the "top Google result" long tail is wide but the head
is hot). Cache the fully-rendered top ~5M question pages:
5M * ~20 KB rendered ≈ 100 GB.  Comfortable in a Redis/CDN tier.
Reputation for ~50M active users at ~16 bytes each ≈ 800 MB - trivially cached.

Bandwidth:

A question page ≈ 20 KB rendered (question + top answers + sidebar).
Read bandwidth at peak: 17K reads/sec * 20 KB ≈ 340 MB/sec served,
mostly absorbed by CDN edge caching of anonymous question pages.

The takeaway from the numbers: the writes are a rounding error; everything is about serving ranked reads and search cheaply from precomputed, cached, denormalized views.

High-Level Design (HLD)

Three read-shaped concerns dominate: the question page (assemble question + answers-by-score + accepted-pinned + reputation), search (relevance ranking over an inverted index), and reputation (a running total updated by an event stream, never recomputed per read). Writes flow through services that update the source-of-truth stores and then emit events that asynchronously update the derived views: search index, reputation counters, and score caches.

                          ┌──────────────────────────┐
                          │         Clients          │
                          │  (web, mobile, anon SEO)  │
                          └────────────┬─────────────┘
                                       │
                          ┌────────────▼─────────────┐
                          │   CDN (anon question      │
                          │   pages) → Load Balancer  │
                          └───┬───────┬───────┬───────┘
             WRITE            │ SEARCH│ READ  │ VOTE/ACCEPT
        ┌──────────────────▼──┐ ┌────▼─────┐ ┌───▼──────────────┐
        │ Q&A Write Service    │ │ Search   │ │ Vote / Accept    │
        │ (post/edit Q & A)    │ │ Service  │ │ Service          │
        └───┬──────────────────┘ └────┬─────┘ └───┬──────────────┘
            │ write (durable)         │ query     │ enqueue
     ┌──────▼───────────┐             │      ┌────▼─────────┐
     │ Content Store     │        ┌────▼────┐ │  Vote Queue  │
     │ (Q & A bodies,    │        │ Search   │ │  (Kafka,     │
     │  NoSQL, sharded   │        │ Cluster  │ │  by item_id) │
     │  by question_id)  │        │ (inverted│ └────┬─────────┘
     └───┬───────────────┘        │  index,  │      │
         │ emit content_event     │  ES/     │ ┌────▼──────────┐
         │                        │  OpenSrch)│ │ Vote/Rep      │
    ┌────▼───────────────────┐    └────▲─────┘ │ Aggregator    │
    │        Event Bus        │        │       │ (dedup, count,│
    │  (Kafka: content,       │  index │       │ reputation)   │
    │   vote, accept, rep)    │◀───────┼───────┴────┬──────────┘
    └──┬─────────┬─────────┬──┘  update │            │
       │         │         │            │       ┌────▼──────────┐
 ┌─────▼───┐ ┌───▼─────┐ ┌─▼──────────┐ │       │ Score Store   │
 │ Indexer │ │ Rep      │ │ Feed/Tag   │ │       │ (item→up/down │
 │ Pipeline│ │ Service  │ │ Builder    │─┘       │  /score/acc)  │
 │ (→search│ │ (running │ └────────────┘         └───────────────┘
 │  index) │ │  totals) │
 └─────────┘ └───┬──────┘
                 │ reputation counters
           ┌─────▼──────────┐
           │ Reputation Store │
           │ (user → rep,     │
           │  event log)      │
           └──────────────────┘

Content write flow (post a question or answer):

  1. Client POST /questions (or /answers). The Q&A Write Service validates, assigns a time-sortable Snowflake id, and writes the row to the durable Content Store. Answers carry question_id.
  2. Seed a Score Store entry (score=0, up=0, down=0) and, for an answer, increment the parent question’s answer_count.
  3. Emit a content_created event on the Event Bus. The Indexer Pipeline consumes it and adds the document to the search index (near-real-time, seconds later). The Feed/Tag Builder inserts it into the relevant tag lists and home feed.

Vote / accept flow:

  1. Client POST /vote {item_id, value}. The Vote Service authenticates, writes an idempotent per-(user, item) vote record (enforces one-user-one-vote, lets the client render optimistically), enqueues the vote on Kafka partitioned by item_id, and acks in under 150ms.
  2. The Vote/Rep Aggregator consumes the partition, deduplicates against the user’s previous vote, updates the Score Store counters in batches, and emits a reputation_event (e.g. +10 to the answerer) onto the Event Bus.
  3. Accepting an answer is POST /questions/{id}/accept {answer_id}. This is a small transactional update (see deep dive 3) that sets exactly one accepted answer and emits a +15 reputation event.
  4. The Reputation Service consumes reputation events and applies them to the user’s running total - never recomputed, only incremented.

Read flow - the hot path:

  1. Client GET /questions/{id}. For anonymous traffic this is served straight from the CDN/rendered-page cache. On a miss, the Read path assembles the page: fetch the question body from the Content Cache, fetch its answers, order them by (is_accepted DESC, score DESC), overlay current scores from the Score Store, and annotate each author with their reputation from the Reputation cache.
  2. Hydrate everything with batched reads; overlay the logged-in user’s own vote/accept state so arrows and the accept checkmark render correctly.
  3. GET /search?q=... goes to the Search Service, which queries the inverted-index cluster with a relevance-ranked query (deep dive 1) and hydrates the top results.

The key insight is the split: writes are cheap and just emit events; every expensive ranked view - answers-by-score, reputation totals, the search index - is a derived, cached, event-updated structure, so a read is an assembly of precomputed pieces, not a computation.

Component Deep Dive

The hard parts, naive-first then evolved: (1) search with relevance ranking, (2) the reputation running total, (3) voting and the exactly-one-accepted-answer constraint, (4) assembling the question page cheaply.

1. Search with relevance ranking

This is the distinctive hard part of a Q&A system and where most designs hand-wave. Search is the primary discovery path - most users arrive via a query, not a browse.

Approach A: LIKE / full-table scan in the primary DB (the naive query)

SELECT * FROM questions
WHERE title LIKE '%python list comprehension slow%'
   OR body  LIKE '%python list comprehension slow%'
ORDER BY score DESC
LIMIT 25;

Where it breaks:

  • A leading-wildcard LIKE cannot use an index. It is a full scan of 100M question rows (plus 1B answers) per query. At 2,600 searches/sec this is instantly impossible - it saturates the primary database and blocks the write path.
  • It matches substrings, not meaning. It will not match list comprehensions are slow (plural, reordered), will not stem slow/slower, and has no notion of relevance beyond score DESC. Typing more words makes it worse, not better.
  • No ranking blend. A Q&A relevance signal is text match plus votes plus acceptance plus recency plus tag match. ORDER BY score throws all of that away.

Approach B: a dedicated inverted index with a blended relevance score (the answer)

Move search off the primary DB entirely into a purpose-built search cluster (Elasticsearch / OpenSearch, or a Lucene-based service). The core structure is an inverted index: a map from each term to a posting list of the documents that contain it.

Term        Posting list (doc_id, term_freq, positions, field)
--------    ----------------------------------------------------
"python"  → [ (q_412, 3, [..], title), (a_9931, 1, [..], body), ... ]
"list"    → [ (q_412, 2, [..], body),  (q_88, 5, [..], title), ... ]
"comprehension" → [ (q_412, 4, ...), ... ]
"slow"    → (stemmed to "slow") [ (q_412, 1, ...), (a_77, 2, ...), ... ]

At index time each document (a question, and each answer as its own document) is analyzed: lowercased, tokenized, stopword-filtered, stemmed (comprehensions → comprehension, slower → slow), and optionally synonym-expanded. At query time the query string goes through the same analyzer, so list comprehensions are slow and slow list comprehension produce the same query terms. A query is then an intersection/union of posting lists - milliseconds, not a scan.

Text relevance scoring (BM25). For a matched document, the search engine computes a text-relevance score. The modern default is BM25, which rewards documents where the query terms are frequent (term frequency) but rare across the whole corpus (inverse document frequency), with saturation so a term appearing 50 times is not 50x better than once, and length normalization so a short precise title is not penalized against a long rambling body.

BM25(doc, query) = Σ over query terms t:
    IDF(t) * ( f(t,doc) * (k1 + 1) )
           / ( f(t,doc) + k1 * (1 - b + b * |doc| / avg_doc_len) )
  where f(t,doc) = term frequency, k1≈1.2, b≈0.75, IDF from corpus stats.

But BM25 alone is not Q&A relevance. The best answer to python list comprehension slow is the highly-upvoted, accepted, canonical one - not merely the one with the best keyword density. So the final rank is a function-score / learning-to-rank blend on top of BM25:

final_score(doc, query) =
      w_text  * BM25(doc, query)                 // does the text match?
    + w_field * title_match_boost                // title match >> body match
    + w_votes * log10(1 + score)                 // community endorsement
    + w_acc   * (is_accepted ? 1 : 0)            // accepted answers rank up
    + w_tag   * tag_overlap(query_tags, doc)     // tag relevance
    + w_fresh * recency_decay(created_at)         // newer, all else equal
    - w_dupe  * duplicate_penalty                 // demote known duplicates

The vote score, acceptance flag, tags, and timestamp are stored as doc values on each indexed document and updated by the same event stream that updates the Score Store, so ranking has them without a join. The weights w_* start hand-tuned and are later learned from click-through data (learning-to-rank) - the platform logs which result a user clicked and stayed on, and trains a ranker to reproduce those choices.

Keeping the index current. The Indexer Pipeline consumes content_created, content_edited, vote_score_changed, and accept_changed events off the Event Bus and updates the corresponding documents. Indexing is near-real-time, not synchronous: a new question is searchable within seconds. Score/acceptance changes update the doc-value fields (a partial update) rather than re-analyzing the body, which is cheap.

Sharding the index. The index is sharded across nodes by doc_id (routing), with each shard holding a slice of documents and every query scattered to all shards and gathered (scatter-gather), each shard returning its top-K and a coordinator merging. Replicas per shard serve read scale and HA. Question and answer documents can live in the same index (a type field) so a query surfaces both.

ApproachMatch qualityCost per queryRankingVerdict
LIKE on primary DBSubstring only, no stemmingFull scan of 100M+ rowsscore onlyImpossible at scale
Inverted index + BM25 + blendStemmed, ranked, tag/vote-awarePosting-list intersection, msText + votes + accept + recencyThe answer

Why not just Postgres full-text search (tsvector/GIN)? It is a fine starting point and genuinely good up to moderate scale - a GIN index over tsvector avoids the full scan. But at 1.1B documents with a multi-signal ranking blend, click-through learning-to-rank, faceting by tag, and independent read scaling, a dedicated search cluster is the right tool. I would mention Postgres FTS as the pragmatic v1 and the dedicated cluster as the scaled answer.

2. The reputation running total

Reputation is a number on every user derived from every reputation-affecting event in their history: upvotes received (+10), accepted answers (+15), accepting an answer (+2), downvotes received (-2), and so on. It appears on every answer, next to every author name, everywhere.

Approach A: compute reputation on read by summing the event log (the naive query)

SELECT SUM(rep_delta) FROM reputation_events WHERE target_user_id = ?;

Where it breaks:

  • A prolific user has received millions of votes. Summing their entire reputation history on every page that shows their name - and a question page shows many names - is a massive aggregation per read. On a hot question with 30 answers you would run 30 such sums.
  • Reputation is read constantly (it is on every avatar) and this makes the cheapest, most frequent UI element one of the most expensive queries. It scales with a user’s fame, so exactly the most-shown users are the most expensive.

Approach B: a denormalized running total updated by an event stream (the answer)

Store reputation as a single materialized counter per user, and update it incrementally whenever a reputation event occurs. Never re-sum.

Reputation Store:
  user_id   BIGINT   PRIMARY KEY
  reputation BIGINT               // the running total, read directly
  updated_at TIMESTAMP

Reputation event log (append-only, durable audit trail):
  event_id     BIGINT PRIMARY KEY   // Snowflake
  target_user  BIGINT               // who gains/loses rep
  source_type  ENUM(upvote_q, upvote_a, accept, downvote, ...)
  source_id    BIGINT               // the item that caused it
  actor_id     BIGINT               // who voted/accepted (for reversal)
  rep_delta    INT                  // +10, +15, -2, ...
  created_at   TIMESTAMP

The Reputation Service consumes reputation_event messages off the Event Bus (emitted by the Vote/Rep Aggregator and the Accept flow) and does one cheap update per event:

on reputation_event(target_user, rep_delta, event_id):
    if seen(event_id): return                     # idempotent - dedup replays
    rep_store.increment(target_user, rep_delta)    # single-key counter update
    rep_event_log.append(event)                    # durable audit record

Now a page load reads reputation as a single point lookup (and it is cached, since reputation for active users fits in under a gigabyte). The read cost is O(1) regardless of how famous the user is.

Why this is correct and safe:

  • Idempotency is enforced by event_id: a reputation event is emitted with a stable id derived from its cause (e.g. the vote’s (user, item) plus version), so a Kafka redelivery or aggregator retry cannot double-count. This is the whole reason we can use an at-least-once event bus.
  • Reversibility. Because every event is in the durable append-only log with its actor_id and source_id, reversing reputation is just applying the negative deltas: if a vote is retracted or a brigade is unwound, we emit compensating events. We never have to recompute from scratch, but we can rebuild any user’s total by replaying their log if a counter is ever corrupted.
  • Rebuildable counter. The Reputation Store counter is derived state. The event log is the source of truth. Losing the counter store loses nothing durable - replay the log.

The trade-off is eventual consistency: your reputation reflects a vote a few seconds after it lands, which is completely fine. Stack Overflow itself batches reputation and shows it updating on a delay.

Reputation caps and rules (e.g. “max +200 rep/day from votes”, “first downvote of the day free”) are applied in the Reputation Service as it processes events, before incrementing - it has the per-user daily context to enforce them, and the raw log still records the uncapped event for audit.

3. Voting and the exactly-one-accepted-answer constraint

Voting is mechanically the same firehose problem as any vote system, but at this scale (peak ~180 votes/sec) it is far less severe than a social feed. The interesting correctness constraints are one-user-one-vote and exactly-one-accepted-answer.

Voting - decouple the record from the count. Same pattern as any vote system, and worth stating crisply:

  • The user’s current vote is an idempotent record keyed by (user_id, item_id) with value in {+1, 0, -1}, written synchronously. This enforces one-user-one-vote (re-submitting the same value is a no-op; a change computes a delta) and lets the client render arrows optimistically. No cross-user contention because each user touches only their own key.
  • The aggregate score is updated asynchronously by the Vote/Rep Aggregator, which consumes the item_id-partitioned Kafka stream, combines many votes into one Score Store write per flush, and emits the corresponding reputation event.

Because peak vote volume is modest, we do not strictly need heavy hot-row protection the way a 70K-votes/sec system does, but the async aggregate is still the right design: it is the single seam where we attach reputation events, vote-fraud checks, and idempotency, and it keeps the write path a sub-150ms per-user upsert.

The accepted answer - a real consistency constraint. Exactly one answer per question may be accepted, and only the question’s author may set it. A naive implementation:

UPDATE answers SET is_accepted = true WHERE answer_id = ?;

Where it breaks: if the author double-clicks, or two requests race (accept answer X, then quickly accept answer Y), you can end up with two answers marked accepted, or the reputation grant applied twice. “Two accepted answers” is a visible correctness bug.

Fix: make acceptance a single-question transactional swap keyed on the question. The accepted-answer pointer lives on the question row, not scattered across answer rows:

questions.accepted_answer_id  BIGINT NULL   -- the single source of truth

Accept(question_id, new_answer_id, actor):
  BEGIN;   -- transaction on the question's partition
    q = SELECT ... FROM questions WHERE question_id = ? FOR UPDATE;
    assert actor == q.author_id                    -- only author accepts
    assert new_answer.question_id == question_id    -- answer belongs here
    old = q.accepted_answer_id
    if old == new_answer_id: COMMIT; return          -- idempotent no-op
    UPDATE questions SET accepted_answer_id = new_answer_id WHERE question_id = ?;
  COMMIT;
  -- after commit, emit reputation events transactionally via an outbox:
  emit rep_event(+15, target=new_answer.author, source=accept, id=(question_id, new_answer_id))
  if old: emit rep_event(-15, target=old_answer.author, source=unaccept, id=(question_id, old))

Because there is exactly one accepted_answer_id field on one question row, and the update is guarded by a row lock (FOR UPDATE) on that single question, “exactly one accepted answer” is structurally guaranteed - you cannot have two, because there is one slot. The reputation grant/revoke is idempotent by the (question_id, answer_id) event id and emitted via a transactional outbox (write the event to an outbox table in the same transaction, a relay publishes it) so the DB update and the reputation event cannot diverge. Answer ordering then reads accepted_answer_id from the question and pins that answer first; it is not stored redundantly on each answer row.

4. Assembling the question page cheaply

The question page is the most-read object in the system (most traffic is a search-engine visitor landing on exactly one question). It must show: the question, all its answers ordered by (accepted first, then score DESC), each author’s reputation, current scores, and the viewer’s own vote state - in under 200ms.

Naive assembly: on every request, SELECT the question, SELECT its answers, for each answer SELECT the author and SUM their reputation, SUM the votes per answer to get scores, and sort. That is a fan of joins and aggregations per page view, at 17K views/sec. It melts the database, and it recomputes the same rank for the same question millions of times.

Fix: precompute the ranked, denormalized view and cache the rendered page. Layered:

  1. Denormalized counters. Score (up/down/score) lives in the Score Store as a maintained counter (deep dive 3), not a SUM. Reputation is a maintained counter (deep dive 2), not a SUM. Answer count and accepted-answer pointer are denormalized onto the question row. So assembly reads counters, never aggregates.
  2. Answer ordering. Maintain a per-question sorted structure of answer_id → score (a small Redis ZSET per question, or just sort in-app since a question rarely has more than a few dozen answers). The accepted answer is pinned by reading question.accepted_answer_id first, then the rest by ZREVRANGE. Reordering on a vote is O(log n) on a tiny set.
  3. Rendered-page cache. For anonymous traffic (the majority), cache the fully rendered HTML/JSON of the question page in the CDN and a Redis tier, keyed by question_id and a version stamp bumped on any content/score/accept change. A cache hit is a static serve - the 200ms budget is trivially met and most load never touches application servers at all.
  4. Personalization overlay. Logged-in users need their own vote arrows and the accept button. Serve the shared cached page and overlay a small per-user delta (this user’s votes on these items) fetched from their vote records - a single batched read keyed by user_id. The expensive shared part is cached once; only the tiny personal part is per-user.

This is the same philosophy as the whole system: the read is an assembly of maintained counters and cached fragments, never a recomputation. View counts, incidentally, are themselves a high-write low-value counter handled the same way as votes - buffered and aggregated, never a synchronous UPDATE ... SET views = views + 1 per page load (that would reintroduce hot-row contention on popular questions).

API Design & Data Schema

API

POST /api/v1/questions
Body:
{
  "title": "Why is this list comprehension slower than a for loop?",
  "body": "...markdown...",
  "tags": ["python", "performance", "list-comprehension"]
}
Response 201:
{ "question_id": "1789324411223300", "author_id": "42",
  "created_at": "2026-07-25T09:00:00Z", "score": 0 }
Errors: 400 invalid, 401 unauthenticated, 403 rep too low, 429 rate limited

POST /api/v1/questions/{id}/answers
Body: { "body": "...markdown..." }
Response 201: { "answer_id": "...", "question_id": "...", "score": 0 }

POST /api/v1/vote
Body: { "item_id": "...", "item_type": "question|answer", "value": 1 }
                                                    // value in {1, 0, -1}
Response 202 Accepted:                              // async aggregation
{ "item_id": "...", "your_vote": 1 }
Errors: 400 bad value, 401 unauthenticated, 403 rep too low, 429 rate limited

POST /api/v1/questions/{id}/accept
Body: { "answer_id": "..." }                        // author only, one at a time
Response 200: { "question_id": "...", "accepted_answer_id": "..." }
Errors: 403 not the author, 404 answer not on this question, 409 conflict

GET /api/v1/questions/{id}
Response 200:
{ "question": { "question_id": "...", "title": "...", "body": "...",
                "score": 128, "tags": [...], "author": {...},
                "accepted_answer_id": "...", "your_vote": 0 },
  "answers": [ { "answer_id": "...", "body": "...", "score": 342,
                 "is_accepted": true, "author": { "id": "...",
                 "reputation": 91250 }, "your_vote": 0 }, ... ] }
Latency target: p99 < 200ms (mostly CDN-served for anon)

GET /api/v1/search?q=python+list+comprehension+slow&tags=python&sort=relevance
   sort ∈ {relevance, votes, newest}   &page=1&size=25
Response 200:
{ "results": [ { "type": "question", "question_id": "...", "title": "...",
                 "snippet": "...highlighted...", "score": 128,
                 "answer_count": 12, "is_answered": true,
                 "accepted": true, "tags": [...], "relevance": 8.42 }, ... ],
  "total": 3391, "next_page": 2 }
Latency target: p99 < 300ms

GET /api/v1/questions?tag=python&sort=newest&page=1     // tag browse feed
GET /api/v1/users/{id}                                  // profile + reputation

Pagination is cursor-based for feeds (the cursor encodes the last-seen Snowflake id, stable as new questions arrive) and page-based for search (search results are a ranked snapshot; deep pagination is bounded and rarely used).

Data store choices

Different access patterns, so more than one store. Be explicit.

1. Content Store (questions, answers) - NoSQL wide-column (Cassandra / DynamoDB). Access is point-lookup by id and range scan of “answers for a question” or “recent questions in a tag”. No cross-entity ACID needs on the hot path, and it must scale to a few TB with heavy read fan-out. Wide-column is the fit.

Table: questions
  question_id       BIGINT   PARTITION KEY   -- Snowflake, time-sortable
  author_id         BIGINT
  title             STRING
  body              STRING (markdown)
  tags              LIST<STRING>
  score             INT              -- denormalized from Score Store
  answer_count      INT              -- denormalized
  accepted_answer_id BIGINT NULL     -- the single accept slot (deep dive 3)
  view_count        BIGINT           -- buffered counter
  created_at        TIMESTAMP
  Shard by: question_id   (single-key page reads land on one shard)

Table: answers
  answer_id    BIGINT   PARTITION KEY   -- also queryable by question
  question_id  BIGINT                   -- clustering for "answers of Q"
  author_id    BIGINT
  body         STRING
  score        INT
  created_at   TIMESTAMP
  Shard by: question_id  → all answers for a question co-located,
            clustering key answer_id, so one range scan returns them.

Table: tag_questions       -- "recent/newest questions in a tag"
  tag          STRING   PARTITION KEY
  question_id  BIGINT   CLUSTERING KEY DESC
  Shard by: tag

2. Vote Store - append/upsert, sharded by item_id (Cassandra). The latest vote per (user, item). Reads are “user’s vote on item” (overlay) and “all votes for item” (recount). Never a live aggregate.

Table: votes
  item_id    BIGINT   PARTITION KEY
  user_id    BIGINT   CLUSTERING KEY
  value      TINYINT               -- +1 / 0 / -1 (latest state)
  updated_at TIMESTAMP
  Shard by: item_id → "all votes for item" is single-partition (recount).
  Also: user_votes sharded by user_id for the per-user overlay.

3. Reputation Store - counter + append-only event log (Cassandra / DynamoDB). Single-key counter per user for O(1) reads; durable append-only event log as the source of truth and audit/reversal trail (deep dive 2). Sharded by user_id.

4. Score / ranking cache - Redis. Per-item {up, down, score} counters and small per-question answer-ordering ZSETs, plus the rendered-page cache. A rebuildable index/cache, sharded by item_id / question_id; losing a node loses nothing durable (replay the Vote Store).

5. Search cluster - Elasticsearch / OpenSearch (Lucene inverted index). The full-text index over questions and answers with BM25 + blended ranking (deep dive 1). Sharded by doc_id routing, replicated for read scale and HA. A derived store - fully rebuildable by reindexing the Content Store.

6. User / tag / privilege metadata - relational (Postgres). Low-volume, relationship-heavy, consistency-sensitive: user accounts, tag definitions, privilege thresholds, and the accepted-answer transaction (which benefits from FOR UPDATE and a transactional outbox). Primary with read replicas.

Why NoSQL for content/votes, SQL for metadata and the accept transaction, a dedicated cluster for search: the high-read, partition-scoped, transaction-free paths (questions, answers, votes) want horizontal scale and no joins; the small relational, consistency-sensitive data (accounts, tags, the one accept swap) wants a relational DB; full-text relevance ranking is neither and wants a purpose-built inverted-index engine. Sharding by question_id/item_id/user_id/tag keeps every hot-path operation single-partition; sharding content by created_at would be the classic mistake (all “now” writes and the hottest recent reads land on one shard).

Bottlenecks & Scaling

Where it breaks first, in order, and the fix for each:

1. Search cost and quality (the primary hard part). LIKE on the primary DB is a full scan of 100M+ rows per query and cannot rank. Fix: a dedicated inverted-index cluster (Elasticsearch/OpenSearch) with BM25 text scoring blended with votes, acceptance, recency, and tag match; near-real-time indexing off the event stream; sharded by doc_id with replicas, scatter-gather queries. Learning-to-rank from click-through refines the weights. Covered in deep dive 1.

2. Reputation recomputation. Summing a user’s million-row vote history on every page that shows their name. Fix: a denormalized running-total counter per user, incremented by idempotent reputation events off the bus, backed by a durable append-only event log for audit and reversal. O(1) read. Covered in deep dive 2.

3. Question-page assembly at 17K reads/sec. Joins and aggregations per page view. Fix: denormalized counters (score, answer_count, accepted pointer), small per-question answer-ordering ZSETs, and a rendered-page cache in CDN + Redis for anonymous traffic with a per-user vote overlay for logged-in users. Covered in deep dive 4.

4. Hot question read keys (a viral/“top Google result” question). Millions hit one question. Fix: the rendered-page cache is exactly this defense - anonymous views are a static CDN serve. Replicate the hottest Content/Score cache entries across nodes and add a per-instance local LRU with a few-second TTL. Question bodies are near-immutable, so they cache aggressively at the edge, keyed by a version stamp bumped on edit.

5. View-count and vote write amplification on popular questions. A synchronous views = views + 1 (or score++) per event is hot-row contention on exactly the popular items. Fix: buffer and aggregate both through the same Kafka + aggregator path, flushing combined counter deltas every second or two. Never a synchronous per-event row update on a hot item.

6. Index freshness vs write cost. Reindexing on every vote would thrash the search cluster. Fix: content bodies are indexed once (near-real-time on create/edit); volatile signals (score, acceptance) are stored as doc-values and updated via cheap partial updates, not full reanalysis. Ranking reads them at query time without re-tokenizing.

7. Accepted-answer race / double reputation grant. Fix: a single accepted_answer_id slot on the question row, updated under FOR UPDATE, with idempotent reputation events emitted via a transactional outbox so the DB state and reputation cannot diverge. Covered in deep dive 3.

8. Score/search/reputation stores as rebuildable derived state. Fix: treat the Score cache, Reputation counters, and search index all as derived. On loss, rebuild: replay the Vote Store for scores, replay the reputation event log for totals, reindex the Content Store for search. The source-of-truth stores (content bodies, vote records, event logs) are the only things that must never be lost.

9. Single points of failure. Write/Read/Search/Vote/Accept services are stateless behind load balancers - scale horizontally, any instance serves any request. Kafka, caches, content store, and search cluster are replicated (RF 3). Aggregators, indexers, and the reputation service are scaled, idempotent consumer groups. No single box whose loss stops question pages loading or search returning.

10. Multi-region latency for a global, SEO-driven audience. Fix: deploy Read Services, rendered-page caches, and search replicas per region behind GeoDNS, since most traffic is cacheable anonymous question reads. Because scores, reputation, and index are eventually consistent and rebuildable, cross-region replication is forgiving - a few seconds of lag on a far-region score or a newly-indexed question is invisible. Writes go to a home region and replicate; the durable logs reconcile any divergence.

Wrap-Up

The trade-offs that define this design:

  • Everything expensive is a derived, cached, event-updated view. Answer ordering, reputation totals, question-page renders, and the search index are all maintained asynchronously off one event stream and read as precomputed pieces. We trade a few seconds of staleness - which nobody notices - for turning every read from a recomputation into an assembly. This is the central call in a system that is 100:1 read-heavy.
  • Search is a dedicated inverted index with a blended relevance score, not a database query. BM25 text relevance plus votes, acceptance, recency, and tag match, kept current by near-real-time indexing and refined by click-through learning-to-rank. A LIKE query cannot scale and cannot rank; this is the distinctive hard part of Q&A.
  • Reputation is a running total, never a re-sum. A denormalized per-user counter incremented by idempotent, durably-logged reputation events - O(1) to read on every avatar, rebuildable by replay, and reversible when a vote is retracted or a brigade is unwound.
  • The two exact constraints are enforced structurally. One-user-one-vote lives at a per-user idempotent key; exactly-one-accepted-answer lives in a single accepted_answer_id slot on the question row, updated under a row lock with an idempotent, outboxed reputation grant. Everything else is allowed to be eventually consistent.
  • NoSQL for content and votes, SQL for metadata and the accept transaction, a search cluster for relevance, all sharded by the read key. Each store matches its access pattern; sharding by question_id/item_id/user_id/tag keeps every hot-path operation single-partition.

One-line summary: a read-dominated Q&A system where cheap event-emitting writes feed asynchronously-maintained derived views - answers ranked by score with a single structural accept slot, reputation as an idempotent running total over a durable event log, question pages served from a rendered cache with per-user overlays, and search served from a dedicated inverted index that blends BM25 text relevance with votes, acceptance, recency, and tags - scaling to 100M questions and 1B answers.