pact-database-patterns

Schema design starts with normalization (1NF through BCNF) to eliminate redundancy and ensure data integrity, then selectively denormalizes for performance where query patterns demand it. This layered approach lets your schema evolve safely as requirements change.

Query optimization combines strategic indexing, understanding access patterns, and writing efficient queries. Index the columns used in WHERE, JOIN, and ORDER BY clauses first, then analyze execution plans to spot full table scans or missing indexes.

Safe migrations use versioning and backward-compatible steps: add new columns before dropping old ones, test migrations on replicas first, and coordinate with application deployments. This prevents data loss and minimizes downtime.

Denormalize when normalized queries consistently hit performance limits—typically for aggregations, reports, or read-heavy analytics. Measure impact first with indexes and query rewrites; denormalization trades consistency complexity for query speed.

Data integrity uses constraints (foreign keys, unique, check), transactions for multi-table operations, and validation rules. Foreign keys prevent orphaned records; transactions ensure related updates succeed or fail together.

At scale, indexes reduce full table scans but add write overhead. Prioritize indexes on frequently filtered, joined, or sorted columns; avoid indexing low-cardinality fields. Monitor index usage and remove unused ones to balance read and write performance.