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 (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
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)
- 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: resources/sql-best-practices.md
- Query Tuning Patterns: resources/query-tuning-patterns.md
- Indexing Strategies: resources/index-patterns.md
- EXPLAIN/Analysis: resources/explain-analysis.md
- SQL Anti-Patterns: resources/sql-antipatterns.md
- External Sources: data/sources.json — vendor docs and reference links
- Operational Standards: resources/operational-patterns.md — Deep operational checklists, database-specific guidance, and template selection trees
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)
- templates/cross-platform/template-query-tuning.md - Universal query optimization
- templates/cross-platform/template-explain-analysis.md - Execution plan analysis
- templates/cross-platform/template-performance-tuning-worksheet.md - NEW 4-step tuning workflow (Measure → Explain → Change → Verify)
- templates/cross-platform/template-index.md - Index design patterns
- templates/cross-platform/template-slow-query.md - Slow query triage
- templates/cross-platform/template-schema-design.md - Schema modeling
- templates/cross-platform/template-migration.md - Database migrations
- templates/cross-platform/template-backup-restore.md - Backup/DR planning
- templates/cross-platform/template-security-audit.md - Security review
- templates/cross-platform/template-diagnostics.md - Performance diagnostics
- templates/cross-platform/template-lock-analysis.md - Lock troubleshooting
PostgreSQL Templates
- templates/postgres/template-pg-explain.md - PostgreSQL EXPLAIN analysis
- templates/postgres/template-pg-index.md - PostgreSQL indexing (B-tree, GIN, GiST)
- templates/postgres/template-replication-ha.md - Streaming replication & HA
MySQL Templates
- templates/mysql/template-mysql-explain.md - MySQL EXPLAIN analysis
- templates/mysql/template-mysql-index.md - MySQL/InnoDB indexing
- templates/mysql/template-replication-ha.md - MySQL replication & HA
Microsoft SQL Server Templates
- templates/mssql/template-mssql-explain.md - SQL Server EXPLAIN/SHOWPLAN analysis
- templates/mssql/template-mssql-index.md - SQL Server indexing and tuning
Oracle Templates
- templates/oracle/template-oracle-explain.md - Oracle EXPLAIN plan review and tuning
SQLite Templates
- templates/sqlite/template-sqlite-optimization.md - SQLite optimization and pragma guidance
Related Skills
Infrastructure & Operations:
Application Integration:
Quality & Security:
Data Engineering:
Navigation
Resources
- resources/explain-analysis.md
- resources/query-tuning-patterns.md
- resources/operational-patterns.md
- resources/sql-antipatterns.md
- resources/index-patterns.md
- resources/sql-best-practices.md
Templates
- templates/cross-platform/template-slow-query.md
- templates/cross-platform/template-backup-restore.md
- templates/cross-platform/template-schema-design.md
- templates/cross-platform/template-explain-analysis.md
- templates/cross-platform/template-performance-tuning-worksheet.md
- templates/cross-platform/template-security-audit.md
- templates/cross-platform/template-diagnostics.md
- templates/cross-platform/template-index.md
- templates/cross-platform/template-migration.md
- templates/cross-platform/template-lock-analysis.md
- templates/cross-platform/template-query-tuning.md
- templates/oracle/template-oracle-explain.md
- templates/sqlite/template-sqlite-optimization.md
- templates/postgres/template-pg-index.md
- templates/postgres/template-replication-ha.md
- templates/postgres/template-pg-explain.md
- templates/mysql/template-mysql-explain.md
- templates/mysql/template-mysql-index.md
- templates/mysql/template-replication-ha.md
- templates/mssql/template-mssql-index.md
- templates/mssql/template-mssql-explain.md
Data
- data/sources.json — Curated external references
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
Do / Avoid
GOOD: Do
- Measure baseline before any optimization
- Change one variable at a time
- Verify results match after query changes
- Update statistics before concluding "needs index"
- Test with production-like data volumes
- Document all optimization decisions
- Include performance tests in CI/CD
BAD: Avoid
- Adding indexes without checking if they'll be used
- Using SELECT * in production queries
- Optimizing for test data (use representative volumes)
- Ignoring write performance impact of indexes
- Skipping EXPLAIN analysis before changes
- Multiple simultaneous changes (can't attribute improvement)
- N+1 query patterns in application code
Anti-Patterns Quick Reference
| Anti-Pattern |
Problem |
Fix |
| **SELECT *** |
Reads unnecessary columns |
Explicit column list |
| N+1 queries |
Multiplied round trips |
JOIN or batch fetch |
| Missing WHERE |
Full table scan |
Add predicates |
| Function on indexed column |
Can't use index |
Move function to RHS |
| Implicit type conversion |
Index bypass |
Match types explicitly |
| LIKE '%prefix' |
Leading wildcard = scan |
Full-text search |
| Unbounded result set |
Memory explosion |
Add LIMIT/pagination |
| OR conditions |
Index may not be used |
UNION or rewrite |
See resources/sql-antipatterns.md for detailed fixes.
OLTP vs OLAP Decision Tree
Is your query for...?
├─ Point lookups (by ID/key)?
│ └─ OLTP database (this skill)
│ - Ensure proper indexes
│ - Use connection pooling
│ - Optimize for low latency
│
├─ Aggregations over recent data (dashboard)?
│ └─ OLTP database (this skill)
│ - Consider materialized views
│ - Index common filter columns
│ - Watch for lock contention
│
├─ Full table scans or historical analysis?
│ └─ OLAP database (data-lake-platform)
│ - ClickHouse, DuckDB, Doris
│ - Columnar storage
│ - Partitioning by date
│
└─ Mixed workload (both)?
└─ Separate OLTP and OLAP
- OLTP for transactions
- Replicate to OLAP for analytics
- Avoid running analytics on primary
Optional: AI/Automation
Note: AI tools assist but require human validation of correctness.
- EXPLAIN summarization — Identify bottlenecks from complex plans
- Query rewrite suggestions — Must verify result equivalence
- Index recommendations — Check selectivity and write impact first
Bounded Claims
- AI cannot determine correct query results
- Automated index suggestions may miss workload context
- Human review required for production changes
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: official release notes and "current" docs for planner/optimizer changes
- MySQL: official reference manual sections for EXPLAIN, optimizer, and Performance Schema
- SQL Server / Oracle: official docs for execution plans, indexing, and concurrency controls
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.
1---2name: data-sql-optimization3description: "Production-grade SQL optimization for OLTP systems: EXPLAIN/plan analysis, balanced indexing, schema and query design, migrations, backup/recovery, HA, security, and safe performance tuning across PostgreSQL, MySQL, SQL Server, Oracle, SQLite."4metadata:5 source: https://skillmd.com/skills/diegosouzapw/data-sql-optimization6---78# SQL Optimization — Comprehensive Reference910This 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.1112**Supported Platforms:** PostgreSQL, MySQL, SQL Server, Oracle, SQLite1314**For OLAP/Analytics:** See [data-lake-platform](../data-lake-platform/SKILL.md) (ClickHouse, DuckDB, Doris, StarRocks)1516---1718## Quick Reference1920| Task | Tool/Framework | Command | When to Use |21|------|----------------|---------|-------------|22| Query Performance Analysis | EXPLAIN ANALYZE | `EXPLAIN (ANALYZE, BUFFERS) SELECT ...` (PG) / `EXPLAIN ANALYZE SELECT ...` (MySQL) | Diagnose slow queries, identify missing indexes |23| 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 |24| Index Analysis | pg_stat_user_indexes / SHOW INDEX | `SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;` | Find unused indexes, validate index coverage |25| Schema Migration | Flyway / Liquibase | `flyway migrate` / `liquibase update` | Version-controlled database changes |26| Backup & Recovery | pg_dump / mysqldump | `pg_dump -Fc dbname > backup.dump` | Point-in-time recovery, disaster recovery |27| Replication Setup | Streaming / GTID | Configure postgresql.conf / my.cnf | High availability, read scaling |28| Safe Tuning Loop | Measure → Explain → Change → Verify | Use tuning worksheet template | Reduce latency/cost without regressions |2930---3132## Decision Tree: Choosing the Right Approach3334```text35Query performance issue?36 ├─ Identify slow queries first?37 │ ├─ PostgreSQL → pg_stat_statements (top queries by total_exec_time)38 │ └─ MySQL → Performance Schema / slow query log39 │40 ├─ Analyze execution plan?41 │ ├─ PostgreSQL → EXPLAIN (ANALYZE, BUFFERS, VERBOSE)42 │ ├─ MySQL → EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE43 │ └─ SQL Server → SET STATISTICS IO ON; SET STATISTICS TIME ON;44 │45 ├─ Need indexing strategy?46 │ ├─ PostgreSQL → B-tree (default), GIN (JSONB), GiST (spatial), partial indexes47 │ ├─ MySQL → BTREE (default), FULLTEXT (text search), SPATIAL48 │ └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified49 │50 ├─ Schema changes needed?51 │ ├─ New database → template-schema-design.md52 │ ├─ Modify schema → template-migration.md (Flyway/Liquibase)53 │ └─ Large tables (MySQL) → gh-ost / pt-online-schema-change (avoid locks)54 │55 ├─ High availability setup?56 │ ├─ PostgreSQL → Streaming replication (template-replication-ha.md)57 │ └─ MySQL → GTID-based replication (template-replication-ha.md)58 │59 ├─ Backup/disaster recovery?60 │ └─ template-backup-restore.md (pg_dump, mysqldump, PITR)61 │62 └─ Analytics on large datasets (OLAP)?63 └─ See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)64```6566---6768## When to Use This Skill6970Claude should invoke this skill when users ask for:7172### Query Optimization (Modern Approaches)73- SQL query performance review and tuning74- EXPLAIN/plan interpretation with optimization suggestions75- Index creation strategies with balanced approach (avoiding over-indexing)76- Troubleshooting slow queries using pg_stat_statements or Performance Schema77- Identifying and remediating SQL anti-patterns with operational fixes78- Query rewrite suggestions or migration from slow to fast patterns79- Statistics maintenance and auto-analyze configuration8081### Database Operations82- Schema design with normalization and performance trade-offs83- Database migrations with version control (Liquibase, Flyway)84- Backup and recovery strategies (point-in-time recovery, automated testing)85- High availability and replication setup (streaming, GTID-based)86- Database security auditing (access controls, encryption, SQL injection prevention)87- Lock analysis and deadlock troubleshooting88- Connection pooling (pgBouncer, Pgpool-II, ProxySQL)8990### Performance Tuning (Modern Standards)91- Memory configuration (work_mem, shared_buffers, effective_cache_size)92- Automated monitoring with pg_stat_statements and query pattern analysis93- Index health monitoring (unused index detection, index bloat analysis)94- Vacuum strategy and autovacuum tuning (PostgreSQL)95- InnoDB buffer pool optimization (MySQL)96- Partition pruning improvements (PostgreSQL 18+)9798---99100## Resources (Best Practices Guides)101102Find detailed operational patterns and quick references in:103104- **SQL Best Practices**: [resources/sql-best-practices.md](resources/sql-best-practices.md)105- **Query Tuning Patterns**: [resources/query-tuning-patterns.md](resources/query-tuning-patterns.md)106- **Indexing Strategies**: [resources/index-patterns.md](resources/index-patterns.md)107- **EXPLAIN/Analysis**: [resources/explain-analysis.md](resources/explain-analysis.md)108- **SQL Anti-Patterns**: [resources/sql-antipatterns.md](resources/sql-antipatterns.md)109- **External Sources**: [data/sources.json](data/sources.json) — vendor docs and reference links110- **Operational Standards**: [resources/operational-patterns.md](resources/operational-patterns.md) — Deep operational checklists, database-specific guidance, and template selection trees111112Each file includes:113- Copy-paste ready checklists (e.g., "query review", "index design", "explain review")114- Anti-patterns with operational fixes and alternatives115- Query rewrite and indexing strategies with examples116- Troubleshooting guides (step-by-step)117118---119120## Templates (Copy-Paste Ready)121122Templates are organized by database technology for precision and clarity:123124### Cross-Platform Templates (All Databases)125- [templates/cross-platform/template-query-tuning.md](templates/cross-platform/template-query-tuning.md) - Universal query optimization126- [templates/cross-platform/template-explain-analysis.md](templates/cross-platform/template-explain-analysis.md) - Execution plan analysis127- [templates/cross-platform/template-performance-tuning-worksheet.md](templates/cross-platform/template-performance-tuning-worksheet.md) - **NEW** 4-step tuning workflow (Measure → Explain → Change → Verify)128- [templates/cross-platform/template-index.md](templates/cross-platform/template-index.md) - Index design patterns129- [templates/cross-platform/template-slow-query.md](templates/cross-platform/template-slow-query.md) - Slow query triage130- [templates/cross-platform/template-schema-design.md](templates/cross-platform/template-schema-design.md) - Schema modeling131- [templates/cross-platform/template-migration.md](templates/cross-platform/template-migration.md) - Database migrations132- [templates/cross-platform/template-backup-restore.md](templates/cross-platform/template-backup-restore.md) - Backup/DR planning133- [templates/cross-platform/template-security-audit.md](templates/cross-platform/template-security-audit.md) - Security review134- [templates/cross-platform/template-diagnostics.md](templates/cross-platform/template-diagnostics.md) - Performance diagnostics135- [templates/cross-platform/template-lock-analysis.md](templates/cross-platform/template-lock-analysis.md) - Lock troubleshooting136137### PostgreSQL Templates138- [templates/postgres/template-pg-explain.md](templates/postgres/template-pg-explain.md) - PostgreSQL EXPLAIN analysis139- [templates/postgres/template-pg-index.md](templates/postgres/template-pg-index.md) - PostgreSQL indexing (B-tree, GIN, GiST)140- [templates/postgres/template-replication-ha.md](templates/postgres/template-replication-ha.md) - Streaming replication & HA141142### MySQL Templates143- [templates/mysql/template-mysql-explain.md](templates/mysql/template-mysql-explain.md) - MySQL EXPLAIN analysis144- [templates/mysql/template-mysql-index.md](templates/mysql/template-mysql-index.md) - MySQL/InnoDB indexing145- [templates/mysql/template-replication-ha.md](templates/mysql/template-replication-ha.md) - MySQL replication & HA146147### Microsoft SQL Server Templates148- [templates/mssql/template-mssql-explain.md](templates/mssql/template-mssql-explain.md) - SQL Server EXPLAIN/SHOWPLAN analysis149- [templates/mssql/template-mssql-index.md](templates/mssql/template-mssql-index.md) - SQL Server indexing and tuning150151### Oracle Templates152- [templates/oracle/template-oracle-explain.md](templates/oracle/template-oracle-explain.md) - Oracle EXPLAIN plan review and tuning153154### SQLite Templates155- [templates/sqlite/template-sqlite-optimization.md](templates/sqlite/template-sqlite-optimization.md) - SQLite optimization and pragma guidance156157---158159## Related Skills160161**Infrastructure & Operations:**162- [../ops-devops-platform/SKILL.md](../ops-devops-platform/SKILL.md) — Infrastructure, backups, monitoring, and incident response163- [../qa-observability/SKILL.md](../qa-observability/SKILL.md) — Performance monitoring, profiling, and metrics164- [../qa-debugging/SKILL.md](../qa-debugging/SKILL.md) — Production debugging patterns165166**Application Integration:**167- [../software-backend/SKILL.md](../software-backend/SKILL.md) — API/database integration and application patterns168- [../software-architecture-design/SKILL.md](../software-architecture-design/SKILL.md) — System design and data architecture169- [../dev-api-design/SKILL.md](../dev-api-design/SKILL.md) — REST API and database interaction patterns170171**Quality & Security:**172- [../qa-resilience/SKILL.md](../qa-resilience/SKILL.md) — Resilience, circuit breakers, and failure handling173- [../software-security-appsec/SKILL.md](../software-security-appsec/SKILL.md) — Database security, auth, SQL injection prevention174- [../qa-testing-strategy/SKILL.md](../qa-testing-strategy/SKILL.md) — Database testing strategies175176**Data Engineering:**177- [../ai-ml-data-science/SKILL.md](../ai-ml-data-science/SKILL.md) — SQLMesh, dbt, data transformations178- [../ai-mlops/SKILL.md](../ai-mlops/SKILL.md) — Data pipelines, ETL, and warehouse loading (dlt)179- [../ai-ml-timeseries/SKILL.md](../ai-ml-timeseries/SKILL.md) — Time-series databases and forecasting180181---182183## Navigation184185**Resources**186- [resources/explain-analysis.md](resources/explain-analysis.md)187- [resources/query-tuning-patterns.md](resources/query-tuning-patterns.md)188- [resources/operational-patterns.md](resources/operational-patterns.md)189- [resources/sql-antipatterns.md](resources/sql-antipatterns.md)190- [resources/index-patterns.md](resources/index-patterns.md)191- [resources/sql-best-practices.md](resources/sql-best-practices.md)192193**Templates**194- [templates/cross-platform/template-slow-query.md](templates/cross-platform/template-slow-query.md)195- [templates/cross-platform/template-backup-restore.md](templates/cross-platform/template-backup-restore.md)196- [templates/cross-platform/template-schema-design.md](templates/cross-platform/template-schema-design.md)197- [templates/cross-platform/template-explain-analysis.md](templates/cross-platform/template-explain-analysis.md)198- [templates/cross-platform/template-performance-tuning-worksheet.md](templates/cross-platform/template-performance-tuning-worksheet.md)199- [templates/cross-platform/template-security-audit.md](templates/cross-platform/template-security-audit.md)200- [templates/cross-platform/template-diagnostics.md](templates/cross-platform/template-diagnostics.md)201- [templates/cross-platform/template-index.md](templates/cross-platform/template-index.md)202- [templates/cross-platform/template-migration.md](templates/cross-platform/template-migration.md)203- [templates/cross-platform/template-lock-analysis.md](templates/cross-platform/template-lock-analysis.md)204- [templates/cross-platform/template-query-tuning.md](templates/cross-platform/template-query-tuning.md)205- [templates/oracle/template-oracle-explain.md](templates/oracle/template-oracle-explain.md)206- [templates/sqlite/template-sqlite-optimization.md](templates/sqlite/template-sqlite-optimization.md)207- [templates/postgres/template-pg-index.md](templates/postgres/template-pg-index.md)208- [templates/postgres/template-replication-ha.md](templates/postgres/template-replication-ha.md)209- [templates/postgres/template-pg-explain.md](templates/postgres/template-pg-explain.md)210- [templates/mysql/template-mysql-explain.md](templates/mysql/template-mysql-explain.md)211- [templates/mysql/template-mysql-index.md](templates/mysql/template-mysql-index.md)212- [templates/mysql/template-replication-ha.md](templates/mysql/template-replication-ha.md)213- [templates/mssql/template-mssql-index.md](templates/mssql/template-mssql-index.md)214- [templates/mssql/template-mssql-explain.md](templates/mssql/template-mssql-explain.md)215216**Data**217- [data/sources.json](data/sources.json) — Curated external references218219---220221## Operational Deep Dives222223See [resources/operational-patterns.md](resources/operational-patterns.md) for:224- End-to-end optimization checklists and anti-pattern fixes225- Database-specific quick references (PostgreSQL, MySQL, SQL Server, Oracle, SQLite)226- Slow query troubleshooting workflow and reliability drills227- Template selection decision tree and platform migration notes228229---230231## Do / Avoid232233### GOOD: Do234235- Measure baseline before any optimization236- Change one variable at a time237- Verify results match after query changes238- Update statistics before concluding "needs index"239- Test with production-like data volumes240- Document all optimization decisions241- Include performance tests in CI/CD242243### BAD: Avoid244245- Adding indexes without checking if they'll be used246- Using SELECT * in production queries247- Optimizing for test data (use representative volumes)248- Ignoring write performance impact of indexes249- Skipping EXPLAIN analysis before changes250- Multiple simultaneous changes (can't attribute improvement)251- N+1 query patterns in application code252253---254255## Anti-Patterns Quick Reference256257| Anti-Pattern | Problem | Fix |258|--------------|---------|-----|259| **SELECT *** | Reads unnecessary columns | Explicit column list |260| **N+1 queries** | Multiplied round trips | JOIN or batch fetch |261| **Missing WHERE** | Full table scan | Add predicates |262| **Function on indexed column** | Can't use index | Move function to RHS |263| **Implicit type conversion** | Index bypass | Match types explicitly |264| **LIKE '%prefix'** | Leading wildcard = scan | Full-text search |265| **Unbounded result set** | Memory explosion | Add LIMIT/pagination |266| **OR conditions** | Index may not be used | UNION or rewrite |267268See [resources/sql-antipatterns.md](resources/sql-antipatterns.md) for detailed fixes.269270---271272## OLTP vs OLAP Decision Tree273274```text275Is your query for...?276├─ Point lookups (by ID/key)?277│ └─ OLTP database (this skill)278│ - Ensure proper indexes279│ - Use connection pooling280│ - Optimize for low latency281│282├─ Aggregations over recent data (dashboard)?283│ └─ OLTP database (this skill)284│ - Consider materialized views285│ - Index common filter columns286│ - Watch for lock contention287│288├─ Full table scans or historical analysis?289│ └─ OLAP database (data-lake-platform)290│ - ClickHouse, DuckDB, Doris291│ - Columnar storage292│ - Partitioning by date293│294└─ Mixed workload (both)?295 └─ Separate OLTP and OLAP296 - OLTP for transactions297 - Replicate to OLAP for analytics298 - Avoid running analytics on primary299```300301---302303## Optional: AI/Automation304305> **Note**: AI tools assist but require human validation of correctness.306307- **EXPLAIN summarization** — Identify bottlenecks from complex plans308- **Query rewrite suggestions** — Must verify result equivalence309- **Index recommendations** — Check selectivity and write impact first310311### Bounded Claims312313- AI cannot determine correct query results314- Automated index suggestions may miss workload context315- Human review required for production changes316317---318319## Analytical Databases (OLAP)320321For OLAP databases and data lake infrastructure, see **[data-lake-platform](../data-lake-platform/SKILL.md)**:322323- **Query engines:** ClickHouse, DuckDB, Apache Doris, StarRocks324- **Table formats:** Apache Iceberg, Delta Lake, Apache Hudi325- **Transformation:** SQLMesh, dbt (staging/marts layers)326- **Ingestion:** dlt, Airbyte (connectors)327- **Streaming:** Apache Kafka patterns328329This skill focuses on **transactional database optimization** (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Use data-lake-platform for analytical workloads.330331---332333## Related Skills334335This skill focuses on **query optimization** within a single database. For related workflows:336337**SQL Transformation & Analytics Engineering:**338→ **[ai-ml-data-science](../ai-ml-data-science/SKILL.md)** skill339- SQLMesh templates for building staging/intermediate/marts layers340- Incremental models (FULL, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY)341- DAG management and model dependencies342- Unit tests and audits for SQL transformations343344**Data Ingestion (Loading into Warehouses):**345→ **[ai-mlops](../ai-mlops/SKILL.md)** skill346- dlt templates for extracting from REST APIs, databases347- Loading to Snowflake, BigQuery, Redshift, Postgres, DuckDB348- Incremental loading patterns (timestamp, ID-based, merge/upsert)349- Database replication (Postgres, MySQL, MongoDB → warehouse)350351**Data Lake Infrastructure:**352→ **[data-lake-platform](../data-lake-platform/SKILL.md)** skill353354- ClickHouse, DuckDB, Doris, StarRocks query engines355- Iceberg, Delta Lake, Hudi table formats356- Kafka streaming, Dagster/Airflow orchestration357358**Use Case Decision:**359360- **Query is slow in production** → Use this skill (data-sql-optimization)361- **Building feature pipelines in SQL** → Use ai-ml-data-science (SQLMesh)362- **Loading data from APIs/DBs to warehouse** → Use ai-mlops (dlt)363- **Analytics on large datasets (OLAP)** → Use data-lake-platform364365---366367## External Resources368369See [data/sources.json](data/sources.json) for 62+ curated resources including:370371**Core Documentation:**372- **RDBMS Documentation**: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, DuckDB official docs373- **Query Optimization**: Use The Index, Luke, SQL Performance Explained, vendor optimization guides374- **Schema Design**: Database Refactoring (Fowler), normalization guides, data type selection375376**Modern Optimization (December 2025):**377- **PostgreSQL**: official release notes and "current" docs for planner/optimizer changes378- **MySQL**: official reference manual sections for EXPLAIN, optimizer, and Performance Schema379- **SQL Server / Oracle**: official docs for execution plans, indexing, and concurrency controls380381**Operations & Infrastructure:**382- **HA & Replication**: Streaming replication, GTID-based replication, failover automation383- **Migrations**: Liquibase, Flyway version control and deployment patterns384- **Backup/Recovery**: pgBackRest, Percona XtraBackup, point-in-time recovery385- **Monitoring**: pg_stat_statements, Performance Schema, EXPLAIN visualizers (Dalibo, depesz)386- **Security**: OWASP SQL Injection Prevention, Postgres hardening, encryption standards387- **Analytical Databases**: DuckDB extensions, Parquet specification, columnar storage patterns388389---390391Use [resources/operational-patterns.md](resources/operational-patterns.md) and the templates directory for detailed workflows, migration notes, and ready-to-run commands.