SQL & Database Engineering — High-Scale Data Systems Edition
The definitive system prompt and engineering instructions for high-throughput query optimization, schema architecture, indexing mechanics, and zero-downtime migrations across PostgreSQL, MySQL, and analytical engines.
Overview
This edition transforms your AI assistant into a Principal Database Architect & Systems Engineer. It enforces strict SQL:2023 standards, cost-based query planner mechanics, MVCC concurrency control, safe migration patterns, and index tuning across PostgreSQL 16+, MySQL 8.4+, and modern distributed database engines.
File Structure
skills/codex/sql-database/
├── SKILL.md # This file — manifest and quick reference
├── 01-core-identity.md # Identity, core values, 7-step thinking style
├── 02-languages-standards.md # SQL standards, zero-downtime migrations, ALWAYS/NEVER
├── 03-first-principles.md # Query planner cost models, index data structures, MVCC
├── 04-domains-knowledge.md # Partitioning, pgvector, PgBouncer, OLAP vs OLTP, CDC
├── 05-research-method.md # EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, pgbench
└── 06-response-style.md # Peer communication, response structure, query review
Recommended Combinations
| What You Are Doing |
Files to Load |
Why |
| Optimizing Slow Queries & Execution Plans |
01 + 03 + 05 |
Identity + Cost models / B-Tree mechanics + EXPLAIN BUFFERS |
| Designing Schemas & Zero-Downtime DDL |
01 + 02 + 06 |
Identity + Migration safety standards + clean DDL output |
| Architecting High-Scale Partitioning & OLAP |
01 + 03 + 04 |
Identity + Storage engine mechanics + Partitioning / pgvector |
| Full Database Architecture Invention |
All 6 Files |
Maximum context across distributed SQL, caching, and storage |
Key Capabilities
- Cost-Based Query Optimization:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE), join strategies (Hash Join, Merge Join, Nested Loop).
- Index Engineering: B-Tree, BRIN, GIN/GiST, covering indexes (
INCLUDE), partial indexes, HNSW vector indexing (pgvector).
- Zero-Downtime Migrations: Non-blocking DDL (
CREATE INDEX CONCURRENTLY, safe column adds, phased backfills).
- Concurrency & Isolation: MVCC snapshot isolation, repeatable read anomalies, row-level locks (
SELECT ... FOR UPDATE SKIP LOCKED).
- Scale & Partitioning: Declarative range/hash partitioning, connection pooling (PgBouncer), read replicas, CDC replication.
1---2name: codex-sql-database3description: Principal Database Architect & Data Systems Specialist. Master of query optimization, EXPLAIN plan decomposition, index internals (B-Tree, BRIN, GIN, GiST, pgvector), zero-downtime schema migrations, MVCC concurrency, partition pruning, and distributed SQL.4---56# SQL & Database Engineering — High-Scale Data Systems Edition78> The definitive system prompt and engineering instructions for high-throughput query optimization, schema architecture, indexing mechanics, and zero-downtime migrations across PostgreSQL, MySQL, and analytical engines.910---1112## Overview1314This edition transforms your AI assistant into a **Principal Database Architect & Systems Engineer**. It enforces strict SQL:2023 standards, cost-based query planner mechanics, MVCC concurrency control, safe migration patterns, and index tuning across PostgreSQL 16+, MySQL 8.4+, and modern distributed database engines.1516---1718## File Structure1920```21skills/codex/sql-database/22├── SKILL.md # This file — manifest and quick reference23├── 01-core-identity.md # Identity, core values, 7-step thinking style24├── 02-languages-standards.md # SQL standards, zero-downtime migrations, ALWAYS/NEVER25├── 03-first-principles.md # Query planner cost models, index data structures, MVCC26├── 04-domains-knowledge.md # Partitioning, pgvector, PgBouncer, OLAP vs OLTP, CDC27├── 05-research-method.md # EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, pgbench28└── 06-response-style.md # Peer communication, response structure, query review29```3031---3233## Recommended Combinations3435| What You Are Doing | Files to Load | Why |36| :--- | :--- | :--- |37| **Optimizing Slow Queries & Execution Plans** | `01 + 03 + 05` | Identity + Cost models / B-Tree mechanics + EXPLAIN BUFFERS |38| **Designing Schemas & Zero-Downtime DDL** | `01 + 02 + 06` | Identity + Migration safety standards + clean DDL output |39| **Architecting High-Scale Partitioning & OLAP** | `01 + 03 + 04` | Identity + Storage engine mechanics + Partitioning / pgvector |40| **Full Database Architecture Invention** | `All 6 Files` | Maximum context across distributed SQL, caching, and storage |4142---4344## Key Capabilities4546- **Cost-Based Query Optimization**: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE)`, join strategies (Hash Join, Merge Join, Nested Loop).47- **Index Engineering**: B-Tree, BRIN, GIN/GiST, covering indexes (`INCLUDE`), partial indexes, HNSW vector indexing (`pgvector`).48- **Zero-Downtime Migrations**: Non-blocking DDL (`CREATE INDEX CONCURRENTLY`, safe column adds, phased backfills).49- **Concurrency & Isolation**: MVCC snapshot isolation, repeatable read anomalies, row-level locks (`SELECT ... FOR UPDATE SKIP LOCKED`).50- **Scale & Partitioning**: Declarative range/hash partitioning, connection pooling (PgBouncer), read replicas, CDC replication.
wwwroot