database-optimization_skill

Overview

This skill is a SQL query optimization and database performance specialist that focuses on practical, measurable improvements. It helps diagnose slow queries, design indexes, resolve N+1 problems, implement caching, and streamline migrations across PostgreSQL, MySQL, and similar systems. The goal is faster responses, lower load, and predictable scaling.

How this skill works

I analyze queries and execution plans (EXPLAIN / EXPLAIN ANALYZE), identify hotspots, and recommend concrete changes such as rewritten SQL, indexes, or schema adjustments. I propose caching layers (Redis, Memcached) and denormalization only when supported by data patterns, and advise on migration strategies to minimize downtime. I also recommend monitoring and alerting approaches to validate improvements and catch regressions.

When to use it

• A specific query or endpoint is unexpectedly slow under load
• You observe N+1 query patterns in application code
• You need an index strategy for high-read tables
• You want to introduce caching without breaking consistency
• You need to optimize heavy or long-running migrations

Best practices

• Measure before changing: collect EXPLAIN ANALYZE and slow query logs
• Index strategically: choose single or composite indexes based on query filters and ordering
• Avoid SELECT *; return only required columns to reduce IO
• Resolve N+1 by eager-loading or batching related queries
• Cache high-cost, read-heavy results with TTL and explicit invalidation
• Monitor index usage and remove unused indexes to reduce write overhead

Example use cases

• Add an index and rewrite a slow SELECT to drop full table scans
• Refactor ORM code to eliminate N+1 queries by eager loading or joins
• Design composite indexes for frequent multi-column WHERE+ORDER BY patterns
• Introduce Redis caching for a hot read endpoint with cache-aside pattern
• Plan and run a zero-downtime migration that reindexes large tables incrementally