Database Optimizer

Database Optimizer

Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.

When to Use This Skill

• Analyzing slow queries and execution plans
• Designing optimal index strategies
• Tuning database configuration parameters
• Optimizing schema design and partitioning
• Reducing lock contention and deadlocks
• Improving cache hit rates and memory usage

Core Workflow

1. Analyze Performance — Capture baseline metrics and run EXPLAIN ANALYZE before any changes
2. Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
3. Design Solutions — Create index strategies, query rewrites, schema improvements
4. Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
5. Validate Results — Re-run EXPLAIN ANALYZE, compare costs, measure wall-clock improvement, document changes

⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.

Reference Guide

Load detailed guidance based on context:

Common Operations & Examples

Identify Top Slow Queries (PostgreSQL)

-- Requires pg_stat_statements extensionSELECT query,       calls,       round(total_exec_time::numeric, 2)  AS total_ms,       round(mean_exec_time::numeric, 2)   AS mean_ms,       round(stddev_exec_time::numeric, 2) AS stddev_ms,       rowsFROM   pg_stat_statementsORDER  BY mean_exec_time DESCLIMIT  20;

Capture an Execution Plan

-- Use BUFFERS to expose cache hit vs. disk read ratioEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT o.id, c.nameFROM   orders oJOIN   customers c ON c.id = o.customer_idWHERE  o.status = 'pending'  AND  o.created_at > now() - interval '7 days';

Reading EXPLAIN Output — Key Patterns to Find

Create a Covering Index

-- Covers the filter AND the projected columns, eliminating a heap fetchCREATE INDEX CONCURRENTLY idx_orders_status_created_covering    ON orders (status, created_at)    INCLUDE (customer_id, total_amount);

Validate Improvement

-- Before optimization: save plan & timingEXPLAIN (ANALYZE, BUFFERS) <query>;   -- note "Execution Time: X ms"
-- After optimization: compareEXPLAIN (ANALYZE, BUFFERS) <query>;   -- target meaningful reduction in cost & time
-- Confirm index is actually usedSELECT indexname, idx_scan, idx_tup_read, idx_tup_fetchFROM   pg_stat_user_indexesWHERE  relname = 'orders';

MySQL: Find Slow Queries

-- Inspect slow query log candidatesSELECT * FROM performance_schema.events_statements_summary_by_digestORDER  BY SUM_TIMER_WAIT DESCLIMIT  20;
-- Execution planEXPLAIN FORMAT=JSONSELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;

Constraints

MUST DO

• Capture EXPLAIN (ANALYZE, BUFFERS) output before optimizing — this is the baseline
• Measure performance before and after every change
• Create indexes with CONCURRENTLY (PostgreSQL) to avoid table locks
• Test in non-production; roll back if write performance or replication lag worsens
• Document all optimization decisions with before/after metrics
• Run ANALYZE after bulk data changes to refresh statistics

MUST NOT DO

• Apply optimizations without a measured baseline
• Create redundant or unused indexes
• Make multiple changes simultaneously (impossible to attribute impact)
• Ignore write amplification caused by new indexes
• Neglect VACUUM / statistics maintenance

Output Templates

When optimizing database performance, provide:

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

Maintained by @jeffallan, Principal Consultant at Synergetic Solutions

Documentation