Database Patterns
Forward-only migrations, explicit transactions, measured optimization.
Migrations
Forward-only. No rollbacks. Maintain backward compatibility:
-- Add nullable column (backward compatible)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Later: make required after backfill
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;Break large changes into smaller steps. Use feature flags during transitions.
Query Optimization
Always check execution plans before optimizing:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;Index based on actual query patterns:
-- Composite for common query
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
-- Partial for filtered queries
CREATE INDEX idx_orders_pending ON orders (status) WHERE status = 'pending';Monitor unused indexes. Remove if idx_scan < 100.
N+1 Prevention
Always eager load in loops:
# Good
users = User.query.options(joinedload(User.posts)).all()
# Bad (N+1)
users = User.query.all()
for user in users:
print(user.posts) # N queries!Transactions
Scope to single business operation. Keep short:
async with db.transaction():
order = await create_order(data)
await update_inventory(order.items)
# Commit on exit
# OUTSIDE transaction: send emails, call external APIs
await send_confirmation(order)Never hold transactions during external calls.
Connection Pooling
# Size based on measured peak concurrency
create_engine(
url,
pool_size=15, # Based on load testing
max_overflow=5, # Burst capacity
pool_timeout=30, # Fail fast
pool_recycle=3600, # Prevent stale connections
pool_pre_ping=True # Validate before use
)Monitor utilization. Alert at 80%.
Data Validation
Validate at boundaries, not just in database:
# Validate input before INSERT
validated = CreateUserSchema.parse(input)
if await email_exists(validated.email):
raise ValidationError("Email taken")
# Validate output after retrieval (detect corruption)
return UserOutputSchema.parse(row)Anti-Patterns
- Rollback migrations (use forward-only)
- Indexes without query pattern analysis
- N+1 queries in loops
- Long-running transactions with external calls
- Relying only on DB constraints for validation
- Default pool settings without measurement
References
- audit-logging.md - Immutable audit trails