Cutting search latency from 40s to under 6s across 200,000+ SKUs

The symptom

On CloudQuarks — a B2B/B2C marketplace with 200,000+ store-specific SKUs — catalog search with more than two active filters (price range, vendor, availability, custom attributes) was taking up to 40 seconds to return. Below two filters it was fine. That gap was the first real clue: this wasn't a "the database is slow" problem, it was a specific query shape problem.

Step 1: EXPLAIN ANALYZE before touching anything

Before adding a single index, I ran EXPLAIN (ANALYZE, BUFFERS) on the actual slow query with production-scale data. The plan showed a sequential scan on the products table for every combined filter, and a nested loop join against a JSONB metadata column that had no index coverage at all. The optimizer had no good option — it was scanning close to the full 200k rows for every multi-filter request.

This step matters because it is easy to guess wrong. My first instinct was "add an index on price" — the actual bottleneck turned out to be the JSONB attribute filtering, which a plain B-Tree index on price would not have touched at all.

Step 2: composite and GIN indexes, not more RAM

I added a composite B-Tree index covering (store_id, status, price) for the common filter combination, and a GIN index on the JSONB metadata column for attribute-based filtering. Together with the pagination change below, that removed the full scans from the plan — a real improvement, but repeated searches were still doing the same work every time.

I also switched pagination from OFFSET/LIMIT to keyset (cursor-based) pagination. OFFSET pagination degrades linearly as page depth increases because Postgres still has to scan and discard every prior row; keyset pagination seeks straight to the cursor on an indexed sort key, so deep pages stay cheap, which matters a lot on a 200k-row catalog. It stops helping when you need to jump to an arbitrary page number or sort on a column with no index.

Step 3: Redis, and only where it earns its keep

Indexing fixed the database-side cost, but repeated identical filter combinations (which is most real traffic — a handful of filter combos account for the majority of search volume) were still round-tripping to Postgres every time. I added a Redis caching layer keyed by a deterministic hash of the normalized filter set, with a short TTL and tag-based invalidation tied to catalog and pricing update events, so a price change invalidates only the affected cache tags instead of flushing everything.

With the query changes, indexes and Redis caching in place, advanced search with several active filters dropped from ~40 seconds to under 6 seconds.

What I would tell someone hitting the same wall

Profile before you index, index before you cache, and never cache a query you have not first made fast on a cold path — otherwise a cache miss (a new filter combination, a cold start, an invalidation) still hits the same 40-second query underneath. The cache should be a speed multiplier on an already-reasonable query, not a bandage over a broken one.