Database Patterns Skill
Reference for schema design, migrations, and query optimization.
WHEN_TO_USE
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.
Schema Design Principles
Naming
- Tables: plural, snake_case (
users, order_items).
- Columns: singular, snake_case (
email, created_at).
- Primary keys:
id (auto-increment or UUID).
- Foreign keys:
<singular_table>_id (user_id, order_id).
- Timestamps:
created_at, updated_at, deleted_at (for soft delete).
- Booleans: prefix with
is_ or has_ (is_active, has_verified_email).
Data Types
- Use the most specific type available (e.g.,
timestamptz not varchar for dates).
- Use
uuid for public-facing IDs, bigint for internal IDs.
- Use
text over varchar(n) unless a hard length limit is required.
- Store monetary values as
integer (cents) or numeric(12,2). Never float.
Relationships
- Always define foreign key constraints.
- Use junction tables for many-to-many relationships.
- Add
ON DELETE behavior explicitly (CASCADE, SET NULL, RESTRICT).
Migration Conventions
File Naming
YYYYMMDDHHMMSS_description.sql
Example: 20260215120000_add_user_email_index.sql
Structure
-- 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;
Rules
- One logical change per migration file.
- Every migration must be reversible (include down script).
- Never modify a migration that has been applied to production.
- Test migrations on a copy of production data before deploying.
Indexing Strategies
When to Index
- Columns used in WHERE clauses.
- Columns used in JOIN conditions.
- Columns used in ORDER BY.
- Foreign key columns.
When NOT to Index
- Tables with < 1000 rows (full scan is faster).
- Columns with very low cardinality (e.g., boolean with 50/50 distribution).
- Columns that are rarely queried.
Index Types
| 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) |
Query Optimization
Common Anti-Patterns
| 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 Analysis
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
Look for:
- Seq Scan on large tables — add an index.
- Nested Loop with large outer table — consider hash join.
- Sort without index — add index on sort column.
- High actual rows vs estimated rows — run ANALYZE to update statistics.
1---2name: database-patterns3description: Database design patterns: schema design, migration conventions, indexing strategies, and query optimization. Use when designing schemas, writing migrations, or optimizing queries.4---56# Database Patterns Skill78Reference for schema design, migrations, and query optimization.910## WHEN_TO_USE1112Apply 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.1314## Schema Design Principles1516### Naming17- Tables: plural, snake_case (`users`, `order_items`).18- Columns: singular, snake_case (`email`, `created_at`).19- Primary keys: `id` (auto-increment or UUID).20- Foreign keys: `<singular_table>_id` (`user_id`, `order_id`).21- Timestamps: `created_at`, `updated_at`, `deleted_at` (for soft delete).22- Booleans: prefix with `is_` or `has_` (`is_active`, `has_verified_email`).2324### Data Types25- Use the most specific type available (e.g., `timestamptz` not `varchar` for dates).26- Use `uuid` for public-facing IDs, `bigint` for internal IDs.27- Use `text` over `varchar(n)` unless a hard length limit is required.28- Store monetary values as `integer` (cents) or `numeric(12,2)`. Never `float`.2930### Relationships31- Always define foreign key constraints.32- Use junction tables for many-to-many relationships.33- Add `ON DELETE` behavior explicitly (CASCADE, SET NULL, RESTRICT).3435## Migration Conventions3637### File Naming38```39YYYYMMDDHHMMSS_description.sql40```41Example: `20260215120000_add_user_email_index.sql`4243### Structure44```sql45-- Up migration46ALTER TABLE users ADD COLUMN phone VARCHAR(20);47CREATE INDEX idx_users_phone ON users(phone);4849-- Down migration50DROP INDEX IF EXISTS idx_users_phone;51ALTER TABLE users DROP COLUMN IF EXISTS phone;52```5354### Rules55- One logical change per migration file.56- Every migration must be reversible (include down script).57- Never modify a migration that has been applied to production.58- Test migrations on a copy of production data before deploying.5960## Indexing Strategies6162### When to Index63- Columns used in WHERE clauses.64- Columns used in JOIN conditions.65- Columns used in ORDER BY.66- Foreign key columns.6768### When NOT to Index69- Tables with < 1000 rows (full scan is faster).70- Columns with very low cardinality (e.g., boolean with 50/50 distribution).71- Columns that are rarely queried.7273### Index Types74| Type | Use Case |75|------|----------|76| B-tree (default) | Equality, range queries, sorting |77| Hash | Equality only (faster than B-tree for exact match) |78| GIN | Full-text search, JSONB, array contains |79| GiST | Geospatial, range types |80| Partial | Subset of rows (`WHERE is_active = true`) |81| Composite | Multi-column queries (leftmost prefix rule applies) |8283## Query Optimization8485### Common Anti-Patterns86| Problem | Fix |87|---------|-----|88| SELECT * | Select only needed columns |89| Query inside loop (N+1) | JOIN or batch query |90| Missing LIMIT on large tables | Add LIMIT and pagination |91| String matching with leading wildcard (`LIKE '%term'`) | Use full-text search index |92| Sorting without index | Add index on ORDER BY column |93| Implicit type casting in WHERE | Use matching types |9495### EXPLAIN Analysis96```sql97EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';98```99100Look for:101- **Seq Scan** on large tables — add an index.102- **Nested Loop** with large outer table — consider hash join.103- **Sort** without index — add index on sort column.104- High **actual rows** vs **estimated rows** — run ANALYZE to update statistics.
zebbern