Pick the database by access pattern, not hype. Relational DBs excel at joins, transactions, and ad-hoc queries with strong schema enforcement. NoSQL stores trade some query flexibility for horizontal scale, flexible schema, or specialized data models.

• SQL when you need multi-row transactions, foreign keys, or complex joins
• NoSQL when access is key-based, write volume exceeds single-node SQL, or schema evolves rapidly
• Polyglot persistence: different services use different stores for their domain
• Managed services (RDS, Aurora, DynamoDB) reduce ops burden at scale

Normalization reduces redundancy by splitting data into related tables (1NF → 3NF). Each fact lives in one place, updates stay consistent, and storage is efficient. The cost is joins at read time — acceptable for OLTP with moderate read complexity.

1. Identify entities (User, Order, Product) and relationships (1:N, N:M)
2. Define primary keys — prefer surrogate keys (UUID, bigint) over natural keys
3. Model junction tables for many-to-many (user_roles, order_items)
4. Add constraints: NOT NULL, UNIQUE, FK with ON DELETE policy

Denormalization intentionally duplicates data to optimize reads. Read models (projections, materialized views, CQRS read side) precompute query results so hot paths avoid joins and aggregations at request time.

• Accept duplicated fields (user_name on every comment) to avoid join
• Use change data capture (CDC) to sync read replicas and search indexes
• Version read models so you can migrate schema without downtime
• Document staleness SLA: 'feed may lag 30 seconds behind post'

Partitioning splits data across nodes when one machine can't hold or serve it. Sharding is horizontal partitioning across independent database instances. The shard key determines data locality and query routing — a bad key creates hot shards and negates scale benefits.

user_id → hash(user_id) mod N → Shard k

Shard-0   Shard-1   Shard-2
  │         │         │
Repl-A    Repl-B    Repl-C  (read replicas)

• Shard key must be high-cardinality and evenly distributed
• Avoid monotonically increasing keys (auto-increment ID) on range shards
• Cross-shard queries (aggregations, joins) require scatter-gather — expensive
• Re-sharding: consistent hashing, virtual nodes, or dual-write migration

Replication copies data across nodes for availability, durability, and read scale. The replication topology and sync mode determine consistency guarantees, failover behavior, and write latency.

• Synchronous replication: zero data loss on failover; higher write latency
• Asynchronous replication: lower latency; possible data loss on primary crash
• Read replicas offload read traffic; replication lag causes stale reads
• Automatic failover with Raft/Paxos or managed service (Aurora, Cloud SQL HA)

Indexes are auxiliary data structures (B-tree, hash, GiST, inverted) that accelerate lookups at the cost of write overhead and storage. Query tuning aligns indexes, query shape, and execution plans with actual access patterns.

1. EXPLAIN ANALYZE the slow query — look for Seq Scan on large tables
2. Index columns in order of selectivity and query filter order
3. Avoid SELECT * when a covering index could serve the query
4. Don't over-index write-heavy tables — each index adds insert/update cost

Storage engines determine how data is written, stored, and read on disk. B-tree (PostgreSQL, MySQL InnoDB) optimizes in-place updates with read-optimized pages. LSM-tree (RocksDB, Cassandra, LevelDB) batches writes into sorted runs, excelling at write-heavy workloads.

• WAL (Write-Ahead Log): durability before in-memory apply
• Compaction in LSM merges SSTables — I/O spikes during compaction
• Bloom filters reduce disk reads for non-existent keys in LSM
• Page cache / buffer pool keeps hot B-tree pages in memory