lesson

Databases & Caching

SQL vs NoSQL, indexing, replication, Redis caching patterns.

SQL vs NoSQL — When to Use Each

SQL (Postgres, MySQL)	NoSQL (MongoDB, DynamoDB)
Schema	Fixed, relational	Flexible, document/key-value
Consistency	Strong (ACID)	Eventually consistent (tunable)
Joins	Native	Manual (application-level)
Scaling	Vertical (read replicas help)	Horizontal (sharding built-in)
Best for	Complex queries, transactions, reporting	High throughput, flexible schema, key-value access

Rule of thumb: start with SQL. Move to NoSQL only when you hit a specific limitation.

1. Check cache → hit? Return cached value.
Miss? Query database, store result in cache, return.
On write: invalidate cache key.

Application → Cache → Database (cache handles all reads/writes)

TTL (time-to-live): how long before cached data expires?

Invalidation: when data changes, how do you update the cache?

Thundering herd: what happens when cache expires and 1000 requests hit the DB simultaneously?

Solutions: cache warming, request coalescing (single-flight), stale-while-revalidate.

B-Tree (default): equality, range, ORDER BY

Hash: equality only (fast)

GIN: arrays, JSONB, full-text search

Composite index: index on (a, b) — useful for multi-column WHERE clauses

Golden rule: index columns that appear in WHERE, JOIN, ORDER BY. Don't over-index (slows writes).