Performance Optimization Skill

Comprehensive frameworks for analyzing and optimizing application performance across the entire stack.

Overview

• Application feels slow or unresponsive

• Database queries taking too long

• Frontend bundle size too large

• API response times exceed targets

• Core Web Vitals need improvement

• Preparing for scale or high traffic

Performance Targets

Core Web Vitals (Frontend)

Metric Good Needs Work

LCP (Largest Contentful Paint) < 2.5s < 4s

INP (Interaction to Next Paint) < 200ms < 500ms

CLS (Cumulative Layout Shift) < 0.1 < 0.25

TTFB (Time to First Byte) < 200ms < 600ms

Backend Targets

Operation Target

Simple reads < 100ms

Complex queries < 500ms

Write operations < 200ms

Index lookups < 10ms

Bottleneck Categories

Category Symptoms Tools

Network High TTFB, slow loading Network tab, WebPageTest

Database Slow queries, pool exhaustion EXPLAIN ANALYZE, pg_stat_statements

CPU High usage, slow compute Profiler, flame graphs

Memory Leaks, GC pauses Heap snapshots

Rendering Layout thrashing React DevTools, Performance tab

Protocol References

Database Optimization

See: references/database-optimization.md

Key topics covered:

• N+1 query detection and fixes with SQLAlchemy selectinload

• Index selection strategies (B-tree, GIN, HNSW, Hash)

• EXPLAIN ANALYZE interpretation

• Connection pooling configuration

• Cursor vs offset pagination

Caching Strategies

See: references/caching-strategies.md

Key topics covered:

• Multi-level cache hierarchy (L1-L4)

• Cache-aside and write-through patterns

• Cache invalidation strategies (TTL, event-based, tag-based)

• Redis patterns (strings, hashes, lists, sets)

• Cache stampede prevention

• HTTP caching headers and ETags

Core Web Vitals

See: references/core-web-vitals.md

Key topics covered:

• LCP optimization (images, SSR, critical CSS)

• INP optimization (debounce, Web Workers, task splitting)

• CLS optimization (image dimensions, font loading)

• Measuring with web-vitals library

• Lighthouse auditing

Frontend Performance

See: references/frontend-performance.md

Key topics covered:

• Code splitting with React.lazy()

• Tree shaking and import optimization

• Image optimization (WebP, AVIF, lazy loading)

• Memoization (memo, useMemo, useCallback)

• List virtualization with @tanstack/react-virtual

• Bundle analysis tools

Profiling Tools

See: references/profiling.md

Key topics covered:

• Python profiling (cProfile, py-spy, memory_profiler)

• Chrome DevTools Performance and Memory tabs

• React DevTools Profiler

• PostgreSQL query profiling (pg_stat_statements)

• Flame graph interpretation

• Load testing with k6 and Locust

Quick Diagnostics

Database

-- Find slow queries (PostgreSQL) SELECT query, calls, mean_time / 1000 as mean_seconds FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- Verify index usage EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

Frontend

Lighthouse audit

lighthouse http://localhost:3000 --output=json

Bundle analysis

npx vite-bundle-visualizer # Vite ANALYZE=true npm run build # Next.js

Backend

Profile running FastAPI server

py-spy record --pid $(pgrep -f uvicorn) --output profile.svg

Monitoring Checklist

Before Launch

• Lighthouse score > 90

• Core Web Vitals pass

• Bundle size within budget

• Database queries profiled

• Compression enabled

• CDN configured

Ongoing

• Performance monitoring active

• Alerting for degradation

• Lighthouse CI in pipeline

• Weekly query analysis

• Real User Monitoring (RUM)

Real-World Examples

Hybrid Search Optimization

Problem: Retrieval pass rate was 87.2%, needed >90%

Solution: Increased RRF fetch multiplier from 2x to 3x, added metadata boosting

Results:

• Pass rate: 87.2% -> 91.6% (+5.1%)

• MRR: 0.723 -> 0.777 (+7.4%)

• Query time: 85ms -> 5ms (HNSW index)

LLM Response Caching

Problem: LLM costs projected at $35k/year

Solution: Multi-level cache hierarchy (Claude prompt cache + Redis semantic cache)

Results:

• Baseline: $35k/year -> With caching: $2-5k/year

• Cost reduction: 85-95%

• Latency: 2000ms -> 5-10ms (semantic cache hit)

Vector Index Selection

Problem: Vector searches taking 85ms, needed <10ms

Solution: Switched from IVFFlat to HNSW index

Results:

• Query time: 85ms -> 5ms (17x faster)

• Trade-off: Slower indexing (8s vs 2s) for faster queries

Templates Reference

Template Purpose

database-optimization.ts

N+1 fixes, pagination, pooling

caching-patterns.ts

Redis cache-aside, memoization

frontend-optimization.tsx

React memo, virtualization, code splitting

api-optimization.ts

Compression, ETags, field selection

performance-metrics.ts

Prometheus metrics, performance budget

Extended Thinking Triggers

Use Opus 4.6 adaptive thinking for:

• Complex debugging - Multiple potential causes

• Architecture decisions - Caching strategy selection

• Trade-off analysis - Memory vs CPU vs latency

• Root cause analysis - Performance regression investigation

Related Skills

• caching-strategies

• Detailed Redis caching patterns and cache invalidation

• database-schema-designer

• Indexing strategies and query optimization fundamentals

• observability-monitoring

• Performance monitoring and alerting integration

• devops-deployment

• CDN configuration and infrastructure optimization

Key Decisions

Decision Choice Rationale

Pagination strategy Cursor-based for large datasets Stable performance regardless of offset, handles concurrent inserts

Vector index type HNSW over IVFFlat 17x faster queries, worth slower indexing for read-heavy workloads

Cache hierarchy Multi-level (L1-L4) Optimizes hit rates, reduces load on expensive operations

Bundle splitting Route-based code splitting Reduces initial load, enables parallel downloads

Skill Version: 1.2.0 Last Updated: 2026-01-15 Maintained by: AI Agent Hub Team

Changelog

v1.2.0 (2026-01-15)

• Refactored to reference-based structure

• Moved detailed content to references/ directory

• Reduced SKILL.md from 1079 to ~290 lines

v1.1.0 (2025-12-25)

• Added comprehensive Frontend Bundle Analysis section

• Added React 19 performance patterns

• Added TanStack Virtual list virtualization

v1.0.0 (2025-12-14)

• Initial skill with database optimization, caching, and profiling

Capability Details

database-optimization

Keywords: slow query, n+1, query optimization, explain analyze, index, postgres performance Solves:

• How do I optimize slow database queries?

• Fix N+1 query problems with eager loading

• Use EXPLAIN ANALYZE to diagnose queries

• Add missing indexes for performance

caching-strategies

Keywords: cache, redis, cdn, cache-aside, write-through, semantic cache Solves:

• How do I implement multi-level caching?

• Cache-aside vs write-through patterns

• Semantic cache for LLM responses

• Cache invalidation strategies

frontend-performance

Keywords: bundle size, lazy load, code splitting, tree shaking, lighthouse, web vitals Solves:

• How do I reduce frontend bundle size?

• Implement code splitting with React.lazy()

• Optimize Lighthouse scores

• Fix Core Web Vitals issues

core-web-vitals

Keywords: lcp, inp, cls, core web vitals, ttfb, fid Solves:

• How do I improve Core Web Vitals?

• Optimize LCP (Largest Contentful Paint)

• Fix CLS (Cumulative Layout Shift)

• Improve INP (Interaction to Next Paint)

profiling

Keywords: profile, flame graph, py-spy, chrome devtools, memory leak, cpu bottleneck Solves:

• How do I profile my Python backend?

• Generate flame graphs with py-spy

• Profile React components with DevTools

• Find memory leaks in frontend

bundle-analysis

Keywords: bundle analyzer, vite visualizer, webpack bundle, tree shaking Solves:

• How do I analyze bundle size?

• Use Vite bundle visualizer

• Identify large dependencies

• Optimize bundle with tree shaking