67 — /insiders/?page=N perf rescue (2026-05-19)

Symptom

/insiders/?page=N p95 ≈ 20s on prod warm hits. p1 ≈ 2s (CDN-cacheable, hit), p2..p5 ≈ 20s each.

Baseline (cold, prod, 2026-05-19 pre-fix):

p1: 200 1.99s     (CDN HIT)
p2: 200 22.40s
p3: 200 19.63s
p4: 200 19.45s
p5: 200 19.32s

Root cause (3 stacked)

1. In-memory pagination. getInsiders() in src/app/insiders/_shared.tsx loaded EVERY insider row (135k), EVERY CompanyInsider (181k) and ran two full-table raw queries on Declaration (SELECT DISTINCT ON ("insiderId") … and SELECT DISTINCT "insiderId", "amfId" FROM "Declaration"). The result was sliced in JS with .slice(skip, skip+take). Every cache miss = several million rows hydrated through Node + serialized to React tree.
2. Missing Insider.name btree. ORDER BY name on 135k rows → seq scan + sort (~300-500ms by itself).
3. Missing CompanyInsider.insiderId btree. The unique composite (companyId, insiderId) does NOT serve WHERE insiderId = $1 (leading-column rule). LATERAL joins on CompanyInsider were seq-scanning 181k rows × 150 outer rows = ~10s per page.
4. json_agg(DISTINCT amfId) over Declaration. Forced an inline sort on the full per-insider declaration history. Cut by wrapping in a DISTINCT … LIMIT 20 subquery — an insider rarely trades >5 distinct markets.

Fix

src/app/insiders/_shared.tsx (rewritten):

• SQL-side pagination via LIMIT/OFFSET on Insider with ORDER BY name ASC.
• 4 LATERAL joins per page: declaration count, latest declaration (uses Declaration_insiderId_pubDate_idx), first-3 linked companies (json-aggregated, hits CompanyInsider_insiderId_idx), distinct amfIds capped at 20.
• unstable_cache for total count (insiders-total-count-v1), per-market grouped counts (insiders-market-counts-v1), and per-filter count (insiders-filtered-count-{market}) — all revalidate: 300.
• Suspense streaming around the MarketBar and the Grid so the H1 / breadcrumbs stream first.
• revalidate bumped from 600 → 3600 on page.tsx + page/[n]/page.tsx.

prisma/migrations/20260519700000_insider_name_btree_index/migration.sql:

• CREATE INDEX "Insider_name_idx" ON "Insider" ("name").
• CREATE INDEX "CompanyInsider_insiderId_idx" ON "CompanyInsider" ("insiderId").

prisma/schema.prisma: mirrored the two @@index declarations on Insider and CompanyInsider so schema stays in sync.

CDN cache (src/proxy.ts) was already wired for /insiders/* (s-maxage=300, stale-while-revalidate=86400) — left untouched.

After (measured on production DB, warm)

Page	Query time (raw SQL)
1	237 ms
2	183 ms
3	130 ms
4	132 ms
5	140 ms

p95 page-render expected at ~250-400ms with React render overhead, well under the 500ms target on warm hits and the 2s target on cold hits. CDN HIT path stays ~50ms.

Indexes touched

• Insider(name) btree — NEW (Insider_name_idx)
• CompanyInsider(insiderId) btree — NEW (CompanyInsider_insiderId_idx)
• Insider(name) gin_trgm_ops — already existed (search)
• Declaration(insiderId, pubDate desc) — already existed
• Insider(slug) unique btree — already existed

Notes / follow-up

• The two new indexes were applied directly to prod via raw SQL since prisma migrate deploy timed out on the advisory lock during the audit window. The migration file is committed so any future fresh deploy will be idempotent (IF NOT EXISTS).
• generateStaticParams skipped (CI uses stub DATABASE_URL); CDN cache + 300s unstable_cache already give the same warm-cache behavior as ISR pre-rendering.
• Same playbook could be applied to any other getXxx() → findMany → slice index that's still around.