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.4---56# SQL Optimization — Comprehensive Reference78This 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.910**Supported Platforms:** PostgreSQL, MySQL, SQL Server, Oracle, SQLite1112**For OLAP/Analytics:** See [data-lake-platform](../data-lake-platform/SKILL.md) (ClickHouse, DuckDB, Doris, StarRocks)1314---1516## Quick Reference1718| Task | Tool/Framework | Command | When to Use |19|------|----------------|---------|-------------|20| Query Performance Analysis | EXPLAIN ANALYZE | `EXPLAIN (ANALYZE, BUFFERS) SELECT ...` (PG) / `EXPLAIN ANALYZE SELECT ...` (MySQL) | Diagnose slow queries, identify missing indexes |21| 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 |22| Index Analysis | pg_stat_user_indexes / SHOW INDEX | `SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;` | Find unused indexes, validate index coverage |23| Schema Migration | Flyway / Liquibase | `flyway migrate` / `liquibase update` | Version-controlled database changes |24| Backup & Recovery | pg_dump / mysqldump | `pg_dump -Fc dbname > backup.dump` | Point-in-time recovery, disaster recovery |25| Replication Setup | Streaming / GTID | Configure postgresql.conf / my.cnf | High availability, read scaling |26| Safe Tuning Loop | Measure → Explain → Change → Verify | Use tuning worksheet template | Reduce latency/cost without regressions |2728---2930## Decision Tree: Choosing the Right Approach3132```text33Query performance issue?34 ├─ Identify slow queries first?35 │ ├─ PostgreSQL → pg_stat_statements (top queries by total_exec_time)36 │ └─ MySQL → Performance Schema / slow query log37 │38 ├─ Analyze execution plan?39 │ ├─ PostgreSQL → EXPLAIN (ANALYZE, BUFFERS, VERBOSE)40 │ ├─ MySQL → EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE41 │ └─ SQL Server → SET STATISTICS IO ON; SET STATISTICS TIME ON;42 │43 ├─ Need indexing strategy?44 │ ├─ PostgreSQL → B-tree (default), GIN (JSONB), GiST (spatial), partial indexes45 │ ├─ MySQL → BTREE (default), FULLTEXT (text search), SPATIAL46 │ └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified47 │48 ├─ Schema changes needed?49 │ ├─ New database → template-schema-design.md50 │ ├─ Modify schema → template-migration.md (Flyway/Liquibase)51 │ └─ Large tables (MySQL) → gh-ost / pt-online-schema-change (avoid locks)52 │53 ├─ High availability setup?54 │ ├─ PostgreSQL → Streaming replication (template-replication-ha.md)55 │ └─ MySQL → GTID-based replication (template-replication-ha.md)56 │57 ├─ Backup/disaster recovery?58 │ └─ template-backup-restore.md (pg_dump, mysqldump, PITR)59 │60 └─ Analytics on large datasets (OLAP)?61 └─ See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)62```6364---6566## When to Use This Skill6768Claude should invoke this skill when users ask for:6970### Query Optimization (Modern Approaches)71- SQL query performance review and tuning72- EXPLAIN/plan interpretation with optimization suggestions73- Index creation strategies with balanced approach (avoiding over-indexing)74- Troubleshooting slow queries using pg_stat_statements or Performance Schema75- Identifying and remediating SQL anti-patterns with operational fixes76- Query rewrite suggestions or migration from slow to fast patterns77- Statistics maintenance and auto-analyze configuration7879### Database Operations80- Schema design with normalization and performance trade-offs81- Database migrations with version control (Liquibase, Flyway)82- Backup and recovery strategies (point-in-time recovery, automated testing)83- High availability and replication setup (streaming, GTID-based)84- Database security auditing (access controls, encryption, SQL injection prevention)85- Lock analysis and deadlock troubleshooting86- Connection pooling (pgBouncer, Pgpool-II, ProxySQL)8788### Performance Tuning (Modern Standards)89- Memory configuration (work_mem, shared_buffers, effective_cache_size)90- Automated monitoring with pg_stat_statements and query pattern analysis91- Index health monitoring (unused index detection, index bloat analysis)92- Vacuum strategy and autovacuum tuning (PostgreSQL)93- InnoDB buffer pool optimization (MySQL)94- Partition pruning improvements (PostgreSQL 18+)9596---9798## Resources (Best Practices Guides)99100Find detailed operational patterns and quick references in:101102- **SQL Best Practices**: [resources/sql-best-practices.md](resources/sql-best-practices.md)103- **Query Tuning Patterns**: [resources/query-tuning-patterns.md](resources/query-tuning-patterns.md)104- **Indexing Strategies**: [resources/index-patterns.md](resources/index-patterns.md)105- **EXPLAIN/Analysis**: [resources/explain-analysis.md](resources/explain-analysis.md)106- **SQL Anti-Patterns**: [resources/sql-antipatterns.md](resources/sql-antipatterns.md)107- **External Sources**: [data/sources.json](data/sources.json) — vendor docs and reference links108- **Operational Standards**: [resources/operational-patterns.md](resources/operational-patterns.md) — Deep operational checklists, database-specific guidance, and template selection trees109110Each file includes:111- Copy-paste ready checklists (e.g., "query review", "index design", "explain review")112- Anti-patterns with operational fixes and alternatives113- Query rewrite and indexing strategies with examples114- Troubleshooting guides (step-by-step)115116---117118## Templates (Copy-Paste Ready)119120Templates are organized by database technology for precision and clarity:121122### Cross-Platform Templates (All Databases)123- [templates/cross-platform/template-query-tuning.md](templates/cross-platform/template-query-tuning.md) - Universal query optimization124- [templates/cross-platform/template-explain-analysis.md](templates/cross-platform/template-explain-analysis.md) - Execution plan analysis125- [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)126- [templates/cross-platform/template-index.md](templates/cross-platform/template-index.md) - Index design patterns127- [templates/cross-platform/template-slow-query.md](templates/cross-platform/template-slow-query.md) - Slow query triage128- [templates/cross-platform/template-schema-design.md](templates/cross-platform/template-schema-design.md) - Schema modeling129- [templates/cross-platform/template-migration.md](templates/cross-platform/template-migration.md) - Database migrations130- [templates/cross-platform/template-backup-restore.md](templates/cross-platform/template-backup-restore.md) - Backup/DR planning131- [templates/cross-platform/template-security-audit.md](templates/cross-platform/template-security-audit.md) - Security review132- [templates/cross-platform/template-diagnostics.md](templates/cross-platform/template-diagnostics.md) - Performance diagnostics133- [templates/cross-platform/template-lock-analysis.md](templates/cross-platform/template-lock-analysis.md) - Lock troubleshooting134135### PostgreSQL Templates136- [templates/postgres/template-pg-explain.md](templates/postgres/template-pg-explain.md) - PostgreSQL EXPLAIN analysis137- [templates/postgres/template-pg-index.md](templates/postgres/template-pg-index.md) - PostgreSQL indexing (B-tree, GIN, GiST)138- [templates/postgres/template-replication-ha.md](templates/postgres/template-replication-ha.md) - Streaming replication & HA139140### MySQL Templates141- [templates/mysql/template-mysql-explain.md](templates/mysql/template-mysql-explain.md) - MySQL EXPLAIN analysis142- [templates/mysql/template-mysql-index.md](templates/mysql/template-mysql-index.md) - MySQL/InnoDB indexing143- [templates/mysql/template-replication-ha.md](templates/mysql/template-replication-ha.md) - MySQL replication & HA144145### Microsoft SQL Server Templates146- [templates/mssql/template-mssql-explain.md](templates/mssql/template-mssql-explain.md) - SQL Server EXPLAIN/SHOWPLAN analysis147- [templates/mssql/template-mssql-index.md](templates/mssql/template-mssql-index.md) - SQL Server indexing and tuning148149### Oracle Templates150- [templates/oracle/template-oracle-explain.md](templates/oracle/template-oracle-explain.md) - Oracle EXPLAIN plan review and tuning151152### SQLite Templates153- [templates/sqlite/template-sqlite-optimization.md](templates/sqlite/template-sqlite-optimization.md) - SQLite optimization and pragma guidance154155---156157## Related Skills158159**Infrastructure & Operations:**160- [../ops-devops-platform/SKILL.md](../ops-devops-platform/SKILL.md) — Infrastructure, backups, monitoring, and incident response161- [../qa-observability/SKILL.md](../qa-observability/SKILL.md) — Performance monitoring, profiling, and metrics162- [../qa-debugging/SKILL.md](../qa-debugging/SKILL.md) — Production debugging patterns163164**Application Integration:**165- [../software-backend/SKILL.md](../software-backend/SKILL.md) — API/database integration and application patterns166- [../software-architecture-design/SKILL.md](../software-architecture-design/SKILL.md) — System design and data architecture167- [../dev-api-design/SKILL.md](../dev-api-design/SKILL.md) — REST API and database interaction patterns168169**Quality & Security:**170- [../qa-resilience/SKILL.md](../qa-resilience/SKILL.md) — Resilience, circuit breakers, and failure handling171- [../software-security-appsec/SKILL.md](../software-security-appsec/SKILL.md) — Database security, auth, SQL injection prevention172- [../qa-testing-strategy/SKILL.md](../qa-testing-strategy/SKILL.md) — Database testing strategies173174**Data Engineering:**175- [../ai-ml-data-science/SKILL.md](../ai-ml-data-science/SKILL.md) — SQLMesh, dbt, data transformations176- [../ai-mlops/SKILL.md](../ai-mlops/SKILL.md) — Data pipelines, ETL, and warehouse loading (dlt)177- [../ai-ml-timeseries/SKILL.md](../ai-ml-timeseries/SKILL.md) — Time-series databases and forecasting178179---180181## Navigation182183**Resources**184- [resources/explain-analysis.md](resources/explain-analysis.md)185- [resources/query-tuning-patterns.md](resources/query-tuning-patterns.md)186- [resources/operational-patterns.md](resources/operational-patterns.md)187- [resources/sql-antipatterns.md](resources/sql-antipatterns.md)188- [resources/index-patterns.md](resources/index-patterns.md)189- [resources/sql-best-practices.md](resources/sql-best-practices.md)190191**Templates**192- [templates/cross-platform/template-slow-query.md](templates/cross-platform/template-slow-query.md)193- [templates/cross-platform/template-backup-restore.md](templates/cross-platform/template-backup-restore.md)194- [templates/cross-platform/template-schema-design.md](templates/cross-platform/template-schema-design.md)195- [templates/cross-platform/template-explain-analysis.md](templates/cross-platform/template-explain-analysis.md)196- [templates/cross-platform/template-performance-tuning-worksheet.md](templates/cross-platform/template-performance-tuning-worksheet.md)197- [templates/cross-platform/template-security-audit.md](templates/cross-platform/template-security-audit.md)198- [templates/cross-platform/template-diagnostics.md](templates/cross-platform/template-diagnostics.md)199- [templates/cross-platform/template-index.md](templates/cross-platform/template-index.md)200- [templates/cross-platform/template-migration.md](templates/cross-platform/template-migration.md)201- [templates/cross-platform/template-lock-analysis.md](templates/cross-platform/template-lock-analysis.md)202- [templates/cross-platform/template-query-tuning.md](templates/cross-platform/template-query-tuning.md)203- [templates/oracle/template-oracle-explain.md](templates/oracle/template-oracle-explain.md)204- [templates/sqlite/template-sqlite-optimization.md](templates/sqlite/template-sqlite-optimization.md)205- [templates/postgres/template-pg-index.md](templates/postgres/template-pg-index.md)206- [templates/postgres/template-replication-ha.md](templates/postgres/template-replication-ha.md)207- [templates/postgres/template-pg-explain.md](templates/postgres/template-pg-explain.md)208- [templates/mysql/template-mysql-explain.md](templates/mysql/template-mysql-explain.md)209- [templates/mysql/template-mysql-index.md](templates/mysql/template-mysql-index.md)210- [templates/mysql/template-replication-ha.md](templates/mysql/template-replication-ha.md)211- [templates/mssql/template-mssql-index.md](templates/mssql/template-mssql-index.md)212- [templates/mssql/template-mssql-explain.md](templates/mssql/template-mssql-explain.md)213214**Data**215- [data/sources.json](data/sources.json) — Curated external references216217---218219## Operational Deep Dives220221See [resources/operational-patterns.md](resources/operational-patterns.md) for:222- End-to-end optimization checklists and anti-pattern fixes223- Database-specific quick references (PostgreSQL, MySQL, SQL Server, Oracle, SQLite)224- Slow query troubleshooting workflow and reliability drills225- Template selection decision tree and platform migration notes226227---228229## Do / Avoid230231### GOOD: Do232233- Measure baseline before any optimization234- Change one variable at a time235- Verify results match after query changes236- Update statistics before concluding "needs index"237- Test with production-like data volumes238- Document all optimization decisions239- Include performance tests in CI/CD240241### BAD: Avoid242243- Adding indexes without checking if they'll be used244- Using SELECT * in production queries245- Optimizing for test data (use representative volumes)246- Ignoring write performance impact of indexes247- Skipping EXPLAIN analysis before changes248- Multiple simultaneous changes (can't attribute improvement)249- N+1 query patterns in application code250251---252253## Anti-Patterns Quick Reference254255| Anti-Pattern | Problem | Fix |256|--------------|---------|-----|257| **SELECT *** | Reads unnecessary columns | Explicit column list |258| **N+1 queries** | Multiplied round trips | JOIN or batch fetch |259| **Missing WHERE** | Full table scan | Add predicates |260| **Function on indexed column** | Can't use index | Move function to RHS |261| **Implicit type conversion** | Index bypass | Match types explicitly |262| **LIKE '%prefix'** | Leading wildcard = scan | Full-text search |263| **Unbounded result set** | Memory explosion | Add LIMIT/pagination |264| **OR conditions** | Index may not be used | UNION or rewrite |265266See [resources/sql-antipatterns.md](resources/sql-antipatterns.md) for detailed fixes.267268---269270## OLTP vs OLAP Decision Tree271272```text273Is your query for...?274├─ Point lookups (by ID/key)?275│ └─ OLTP database (this skill)276│ - Ensure proper indexes277│ - Use connection pooling278│ - Optimize for low latency279│280├─ Aggregations over recent data (dashboard)?281│ └─ OLTP database (this skill)282│ - Consider materialized views283│ - Index common filter columns284│ - Watch for lock contention285│286├─ Full table scans or historical analysis?287│ └─ OLAP database (data-lake-platform)288│ - ClickHouse, DuckDB, Doris289│ - Columnar storage290│ - Partitioning by date291│292└─ Mixed workload (both)?293 └─ Separate OLTP and OLAP294 - OLTP for transactions295 - Replicate to OLAP for analytics296 - Avoid running analytics on primary297```298299---300301## Optional: AI/Automation302303> **Note**: AI tools assist but require human validation of correctness.304305- **EXPLAIN summarization** — Identify bottlenecks from complex plans306- **Query rewrite suggestions** — Must verify result equivalence307- **Index recommendations** — Check selectivity and write impact first308309### Bounded Claims310311- AI cannot determine correct query results312- Automated index suggestions may miss workload context313- Human review required for production changes314315---316317## Analytical Databases (OLAP)318319For OLAP databases and data lake infrastructure, see **[data-lake-platform](../data-lake-platform/SKILL.md)**:320321- **Query engines:** ClickHouse, DuckDB, Apache Doris, StarRocks322- **Table formats:** Apache Iceberg, Delta Lake, Apache Hudi323- **Transformation:** SQLMesh, dbt (staging/marts layers)324- **Ingestion:** dlt, Airbyte (connectors)325- **Streaming:** Apache Kafka patterns326327This skill focuses on **transactional database optimization** (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Use data-lake-platform for analytical workloads.328329---330331## Related Skills332333This skill focuses on **query optimization** within a single database. For related workflows:334335**SQL Transformation & Analytics Engineering:**336→ **[ai-ml-data-science](../ai-ml-data-science/SKILL.md)** skill337- SQLMesh templates for building staging/intermediate/marts layers338- Incremental models (FULL, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY)339- DAG management and model dependencies340- Unit tests and audits for SQL transformations341342**Data Ingestion (Loading into Warehouses):**343→ **[ai-mlops](../ai-mlops/SKILL.md)** skill344- dlt templates for extracting from REST APIs, databases345- Loading to Snowflake, BigQuery, Redshift, Postgres, DuckDB346- Incremental loading patterns (timestamp, ID-based, merge/upsert)347- Database replication (Postgres, MySQL, MongoDB → warehouse)348349**Data Lake Infrastructure:**350→ **[data-lake-platform](../data-lake-platform/SKILL.md)** skill351352- ClickHouse, DuckDB, Doris, StarRocks query engines353- Iceberg, Delta Lake, Hudi table formats354- Kafka streaming, Dagster/Airflow orchestration355356**Use Case Decision:**357358- **Query is slow in production** → Use this skill (data-sql-optimization)359- **Building feature pipelines in SQL** → Use ai-ml-data-science (SQLMesh)360- **Loading data from APIs/DBs to warehouse** → Use ai-mlops (dlt)361- **Analytics on large datasets (OLAP)** → Use data-lake-platform362363---364365## External Resources366367See [data/sources.json](data/sources.json) for 62+ curated resources including:368369**Core Documentation:**370- **RDBMS Documentation**: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, DuckDB official docs371- **Query Optimization**: Use The Index, Luke, SQL Performance Explained, vendor optimization guides372- **Schema Design**: Database Refactoring (Fowler), normalization guides, data type selection373374**Modern Optimization (December 2025):**375- **PostgreSQL**: official release notes and "current" docs for planner/optimizer changes376- **MySQL**: official reference manual sections for EXPLAIN, optimizer, and Performance Schema377- **SQL Server / Oracle**: official docs for execution plans, indexing, and concurrency controls378379**Operations & Infrastructure:**380- **HA & Replication**: Streaming replication, GTID-based replication, failover automation381- **Migrations**: Liquibase, Flyway version control and deployment patterns382- **Backup/Recovery**: pgBackRest, Percona XtraBackup, point-in-time recovery383- **Monitoring**: pg_stat_statements, Performance Schema, EXPLAIN visualizers (Dalibo, depesz)384- **Security**: OWASP SQL Injection Prevention, Postgres hardening, encryption standards385- **Analytical Databases**: DuckDB extensions, Parquet specification, columnar storage patterns386387---388389Use [resources/operational-patterns.md](resources/operational-patterns.md) and the templates directory for detailed workflows, migration notes, and ready-to-run commands.
diegosouzapw