A separate search index

Type "biryani" into Tiffin's search box. You expect restaurants that serve biryani, near you, open now, the good ones first. You also expect "biriyani", "briyani" and "chicken dum biryani" to work, and you expect the results before you finish typing.

For the first year, a SQL query did this. Now Tiffin has 15,000 restaurants and about a million dishes across several cities, and search is the slowest page in the app. This lesson is about why, and about the usual fix: a separate system built only for search.

Why the main database struggles

Here's roughly what Tiffin's search query looked like:

SELECT r.id, r.name, r.rating
FROM restaurants r
JOIN menu_items d ON d.restaurant_id = r.id
WHERE r.city_id = 3
  AND r.is_open = true
  AND (d.name ILIKE '%biryani%' OR r.name ILIKE '%biryani%')
GROUP BY r.id
ORDER BY r.rating DESC
LIMIT 20;

It has three problems that no amount of tuning fixes.

The index can't help. A normal B-tree index (Module 2) is sorted, like a phone book. It can find names that start with "biryani". It cannot find names that contain it, because "Chicken Dum Biryani" is filed under C. So ILIKE '%biryani%' reads every dish in the city. With 250,000 dishes in a big city that's tens of milliseconds per search on a good day, and much worse when many people search at once.

It doesn't understand language. "Biriyani" finds nothing. Neither does "biryanis". Searching "paneer pizza" requires both words, in that order, unless you write a much more complicated query.

Ranking is crude. ORDER BY rating puts a 4.8-star restaurant that mentions biryani once above a 4.5-star biryani specialist down the road. Real search ranks by how well the text matches, how close the restaurant is, how popular it is, and more, all at once.

And all of this runs on the same primary or replica that serves orders. At 8pm, thousands of searches a minute compete with checkout.

What a search engine does differently

Search engines like Elasticsearch and OpenSearch (both built on the Lucene library) are built around a different kind of index.

An inverted index maps each word to the list of documents that contain it, the way the index at the back of a book maps a word to page numbers.

Instead of "dish 81 has the name Chicken Dum Biryani", it stores the reverse:

biryani  -> dish 81, dish 204, dish 3310, ...
chicken  -> dish 81, dish 95, dish 1200, ...
dum      -> dish 81, dish 4410, ...

Finding every dish containing "biryani" is now one lookup, not a scan. Finding "chicken biryani" means intersecting two lists, which is fast because the lists are sorted.

Before a word goes into the index, it's passed through an analyzer that lower-cases it, strips plurals ("biryanis" becomes "biryani") and can add synonyms ("veg" means "vegetarian"). The same analyzer runs on the search text, so both sides match. Fuzzy matching handles typos by also accepting words one or two letters different, so "biriyani" still finds biryani.

On top of that, search engines are good at:

• Relevance scoring. Rare words in a query count for more than common ones, and matches in the restaurant name can count more than matches in a dish description.
• Geo filters and sorting. "Within 5 km of this point, nearest first" is a built-in query.
• Facets. "Veg only (312), Under 30 min (90), Offers (45)" counts come back with the results.
• Autocomplete. Suggestions as you type, from a prefix index.

How it fits into Tiffin

The search engine does not replace Postgres. Postgres stays the source of truth: the one place where a restaurant's real menu and opening hours live. The search index is a copy, shaped for searching.

%% caption: Writes go to Postgres. A sync process copies changes into the search index. Searches read only from the index.
flowchart TD
  RT([Restaurant tablet]) -- "edits menu" --> S[Server]
  CA([Customer app]) -- searches --> S
  S -- writes --> PG[("Postgres<br/>source of truth")]:::store
  PG -- changes --> SJ[Sync job]
  SJ --> SI[("Search index<br/>Elasticsearch")]:::hot
  S -- "search queries" --> SI

A search for "biryani near me" goes to the search index and comes back with a list of restaurant IDs, names, ratings and distances: enough to draw the results page. When the customer taps a restaurant, the app loads the real menu and prices from Postgres (or the cache in front of it).

That split matters. Search results can be a few seconds out of date. The menu you order from, and the price you pay, must not be.

What goes in the index

You don't copy tables one to one. You build one document per thing a customer searches for, with everything needed to filter and rank it:

{
  "restaurant_id": 4812,
  "name": "Paradise Biryani House",
  "city_id": 3,
  "location": { "lat": 18.5204, "lon": 73.8567 },
  "cuisines": ["hyderabadi", "mughlai"],
  "dishes": ["chicken dum biryani", "mutton biryani", "double ka meetha"],
  "is_veg": false,
  "rating": 4.5,
  "avg_prep_minutes": 22,
  "is_open": true
}

Joins happen once, when the document is built, not on every search. This is called denormalising, and it's normal for a search index.

Keeping the index in sync

This is the hard part. When a restaurant marks a dish as sold out or closes for the night, the index has to find out. Three common ways, from simplest to most robust:

For Tiffin today, the periodic job is a fine start: every 30 seconds, select rows with updated_at newer than the last run, rebuild those documents, and send them to the index in bulk. Menu and opening-hours changes are rare compared to searches, and 30 seconds of staleness is acceptable.

As Tiffin grows, the change-stream approach becomes the standard one. Module 15 covers the outbox pattern for publishing events reliably, and Module 17 covers change data capture, which reads changes straight from the database's log.

Whatever you choose, plan for a full rebuild. You'll change the analyzer, add a field, or find the index drifted. Build a fresh index from Postgres alongside the live one, then switch an alias to point at it. Searches never notice.

Should you add it yet?

A search cluster is a real new system: three or more nodes for redundancy, its own memory and disk sizing, upgrades, monitoring, and a sync pipeline that can break. Before adding it, try the steps in between.

Postgres can do a lot more than ILIKE:

• Trigram indexes (pg_trgm) make ILIKE '%biryani%' and fuzzy "similar to" matches use an index.
• Full-text search (tsvector with a GIN index) gives you stemming, multiple words and basic ranking.
• PostGIS handles "within 5 km" efficiently.

These scale further than people expect, to millions of rows, and you keep one database and no sync problem. Move to a dedicated engine when you need relevance tuning, typo tolerance, autocomplete and facets together, or when search load is big enough that you want it off the database entirely. For Tiffin, with 15,000 restaurants, a million dishes and search as the first thing every customer does, that time has come.