| name | data-sql-optimization |
| description | Production-grade SQL optimization with AI-assisted query analysis, EXPLAIN ANALYZE automation, balanced indexing strategies, performance tuning, schema design, and operations across PostgreSQL, MySQL, SQL Server, Oracle, SQLite. |
SQL Optimization — Comprehensive Reference
This skill equips Claude with actionable checklists, patterns, and templates for transactional (OLTP) SQL optimization: AI-powered query analysis, automated EXPLAIN interpretation, intelligent indexing (avoiding over-indexing), performance monitoring with pg_stat_statements, schema evolution, migrations, backup/recovery, high availability, and security.
Supported Platforms: PostgreSQL, MySQL, SQL Server, Oracle, SQLite
For OLAP/Analytics: See data-lake-platform (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 |
| Query Optimization | AI-assisted tools | pgai (PostgreSQL) / Performance Schema (MySQL) | ML-based query plan analysis, predictive caching |
Decision Tree: Choosing the Right Approach
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
Claude should invoke this skill when users ask for:
Query Optimization (Modern Approaches)
- AI-assisted SQL query performance review and tuning
- Automated EXPLAIN ANALYZE 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:
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)
PostgreSQL Templates
MySQL Templates
Microsoft SQL Server Templates
Oracle Templates
SQLite Templates
Related Skills
Infrastructure & Operations:
Application Integration:
Quality & Security:
Data Engineering & AI:
Navigation
Resources
Templates
Data
Operational Deep Dives
See resources/operational-patterns.md for:
- End-to-end optimization checklists and anti-pattern fixes
- Database-specific quick references (PostgreSQL, MySQL, SQL Server, Oracle, SQLite)
- Slow query troubleshooting workflow and reliability drills
- Template selection decision tree and platform migration notes
Analytical Databases (OLAP)
For OLAP databases and data lake infrastructure, see data-lake-platform:
- Query engines: ClickHouse, DuckDB, Apache Doris, StarRocks
- Table formats: Apache Iceberg, Delta Lake, Apache Hudi
- Transformation: SQLMesh, dbt (staging/marts layers)
- Ingestion: dlt, Airbyte (connectors)
- Streaming: Apache Kafka patterns
This skill focuses on transactional database optimization (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Use data-lake-platform for analytical workloads.
Related Skills
This skill focuses on query optimization within a single database. For related workflows:
SQL Transformation & Analytics Engineering:
→ ai-ml-data-science skill
- SQLMesh templates for building staging/intermediate/marts layers
- Incremental models (FULL, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY)
- DAG management and model dependencies
- Unit tests and audits for SQL transformations
Data Ingestion (Loading into Warehouses):
→ ai-mlops skill
- dlt templates for extracting from REST APIs, databases
- Loading to Snowflake, BigQuery, Redshift, Postgres, DuckDB
- Incremental loading patterns (timestamp, ID-based, merge/upsert)
- Database replication (Postgres, MySQL, MongoDB → warehouse)
Data Lake Infrastructure:
→ data-lake-platform skill
- ClickHouse, DuckDB, Doris, StarRocks query engines
- Iceberg, Delta Lake, Hudi table formats
- Kafka streaming, Dagster/Airflow orchestration
Use Case Decision:
- Query is slow in production → Use this skill (data-sql-optimization)
- Building feature pipelines in SQL → Use ai-ml-data-science (SQLMesh)
- Loading data from APIs/DBs to warehouse → Use ai-mlops (dlt)
- Analytics on large datasets (OLAP) → Use data-lake-platform
External Resources
See data/sources.json for 62+ curated resources including:
Core Documentation:
- RDBMS Documentation: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, DuckDB official docs
- Query Optimization: Use The Index, Luke, SQL Performance Explained, vendor optimization guides
- Schema Design: Database Refactoring (Fowler), normalization guides, data type selection
Modern Optimization (December 2025):
- PostgreSQL 18: 3x I/O performance improvements, uuidv7(), virtual generated columns, protocol 3.2, faster major upgrades
- MySQL 8.4/9.0: Thread pool management, parallel processing, AI integration, workload analysis
- DuckDB: Columnar optimization, zone maps, vectorized execution, query optimizer insights (100x improvements)
Operations & Infrastructure:
- HA & Replication: Streaming replication, GTID-based replication, failover automation
- Migrations: Liquibase, Flyway version control and deployment patterns
- Backup/Recovery: pgBackRest, Percona XtraBackup, point-in-time recovery
- Monitoring: pg_stat_statements, Performance Schema, EXPLAIN visualizers (Dalibo, depesz)
- Security: OWASP SQL Injection Prevention, Postgres hardening, encryption standards
- Analytical Databases: DuckDB extensions, Parquet specification, columnar storage patterns
Use resources/operational-patterns.md and the templates directory for detailed workflows, migration notes, and ready-to-run commands.
