performance-optimization（性能优化）

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)

Backend Targets

Bottleneck Categories

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

Extended Thinking Triggers

Use Opus 4.5 extended 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

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