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