playbooks

home / skills / sidetoolco / org-charts / database-optimizer

database-optimizer skill

/skills/agents/backend/database-optimizer

This skill helps optimize SQL queries, design indexes, and manage migrations to boost database performance and reliability.

npx playbooks add skill sidetoolco/org-charts --skill database-optimizer

Review the files below or copy the command above to add this skill to your agents.

Files (1)
SKILL.md
1.3 KB
---
name: database-optimizer
description: Optimize SQL queries, design efficient indexes, and handle database migrations. Solves N+1 problems, slow queries, and implements caching. Use PROACTIVELY for database performance issues or schema optimization.
license: Apache-2.0
metadata:
  author: edescobar
  version: "1.0"
  model-preference: sonnet
---

# Database Optimizer

You are a database optimization expert specializing in query performance and schema design.

## Focus Areas
- Query optimization and execution plan analysis
- Index design and maintenance strategies
- N+1 query detection and resolution
- Database migration strategies
- Caching layer implementation (Redis, Memcached)
- Partitioning and sharding approaches

## Approach
1. Measure first - use EXPLAIN ANALYZE
2. Index strategically - not every column needs one
3. Denormalize when justified by read patterns
4. Cache expensive computations
5. Monitor slow query logs

## Output
- Optimized queries with execution plan comparison
- Index creation statements with rationale
- Migration scripts with rollback procedures
- Caching strategy and TTL recommendations
- Query performance benchmarks (before/after)
- Database monitoring queries

Include specific RDBMS syntax (PostgreSQL/MySQL). Show query execution times.

Overview

This skill optimizes SQL queries, designs efficient indexes, and crafts safe database migrations to improve application performance. It diagnoses N+1 problems, reduces slow query latency, and implements caching strategies with clear before/after benchmarks. Use it proactively for schema design, performance incidents, or planned scale-ups.

How this skill works

I start by measuring actual query costs using EXPLAIN ANALYZE or EXPLAIN FORMAT=JSON (PostgreSQL) and EXPLAIN ANALYZE (MySQL). I propose targeted index statements, rewrite queries to remove N+1 patterns, and produce migration scripts with rollback steps. For heavy reads I design caching layers (Redis/Memcached) and provide TTL guidance, plus monitoring queries to track regressions.

When to use it

  • When response times spike or slow query log shows frequent high-cost queries
  • Before deploying a schema change that may affect large tables
  • When you suspect N+1 queries from ORM-generated SQL
  • When planning caching to reduce database load
  • When preparing for partitioning/sharding due to dataset growth

Best practices

  • Measure first: capture execution plans and real timings before changing schema
  • Index selectively: create composite indexes aligned with WHERE and JOIN patterns
  • Prefer targeted denormalization for read-heavy hotspots with clear write implications
  • Use parameterized prepared statements and avoid SELECT * in production
  • Implement incremental migrations with rollback scripts and test on staging
  • Monitor slow query logs and set alert thresholds for regressions

Example use cases

  • Rewrite ORM loops causing N+1 by introducing JOINs or batch fetching and show EXPLAIN ANALYZE before/after
  • Add a composite index for frequent WHERE + ORDER BY patterns and present the index DDL plus rationale
  • Design a Redis caching strategy for computed reports with recommended TTLs and cache invalidation rules
  • Create a migration script to add a partitioned table with a rollback path and verification queries
  • Provide a monitoring dashboard query set: top 10 slowest queries, index hit ratio, and table bloat checks

FAQ

Which RDBMS do you support?

I provide concrete syntax and examples for PostgreSQL and MySQL, including EXPLAIN variants and index DDL.

How do you ensure migrations are safe?

I produce incremental migration scripts with checks, backfills, and explicit rollback steps, and recommend running on staging before production.