data-sql-optimization

What this skill does

# SQL Optimization — Comprehensive Reference

This skill provides actionable checklists, patterns, and templates for **transactional (OLTP) SQL optimization**: measurement-first triage, EXPLAIN/plan interpretation, balanced indexing (avoiding over-indexing), performance monitoring, schema evolution, migrations, backup/recovery, high availability, and security.

**Supported Platforms:** PostgreSQL, MySQL, SQL Server, Oracle, SQLite

**For OLAP/Analytics:** See [data-lake-platform](../data-lake-platform/SKILL.md) (ClickHouse, DuckDB, Doris, StarRocks)

---

## Quick Reference

| Task | Tool/Framework | Command | When to Use |
|------|----------------|---------|-------------|
| Query Performance Analysis | EXPLAIN ANALYZE | `EXPLAIN (ANALYZE, BUFFERS) SELECT ...` (PG) / `EXPLAIN ANALYZE SELECT ...` (MySQL) | Diagnose slow queries, identify missing indexes |
| Find Slow Queries | pg_stat_statements / slow query log | `SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;` | Identify performance bottlenecks in production |
| Index Analysis | pg_stat_user_indexes / SHOW INDEX | `SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;` | Find unused indexes, validate index coverage |
| Schema Migration | Flyway / Liquibase | `flyway migrate` / `liquibase update` | Version-controlled database changes |
| Backup & Recovery | pg_dump / mysqldump | `pg_dump -Fc dbname > backup.dump` | Point-in-time recovery, disaster recovery |
| Replication Setup | Streaming / GTID | Configure postgresql.conf / my.cnf | High availability, read scaling |
| Safe Tuning Loop | Measure -> Explain -> Change -> Verify | Use tuning worksheet template | Reduce latency/cost without regressions |

---

## Decision Tree: Choosing the Right Approach

```text
Query performance issue?
    ├─ Identify slow queries first?
    │   ├─ PostgreSQL -> pg_stat_statements (top queries by total_exec_time)
    │   └─ MySQL -> Performance Schema / slow query log
    │
    ├─ Analyze execution plan?
    │   ├─ PostgreSQL -> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    │   ├─ MySQL -> EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE
    │   └─ SQL Server -> SET STATISTICS IO ON; SET STATISTICS TIME ON;
    │
    ├─ Need indexing strategy?
    │   ├─ PostgreSQL -> B-tree (default), GIN (JSONB), GiST (spatial), partial indexes
    │   ├─ MySQL -> BTREE (default), FULLTEXT (text search), SPATIAL
    │   └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified
    │
    ├─ Schema changes needed?
    │   ├─ New database -> template-schema-design.md
    │   ├─ Modify schema -> template-migration.md (Flyway/Liquibase)
    │   └─ Large tables (MySQL) -> gh-ost / pt-online-schema-change (avoid locks)
    │
    ├─ High availability setup?
    │   ├─ PostgreSQL -> Streaming replication (template-replication-ha.md)
    │   └─ MySQL -> GTID-based replication (template-replication-ha.md)
    │
    ├─ Backup/disaster recovery?
    │   └─ template-backup-restore.md (pg_dump, mysqldump, PITR)
    │
    └─ Analytics on large datasets (OLAP)?
        └─ See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)
```

---

## When to Use This Skill

Codex should invoke this skill when users ask for:

### Query Optimization (Modern Approaches)
- SQL query performance review and tuning
- EXPLAIN/plan interpretation with optimization suggestions
- Index creation strategies with balanced approach (avoiding over-indexing)
- Troubleshooting slow queries using pg_stat_statements or Performance Schema
- Identifying and remediating SQL anti-patterns with operational fixes
- Query rewrite suggestions or migration from slow to fast patterns
- Statistics maintenance and auto-analyze configuration

### Database Operations
- Schema design with normalization and performance trade-offs
- Database migrations with version control (Liquibase, Flyway)
- Backup and recovery strategies (point-in-time recovery, automated testing)
- High availability and replication setup (streaming, GTID-based)
- Database security auditing (access controls, encryption, SQL injection prevention)
- Lock analysis and deadlock troubleshooting
- Connection pooling (pgBouncer, Pgpool-II, ProxySQL)

### Performance Tuning (Modern Standards)
- Memory configuration (work_mem, shared_buffers, effective_cache_size)
- Automated monitoring with pg_stat_statements and query pattern analysis
- Index health monitoring (unused index detection, index bloat analysis)
- Vacuum strategy and autovacuum tuning (PostgreSQL)
- InnoDB buffer pool optimization (MySQL)
- Partition pruning improvements (PostgreSQL 18+)

---

## Resources (Best Practices Guides)

Find detailed operational patterns and quick references in:

- **SQL Best Practices**: [references/sql-best-practices.md](references/sql-best-practices.md)
- **Query Tuning Patterns**: [references/query-tuning-patterns.md](references/query-tuning-patterns.md)
- **Indexing Strategies**: [references/index-patterns.md](references/index-patterns.md)
- **EXPLAIN/Analysis**: [references/explain-analysis.md](references/explain-analysis.md)
- **SQL Anti-Patterns**: [references/sql-antipatterns.md](references/sql-antipatterns.md)
- **External Sources**: [data/sources.json](data/sources.json) — vendor docs and reference links
- **Operational Standards**: [references/operational-patterns.md](references/operational-patterns.md) — Deep operational checklists, database-specific guidance, and template selection trees
- **Connection Pooling**: [references/connection-pooling-patterns.md](references/connection-pooling-patterns.md) — PgBouncer, RDS Proxy, pool sizing, connection leak troubleshooting
- **Partition Strategies**: [references/partition-strategies.md](references/partition-strategies.md) — Range/list/hash partitioning, pruning, maintenance, migration patterns
- **Monitoring & Alerting**: [references/monitoring-alerting-patterns.md](references/monitoring-alerting-patterns.md) — pg_stat_statements dashboards, alert thresholds, slow query pipelines

Each file includes:
- Copy-paste ready checklists (e.g., "query review", "index design", "explain review")
- Anti-patterns with operational fixes and alternatives
- Query rewrite and indexing strategies with examples
- Troubleshooting guides (step-by-step)

---

## Templates (Copy-Paste Ready)

Templates are organized by database technology for precision and clarity:

### Cross-Platform Templates (All Databases)
- [assets/cross-platform/template-query-tuning.md](assets/cross-platform/template-query-tuning.md) - Universal query optimization
- [assets/cross-platform/template-explain-analysis.md](assets/cross-platform/template-explain-analysis.md) - Execution plan analysis
- [assets/cross-platform/template-performance-tuning-worksheet.md](assets/cross-platform/template-performance-tuning-worksheet.md) - **NEW** 4-step tuning workflow (Measure -> Explain -> Change -> Verify)
- [assets/cross-platform/template-index.md](assets/cross-platform/template-index.md) - Index design patterns
- [assets/cross-platform/template-slow-query.md](assets/cross-platform/template-slow-query.md) - Slow query triage
- [assets/cross-platform/template-schema-design.md](assets/cross-platform/template-schema-design.md) - Schema modeling
- [assets/cross-platform/template-migration.md](assets/cross-platform/template-migration.md) - Database migrations
- [assets/cross-platform/template-backup-restore.md](assets/cross-platform/template-backup-restore.md) - Backup/DR planning
- [assets/cross-platform/template-security-audit.md](assets/cross-platform/template-security-audit.md) - Security review
- [assets/cross-platform/template-diagnostics.md](assets/cross-platform/template-diagnostics.md) - Performance diagnostics
- [assets/cross-platform/template-lock-analysis.md](assets/cross-platform/template-lock-analysis.md) - Lock troubleshooting

### PostgreSQL Templates
- [assets/postgres/template-pg-explain.md](assets/postgres/template-pg-explain.md) - PostgreSQL EXPLAIN analysis
- [assets/postgres/template-pg-index.