# SneakerPicks.nl Performance Audit

**Date:** 2026-02-20  
**Stack:** Astro SSR + Tailwind v4.2 + PostgreSQL  
**Data:** ~4,100 products, ~3,700 offers

---

## 📊 Current Performance Summary

| Page | Response Time | Status |
|------|--------------|--------|
| Homepage (`/`) | ~339ms | ✅ OK |
| Sneakers listing (`/sneakers`) | ~230ms | ✅ OK |
| Product detail (staging) | ~458ms | ⚠️ Slow-ish |

Server response times are acceptable but can be improved significantly with caching.

---

## 🔴 Critical Issues

### 1. Missing Index on `product_offers.product_id` — **HIGH IMPACT**

**Problem:** The `product_offers` table has NO index on `product_id`. Every JOIN between `products` and `product_offers` does a sequential scan on all 3,700+ rows. The EXPLAIN ANALYZE confirms seq scans on both tables.

**Current indexes on product_offers:** `pkey(id)`, `feed_id+external_id`, `feed_id`, `last_seen_at`  
**Missing:** `product_id`, `(product_id, in_stock)` composite

**Fix:**
```sql
CREATE INDEX idx_product_offers_product_instock ON product_offers (product_id, in_stock) WHERE in_stock = true;
CREATE INDEX idx_product_offers_product_price ON product_offers (product_id, price) WHERE in_stock = true;
```

**Impact:** Query time reduction from ~14ms → ~3-5ms. More impactful as data grows.

### 2. No Caching Layer — **HIGH IMPACT**

**Problem:** Zero caching found in the codebase. Every page load hits the database multiple times:
- **Homepage:** 4 DB queries (trending, price drops, product count, shop count)
- **Sneakers listing:** 3 DB queries (products, count, brands)
- **Product detail:** 2 DB queries (product + offers)

**Fix:** Add in-memory cache with TTL (no Redis needed at this scale):

```typescript
// src/lib/cache.ts
const cache = new Map<string, { data: any; expires: number }>();

export function cached<T>(key: string, ttlMs: number, fn: () => Promise<T>): Promise<T> {
  const hit = cache.get(key);
  if (hit && hit.expires > Date.now()) return Promise.resolve(hit.data);
  return fn().then(data => { cache.set(key, { data, expires: Date.now() + ttlMs }); return data; });
}
```

Recommended TTLs:
- Product count, shop count, brands: **5 minutes**
- Trending/popular products: **2 minutes**
- Product listing pages: **1 minute**
- Product detail: **2 minutes**

**Impact:** ~80% reduction in DB queries. Homepage goes from 4 queries to 0 on cache hit.

### 3. CartesianChart.js is 308KB — **HIGH IMPACT**

**Problem:** `CartesianChart.zK4JLge4.js` (308KB) + `BarChart.Dp1-1nHK.js` (27KB) + `AnalyticsCharts.CkaRvFMz.js` (21KB) + `PriceHistoryChart.CHXGEgXJ.js` (14KB) = **~370KB of chart JS** shipped to clients.

**Fix options:**
1. **Lazy load charts** — Only load chart JS when the chart component scrolls into view
2. **Replace with lightweight alternative** — Use `chart.css` or a smaller lib for simple price history
3. **Admin-only charts** — If AnalyticsCharts/BarChart are admin-only, ensure they're not in the public bundle
4. **Dynamic import** — `const Chart = await import('./CartesianChart')` in client components

**Impact:** ~370KB less JS for most page loads. Massive improvement on mobile.

---

## 🟡 Medium Issues

### 4. Caddy Has No Static Asset Caching

**Problem:** The Caddyfile is bare-bones — just `reverse_proxy`, no cache headers for static assets.

**Fix:** Update Caddyfile:
```
sneakerpicks.nl, www.sneakerpicks.nl {
    reverse_proxy 172.18.0.1:3001

    header /_astro/* {
        Cache-Control "public, max-age=31536000, immutable"
    }

    header /favicon* {
        Cache-Control "public, max-age=86400"
    }

    log {
        output stdout
        format console
    }
}
```

Astro already hashes filenames in `_astro/`, so immutable caching is safe.

**Impact:** Repeat visits load instantly. Saves bandwidth significantly.

### 5. Homepage Makes 4 Sequential DB Queries

**Problem:** `index.astro` calls `getTrendingFromDB`, `getPriceDropsFromDB`, `getProductCount`, `getShopCount` — likely sequential (awaited one by one).

**Fix:** Use `Promise.all()`:
```typescript
const [trending, priceDrops, totalProductsRaw, totalShops] = await Promise.all([
  getTrendingFromDB(8),
  getPriceDropsFromDB(6),
  getProductCount(),
  getShopCount(),
]);
```

**Impact:** Homepage DB time reduced from ~56ms (4×14ms) to ~14ms (parallel).

### 6. CSS Bundle is 37.6KB (Single File)

**Problem:** Only one CSS file (`_slug_.BHlTTw7I.css`, 37.6KB) serves all pages. Not terrible, but worth checking if Tailwind is purging properly.

**Check:** The file is for the slug page specifically. Tailwind v4.2 purges by default. **Likely OK** — just verify it's not loaded on pages that don't need it.

### 7. Product Images Not Optimized

**Problem:** Images come from external URLs (`image_url` from feeds). No WebP conversion, no srcset, no CDN proxy. Only `loading="lazy"` on ProductCard (good).

**Fix options:**
1. **Image proxy with Caddy or Cloudflare** — Resize + convert to WebP on the fly
2. **Use Astro's `<Image>` component** — Handles optimization for local images
3. **Cloudflare in front** — Free plan gives image optimization + CDN

**Impact:** 30-50% image size reduction. Faster LCP on listing pages.

---

## 🟢 Low Priority / Already Good

### 8. Database Query Pattern ✅
- No N+1 queries — `getProductsFromDB` correctly fetches products first, then batch-fetches offers with `ANY($1)`
- Query structure is solid

### 9. Database Indexes ✅ (Mostly)
- `idx_products_slug` exists (for product detail lookups)
- `idx_products_brand` exists (for brand filtering)
- `idx_products_search` (GIN) exists (for search)
- Only missing: `product_offers.product_id` (see #1)

### 10. Astro SSR ✅
- Astro renders server-side, sending minimal JS to client
- No unnecessary hydration detected for listing pages

---

## 📋 Prioritized Action Plan

| # | Action | Effort | Impact | Est. Speedup |
|---|--------|--------|--------|-------------|
| 1 | Add `product_offers(product_id, in_stock)` index | 5 min | 🔴 High | -10ms/query |
| 2 | Add in-memory cache layer | 1 hour | 🔴 High | -80% DB load |
| 3 | `Promise.all()` for homepage queries | 10 min | 🟡 Medium | -40ms homepage |
| 4 | Caddy static asset cache headers | 10 min | 🟡 Medium | Instant repeat visits |
| 5 | Lazy load chart JS (or remove from public) | 30 min | 🔴 High | -370KB JS |
| 6 | Image optimization (Cloudflare/proxy) | 2 hours | 🟡 Medium | -30-50% images |

### Quick Wins (< 30 minutes total):
```sql
-- Run on production DB:
CREATE INDEX CONCURRENTLY idx_product_offers_product_instock 
  ON product_offers (product_id, in_stock) WHERE in_stock = true;
```

```typescript
// In src/pages/index.astro — parallelize queries:
const [trending, priceDrops, totalProductsRaw, totalShops] = await Promise.all([
  getTrendingFromDB(8), getPriceDropsFromDB(6), getProductCount(), getShopCount()
]);
```

```
# In Caddyfile — add cache headers for hashed assets:
header /_astro/* Cache-Control "public, max-age=31536000, immutable"
```