database-design

Schema design balances normalization for data consistency with denormalization for query performance. Choose appropriate data types, establish clear primary and foreign keys, and plan for growth by avoiding redundancy while considering read patterns. This foundation prevents slow queries and migration headaches later.

Query optimization uses EXPLAIN ANALYZE to identify bottlenecks, eliminates N+1 query problems, and applies indexing strategies. For PostgreSQL, MySQL, and ORMs like SQLAlchemy and Django ORM, profiling reveals where queries spend time, then targeted indexes and query rewrites restore performance.

Indexing strategies—B-Tree for range queries, Hash for equality, GIN for complex types—accelerate reads on common query patterns. Composite indexes combine multiple columns efficiently. Balance index coverage against write overhead by targeting the queries that run most frequently and have the highest impact.

Reversible migrations apply schema changes incrementally while maintaining transactional integrity. PostgreSQL, MySQL, and ORM tools like SQLAlchemy and Django ORM support versioned migration files that roll forward and back. Plan migrations to avoid locking tables during peak usage.

Transactional databases prioritize normalization and ACID compliance to prevent conflicts and ensure consistency. Analytical databases denormalize and pre-aggregate data for fast reporting across large datasets. Design choices—data types, indexes, schema structure—differ based on whether the workload emphasizes writes or complex reads.

Denormalization trades data redundancy for query speed when normalized queries become bottlenecks. Common cases include storing aggregates, flattening hierarchies, or duplicating foreign key data to avoid joins. Measure with EXPLAIN ANALYZE before and after; denormalize only where profiling shows measurable gains.