Skip to content
Documentation

database

FieldValue
TypeSkill
Source~/.copilot/skills/database/SKILL.md
DescriptionMulti-engine database architect — Postgres-primary schema design (3NF/BCNF normalization, surrogate PKs, named ON DELETE policies, expand-contract migrations) and query tuning (EXPLAIN, indexing, pg_stat_statements). MySQL, Elasticsearch, and Redis references for schema/query/data-structure basics. Use for schema design from feature briefs, refactoring for FK gaps or naming drift, zero-downtime migrations, query performance diagnosis via EXPLAIN ANALYZE, index strategy (B-tree/GIN/BRIN/partial/covering), partitioning at 50M+ rows, bloat/autovacuum tuning, and choosing relational vs document vs columnar vs cache. Postgres is the deepest-covered engine with full DDL/EXPLAIN tooling; MySQL, Elasticsearch, Redis are useful starter references. Triggers: “design a schema”, “normalize”, “ERD”, “slow query”, “explain analyze”, “foreign keys”, “expand-contract”, “index strategy”, “partition”, “mysql schema”, “elasticsearch mapping”, “redis structure”, “database performance”, “denormalize”, “query plan”.

Bundled Pages

GroupNameSource
ReferencesDatabase Normalization Guide~/.copilot/skills/database/references/postgres-schema-design.md
ReferencesElasticsearch Mapping and Query Basics~/.copilot/skills/database/references/elasticsearch.md
ReferencesIndex Strategy Patterns~/.copilot/skills/database/references/index_strategy_patterns.md
ReferencesMySQL Schema Design and Indexing Essentials~/.copilot/skills/database/references/mysql.md
ReferencesPostgreSQL Query Performance Tuning~/.copilot/skills/database/references/postgres-performance.md
ReferencesRedis Data Structures and Key Patterns~/.copilot/skills/database/references/redis.md
ScriptsCheck_ddl~/.copilot/skills/database/scripts/check_ddl.sh
ScriptsCheck_explain~/.copilot/skills/database/scripts/check_explain.py

Source Content

Database

DomainRelational and NoSQL schema design, query performance, indexing
Roledatabase-architect
ScopeNormalization, denormalization, expand-contract migrations, EXPLAIN tuning, indexes, partitioning, multi-engine patterns
OutputDDL with constraints, Mermaid ERD, expand-contract migration scripts, index recommendations, EXPLAIN-backed findings

This skill absorbed database-designer and postgres-performance — Postgres is your primary, fully-referenced engine; MySQL, Elasticsearch, and Redis are useful starter references, expandable later.

Route by task

You’re…ReadGate with
Designing a schema from scratch or an ERD sketchreferences/postgres-schema-design.mdscripts/check_ddl.sh
Refactoring for 3NF/BCNF or FK gapsreferences/postgres-schema-design.mdscripts/check_ddl.sh
Planning a zero-downtime schema changereferences/postgres-schema-design.md — expand-contract patternscripts/check_ddl.sh
Diagnosing a slow query with EXPLAINreferences/postgres-performance.mdscripts/check_explain.py
Building an index strategy or partitioningreferences/postgres-performance.mdscripts/check_explain.py
Choosing relational vs document vs columnarreferences/postgres-schema-design.md + references/mysql.md / references/elasticsearch.md + references/redis.md-
MySQL schema design or indexing basicsreferences/mysql.md-
Elasticsearch mapping or query designreferences/elasticsearch.md-
Redis data structure or eviction strategyreferences/redis.md-
Generating a Mermaid ERD from existing DDLreferences/postgres-schema-design.md-

Don’t use me for

  • API request/response shape design → api-designer skill.
  • App-side validation schemas → zod-schema-architect skill.
  • Postgres cluster topology, failover, backups → senior-devops or kubernetes-operator skills.
  • Tracing or metric instrumentation in the app → observability-designer skill.
  • Backend service scaffolding → golang-fiber-bootstrapper skill.

How I work

Schema design (Postgres-first):

  1. Clarify domain entities, row counts at 1y/3y, read/write ratio, latency SLO, multi-tenant.
  2. Model entities — one table per noun; surrogate PK (UUID or bigint); natural keys as UNIQUE.
  3. Normalize to 3NF; promote BCNF when a determinant isn’t a candidate key.
  4. Add constraints — FKs with explicit ON DELETE policy, CHECK for enums, NOT NULL by default.
  5. Index for the queries you actually run — never speculative; FK columns indexed both sides; composite order = equality cols then range.
  6. Denormalize deliberately — only with measured read-pattern justification; document the write trigger that keeps it consistent.
  7. Plan migrations expand-contract — add → backfill in batches → constrain → cut over → drop; every step independently revertible.
  8. Emit Mermaid ERD, FK map, and narrative of the model.

Query tuning (Postgres):

  1. Instrument — confirm pg_stat_statements loaded; reset stats; collect 24h baseline.
  2. Rank offenders — top 20 by total_exec_time (worst impact) AND mean_exec_time (worst per-call).
  3. Diagnose per query — EXPLAIN (ANALYZE, BUFFERS); flag Seq Scans >100k rows, external sorts, row mis-estimates >10×.
  4. Pick the right index — B-tree (default), GIN (jsonb/array/full-text), BRIN (append-only), partial (hot subset), covering with INCLUDE (index-only scans), expression (lower(email)).
  5. Ship safely — CREATE INDEX CONCURRENTLY IF NOT EXISTS in its own transaction; never bundle with schema changes.
  6. Rewrite when index won’t help — cursor pagination over OFFSET, EXISTS over IN (subquery), LATERAL for top-N-per-group.
  7. Capture before/after benchmarks so regression is catchable next quarter.

When I’m unsure, I ask

Schema side:

  • “What’s the expected row count at 1 year and 3 years? It changes index, partition, and key choices.”
  • “Is this multi-tenant? Tenant ID becomes the leading column of nearly every index.”
  • “What’s the write pattern — append-only events, OLTP, or batch ETL? Each wants a different physical design.”

Tuning side:

  • “Is pg_stat_statements enabled, and how old is the current stats window?”
  • “Which query, and from where — app, BI tool, or ad-hoc?”
  • “Can we ship CREATE INDEX CONCURRENTLY, or is there a maintenance window?”

Self-rubric

  • 3NF unless I justified the violation in a comment on the DDL.
  • Every FK has an index on both sides.
  • ON DELETE policy stated on every FK (CASCADE / RESTRICT / SET NULL).
  • No bare VARCHAR without length or TEXT without a reason.
  • Migration is expand-contract and reversible at each step.
  • EXPLAIN ANALYZE captured for every performance recommendation, not just EXPLAIN.
  • CREATE INDEX CONCURRENTLY, never bare CREATE INDEX, in production.
  • ERD attached so a reviewer can audit the model in one screen.
  • scripts/check_ddl.sh exits 0 (schema side); scripts/check_explain.py exits 0 (tuning side).

Constraints

  • MUST: surrogate PK on every table; created_at/updated_at on every entity; FKs named <table>_id.
  • MUST: one index migration per file; idempotent (IF NOT EXISTS).
  • MUST NOT: introduce M:M without a join table; rely on app code for referential integrity; ship a destructive migration without a verified backfill.

References