An E-commerce Search That No Longer Keeps Guests Waiting

The challenge

The platform — a specialty home goods retailer — ran its product catalog on PostgreSQL. Twelve million SKUs across 400 categories, with faceted filters for price, brand, rating, availability, and material. The search infrastructure was PostgreSQL-backed: full-text search with tsvector for keyword matching, and multi-column filters for faceted navigation.

During normal traffic — 2,000 concurrent sessions — search queries ran in 80-150ms. Serviceable for everyday use. During the previous Black Friday, traffic spiked to 18,000 concurrent sessions. Search latency climbed to 3.2 seconds. Cart abandonment rates increased 34% compared to the hour before the spike.

The engineering team (eight people, Rails stack with pg_search) had two months before the next Black Friday. The options they considered: migrate search to Elasticsearch (estimated 6-8 weeks), add read replicas (estimated $3,200/month in additional infrastructure), or optimize the existing queries.

They decided to explore optimization first.

The setup

Gold Lapel was deployed as a sidecar container in the existing Kubernetes cluster. Each API pod received a Gold Lapel container, connecting upstream to the primary PostgreSQL instance through the existing PgBouncer pool.

# Kubernetes deployment — Gold Lapel as a sidecar
containers:
  - name: goldlapel
    image: goldlapel/goldlapel:latest
    args:
      - --upstream
      - 'postgresql://shop:password@pg-primary:5432/ecommerce'
    ports:
      - containerPort: 7932

# App container points DATABASE_URL at localhost:7932
  - name: api
    env:
      - name: DATABASE_URL
        value: postgresql://shop:password@localhost:7932/ecommerce

Deployment was completed in a single pull request. The Rails application's database configuration was unchanged except for the connection host.

What Gold Lapel found

Within the first four hours of production traffic, the proxy identified three categories of optimization opportunity:

• Missing composite indexes. The most common faceted search pattern — category + price range + availability — was hitting a sequential scan on a 12-million-row table. PostgreSQL had single-column indexes on each field, but no composite index matching the actual filter combination. Gold Lapel created a composite index with the correct column order (category, availability, price) and a partial index for the common WHERE available = true case.
• Facet count aggregation. Every search page displayed facet counts — "Brand X (142 results), Brand Y (89 results)" — computed by a GROUP BY query that ran once per page load, scanning the full filtered result set. Gold Lapel materialized the most common category-level facet counts, reducing a 400ms aggregation to a 6ms view lookup.
• N+1 product detail fetches. The Rails serializer was loading product images and reviews in separate queries per product — a classic N+1 pattern. For a page of 48 products, this meant 97 queries (1 search + 48 images + 48 reviews). Gold Lapel detected the pattern and batched them into 3 queries.

The results

Black Friday

The real test arrived eight weeks after deployment. Black Friday traffic peaked at 22,000 concurrent sessions — 22% higher than the previous year's spike that had caused the 3.2-second latency.

Search P95 latency during peak: 45ms. No degradation from the baseline. Database CPU peaked at 48% (compared to 94% the previous year at lower traffic). The Elasticsearch migration was no longer needed.

The additional infrastructure cost: zero. No read replicas were added. The existing PostgreSQL instance handled the load because the queries reaching it were dramatically more efficient.

Key takeaways

• Optimization before scaling is worth exploring. The instinct to add infrastructure — read replicas, Elasticsearch, bigger instances — is entirely reasonable. But a 3.2-second query running on a read replica is still a 3.2-second query. Optimization addresses the root cause; scaling distributes the load.
• Composite indexes make a significant difference. Single-column indexes are necessary but insufficient for multi-filter queries. The correct composite index turned a sequential scan into an index-only scan — from 890ms to 32ms on a 12-million-row table.
• N+1 patterns can appear in unexpected places. The Rails team had done thorough work optimizing their ActiveRecord queries. The serializer layer, generating 96 additional queries per page load, was a less obvious source. Automatic detection complements manual review well.
• Peak traffic reveals steady-state problems. The 3.2-second latency at Black Friday was the same performance problem that caused 150ms latency on normal days — just amplified by concurrency. Fixing the query efficiency solved both.