Database Patterns Skill

Purpose

Best practices for database design, migrations, queries, and optimization.

Auto-Invoke Triggers

• •Designing database schema
• •Creating migrations
• •Writing complex queries
• •Optimizing database performance

Schema Design Principles

Naming Conventions

Column Rules

• •Always define NOT NULL unless nullable needed
• •Set explicit max lengths for strings
• •Use appropriate data types (don't over-size)
• •Add default values where sensible
• •Include CreatedAt, UpdatedAt on all tables

Data Types

Recommended Types

Avoid

• •FLOAT for money (use DECIMAL)
• •CHAR (use VARCHAR)
• •Oversized types (VARCHAR(MAX) when 255 is enough)

Relationships

Types

Foreign Key Rules

• •Always create FK constraints
• •Define ON DELETE behavior explicitly
• •Index foreign key columns
• •Use CASCADE sparingly (prefer RESTRICT)

ON DELETE Options

Indexing Strategy

When to Index

• •Primary keys (automatic)
• •Foreign keys
• •Columns in WHERE clauses
• •Columns in ORDER BY
• •Columns in JOIN conditions
• •Unique constraints

When NOT to Index

• •Low cardinality columns (boolean, status)
• •Rarely queried columns
• •Frequently updated columns
• •Small tables (< 1000 rows)

Index Types

Composite Index Rules

• •Order columns by selectivity (high first)
• •Leftmost prefix is usable alone
• •Max 3-4 columns typically

Migration Best Practices

Rules

• •One logical change per migration
• •Always reversible (include down migration)
• •Test on copy of production data
• •Never modify released migrations
• •Keep migrations idempotent

Safe Operations

• •Add nullable column
• •Add index (CONCURRENTLY in PostgreSQL)
• •Add table
• •Add constraint (with validation)

Dangerous Operations

• •Drop column (data loss)
• •Rename column (breaks app)
• •Change column type (may fail)
• •Drop table (data loss)

Migration Naming

{timestamp}_{action}_{description}
20240115120000_add_users_email_index
20240116090000_create_orders_table
20240117150000_add_status_to_orders

Query Optimization

Performance Rules

• •SELECT only needed columns (no SELECT *)
• •Use indexes (check EXPLAIN)
• •Avoid N+1 queries
• •Paginate large results
• •Use connection pooling

N+1 Problem

# BAD: 1 query for posts, N queries for authors
posts = get_all_posts()
for post in posts:
    author = get_author(post.author_id)  # N queries!

# GOOD: 1 query with JOIN or eager loading
posts = get_posts_with_authors()  # 1 query

Pagination

• •Use LIMIT/OFFSET for simple cases
• •Use cursor-based for large datasets
• •Always have ORDER BY with pagination
• •Consider total count cost

Transaction Guidelines

ACID Properties

• •Atomicity - All or nothing
• •Consistency - Valid state to valid state
• •Isolation - Concurrent transactions isolated
• •Durability - Committed data persists

Transaction Rules

• •Keep transactions short
• •Don't do I/O inside transactions
• •Handle deadlocks with retry
• •Use appropriate isolation level

Isolation Levels

Default: Read Committed (usually sufficient)

Connection Pooling

Settings

Rules

• •Always use pooling in production
• •Size pool based on: (cores * 2) + disk spindles
• •Monitor pool usage
• •Handle connection timeouts gracefully

Backup & Recovery

Backup Types

Recovery Checklist

• • Backups tested regularly
• • Recovery time documented
• • Point-in-time recovery possible
• • Backups stored off-site
• • Retention policy defined

Best Practices

DO

• •Use migrations for all schema changes
• •Index foreign keys
• •Define explicit constraints
• •Use transactions for multi-step operations
• •Test queries with EXPLAIN
• •Use connection pooling

DON'T

• •Store files in database (use object storage)
• •Use SELECT *
• •Ignore slow query logs
• •Skip backups
• •Use ORM for complex reports (use raw SQL)
• •Store passwords without hashing