Optimize Database Performance

The Database Performance Optimizer is an autonomous agent that analyzes database systems, identifies bottlenecks, and implements optimization strategies across SQL/NoSQL databases, caching layers, and data pipelines. It works in five stages: first assessing schemas, indexes, query patterns, configuration files, slow query logs, and current cache hit rates; then analyzing performance with EXPLAIN plans, reviewing table sizes and fragmentation, connection pooling, and data pipeline bottlenecks; then developing an optimization strategy prioritized by impact versus effort, designing index strategies, planning caching layers (Redis, Memcached, or application-level), and architecting data partitioning and sharding; then producing migration scripts for schema changes with rollback procedures and a deployment sequence that minimizes downtime; and finally defining performance benchmarks, setting up continuous monitoring dashboards, and creating automated regression tests.

Use this agent when a database system has known or suspected performance problems and you need a structured, measured optimization plan rather than ad hoc query tweaks - its first guideline is to always establish baseline metrics before touching anything, and every change ships with a rollback plan and staging validation before production. It fits systems with slow query logs, growing table sizes, or caching layers that need architecture (not just tuning), and it favors automating routine optimization tasks into reusable scripts and staying current with new database engine features rather than re-deriving the same fixes by hand each time. It is not meant for one-off query fixes without measurement, since the agent's discipline is incremental changes validated against a baseline, and it explicitly favors staged, monitored rollouts over broad simultaneous changes.

-- Example index optimization
CREATE INDEX CONCURRENTLY idx_users_active_created 
ON users (status, created_at) 
WHERE status = 'active';

Given a database system and its configuration, the agent produces an Executive Summary (current performance baseline, key bottlenecks, expected improvements, timeline and risks), a Detailed Optimization Plan with concrete index changes like the one above, a Caching Strategy (including Redis connection configuration - host, port, db, max_connections, socket_keepalive, health_check_interval), Configuration Changes (parameter tuning, connection pool sizing, memory allocation, disk I/O), and a Monitoring Setup specifying key performance indicators, alert thresholds and escalation procedures, dashboard configurations, and automated health checks.

Backend and database engineers dealing with query slowness, cache misses, or data pipeline bottlenecks who need both the diagnosis and a safe rollout plan - not just a list of recommendations. It suits teams that want optimizations tied to measured baselines and automated regression testing rather than one-time tuning, and that need to balance read versus write performance trade-offs against workload while planning for data growth.