Section 9: The Best Answer Is the One Already Written - Deep Dive: Search and Duplicate Detection

Executive Summary: Section 8 settled ranking inside a page. This section is about finding the page. Search is one OpenSearch document per question, fed from the same outbox stream as everything else, at most 30 seconds behind and always rebuildable from Postgres. Ranking is BM25 with the title boosted, shaped by a dampened vote score, an accepted-answer bonus and a mild recency decay. The part that protects quality is on the ask page: while Priya types a title, a hybrid of lexical match and embedding nearest-neighbor search over 46 GB of int8 vectors shows the five most similar existing questions. Every duplicate stopped there is one fewer page that splits the answers.

Interviewer: "80% of your readers come from Google. Why does on-site search earn a deep dive?"

You: "On-site search is real load, 20M queries a day, about 700 a second at peak, and it's how signed-in people find things. But duplicate prevention matters more. Daniel's trigram answer lives on Priya's question. If someone posts the same question again tomorrow, it gets a weaker answer or none, and both pages compete in Google for the same error message. A reader can land on the worse one. Every duplicate splits the answers, so I want to stop it before it's posted, not clean up after."

The Index Is a Projection, Not a Store

Each question becomes one search document: title, body, tags, the text of its accepted answer and its top three answers by score, plus score, positive_score (score floored at zero), answer_count, has_accepted, created_at, a duplicate_of field, and a 768-dimension title embedding. A reader searching for "trigram" should find Priya's question even though only Daniel's answer uses that word, which is why answer text is part of the question's document.

The same index also serves two of the tag listing sorts. "New" and "unanswered" for the postgresql tag are plain filtered queries on tags, created_at and answer_count, with no text match at all. Only "hot" comes from elsewhere, the Redis sorted sets of the top 1,000 questions per tag that Section 8's ranking maintains.

The indexer never trusts the event payload for content. On any event for a question it re-reads the question and its top answers from a replica and writes the whole document. Two rules keep that safe under at-least-once delivery:

• Versioned writes. The document ID is question_id, and each write carries an external version: the question's doc_version, read along with the rest of the document. OpenSearch rejects a write whose version isn't newer, so a redelivered or reordered event can't move the index backwards.
• No stale reads. Each event carries the doc_version its transaction wrote. If the replica returns an older value, the indexer reads the primary instead, the same rule Section 7's bundle rebuild follows with last_activity_at.

doc_version is one addition to Section 5's schema: a BIGINT NOT NULL DEFAULT 0 column on the question's posts row, bumped in the same transaction as any change that affects the search document. That means a new answer, an edit, an acceptance, a close, and a vote on the question or one of its answers (Section 8's vote transaction does it beside the score). Comment votes and comments leave it alone, because the document holds neither. Why not version by the event's position in the commit log? A log position belongs to one physical cluster. When a logical shard moves to a new cluster, its positions restart at unrelated values, and every update for those questions could be rejected as "not newer". A counter on the row moves with the row. The price is that every answer vote also locks its question's row, which at the viral 5.6 votes a second is nothing, and Section 8's buffered-counter flip covers it the same way.

Votes need one more rule. At 350 vote events a second, rewriting a whole document per vote would be wasteful. A partial update of just score looks cheaper, but OpenSearch's update API can't carry an external version, so it isn't ordered against the versioned full writes, and a late one could move the score backwards. So the indexer coalesces vote events per question and, once per 30-second window, does the same versioned re-read-and-write as for any other event. A question whose answers receive 5.6 votes a second, like Priya's on the viral day, costs one document write every 30 seconds instead of about 170.

Key Insight: The search index holds nothing that doesn't also live in Postgres. When a mapping changes or the cluster is lost, we bulk-load a new index from a replica snapshot, replay Kafka from the snapshot's position, and swap the alias. Search can go wrong. Data can't be lost in it.

Quick check before moving on: