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.
Walkthrough
Write then find
Staleness is part of the contract
1 / 4
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.
- Why not SQL:
ILIKE '%laptop%'+ facets on 10M rows, p95 seconds; no relevance ranking. - OLTP:
productstable Postgres; all writes transactional; price changes here only. - 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}}. - Search API:
GET /search?q=laptop&category=electronics&min_price=...→ ES bool query + aggs for facet counts; never trust client filters without server validation. - Autocomplete: separate suggest endpoint rate-limited; respect
visible=truesynced from SQL. - Staleness: “usually indexed within 3s”; product detail
GET /products/{id}reads SQL for checkout price. - Outage: ES down → degrade to SQL id lookup + “search temporarily limited” banner; queue indexer backlog.
- Reindex: new mapping
price_centslong → createproducts_v2, bulk reindex, alias flipproducts_current.
What interviewers listen for: SQL source of truth; async ingest; denormalized doc; tenant filter; lag honesty; rebuild story.
Napkin math
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”:
- Queries OLTP cannot do, full-text, facets, prefix scale.
- Document fields, text vs keyword vs numeric; suggest field if needed.
- Update path, outbox/CDC; staleness budget.
- Read path, search vs SQL by id.
- Multi-tenant / authz filter on every query.
- Failure & reindex, alias swap, backfill.
- Anti-patterns, LIKE, ES-only writes.
Phrases: “derived index,” “denormalize for filters,” “canonical in OLTP,” “bulk reindex with alias cutover.”
- Degraded search. Name fallback behavior when cluster red.
Answer checklist
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
tsvectormay 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
Stronger sketch
Good
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
- about 8 min
Product full-text
Users search product titles and descriptions with relevance ranking across millions of SKUs. OLTP is Postgres. Design the search index role, document fields, and why SQL alone is insufficient.
- about 8 min
Autocomplete
As users type in the search box, you must suggest product titles within 50ms. How do you index prefixes, keep suggestions aligned with product visibility in SQL, and avoid scanning the primary DB?
- about 8 min
Async indexer
Admins create products in SQL; shoppers complain new items appear in search 30-60 seconds late. Explain an event-driven index update path, acceptable lag you would document, and what you would not promise.
- about 8 min
Facet catalog
Users filter by category, brand, price range, and in-stock on millions of products. Design the search document shape and query style without full table scans in Postgres.
Ready to practise?
Write an answer, get AI coaching, then revise. You are not building a real system.