Database

• SQL fundamentals
• Access to a relational database

Decision Tree

Database operation?
+- Schema change?
| +- New table/column? -> Migration (up + down)
| +- Rename/drop? -> Migration + verify no dependents
| - Index needed? -> CREATE INDEX CONCURRENTLY (avoid locks)
+- Query performance?
| +- Slow query? -> EXPLAIN ANALYZE -> add index or rewrite
| +- N+1 problem? -> Use eager loading / JOIN
| - Large result set? -> Pagination (cursor-based preferred)
+- Data integrity?
| +- Multiple writes? -> Use transaction
| - Concurrent access? -> Optimistic concurrency (version column)
- Connection management?
 - Always use connection pooling, never open/close per query

Performance Best Practices

Database Optimization Checklist

• Add indexes on foreign keys
• Add indexes on columns in WHERE/JOIN/ORDER BY
• Use composite indexes for multi-column queries
• Analyze query execution plans
• Fix N+1 queries with eager loading
• Use connection pooling
• Cache frequently accessed data
• Denormalize for read-heavy workloads
• Partition large tables
• Archive old data
• Monitor slow query logs

Query Analysis

Analyze Query Performance:

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- MySQL
EXPLAIN
SELECT * FROM users WHERE email = '[email protected]';

Look for:

• Seq Scan - Table scan (bad for large tables)
• Index Scan - Uses index (good)
• Nested Loop - Join method (can be slow)
• Hash Join - Better for large datasets

Core Rules

1. Always use migrations - Never modify production schemas by hand; every change gets a versioned migration with rollback support.
2. Parameterize all queries - Never concatenate user input into SQL strings; use parameterized queries or ORM bindings.
3. Index foreign keys and filter columns - Every foreign key and every column used in WHERE, JOIN, or ORDER BY should have an index.
4. Use connection pooling - Never open/close connections per request; configure a pool with sensible min/max sizes.
5. Keep transactions short - Acquire locks late, release early; never hold a transaction open across network calls.
6. Prefer cursor-based pagination - Offset pagination degrades on large tables; use keyset (cursor) pagination for stable performance.
7. Validate data at the boundary - Enforce NOT NULL, CHECK, and UNIQUE constraints in the schema, not only in application code.
8. Monitor slow queries - Enable slow-query logging and review EXPLAIN plans regularly in production.

Anti-Patterns

ORM Frameworks

Popular ORMs:

• Python: SQLAlchemy, Django ORM, Peewee
• Node.js: Sequelize, TypeORM, Prisma
• Java: Hibernate, JPA, MyBatis
• PHP: Doctrine, Eloquent (Laravel)
• Ruby: ActiveRecord (Rails)

Resources

Database Docs:

• PostgreSQL Documentation
• MySQL Documentation
• Amazon RDS Documentation

Tools:

• Query Optimization: EXPLAIN ANALYZE, query plan visualizers
• Monitoring: pg_stat_statements, slow query logs
• Migration Tools: Flyway, Liquibase, Alembic

See Also: Skills.md - AGENTS.md - PostgreSQL - MySQL/MariaDB

Last Updated: January 27, 2026

Scripts

Troubleshooting

References

• Migrations Indexing Queries
• Transactions Pooling Orm