ACID Guarantees Have Real Costs
Atomicity, Consistency, Isolation, Durability require coordination and disk syncs. Each guarantee adds latency and reduces throughput. Know which guarantees you actually need—many workloads don't require serializable isolation.
Indexes Speed Reads but Slow Writes
Every index must be updated on every write. A table with 5 indexes does 5x the write work. Index what you query, remove what you don't. The right indexes can make queries 1000x faster; wrong indexes waste resources.
Replication is for Availability, Sharding is for Scale
Replication copies all data to multiple nodes (read scaling, failover). Sharding splits data across nodes (write scaling, storage scaling). Most systems need replication; fewer need sharding.
SQL vs NoSQL is About Data Model, Not Scale
Both can scale. SQL offers joins, transactions, and schema enforcement. NoSQL offers flexible schemas and specific access patterns (documents, key-value, wide-column). Choose based on your data relationships and query patterns.
Denormalization Trades Storage for Read Performance
Normalized data avoids duplication but requires joins. Denormalized data duplicates but enables single-table queries. At scale, denormalization often wins because joins across shards are expensive or impossible.
The N+1 Query Problem is Everywhere
Fetching a list then querying each item individually creates O(N) database calls. This is the most common performance bug. Solutions: eager loading (JOINs), batch queries, or DataLoader patterns.
SystemExperts