Turn documents around
A full-text engine analyzes a document into terms, then builds an inverted index. Instead of storing just document → words, it stores term → documents. For the three titles, the postings for camera contain all three document IDs. Postings may also carry term frequency and positions, allowing ranked and phrase queries. Compression can make sorted postings economical, although the term dictionary, postings and auxiliary metadata still cost storage.
The first query plan now looks different. Analyze the input in a way compatible with the indexed field, find the term dictionary entry, visit its postings, apply filters, rank candidates, and retrieve the winning documents. A two-term query can intersect or combine postings before fetching all document bodies. That's the actual reason an inverted index helps. It avoids repeatedly reading the full text of unrelated documents for common query shapes.
But analysis changes semantics. A tokenizer might split punctuation. Lowercasing may normalize Camera and camera. Stemming might make running and run related, while a language-specific analyzer might behave differently. None of that guarantees literal substring matching. A user who types mera expecting to match camera is asking for infix matching, not ordinary word search. N-grams or a trigram index can support that, at meaningful index-size and false-candidate cost. PostgreSQL's pg_trgm is one practical alternative when the use case is substring matching inside a relational database, not a full search product.
Make the product requirements explicit
Suppose users want "red waterproof camera bag" and should see products with those concepts even if the title order differs. A text analyzer and relevance ranking make sense. Exact SKU matching should usually be a separate boosted or filtered path. Typo tolerance may require fuzzy term expansion, controlled to prevent explosive work on popular prefixes. Phrase search needs positions or another field representation, not just a set of words. And a user searching a restricted catalog must never see unauthorized documents merely because they matched a term.
I would keep the source database authoritative for listing updates and commercial rules. Indexing can happen through a bounded pipeline, with stable document IDs, version information and explicit freshness targets. This does not mean search needs to re-fetch all rows on every request. It means the application has to decide which fields can be served from the derived index and which decisions, such as purchase eligibility or current price, demand authoritative validation.
There is a temptation to call the index "eventually consistent" and move on. That hides a real product decision. A five-second visibility delay for new listings may be acceptable. A five-second delay for removing a prohibited listing may not be. Revoked visibility can need query-time authorization or exclusion even when indexing lags. Later lessons will address index refresh and controlled migrations. Here the essential decision is that the query's semantics determine the index structure.
Show that the index actually helped
My proof would use a representative query set, not one search for camera. Include common terms with enormous postings, rare terms, phrases, SKU lookups, prefix input, misspellings, and unauthorized candidates. Measure candidate documents examined, dictionary lookups, postings work, fetch cost, p50/p99, memory, and indexing throughput. Run the same queries on warm and cold caches. Account for the cost of indexing new and updated documents too.
With 80 million listings, an inverted index does not magically make the most common term selective. If sale appears in 60 million documents, a naive full postings traversal is still expensive. The engine needs conjunction ordering, top-K retrieval, filters, and sometimes precomputed features. A ranking query can be fast without materializing every matching document because it only needs a small set of competitive candidates.
The interview distinction I want to hear is not "use Elasticsearch". It is what access pattern became expensive, which data structure changes that cost, and which semantics the new structure still cannot promise. If you can make those three statements precisely, choosing PostgreSQL full-text search, trigram indexing, Lucene, or a managed service becomes a reasoned design decision rather than a brand preference.
