Database Design#

Design efficient, scalable database schemas.

Normalization#

Normal Form	Rule	Violation Example
1NF	Atomic columns, no repeating groups	phone_numbers: "555-0100,555-0200"
2NF	1NF + all non-key cols depend on full PK	Orders table with product details
3NF	2NF + no transitive dependencies	Employee with department_name (dept info in dept table)

When to denormalize: Read-heavy workloads, reporting, caching layers.

Indexing Strategy#

Index Types#

Index	Use Case	Tradeoff
B-Tree	General purpose, equality + range	Default, usually best
Hash	Equality only	Fast lookups, no sorting
GIN	Array/JSON	Slightly slower writes
BRIN	Huge sorted tables	Very small size

Rules#

• Index columns used in WHERE, JOIN, ORDER BY
• Index foreign keys
• Covering indexes for frequent queries
• Don't over-index (slows writes, uses space)
• Drop unused indexes (use pg_stat_user_indexes)

Migrations#

• One file per change, sequential naming
• Always reversible (up/down pair)
• Test on staging before production
• Run in transactions where possible
• Avoid locking tables on large tables (use pt-online-schema-change)

Query Optimization#

• EXPLAIN ANALYZE every slow query
• Look for: Seq scans, nested loops, temp files
• Common fixes: missing index, bad join order, OR conditions
• N+1 problem: batch find / includes / joins
• Limit offsets for pagination (use cursor-based)