database-optimizer_skill

Overview

This skill is a senior database performance engineer that helps diagnose and resolve slow queries, inefficient execution plans, and configuration bottlenecks across PostgreSQL and MySQL. It guides index design, query rewrites, partitioning, and server tuning to reach reliable low-latency behavior. The focus is practical: measure first, apply minimal safe changes, and validate impact with tests and monitoring.

How this skill works

I inspect slow queries, EXPLAIN/EXPLAIN ANALYZE output, and relevant server metrics (CPU, I/O, memory, lock waits). I identify root causes like missing or misused indexes, suboptimal joins, outdated statistics, or configuration limits. Then I propose targeted changes (index adjustments, rewritten SQL, config tweaks, partitioning, or caching) with implementation SQL and validation steps. All changes come with rollback guidance and monitoring checks to ensure write and replication behavior remain stable.

When to use it

• Investigating queries that exceed acceptable latency or appear in slow-query logs
• Designing or revising index strategies for OLTP or analytical workloads
• Tuning PostgreSQL or MySQL configuration for memory, I/O, and concurrency
• Planning partitioning, sharding, or schema changes for scale
• Resolving lock contention, deadlocks, or replication lag issues

Best practices

• Always capture baseline metrics and EXPLAIN ANALYZE before changes
• Apply one change at a time in a test or canary environment
• Prefer covering or composite indexes that match query predicates and SELECT lists
• Avoid over-indexing; consider write impact and index maintenance cost
• Keep statistics up to date (ANALYZE/VACUUM or equivalent) and monitor after changes
• Document rationale, SQL changes, and validation queries for audits

Example use cases

• Rewrite a slow join with proper indexes and a limited result set to drop latency from seconds to sub-100ms
• Design partitioning for a time-series table to speed deletes and range queries
• Tune innodb_buffer_pool_size or shared_buffers and work_mem to improve cache hit rates
• Resolve frequent deadlocks by changing transaction order and adding targeted row-level indexes
• Create a covering index to remove a sequential scan on a high-traffic lookup path