# database

> Use for database design and optimization. Covers schema design, primary key selection (UUID/ULID/serial), safe migrations, query optimization with EXPLAIN ANALYZE, indexing strategies (B-tree, partial, composite), and normalization tradeoffs. Activate when working with SQL files, migrations, or database schema decisions.

- Author: Levi Figueira
- Repository: levifig/loaf
- Version: 20260124004017
- Stars: 1
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/levifig/loaf
- Web: https://mule.run/skillshub/@@levifig/loaf~database:20260124004017

---

---
name: database
description: >-
  Use for database design and optimization. Covers schema design, primary key
  selection (UUID/ULID/serial), safe migrations, query optimization with EXPLAIN
  ANALYZE, indexing strategies (B-tree, partial, composite), and normalization
  tradeoffs. Activate when working with SQL files, migrations, or database
  schema decisions.
agent: backend-dev
allowed-tools: 'Read, Write, Edit, Glob, Grep, Bash(psql:*, sqlite3:*, mysql:*)'
---

# 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

| File | Use When |
|------|----------|
| [schema-design.md](reference/schema-design.md) | Creating tables, choosing keys, audit patterns |
| [migrations.md](reference/migrations.md) | Writing safe, reversible migrations |
| [query-optimization.md](reference/query-optimization.md) | Debugging slow queries, N+1 detection |
| [indexing.md](reference/indexing.md) | Choosing index types, composite index order |

## Quick Reference

### Primary Key Selection

| Type | Use When | Avoid When |
|------|----------|------------|
| UUID v4 | Distributed systems, no sequential exposure | Need sortability, space-constrained |
| ULID | Need sortability + uniqueness | Legacy system compatibility |
| Serial/Identity | Single database, simple use case | Distributed writes, ID exposure concerns |

### Migration Safety

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

### Index Decision Tree

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