Databases
SQL vs NoSQL, ACID, indexing
The problem
Data has to live somewhere durable, and the choice of home shapes everything: your data model, your queries, how you scale, and what guarantees you get. Picking a database that doesn’t match your access pattern is one of the most expensive mistakes in system design — and "which store, and why?" is the single most common database moment in interviews.
Cache is a job, Redis is a worker
A cache is a role — a disposable fast copy of data whose real home is elsewhere. Redis is a technology — an in-memory store that famously plays that role, but can also be a primary store with persistence turned on. Same worker, different jobs. The question is never "is X a cache?" but "what role is X playing here, and is it configured for it?"
SQL and ACID
Relational databases (PostgreSQL, MySQL) store structured tables whose records reference each other, queryable with joins — and run on the ACID guarantees: Atomicity (grouped writes succeed or fail together — a crash mid-payment rolls back cleanly), Consistency (data never violates its own rules), Isolation (concurrent transactions queue instead of trampling — two buyers can’t both get the last ticket), Durability (committed means crash-proof). That’s why money lives in SQL.
NoSQL — four flavors, four shapes
Key-value (Redis, DynamoDB): key → value, blazing fast, no complex queries. Document (MongoDB): key → self-contained JSON blob, flexible schema — profiles, product catalogs. Wide-column (Cassandra): built to swallow enormous write volume — logs, metrics, time-series; queries are rigid by design. Graph (Neo4j): nodes and edges, for when relationships are the question — friends-of-friends. Each flavor is one access pattern done extremely well.
Indexing — reads jump, writes pay
A table is an unordered heap; finding one email means scanning all 50M rows. An index is a separate sorted structure mapping value → row location, and sorted means you can binary-search: ~26 hops instead of 50M reads. The default structure is a B-tree (handles ranges too); a hash index is O(1) but exact-match only. The bill: every write must also update every index — eight indexes turn one INSERT into nine writes. Faster reads, slower writes; every index must earn its keep.
When things break
Primary dies → no writes until a replica is promoted (automated failover). Replication lag → users miss their own writes (fix: read-your-writes routing). Hot shard → one partition drowns (better shard key, composite keys). The recurring theme: copies are fast, copies go stale — DNS, caches, and replicas are the same trade-off at three layers.