Post Search

Post search lets users find posts (tweets, messages, articles) by keyword across an enormous, constantly-growing corpus, ranked by relevance and recency. The core realisation is that your primary database is the wrong tool for this — full-text ranking over billions of documents is what a dedicated search engine and its inverted index exist for. The design is mostly about building that index and keeping it in sync. It builds directly on Elasticsearch.

1. Requirements

Functional:

• Search posts by keywords; return the most relevant matches.
• Results ranked by relevance and recency; paginated.
• New posts become searchable quickly.

Non-functional:

• Fast queries — sub-second even over a huge corpus.
• Scale — billions of posts; high query volume.
• Fresh-ish — a new post searchable within seconds (slight indexing lag OK).
• Relevance quality matters — not just substring matching.

2. Why not just the database

A LIKE '%term%' query can't use an index, scans everything, and does no relevance ranking — unusable at scale. Full-text search needs an inverted index: a map from each term to the list of documents containing it, so "find posts with kampala" is a direct lookup of one term's posting list, not a scan (Database Indexing explains why the index direction matters).

inverted index:
  "kampala" → [post 12, post 88, post 403, ...]
  "startup" → [post 88, post 950, ...]
query "kampala startup" → intersect the two posting lists → rank → return

Elasticsearch builds and serves exactly this, with tokenisation, stemming, and relevance scoring (BM25) built in.

3. Architecture: search as a secondary index

The crucial principle: your database stays the source of truth; the search engine is a secondary, derived index. You write posts to the primary DB as normal, then feed them into the search index:

write post → primary DB (source of truth)
          → index into Elasticsearch (searchable copy)
query      → Elasticsearch → post ids → (optionally hydrate from DB/cache)

Never make the search engine your only copy — it's a derived view you can rebuild from the source if it's lost or corrupted.

4. Keeping the index in sync

How do new/edited posts reach the index? Two options:

• Dual write: the app writes to the DB and the index together — simple but fragile (one can succeed while the other fails, drifting them apart).
• Change Data Capture (recommended): stream the DB's change log into an indexing pipeline so every committed write reliably flows to Elasticsearch, decoupled and replayable. This is the robust way to keep a search index consistent with its source.

Indexing is async, which is why there's a small, acceptable lag before a post is searchable.

5. Ranking and pagination

• Relevance: Elasticsearch scores by term match (BM25); boost by recency and engagement so fresh, popular posts rank higher — mechanism vs. policy again, like the News Feed.
• Pagination: deep offset pagination is slow in search engines; use search_after / cursor pagination (see API Design) for stable, efficient paging.

6. Scaling

• Sharding the index: Elasticsearch splits the index into shards across nodes; a query scatters to all shards and gathers the top results. Scales with corpus size.
• Replicas for query throughput and availability (Scaling Reads).
• Hot vs. cold: recent posts are queried most — a time-based index (indices per week/month) lets you keep recent data hot and archive old indices cheaply.

7. Deep dives

• Typos / fuzzy: fuzzy matching and autocomplete (edge n-grams) improve recall.
• Rebuild: because the index is derived, you can reindex the whole corpus from the source DB (or a snapshot) after a mapping change.
• Filtering: combine the text query with structured filters (author, date, language) in one query.

What to emphasise

Name the inverted index and, above all, search as a derived secondary index kept in sync via CDC:

"Full-text ranking over billions of posts needs an inverted index, not a database scan, so I'd use a search engine like Elasticsearch — but critically as a secondary index, not the source of truth. Posts are written to the primary database, and I keep the search index in sync by streaming the database's change log through CDC into an async indexing pipeline, so every write reliably becomes searchable within seconds and I can rebuild the index from the source if needed. Results rank by BM25 relevance boosted by recency, paginate with a cursor rather than deep offsets, and the index shards across nodes with time-based indices so recent data stays hot."

Next up: Nearby Places — geospatial search over static points.