🗄️ Database Fundamentals

Choosing, designing, and querying databases effectively.

Database types

Type	Examples	Best for
Relational (SQL)	PostgreSQL, MySQL	Structured data, transactions, relations
Document	MongoDB, Firestore	Flexible schemas, nested data
Key-Value	Redis, DynamoDB	Caching, sessions, high throughput
Graph	Neo4j, ArangoDB	Relationships-heavy data
Time-series	InfluxDB, TimescaleDB	Metrics, IoT, logs
Vector	Pinecone, pgvector	Embeddings, similarity search

Schema design principles

• Normalize to reduce duplication (3NF for OLTP)
• Denormalize for read performance when needed
• Every table needs a primary key
• Use foreign keys for referential integrity
• Add indexes on frequently queried columns
• Use appropriate data types (don't store dates as strings)

Index guidelines

• Index columns used in WHERE, JOIN, ORDER BY
• Composite indexes: column order matters (leftmost prefix)
• Don't over-index — each index slows writes
• Monitor slow queries to find missing indexes
• Use EXPLAIN / EXPLAIN ANALYZE to check query plans

Common query pitfalls

Pitfall	Problem	Fix
N+1 queries	1 query + N sub-queries	Use JOIN or eager loading
SELECT *	Fetches unnecessary data	Select only needed columns
Missing index	Full table scan	Add appropriate index
No pagination	Returns all rows	Use LIMIT + OFFSET or cursor
String matching	LIKE '%term%' can't use index	Full-text search or trigram index

Migrations

• Always use migration files, never manual SQL in production
• Migrations must be reversible (up + down)
• Test migrations on a copy of production data
• Deploy migrations separately from application code
• Never delete data in a migration without a backup plan