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
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
- Query Tuning Patterns: references/query-tuning-patterns.md
- Indexing Strategies: references/index-patterns.md
- EXPLAIN/Analysis: references/explain-analysis.md
- SQL Anti-Patterns: references/sql-antipatterns.md
- External Sources: data/sources.json — vendor docs and reference links
- Operational Standards: references/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)
- assets/cross-platform/template-query-tuning.md - Universal query optimization
- assets/cross-platform/template-explain-analysis.md - Execution plan analysis
- assets/cross-platform/template-performance-tuning-worksheet.md - NEW 4-step tuning workflow (Measure -> Explain -> Change -> Verify)
- assets/cross-platform/template-index.md - Index design patterns
- assets/cross-platform/template-slow-query.md - Slow query triage
- assets/cross-platform/template-schema-design.md - Schema modeling
- assets/cross-platform/template-migration.md - Database migrations
- assets/cross-platform/template-backup-restore.md - Backup/DR planning
- assets/cross-platform/template-security-audit.md - Security review
- assets/cross-platform/template-diagnostics.md - Performance diagnostics
- assets/cross-platform/template-lock-analysis.md - Lock troubleshooting
PostgreSQL Templates
- assets/postgres/template-pg-explain.md - PostgreSQL EXPLAIN analysis
- assets/postgres/template-pg-index.md - PostgreSQL indexing (B-tree, GIN, GiST)
- assets/postgres/template-replication-ha.md - Streaming replication & HA
MySQL Templates
- assets/mysql/template-mysql-explain.md - MySQL EXPLAIN analysis
- assets/mysql/template-mysql-index.md - MySQL/InnoDB indexing
- assets/mysql/template-replication-ha.md - MySQL replication & HA
Microsoft SQL Server Templates
- assets/mssql/template-mssql-explain.md - SQL Server EXPLAIN/SHOWPLAN analysis
- assets/mssql/template-mssql-index.md - SQL Server indexing and tuning
Oracle Templates
- assets/oracle/template-oracle-explain.md - Oracle EXPLAIN plan review and tuning
SQLite Templates
- assets/sqlite/template-sqlite-optimization.md - SQLite optimization and pragma guidance
Related Skills
Infrastructure & Operations:
Application Integration:
Quality & Security:
Data Engineering:
Navigation
Resources
- references/explain-analysis.md
- references/query-tuning-patterns.md
- references/operational-patterns.md
- references/sql-antipatterns.md
- references/index-patterns.md
- references/sql-best-practices.md
Templates
- assets/cross-platform/template-slow-query.md
- assets/cross-platform/template-backup-restore.md
- assets/cross-platform/template-schema-design.md
- assets/cross-platform/template-explain-analysis.md
- assets/cross-platform/template-performance-tuning-worksheet.md
- assets/cross-platform/template-security-audit.md
- assets/cross-platform/template-diagnostics.md
- assets/cross-platform/template-index.md
- assets/cross-platform/template-migration.md
- assets/cross-platform/template-lock-analysis.md
- assets/cross-platform/template-query-tuning.md
- assets/oracle/template-oracle-explain.md
- assets/sqlite/template-sqlite-optimization.md
- assets/postgres/template-pg-index.md
- assets/postgres/template-replication-ha.md
- assets/postgres/template-pg-explain.md
- assets/mysql/template-mysql-explain.md
- assets/mysql/template-mysql-index.md
- assets/mysql/template-replication-ha.md
- assets/mssql/template-mssql-index.md
- assets/mssql/template-mssql-explain.md
Data
- data/sources.json — Curated external references
Operational Deep Dives
See references/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 references/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 (Current):
- 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 references/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---5
6# SQL Optimization — Comprehensive Reference
7
8This 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.
9
10**Supported Platforms:** PostgreSQL, MySQL, SQL Server, Oracle, SQLite
11
12**For OLAP/Analytics:** See [data-lake-platform](../data-lake-platform/SKILL.md) (ClickHouse, DuckDB, Doris, StarRocks)
13
14---
15
16## Quick Reference
17
18| 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 |
27
28---
29
30## Decision Tree: Choosing the Right Approach
31
32```text
33Query performance issue?
34 ├─ Identify slow queries first?
35 │ ├─ PostgreSQL -> pg_stat_statements (top queries by total_exec_time)
36 │ └─ MySQL -> Performance Schema / slow query log
37 │
38 ├─ Analyze execution plan?
39 │ ├─ PostgreSQL -> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
40 │ ├─ MySQL -> EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE
41 │ └─ 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 indexes
45 │ ├─ MySQL -> BTREE (default), FULLTEXT (text search), SPATIAL
46 │ └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified
47 │
48 ├─ Schema changes needed?
49 │ ├─ New database -> template-schema-design.md
50 │ ├─ 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```
63
64---
65
66## When to Use This Skill
67
68Codex should invoke this skill when users ask for:
69
70### Query Optimization (Modern Approaches)
71- SQL query performance review and tuning
72- EXPLAIN/plan interpretation with optimization suggestions
73- Index creation strategies with balanced approach (avoiding over-indexing)
74- Troubleshooting slow queries using pg_stat_statements or Performance Schema
75- Identifying and remediating SQL anti-patterns with operational fixes
76- Query rewrite suggestions or migration from slow to fast patterns
77- Statistics maintenance and auto-analyze configuration
78
79### Database Operations
80- Schema design with normalization and performance trade-offs
81- 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 troubleshooting
86- Connection pooling (pgBouncer, Pgpool-II, ProxySQL)
87
88### Performance Tuning (Modern Standards)
89- Memory configuration (work_mem, shared_buffers, effective_cache_size)
90- Automated monitoring with pg_stat_statements and query pattern analysis
91- 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+)
95
96---
97
98## Resources (Best Practices Guides)
99
100Find detailed operational patterns and quick references in:
101
102- **SQL Best Practices**: [references/sql-best-practices.md](references/sql-best-practices.md)
103- **Query Tuning Patterns**: [references/query-tuning-patterns.md](references/query-tuning-patterns.md)
104- **Indexing Strategies**: [references/index-patterns.md](references/index-patterns.md)
105- **EXPLAIN/Analysis**: [references/explain-analysis.md](references/explain-analysis.md)
106- **SQL Anti-Patterns**: [references/sql-antipatterns.md](references/sql-antipatterns.md)
107- **External Sources**: [data/sources.json](data/sources.json) — vendor docs and reference links
108- **Operational Standards**: [references/operational-patterns.md](references/operational-patterns.md) — Deep operational checklists, database-specific guidance, and template selection trees
109
110Each file includes:
111- Copy-paste ready checklists (e.g., "query review", "index design", "explain review")
112- Anti-patterns with operational fixes and alternatives
113- Query rewrite and indexing strategies with examples
114- Troubleshooting guides (step-by-step)
115
116---
117
118## Templates (Copy-Paste Ready)
119
120Templates are organized by database technology for precision and clarity:
121
122### Cross-Platform Templates (All Databases)
123- [assets/cross-platform/template-query-tuning.md](assets/cross-platform/template-query-tuning.md) - Universal query optimization
124- [assets/cross-platform/template-explain-analysis.md](assets/cross-platform/template-explain-analysis.md) - Execution plan analysis
125- [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)
126- [assets/cross-platform/template-index.md](assets/cross-platform/template-index.md) - Index design patterns
127- [assets/cross-platform/template-slow-query.md](assets/cross-platform/template-slow-query.md) - Slow query triage
128- [assets/cross-platform/template-schema-design.md](assets/cross-platform/template-schema-design.md) - Schema modeling
129- [assets/cross-platform/template-migration.md](assets/cross-platform/template-migration.md) - Database migrations
130- [assets/cross-platform/template-backup-restore.md](assets/cross-platform/template-backup-restore.md) - Backup/DR planning
131- [assets/cross-platform/template-security-audit.md](assets/cross-platform/template-security-audit.md) - Security review
132- [assets/cross-platform/template-diagnostics.md](assets/cross-platform/template-diagnostics.md) - Performance diagnostics
133- [assets/cross-platform/template-lock-analysis.md](assets/cross-platform/template-lock-analysis.md) - Lock troubleshooting
134
135### PostgreSQL Templates
136- [assets/postgres/template-pg-explain.md](assets/postgres/template-pg-explain.md) - PostgreSQL EXPLAIN analysis
137- [assets/postgres/template-pg-index.md](assets/postgres/template-pg-index.md) - PostgreSQL indexing (B-tree, GIN, GiST)
138- [assets/postgres/template-replication-ha.md](assets/postgres/template-replication-ha.md) - Streaming replication & HA
139
140### MySQL Templates
141- [assets/mysql/template-mysql-explain.md](assets/mysql/template-mysql-explain.md) - MySQL EXPLAIN analysis
142- [assets/mysql/template-mysql-index.md](assets/mysql/template-mysql-index.md) - MySQL/InnoDB indexing
143- [assets/mysql/template-replication-ha.md](assets/mysql/template-replication-ha.md) - MySQL replication & HA
144
145### Microsoft SQL Server Templates
146- [assets/mssql/template-mssql-explain.md](assets/mssql/template-mssql-explain.md) - SQL Server EXPLAIN/SHOWPLAN analysis
147- [assets/mssql/template-mssql-index.md](assets/mssql/template-mssql-index.md) - SQL Server indexing and tuning
148
149### Oracle Templates
150- [assets/oracle/template-oracle-explain.md](assets/oracle/template-oracle-explain.md) - Oracle EXPLAIN plan review and tuning
151
152### SQLite Templates
153- [assets/sqlite/template-sqlite-optimization.md](assets/sqlite/template-sqlite-optimization.md) - SQLite optimization and pragma guidance
154
155---
156
157## Related Skills
158
159**Infrastructure & Operations:**
160- [../ops-devops-platform/SKILL.md](../ops-devops-platform/SKILL.md) — Infrastructure, backups, monitoring, and incident response
161- [../qa-observability/SKILL.md](../qa-observability/SKILL.md) — Performance monitoring, profiling, and metrics
162- [../qa-debugging/SKILL.md](../qa-debugging/SKILL.md) — Production debugging patterns
163
164**Application Integration:**
165- [../software-backend/SKILL.md](../software-backend/SKILL.md) — API/database integration and application patterns
166- [../software-architecture-design/SKILL.md](../software-architecture-design/SKILL.md) — System design and data architecture
167- [../dev-api-design/SKILL.md](../dev-api-design/SKILL.md) — REST API and database interaction patterns
168
169**Quality & Security:**
170- [../qa-resilience/SKILL.md](../qa-resilience/SKILL.md) — Resilience, circuit breakers, and failure handling
171- [../software-security-appsec/SKILL.md](../software-security-appsec/SKILL.md) — Database security, auth, SQL injection prevention
172- [../qa-testing-strategy/SKILL.md](../qa-testing-strategy/SKILL.md) — Database testing strategies
173
174**Data Engineering:**
175- [../ai-ml-data-science/SKILL.md](../ai-ml-data-science/SKILL.md) — SQLMesh, dbt, data transformations
176- [../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 forecasting
178
179---
180
181## Navigation
182
183**Resources**
184- [references/explain-analysis.md](references/explain-analysis.md)
185- [references/query-tuning-patterns.md](references/query-tuning-patterns.md)
186- [references/operational-patterns.md](references/operational-patterns.md)
187- [references/sql-antipatterns.md](references/sql-antipatterns.md)
188- [references/index-patterns.md](references/index-patterns.md)
189- [references/sql-best-practices.md](references/sql-best-practices.md)
190
191**Templates**
192- [assets/cross-platform/template-slow-query.md](assets/cross-platform/template-slow-query.md)
193- [assets/cross-platform/template-backup-restore.md](assets/cross-platform/template-backup-restore.md)
194- [assets/cross-platform/template-schema-design.md](assets/cross-platform/template-schema-design.md)
195- [assets/cross-platform/template-explain-analysis.md](assets/cross-platform/template-explain-analysis.md)
196- [assets/cross-platform/template-performance-tuning-worksheet.md](assets/cross-platform/template-performance-tuning-worksheet.md)
197- [assets/cross-platform/template-security-audit.md](assets/cross-platform/template-security-audit.md)
198- [assets/cross-platform/template-diagnostics.md](assets/cross-platform/template-diagnostics.md)
199- [assets/cross-platform/template-index.md](assets/cross-platform/template-index.md)
200- [assets/cross-platform/template-migration.md](assets/cross-platform/template-migration.md)
201- [assets/cross-platform/template-lock-analysis.md](assets/cross-platform/template-lock-analysis.md)
202- [assets/cross-platform/template-query-tuning.md](assets/cross-platform/template-query-tuning.md)
203- [assets/oracle/template-oracle-explain.md](assets/oracle/template-oracle-explain.md)
204- [assets/sqlite/template-sqlite-optimization.md](assets/sqlite/template-sqlite-optimization.md)
205- [assets/postgres/template-pg-index.md](assets/postgres/template-pg-index.md)
206- [assets/postgres/template-replication-ha.md](assets/postgres/template-replication-ha.md)
207- [assets/postgres/template-pg-explain.md](assets/postgres/template-pg-explain.md)
208- [assets/mysql/template-mysql-explain.md](assets/mysql/template-mysql-explain.md)
209- [assets/mysql/template-mysql-index.md](assets/mysql/template-mysql-index.md)
210- [assets/mysql/template-replication-ha.md](assets/mysql/template-replication-ha.md)
211- [assets/mssql/template-mssql-index.md](assets/mssql/template-mssql-index.md)
212- [assets/mssql/template-mssql-explain.md](assets/mssql/template-mssql-explain.md)
213
214**Data**
215- [data/sources.json](data/sources.json) — Curated external references
216
217---
218
219## Operational Deep Dives
220
221See [references/operational-patterns.md](references/operational-patterns.md) for:
222- End-to-end optimization checklists and anti-pattern fixes
223- Database-specific quick references (PostgreSQL, MySQL, SQL Server, Oracle, SQLite)
224- Slow query troubleshooting workflow and reliability drills
225- Template selection decision tree and platform migration notes
226
227---
228
229## Do / Avoid
230
231### GOOD: Do
232
233- Measure baseline before any optimization
234- Change one variable at a time
235- Verify results match after query changes
236- Update statistics before concluding "needs index"
237- Test with production-like data volumes
238- Document all optimization decisions
239- Include performance tests in CI/CD
240
241### BAD: Avoid
242
243- Adding indexes without checking if they'll be used
244- Using SELECT * in production queries
245- Optimizing for test data (use representative volumes)
246- Ignoring write performance impact of indexes
247- Skipping EXPLAIN analysis before changes
248- Multiple simultaneous changes (can't attribute improvement)
249- N+1 query patterns in application code
250
251---
252
253## Anti-Patterns Quick Reference
254
255| 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 |
265
266See [references/sql-antipatterns.md](references/sql-antipatterns.md) for detailed fixes.
267
268---
269
270## OLTP vs OLAP Decision Tree
271
272```text
273Is your query for...?
274├─ Point lookups (by ID/key)?
275│ └─ OLTP database (this skill)
276│ - Ensure proper indexes
277│ - Use connection pooling
278│ - Optimize for low latency
279│
280├─ Aggregations over recent data (dashboard)?
281│ └─ OLTP database (this skill)
282│ - Consider materialized views
283│ - Index common filter columns
284│ - Watch for lock contention
285│
286├─ Full table scans or historical analysis?
287│ └─ OLAP database (data-lake-platform)
288│ - ClickHouse, DuckDB, Doris
289│ - Columnar storage
290│ - Partitioning by date
291│
292└─ Mixed workload (both)?
293 └─ Separate OLTP and OLAP
294 - OLTP for transactions
295 - Replicate to OLAP for analytics
296 - Avoid running analytics on primary
297```
298
299---
300
301## Optional: AI/Automation
302
303> **Note**: AI tools assist but require human validation of correctness.
304
305- **EXPLAIN summarization** — Identify bottlenecks from complex plans
306- **Query rewrite suggestions** — Must verify result equivalence
307- **Index recommendations** — Check selectivity and write impact first
308
309### Bounded Claims
310
311- AI cannot determine correct query results
312- Automated index suggestions may miss workload context
313- Human review required for production changes
314
315---
316
317## Analytical Databases (OLAP)
318
319For OLAP databases and data lake infrastructure, see **[data-lake-platform](../data-lake-platform/SKILL.md)**:
320
321- **Query engines:** ClickHouse, DuckDB, Apache Doris, StarRocks
322- **Table formats:** Apache Iceberg, Delta Lake, Apache Hudi
323- **Transformation:** SQLMesh, dbt (staging/marts layers)
324- **Ingestion:** dlt, Airbyte (connectors)
325- **Streaming:** Apache Kafka patterns
326
327This skill focuses on **transactional database optimization** (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Use data-lake-platform for analytical workloads.
328
329---
330
331## Related Skills
332
333This skill focuses on **query optimization** within a single database. For related workflows:
334
335**SQL Transformation & Analytics Engineering:**
336-> **[ai-ml-data-science](../ai-ml-data-science/SKILL.md)** skill
337- SQLMesh templates for building staging/intermediate/marts layers
338- Incremental models (FULL, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY)
339- DAG management and model dependencies
340- Unit tests and audits for SQL transformations
341
342**Data Ingestion (Loading into Warehouses):**
343-> **[ai-mlops](../ai-mlops/SKILL.md)** skill
344- dlt templates for extracting from REST APIs, databases
345- Loading to Snowflake, BigQuery, Redshift, Postgres, DuckDB
346- Incremental loading patterns (timestamp, ID-based, merge/upsert)
347- Database replication (Postgres, MySQL, MongoDB -> warehouse)
348
349**Data Lake Infrastructure:**
350-> **[data-lake-platform](../data-lake-platform/SKILL.md)** skill
351
352- ClickHouse, DuckDB, Doris, StarRocks query engines
353- Iceberg, Delta Lake, Hudi table formats
354- Kafka streaming, Dagster/Airflow orchestration
355
356**Use Case Decision:**
357
358- **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-platform
362
363---
364
365## External Resources
366
367See [data/sources.json](data/sources.json) for 62+ curated resources including:
368
369**Core Documentation:**
370- **RDBMS Documentation**: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, DuckDB official docs
371- **Query Optimization**: Use The Index, Luke, SQL Performance Explained, vendor optimization guides
372- **Schema Design**: Database Refactoring (Fowler), normalization guides, data type selection
373
374**Modern Optimization (Current):**
375- **PostgreSQL**: official release notes and "current" docs for planner/optimizer changes
376- **MySQL**: official reference manual sections for EXPLAIN, optimizer, and Performance Schema
377- **SQL Server / Oracle**: official docs for execution plans, indexing, and concurrency controls
378
379**Operations & Infrastructure:**
380- **HA & Replication**: Streaming replication, GTID-based replication, failover automation
381- **Migrations**: Liquibase, Flyway version control and deployment patterns
382- **Backup/Recovery**: pgBackRest, Percona XtraBackup, point-in-time recovery
383- **Monitoring**: pg_stat_statements, Performance Schema, EXPLAIN visualizers (Dalibo, depesz)
384- **Security**: OWASP SQL Injection Prevention, Postgres hardening, encryption standards
385- **Analytical Databases**: DuckDB extensions, Parquet specification, columnar storage patterns
386
387---
388
389Use [references/operational-patterns.md](references/operational-patterns.md) and the templates directory for detailed workflows, migration notes, and ready-to-run commands.
aibot88