Database Optimizer

Database Optimizer

When to Use / When Not to Use

Use when:

• EXPLAIN plan is in hand but the fix is server config, not query rewriting
• Investigating lock contention, VACUUM lag, or statistics staleness
• Tuning shared_buffers, work_mem, or InnoDB buffer pool
• Designing partitioning strategy or index structure

Do not use when:

• The fix is rewriting a slow SQL query (use sql-pro)
• The bottleneck is connection pool exhaustion (use connection-pool-tuner)

Process

1. Initial triage — Confirm: database engine + version, deployment type (self-managed vs. cloud-managed), and whether direct connection is available
2. Capture baseline — Run EXPLAIN (ANALYZE, BUFFERS) before any changes
3. Identify bottlenecks — Find inefficient queries, missing indexes, config issues from the plan
4. Design solutions — Index strategy, query rewrites, schema or config improvements
5. Implement incrementally — One change at a time; validate each before proceeding
6. Validate results — Re-run EXPLAIN ANALYZE, compare costs, measure wall-clock improvement

On cloud-managed databases (RDS, Cloud SQL, Aurora): ALTER SYSTEM and my.cnf edits are unavailable. Use parameter groups or the console instead.

Use sequential-thinking if available — it enforces the baseline-capture step and prevents skipping directly to index creation.

Output Template

For each optimization task, provide:

1. Performance analysis with baseline metrics (query time, cost, buffer hit ratio)
2. Identified bottlenecks with EXPLAIN evidence
3. Optimization strategy with specific changes
4. Implementation SQL / config changes
5. Validation queries to measure improvement
6. Monitoring recommendations

What Claude Does / What You Do

Reference Guide

EXPLAIN Output — Key Patterns

-- Always use BUFFERS to see cache hit vs. disk read ratio
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.status = 'pending'
  AND  o.created_at > now() - interval '7 days';

Constraints

MUST DO:

• Capture EXPLAIN (ANALYZE, BUFFERS) before any changes — this is the baseline
• Create PostgreSQL indexes with CONCURRENTLY to avoid table locks
• Test in non-production; roll back if write performance or replication lag worsens
• Make one change at a time — measure before making the next change

MUST NOT DO:

• Apply optimizations without a measured baseline
• Create redundant or unused indexes
• Make multiple changes simultaneously
• Use ALTER SYSTEM on Amazon RDS or other cloud-managed databases

Related Skills

• sql-pro — rewriting slow queries when the server is correctly configured
• connection-pool-tuner — pool sizing after server config is validated
• sre-engineer — monitoring and alerting on database golden signals