AgentSkillsCN

database-design

涵盖 Schema 设计、优化与数据库管理。包括主键选择（UUID/ULID/序列号）、安全迁移、借助 EXPLAIN ANALYZE 进行查询优化、索引策略（B 树、部分索引、复合索引）以及规范化权衡。在设计表结构、编写迁移脚本，或当用户询问“我该选用哪种主键？”或“如何优化这条查询？”时，可加以运用。

SKILL.md
--- frontmatter
name: database-design
description: >-
  Covers schema design, optimization, and database administration. Includes
  primary key selection (UUID/ULID/serial), safe migrations, query optimization
  with EXPLAIN ANALYZE, indexing strategies (B-tree, partial, composite), and
  normalization tradeoffs. Use when designing tables, writing migrations, or
  when the user asks "what primary key should I use?" or "how do I optimize this
  query?"
version: 1.16.0

Database Skill

Domain knowledge for database administration and development. Covers schema design, migrations, query optimization, and indexing strategies.

When to Use This Skill

  • •Designing new tables or modifying existing schemas
  • •Writing or reviewing database migrations
  • •Optimizing slow queries
  • •Planning index strategies
  • •Evaluating normalization vs denormalization tradeoffs

Key Reference Files

FileUse When
schema-design.mdCreating tables, choosing keys, audit patterns
migrations.mdWriting safe, reversible migrations
query-optimization.mdDebugging slow queries, N+1 detection
indexing.mdChoosing index types, composite index order

Quick Reference

Primary Key Selection

TypeUse WhenAvoid When
UUID v4Distributed systems, no sequential exposureNeed sortability, space-constrained
ULIDNeed sortability + uniquenessLegacy system compatibility
Serial/IdentitySingle database, simple use caseDistributed writes, ID exposure concerns

Migration Safety

code
Safe (online):     ADD COLUMN (nullable), ADD INDEX CONCURRENTLY, CREATE TABLE
Unsafe (offline):  ADD COLUMN NOT NULL (without default), DROP COLUMN, RENAME

Index Decision Tree

code
High cardinality + equality lookups  → B-tree (default)
Low cardinality + many values        → Consider partial index
Array/JSON containment               → GIN
Geometric/range queries              → GiST
Exact equality only                  → Hash (rare)

Core Principles

  1. •Data integrity first - Constraints catch bugs that code misses
  2. •Plan for scale - Design decisions are expensive to change
  3. •Measure before optimizing - Use EXPLAIN ANALYZE, not intuition
  4. •Backward compatibility - Migrations must not break running code

Related Skills

  • •See foundations for universal code quality principles
  • •See infrastructure for connection pooling and deployment patterns