Data & Storage

Search & Indexing

Serve text or filter-heavy queries from a search index beside the system of record.

Intermediate, about 65 minutes to read, 4 open questions to practise.

1Understand

The idea

Search and indexing answer queries OLTP was never meant to serve at scale: full-text relevance, prefix autocomplete, faceted filters on millions of rows. The search cluster (Elasticsearch/OpenSearch/Algolia-class) holds a derived, denormalized projection; Postgres remains canonical for money, inventory, and compliance.

Interview architecture is ingest path + query path + failure/rebuild path. Dual-writing SQL and search in one request is fragile; async CDC or outbox events with stated staleness (2-5s typical) is the usual production pattern.

Promising “instant search after every write” without a read-after-write exception path (lookup by id from SQL) is a trap. LIKE '%phone%' on a billion-row table is not a search design, it is a table scan with marketing.

Intuition

Inverted index maps normalized token → posting list of doc ids; scoring (BM25, boosts) ranks results. Analyzers stem/locale (e.g. running → run). Autocomplete: completion suggester or edge n-grams on title.prefix, tune for false positives on short prefixes.

Document model. One search doc per product (or parent-child for variants): fields you filter, sort, facet, and highlight. Denormalize category name, price, stock flag, joins at query time in ES are painful.

Ingest: after SQL commit → outbox ProductUpserted → indexer consumer builds JSON doc → bulk index API. Deletes need tombstone events. Mapping changes (text → keyword) often require reindex to new index + alias swap.

Query path: search service builds bool query: must full-text, filter tenant/category (no scoring), aggregations for facets. Security: every query includes filter: { term: { tenant_id } }, missing filter = cross-tenant leak.

Lag & UX: search list may lag SQL; detail page by id can read SQL for authoritative price. Admin “preview” may force SQL or refresh=wait_for on index, expensive, not default.

Rebuild: export SQL or replay event log → bulk index; blue/green index aliases; prove you can survive total ES loss.

Ranking & ops. Business boosts (sponsored SKU) belong in query time or index time boosts, mention A/B on ranking offline; not interview deep dive but shows maturity.

Personalization vs search. Personalized ranking may stay in app layer after ES returns candidate ids, keeps index shared and simpler.

Throughput planning. Bulk indexing 5k docs/sec may require larger ES data nodes and refresh_interval tuning, mention backpressure if OLTP outpaces indexer.

What you will be able to do

  • Keep OLTP as source of truth; search as a derived index
  • Choose sync vs async index updates
  • Explain autocomplete / full-text at a high level

When this comes up

  • Full-text search
  • Autocomplete
  • Secondary index lag
  • Denormalized documents

2See it

Architecture sketch

Derived search index

OLTP remains source of truth. Search is rebuilt/synced asynchronously.

100%
ClientServiceStoreQueueOther

Walkthrough

Write then find

Staleness is part of the contract

1 / 4

100%

Admin creates a product in Postgres. That commit is the source of truth.

Read the walkthrough as text

Worked example, marketplace catalog: search + typeahead + filters.

Queries: full-text on title/description; filters category, price range, in-stock; typeahead on title; 10M products.

  1. Why not SQL: ILIKE '%laptop%' + facets on 10M rows, p95 seconds; no relevance ranking.
  2. OLTP: products table Postgres; all writes transactional; price changes here only.
  3. Indexer: transactional outbox on product update → consumer maps to ES doc {id, title, description, category_id, category_name, price_cents, in_stock, tenant_id, suggest: {input: title}}.
  4. Search API: GET /search?q=laptop&category=electronics&min_price=... → ES bool query + aggs for facet counts; never trust client filters without server validation.
  5. Autocomplete: separate suggest endpoint rate-limited; respect visible=true synced from SQL.
  6. Staleness: “usually indexed within 3s”; product detail GET /products/{id} reads SQL for checkout price.
  7. Outage: ES down → degrade to SQL id lookup + “search temporarily limited” banner; queue indexer backlog.
  8. Reindex: new mapping price_cents long → create products_v2, bulk reindex, alias flip products_current.

What interviewers listen for: SQL source of truth; async ingest; denormalized doc; tenant filter; lag honesty; rebuild story.

Napkin math

Search napkin

Postgres = source of truth

index = derived (ES / OpenSearch)

sync via CDC / outbox, lag target < 1-5s

query path hits search, not LIKE on OLTP

rebuild = reindex from SoT on corruption

Search can be stale. Writes still land in the primary DB.

3Answer it

How to answer in an interview

When “search,” “autocomplete,” “filter millions of products”:

  1. Queries OLTP cannot do, full-text, facets, prefix scale.
  2. Document fields, text vs keyword vs numeric; suggest field if needed.
  3. Update path, outbox/CDC; staleness budget.
  4. Read path, search vs SQL by id.
  5. Multi-tenant / authz filter on every query.
  6. Failure & reindex, alias swap, backfill.
  7. Anti-patterns, LIKE, ES-only writes.

Phrases: “derived index,” “denormalize for filters,” “canonical in OLTP,” “bulk reindex with alias cutover.”

  1. Degraded search. Name fallback behavior when cluster red.

Answer checklist

1. Queries the primary DB cannot serve well. 2. Index document shape and fields. 3. How updates reach the index (sync vs async). 4. Staleness users will accept. 5. Rebuild / backfill plan.

Tradeoffs & numbers

  • Async index: protects write latency; search stale seconds, acceptable for browse, not for stock-critical unless compensated.
  • Sync dual-write: mental simplicity; split-brain on partial failure, avoid in interviews unless strong repair job.
  • Denormalization: fast facets; category rename fans out reindex, batch update by query.
  • Dedicated cluster: $ and ops; worth >1M docs or heavy text.
  • Managed Algolia: speed to market; cost at scale; less control than self-hosted ES.
  • When NOT dedicated search: <50k rows, simple prefix on indexed column, Postgres tsvector may suffice.
  • Index-only price display: risky, checkout must read SQL for authoritative money fields.

Kinds of questions (4)

Same pattern, different interview surfaces. Recognise which variant you are in before you answer.

  • Full-text product search

    Example. Users query “wireless noise canceling” across 8M SKUs with relevance, not exact match. Separate canonical OLTP from search projection; state ingest lag (seconds) and rebuild path if the index corrupts.

    How. BM25 on `title^2`, `description`; filters on stock/price; SQL remains canonical; indexer via outbox. Async indexer from CDC; query may be seconds stale; fallback GET-by-id from SQL for read-after-write.

  • Autocomplete prefix index

    Example. Typeahead shows top 8 products after three characters without scanning full table. Separate canonical OLTP from search projection; state ingest lag (seconds) and rebuild path if the index corrupts.

    How. Completion field on normalized title; rate limit endpoint; hide SKUs with `visible=false` synced from OLTP. Async indexer from CDC; query may be seconds stale; fallback GET-by-id from SQL for read-after-write.

  • Async index via events

    Example. Flash sale updates 50k prices/minute; writes cannot block on ES round-trip. Separate canonical OLTP from search projection; state ingest lag (seconds) and rebuild path if the index corrupts.

    How. Bulk consumer batches 500 docs/sec; lag metric on indexer queue; detail API reads SQL price until search catches up. Async indexer from CDC; query may be seconds stale; fallback GET-by-id from SQL for read-after-write.

  • Filter-heavy catalog

    Example. 20 facet dimensions (brand, size, color) with counts beside results. Separate canonical OLTP from search projection; state ingest lag (seconds) and rebuild path if the index corrupts.

    How. Denormalize facet fields on doc; ES aggregations; avoid SQL JOIN fan-out at query time. Async indexer from CDC; query may be seconds stale; fallback GET-by-id from SQL for read-after-write.

Common mistakes

Failure modes the AI coach watches for when you practise open questions.

Search is not the ledger

Avoid. LIKE %phone% on 500M rows, or dual-write without a rebuild story.

Prefer. Async index from OLTP + stated staleness + rebuild/backfill path.

Weak sketch

Bad

100%

Stronger sketch

Good

100%
  • Search as source of truth

    Why it happens. Search engines optimize retrieval and scoring; they are not transactional ledgers for money or inventory.

    How it fails. Price updates written only to Elasticsearch; Postgres product row stale for checkout.

    Fix. OLTP commit first; index is rebuildable projection; checkout reads authoritative SQL.

  • Ignores index lag

    Why it happens. Async pipelines always have indexer lag; promises of instant browse after write set false user expectations.

    How it fails. “Search reflects every write synchronously” while using a queue-based indexer at 2k updates/sec.

    Fix. State typical lag (e.g. 2-5s); use SQL by-id for read-after-write on product detail or admin preview.

  • SQL LIKE at scale

    Why it happens. Leading wildcards prevent index use; relevance and facets do not exist; latency grows linearly with table size.

    How it fails. Architecture slide: `SELECT * FROM products WHERE title LIKE '%phone%'` at 500M rows.

    Fix. Inverted index with analyzers; denormalized filter fields; dedicated search tier or `tsvector` only at small scale.

4Practise

Concept check

A short quiz on this lesson before open questions. Optional, and it does not gate practice.

Open questions

Ready to practise?

Write an answer, get AI coaching, then revise. You are not building a real system.

Practise with an open question

All systems lessons