Database design patterns: schema design, migration conventions, indexing strategies, and query optimization. Use when designing schemas, writing migrations, or optimizing queries.
Reference for schema design, migrations, and query optimization.
Apply this skill when designing database schemas, writing migration files, adding indexes, or optimizing slow queries. Use the naming conventions and indexing strategies as a checklist before finalizing schema changes.
users, order_items).email, created_at).id (auto-increment or UUID).<singular_table>_id (user_id, order_id).created_at, updated_at, deleted_at (for soft delete).is_has_is_activehas_verified_emailtimestamptz not varchar for dates).uuid for public-facing IDs, bigint for internal IDs.text over varchar(n) unless a hard length limit is required.integer (cents) or numeric(12,2). Never float.ON DELETE behavior explicitly (CASCADE, SET NULL, RESTRICT).YYYYMMDDHHMMSS_description.sql
Example: 20260215120000_add_user_email_index.sql
-- Up migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
-- Down migration
DROP INDEX IF EXISTS idx_users_phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;
| Type | Use Case |
|---|---|
| B-tree (default) | Equality, range queries, sorting |
| Hash | Equality only (faster than B-tree for exact match) |
| GIN | Full-text search, JSONB, array contains |
| GiST | Geospatial, range types |
| Partial | Subset of rows (WHERE is_active = true) |
| Composite | Multi-column queries (leftmost prefix rule applies) |
| Problem | Fix |
|---|---|
| SELECT * | Select only needed columns |
| Query inside loop (N+1) | JOIN or batch query |
| Missing LIMIT on large tables | Add LIMIT and pagination |
String matching with leading wildcard (LIKE '%term') | Use full-text search index |
| Sorting without index | Add index on ORDER BY column |
| Implicit type casting in WHERE | Use matching types |
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
Look for: