How we made search 10x faster
One GIN index, one query rewrite, and a lesson about counting rows you never show.
MAmagic-test1 min read
Search used to ILIKE '%term%' across three tables and then paginate in application code. At a few thousand items that is fine. At a few hundred thousand it is a sequential scan per keystroke.
What changed
- A GIN index on the searchable columns
- Ranking pushed into the query instead of sorting in JS
- The total-count query dropped entirely — nobody reads page 400
Median latency went from 940ms to 84ms.
- Engineering
- Performance