Instrucciones de origen · Vista previa de solo lectura
name
performance-skill
description
Performance optimization patterns for multi-tenant SaaS at scale — backend (query optimization, N+1 detection, caching, connection pooling, BullMQ tuning), frontend (bundle analysis, React profiling, virtualization, lazy loading), and database (indexing, query plans, TimescaleDB optimization). Trigger when diagnosing slow queries, optimizing page load, reducing bundle size, tuning job queues, or planning for scale. If the user mentions "slow", "performance", "latency", "bundle size", "N+1", "cache", "pool", "EXPLAIN", "index", "profiling", "virtualization", "pagination", "memory leak", "CPU", "p95", "p99", "throughput", or "bottleneck" — use this skill.
SKILL: Performance Optimization for Multi-Tenant SaaS at Scale
Stack
Backend: NestJS + Prisma/Drizzle + PostgreSQL + TimescaleDB + BullMQ + Redis
Frontend: Next.js 16 + React 19 + TanStack Query + TanStack Table
Scale context: Largest customer has 50,000 recipients across 4 workspaces, 5 domains, 35,000 records in a single workspace.
IDENTITY
You are a senior performance engineer. You:
Measure before optimizing — never guess where the bottleneck is
Set performance budgets and enforce them with automated tooling
Optimize the critical path first, ignore cold paths until they matter
Understand that multi-tenant systems have unique performance characteristics — one tenant's load pattern can degrade all tenants
Think in percentiles (p50, p95, p99), not averages
PERFORMANCE BUDGETS — NON-NEGOTIABLE
Metric
Target
Measurement
API response (p95)
< 200ms
Server-side middleware timer
API response (p99)
< 500ms
Server-side middleware timer
Page load (LCP)
< 2s
Lighthouse CI
First Input Delay (FID)
< 100ms
Lighthouse CI
Cumulative Layout Shift (CLS)
< 0.1
Lighthouse CI
Table render (10K rows)
< 500ms
React Profiler
Table render (35K rows)
< 1.5s
React Profiler (virtualized)
Bundle size (main chunk)
< 200KB gzip
next/bundle-analyzer
Total JS transferred
< 500KB gzip
Lighthouse CI
Database query (hot path)
< 50ms
pg_stat_statements
Database query (dashboard)
< 200ms
pg_stat_statements
Redis cache hit
> 90%
Redis INFO stats
BullMQ job throughput
> 100 jobs/sec
BullMQ dashboard
Memory (API server)
< 512MB RSS
Process monitoring
Connection pool wait
< 10ms (p95)
PgBouncer stats
When a budget is violated: That is a production incident. Investigate immediately.
PERFORMANCE DIAGNOSIS FLOW
When something is slow, follow this exact sequence. Do NOT skip steps.
Step 1: WHERE is it slow?
├── API endpoint? → Step 2a
├── Page load? → Step 2b
├── Background job? → Step 2c
└── Database? → Step 2d
Step 2a: API Endpoint Diagnosis
├── Add timing middleware → identify which layer is slow
│ controller → service → repository → database
├── Check pg_stat_statements → is the query slow?
├── Check Redis → is caching working? Hit ratio?
├── Check connection pool → are queries waiting for connections?
└── Check N+1 → is the service making N queries in a loop?
Step 2b: Page Load Diagnosis
├── Run Lighthouse → which metric fails?
├── Run bundle analyzer → which package is too large?
├── React Profiler → which component re-renders too often?
├── Network tab → which request is the waterfall bottleneck?
└── Check server-side → is getServerSideProps/RSC fetch slow?
Step 2c: Background Job Diagnosis
├── BullMQ dashboard → is the queue backed up?
├── Check concurrency → too low? Too high (pool exhaustion)?
├── Check rate limits → per-domain throttling appropriate?
├── Check batch size → too small (overhead) or too large (memory)?
└── Check external API → rate limited? Timeout?
Step 2d: Database Diagnosis
├── EXPLAIN ANALYZE the query
├── Check for sequential scans on large tables
├── Check for missing indexes
├── Check for lock contention (pg_stat_activity)
├── Check for bloat (pg_stat_user_tables)
└── Check TimescaleDB chunk sizing
See frontend-perf.md for bundle optimization, image optimization, and Lighthouse CI setup.
DATABASE PERFORMANCE
Detailed patterns with SQL examples: see database-perf.md
Index Strategy for Multi-Tenant
-- HOT PATH: always create (every CRUD operation filters by domain)CREATE INDEX idx_{table}_domain ON {table}(domain_id);
-- DASHBOARDS: add when proven (cross-domain reporting)CREATE INDEX idx_{table}_workspace ON {table}(workspace_id);
-- BILLING: add when proven (subscription enforcement)CREATE INDEX idx_{table}_company ON {table}(company_id);
-- NEVER: composite with all three (wastes space, rarely matches query pattern)-- BAD: CREATE INDEX idx_bad ON {table}(company_id, workspace_id, domain_id);
EXPLAIN ANALYZE Reading Guide
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT*FROM recipients WHERE domain_id ='abc'AND email LIKE'%@example.com';
What to look for:
Seq Scan on table with >1K rows → missing index
Nested Loop with high row count → potential N+1 at DB level
Sort without Index Scan → missing sort index
Hash Join with large hash table → consider increasing work_mem
Buffers: shared read >> shared hit → cold cache, data not in memory
TimescaleDB Optimization
-- Chunk interval: adjust based on data volume-- Rule: each chunk should hold 25% of available memory worth of dataSELECT create_hypertable('email_events', 'time',
chunk_time_interval =>INTERVAL'7 days');
-- Retention: drop old chunks automaticallySELECT add_retention_policy('email_events', INTERVAL'90 days');
-- Compression: compress chunks older than 7 daysALTER TABLE email_events SET (
timescaledb.compress,
timescaledb.compress_segmentby ='domain_id',
timescaledb.compress_orderby ='time DESC'
);
SELECT add_compression_policy('email_events', INTERVAL'7 days');
-- Continuous aggregates: pre-computed rollupsCREATE MATERIALIZED VIEW email_events_hourly
WITH (timescaledb.continuous) ASSELECT
domain_id,
time_bucket('1 hour', time) AS bucket,
event_type,
COUNT(*) AS count
FROM email_events
GROUPBY domain_id, time_bucket('1 hour', time), event_type
WITHNO DATA;
SELECT add_continuous_aggregate_policy('email_events_hourly',
start_offset =>INTERVAL'3 hours',
end_offset =>INTERVAL'1 hour',
schedule_interval =>INTERVAL'1 hour');
See database-perf.md for complete index strategy, query plan analysis, pg_stat_statements setup, vacuum tuning, and partitioning.
PERFORMANCE ANTI-PATTERNS — NEVER DO THESE
Backend Anti-Patterns
Anti-Pattern
Why It's Bad
Fix
SELECT *
Fetches unused columns, wastes I/O
Select only needed columns
Query in a for loop
N+1 — linear query growth
Use JOIN or inArray()
No pagination
Fetches unbounded rows
Cursor-based pagination
Caching without TTL
Stale data forever
Always set TTL
Caching without invalidation
Stale data until TTL
Invalidate on mutation
JSON.parse large payloads synchronously
Blocks event loop
Stream parse, or offload to worker
Synchronous file I/O
Blocks event loop
Use fs.promises or streams
Unbounded Promise.all
Memory explosion
Use pLimit or chunked batches
Global rate limit instead of per-tenant
One tenant starves others
Rate limit per domain
No connection pooling
Connection churn
Use PgBouncer
Frontend Anti-Patterns
Anti-Pattern
Why It's Bad
Fix
Fetching all rows client-side
Memory explosion, slow render
Server-side pagination
No virtualization for large tables
DOM node explosion
TanStack Virtual
useEffect for derived state
Unnecessary re-renders
useMemo
Importing entire icon library
Bundle bloat
Tree-shakeable imports
No staleTime in TanStack Query
Refetch on every mount
Set appropriate staleTime
useQuery in a loop
N+1 at the API level
Batch endpoint or single query
Images without next/image
No lazy loading, no optimization
Always use next/image
No code splitting
Monolithic bundle
dynamic() for heavy routes
Database Anti-Patterns
Anti-Pattern
Why It's Bad
Fix
Composite index (company_id, workspace_id, domain_id)
