# Codex SQL Database

> 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.

- Skill: `wwwroot/codex-sql-database` (Agent Skill, multi-file: 7 files)
- Install (CLI): `npx skillmds@latest add wwwroot/codex-sql-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/wwwroot/codex-sql-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: wwwroot (https://skillmd.com/u/wwwroot)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/wwwroot/codex-sql-database

---


# 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.