domains / data / database-performance-optimization / database-optimizer

database-optimizer

Skill card

combined

The copies overlap in scope but disagree on the procedure. Jeffallan/claude-skills provides a concrete, executable workflow (baseline EXPLAIN, incremental changes, specific SQL examples, and strict MUST/MUST NOT constraints). The other two copies (rmyndharis and sickn33) are nearly identical generic capability lists that lack a specific workflow, instead offering broad topic coverage (NoSQL, Cloud, N+1) and generic instructions. Crowning one would hide the alternative approach of broad capability mapping vs. specific procedural tuning.

Repositories

Other copies

Combined skill

---
name: database-optimizer
description: Optimizes database queries and improves performance across PostgreSQL, MySQL, and modern cloud/NoSQL systems. Use for slow queries, execution plans, index design, and scalability.
---

# Database Optimizer

## When to Use
- Analyzing slow queries and execution plans.
- Designing index strategies (B-tree, GIN, BRIN, etc.).
- Resolving N+1 query issues and optimizing ORM usage.
- Tuning cloud database configurations (RDS, Aurora, etc.).

## Core Workflow
1. **Analyze Performance**: Capture baseline metrics. Run `EXPLAIN (ANALYZE, BUFFERS)` (PostgreSQL) or `EXPLAIN FORMAT=JSON` (MySQL) before any changes.
2. **Identify Bottlenecks**: Look for Seq Scans, stale statistics, low buffer hit rates, or N+1 patterns in application logs.
3. **Design Solutions**: 
   - **Indexing**: Create covering or partial indexes. Use `CONCURRENTLY` for PostgreSQL.
   - **Query Rewriting**: Optimize JOINs, CTEs, and subqueries.
   - **Caching**: Evaluate multi-tier caching (L1 App, L2 Redis, L3 DB) for read-heavy workloads.
4. **Implement Changes**: Apply optimizations incrementally. Do not make multiple changes simultaneously.
5. **Validate Results**: Re-run execution plans. Compare costs and wall-clock time. Ensure write performance and replication lag have not degraded.

## Constraints
- **MUST**: Capture baseline `EXPLAIN` output before optimizing.
- **MUST**: Test in non-production first.
- **MUST NOT**: Create redundant indexes or ignore write amplification.
- **MUST NOT**: Apply optimizations without a measured baseline.

## Reference Topics
- **PostgreSQL**: `pg_stat_statements`, `shared_buffers`, `work_mem`.
- **MySQL**: `performance_schema`, `innodb_buffer_pool_size`.
- **Cloud**: RDS Performance Insights, Aurora Serverless scaling.
- **NoSQL**: MongoDB aggregation pipelines, DynamoDB GSI/LSI.

## Output Format
1. Baseline metrics (query time, cost, buffer hit ratio).
2. Identified bottlenecks with EXPLAIN evidence.
3. Optimization strategy (SQL/config changes).
4. Validation queries and results.
5. Monitoring recommendations.