Design a Search Indexing Pipeline

Type "wireles headphone" into PhoenixWorld's search box and you expect the right products, ranked well, spelled-forgiven, back in a few dozen milliseconds. Behind that box is a search engine whose whole job is relevance: an inverted index that scores documents against fuzzy human queries. But that index is a copy. The truth lives in Postgres, where prices change, inventory drops to zero, products get discontinued, and descriptions get edited all day long. A search indexing pipeline is the machine that keeps the copy honest.

PhoenixWorld's search is drifting. A product's price changed in Postgres an hour ago, but search still shows the old one. A discontinued item still appears in results, and customers click through to a dead page. The cause is the pipeline that was supposed to keep search current: today the checkout service writes to Postgres and then calls the search cluster to update the document. When the second call fails, or the service crashes between the two, Postgres and search disagree, and nobody notices until a customer does.

Sana, who built the clickstream event pipeline earlier, gets handed the problem because it's the same shape: a stream of changes that has to land somewhere derived, in order, without loss. Her first realization is that the current design is broken at the root. Writing to two systems in one breath, the database and the search index, is the dual-write problem, and it cannot be made reliable by adding retries. This chapter is about doing it the only way that actually stays consistent: derive the index from the database's own change log, and treat the index as a disposable projection you can always rebuild.

Step 1 - Understand the Problem and Establish Design Scope

"Keep search up to date" hides several systems: the relevance and ranking engine, the query-serving path, and the indexing pipeline. The clarifying questions pin down that we're building the last one.

Candidate: Am I designing the search relevance and query serving, or the pipeline that keeps the index current?

Interviewer: Just the indexing pipeline. Assume a search engine with an inverted index already exists and serves queries well. Your job is to make sure what it holds matches the catalog.

Candidate: What's the source of truth, and how big is the catalog?

Interviewer: Postgres. About 50 million products, spread across products, categories, sellers, inventory, and reviews tables. A search result needs fields from several of those at once.

Candidate: How fresh does the index need to be after a change in Postgres?

Interviewer: Seconds to a minute is fine. If a price changes, search can lag briefly. This is discovery, not a checkout ledger, so eventual consistency is acceptable, as long as it actually converges and doesn't drift forever like today.

Candidate: What kinds of changes drive updates?

Interviewer: Per-product edits (price, inventory, description), new and discontinued products, review activity, and occasionally a change to a shared entity, like renaming a category or a seller changing their display name, which touches many products at once.

Candidate: How correct does it have to be? Can a product be briefly wrong?

Interviewer: Briefly wrong is fine; permanently wrong is not. A discontinued product must eventually leave the index, and a stale update must never overwrite a newer one. The index should converge to the source.

Candidate: Do we ever need to rebuild the whole index, and can search go down while we do?

Interviewer: Yes, regularly, whenever the mapping or analyzer changes you reindex all 50 million. And no, search absolutely cannot go down. It's on the revenue path.

With that, the shape is clear: a change-driven, eventually-consistent projection that must converge, tolerate shared-entity fan-out, and be rebuildable without downtime.

Functional requirements

• Reflect every relevant Postgres change (insert, update, delete, across several tables) into a denormalized search document, within the freshness window.
• Assemble each search document from multiple source tables (product plus category, seller, inventory, review aggregates).
• Remove discontinued or deleted products from the index.
• Rebuild the entire index (a full reindex) with zero search downtime.
• Backfill or repair a subset of the index safely, without a maintenance window.