Database Optimizer

Purpose

Provides expert database performance tuning and optimization across major database systems (PostgreSQL, MySQL, MongoDB, Redis) specializing in query optimization, index design, execution plan analysis, and system configuration. Achieves sub-second query performance and optimal resource utilization through systematic optimization approaches.

When to Use

• Query execution time exceeds performance targets (>100ms for OLTP, >5s for analytics)
• Database CPU/memory/I/O utilization consistently above 70%
• Application experiencing database connection exhaustion or timeouts
• Slow query log shows problematic patterns or missing indexes
• Database struggling to handle expected load or traffic spikes
• Replication lag exceeding acceptable thresholds (>1s for critical systems)
• Need to optimize database configuration for specific workload (OLTP vs OLAP)
• Planning database capacity or horizontal scaling strategy

Quick Start

Invoke this skill when:

• Slow queries need optimization (EXPLAIN ANALYZE shows issues)
• Index strategy needs design or review
• Database configuration tuning required
• Capacity planning or scaling decisions needed

Do NOT invoke when:

• Simple CRUD operations with no performance issues
• Schema design without optimization focus (use database-administrator)
• Application-level caching only (use backend-developer)

Core Capabilities

Query Optimization

• Analyzing execution plans and identifying bottlenecks
• Rewriting queries for optimal performance
• Optimizing joins, subqueries, and aggregations
• Implementing query result caching strategies

Index Design

• Designing appropriate index types (B-tree, GIN, BRIN, hash)
• Creating composite indexes for multi-column queries
• Implementing partial indexes for specific query patterns
• Managing index maintenance and avoiding bloat

Database Configuration

• Tuning database parameters for specific workloads
• Optimizing memory allocation (buffer pool, cache sizes)
• Configuring connection pooling and concurrency settings
• Implementing partitioning strategies for large tables

Performance Monitoring

• Setting up query performance monitoring and alerting
• Analyzing slow query logs and identifying patterns
• Implementing database metrics collection (EXPLAIN ANALYZE)
• Creating performance baselines and capacity planning

Decision Framework

Optimization Priority Matrix

| Symptom | First Action | Tool | |---------|--------------|------| | Query >100ms | EXPLAIN ANALYZE | Execution plan review | | High CPU | pg_stat_statements | Find top queries | | High I/O | Index review | Missing index detection | | Connection exhaustion | Pool tuning | PgBouncer/connection limits | | Replication lag | Write optimization | Batch operations |

Index Decision Tree

Query Performance Issue
│
├─ WHERE clause filtering?
│  └─ Create B-tree index on filter columns
│
├─ JOIN operations slow?
│  └─ Index foreign key columns
│
├─ ORDER BY/GROUP BY expensive?
│  └─ Include sort columns in index
│
├─ Covering index possible?
│  └─ Add INCLUDE columns to avoid heap fetches
│
└─ Selective queries (status='active')?
   └─ Use partial index with WHERE clause

Core Workflow: Slow Query Optimization

Scenario: Production query taking 3.2s, needs to be <100ms

Step 1: Capture baseline with EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT u.id, u.email, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
  AND u.status = 'active'
GROUP BY u.id, u.email
ORDER BY total_spent DESC
LIMIT 100;

Step 2: Identify issues from execution plan

• Sequential scans instead of index scans
• High shared reads (cache misses)
• Missing indexes on filter/join columns

Step 3: Create strategic indexes

-- Covering index for users with partial index
CREATE INDEX CONCURRENTLY idx_users_status_created_active 
  ON users (status, created_at) 
  INCLUDE (id, email)
  WHERE status = 'active';

-- Covering index for orders JOIN
CREATE INDEX CONCURRENTLY idx_orders_user_id_total 
  ON orders (user_id) 
  INCLUDE (id, total);

-- Update statistics
ANALYZE users;
ANALYZE orders;

Step 4: Verify optimization

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
-- Same query - should now show:
-- - Index Only Scan instead of Seq Scan
-- - Heap Fetches: 0
-- - Execution Time: <100ms

Expected outcome:

• Execution time reduced by 95%+ (3205ms -> 87ms)
• Buffer reads eliminated (all hits from cache)
• Sequential scans replaced with index scans
• Query plan stable and predictable

Quick Reference: Performance Targets

| Metric | OLTP Target | Analytics Target | |--------|-------------|------------------| | P50 latency | <50ms | <2s | | P95 latency | <100ms | <5s | | P99 latency | <200ms | <10s | | Cache hit ratio | >95% | >90% | | Index usage | >95% | >80% |

Quick Reference: Configuration Guidelines

| Parameter | Formula | Example (32GB RAM) | |-----------|---------|-------------------| | shared_buffers | 25% of RAM | 8GB | | effective_cache_size | 75% of RAM | 24GB | | work_mem | RAM / max_connections / 4 | 40MB | | maintenance_work_mem | 10% of RAM | 2GB | | random_page_cost | 1.1 (SSD) / 4.0 (HDD) | 1.1 |

Red Flags - When to Escalate

| Observation | Action | |-------------|--------| | Query complexity explosion | Escalate to architect for schema redesign | | Replication lag >10s | Escalate to DBA for infrastructure review | | Connection pool exhaustion | Review application connection handling | | Disk I/O saturation | Consider read replicas or caching layer |

Additional Resources

• Detailed Technical Reference: See REFERENCE.md

  • Database configuration tuning workflows
  • Partitioning strategies for time-series data
  • Advanced monitoring queries

• Code Examples & Patterns: See EXAMPLES.md

  • Anti-patterns (over-indexing, premature denormalization)
  • Quality checklist for optimization projects
  • Index monitoring and maintenance queries