Qdatabase-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:

Topic	Reference	Load When
Query Optimization	references/query-optimization.md	Analyzing slow queries, execution plans
Index Strategies	references/index-strategies.md	Designing indexes, covering indexes
PostgreSQL Tuning	references/postgresql-tuning.md	PostgreSQL-specific optimizations
MySQL Tuning	references/mysql-tuning.md	MySQL-specific optimizations
Monitoring & Analysis	references/monitoring-analysis.md	Performance metrics, diagnostics

Common Operations & Examples

Identify Top Slow Queries (PostgreSQL)

-- Requires pg_stat_statements extension
SELECT 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,
       rows
FROM   pg_stat_statements
ORDER  BY mean_exec_time DESC
LIMIT  20;

Capture an Execution Plan

-- Use BUFFERS to expose 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';

Reading EXPLAIN Output — Key Patterns to Find

Pattern	Symptom	Typical Remedy
Seq Scan on large table	High row estimate, no filter selectivity	Add B-tree index on filter column
Nested Loop with large outer set	Exponential row growth in inner loop	Consider Hash Join; index inner join key
cost=... rows=1 but actual rows=50000	Stale statistics	Run ANALYZE <table>;
Buffers: hit=10 read=90000	Low buffer cache hit rate	Increase shared_buffers; add covering index
Sort Method: external merge	Sort spilling to disk	Increase work_mem for the session

Create a Covering Index

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

Validate Improvement

-- Before optimization: save plan & timing
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- note "Execution Time: X ms"

-- After optimization: compare
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- target meaningful reduction in cost & time

-- Confirm index is actually used
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL: Find Slow Queries

-- Inspect slow query log candidates
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- Execution plan
EXPLAIN FORMAT=JSON
SELECT * 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

Code Patterns

Pattern 1: EXPLAIN ANALYZE Reading

-- Baseline query: full scan detected
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped' AND user_id = 42;
-- Output: Seq Scan, cost=0..5000 rows=1200, buffers: shared read=450
-- Remedy: CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Pattern 2: Index Creation Strategy

-- Covering index eliminates heap fetches for frequent queries
CREATE INDEX CONCURRENTLY idx_orders_covering
    ON orders (user_id, status)
    INCLUDE (total, created_at);
-- After creation: verify with pg_stat_user_indexes (idx_scan > 0)

Pattern 3: Query Rewrite for Performance

-- Inefficient: correlated subquery (executes N times)
SELECT u.id, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;
-- Optimized: single join with aggregation
SELECT u.id, COUNT(o.id) AS order_count FROM users u 
LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;

Comment Template

-- OPTIMIZATION: [name]
-- Rationale: [why this change — e.g., "eliminates Seq Scan on large table"]
-- Baseline: [before metric — e.g., "cost=5000 rows=50000, 320ms"]
-- Expected: [after metric — e.g., "cost=50 rows=100, <5ms"]
-- Date: [YYYY-MM-DD] | Author: [name]
-- Validated: [yes/no] | Metrics: [actual after value if measured]

Lint Rules

• sqlfluff: Run sqlfluff lint --dialect postgres <file>.sql to catch missing indexes, N+1 patterns, missing WHERE clauses
• EXPLAIN Output Validation: Seq Scans on large tables (>1M rows) without filters = violation; cost estimates differing >50% from actual = stale stats
• Slow Query Log Analysis: Queries >1s in production require optimization; prioritize by (frequency × duration)

Security Checklist

• Parameterized Queries: All user input via bind variables; no string concatenation in SQL
• Least Privilege DB Users: App user lacks ALTER/DROP; read-only user for reporting; separate admin credentials
• Audit Logging: log_statement = 'all' (PostgreSQL) or general log enabled; DML changes tracked
• Connection Encryption: TLS 1.2+ for all client-DB connections; certificates validated
• Backup Testing: Recovery drill monthly; verify encryption keys accessible; document RTO/RPO

Anti-patterns (Wrong → Correct)

1. SELECT * from large table: Use explicit columns for query plan efficiency and schema stability
2. Missing WHERE clause indexes: Every filter column should be indexed; validate with EXPLAIN
3. N+1 from ORM: Batch queries or use JOIN; check generated SQL with EXPLAIN before shipping
4. Full table scans on large tables: Add B-tree index on filter; use EXPLAIN to verify Seq Scan → Index Scan
5. No query plan review: Always run EXPLAIN ANALYZE before & after; save baseline; document improvement

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