Databases

Relational vs NoSQL, ACID, replication, and how to pick and scale a database for your access patterns.

Almost every design has a database at its center, and choosing one is usually the first real decision in your high-level design. Interviewers don't expect you to know every product. They want to see that you pick a database for the shape of your data and your access patterns, and that you know what the choice costs you.

Relational vs NoSQL

[!INTERVIEW] Default to Postgres "I'll use Postgres: the data is relational, we need transactions for bookings, and a single primary with read replicas easily handles a few thousand writes per second" is a strong default. Move off it only when you can name the reason: write volume beyond one node, a key-value access pattern at huge scale, or a schema that varies per record.

The main families

Relational (PostgreSQL, MySQL): the safe default. Strong consistency, rich queries, mature tooling. A single well-sized node goes further than most candidates think (see Numbers to Know).
Key-value (DynamoDB, Redis): look up a value by its key, very fast, at almost any scale. You give up ad hoc queries.
Document (MongoDB, Firestore): JSON-like records with flexible fields. Good when each record is read and written as a whole.
Wide-column (Cassandra, ScyllaDB, Bigtable): rows grouped by a partition key and sorted within it. Built for very high write throughput, such as time series, messages and activity logs.
Graph (Neo4j): relationships are first-class. Rarely needed in interviews; a relational table of edges is usually enough.
Specialized: time-series (TimescaleDB, InfluxDB), analytics/OLAP (ClickHouse, BigQuery), vector search (pgvector), full-text search (Elasticsearch — see Search Systems).

See PostgreSQL, DynamoDB and Cassandra for the details of each.

ACID, briefly

Transactions are what make relational databases the default for anything involving money or inventory:

Atomicity: all of a transaction's writes happen, or none do.
Consistency: constraints (unique keys, foreign keys, checks) hold after every transaction.
Isolation: concurrent transactions don't see each other's half-done work. Most databases default to *read committed*; *serializable* is the strongest and the slowest.
Durability: once committed, the data survives a crash. That guarantee comes from the write-ahead log.

Scaling a database

Work through these in order, and only take the next step when the current one runs out:

Index the queries you actually run (Database Indexing).
Scale up: a bigger machine. Hundreds of GB of RAM and fast NVMe are ordinary.
Cache hot reads (Caching).
Read replicas: copies of the primary that serve reads. They lag behind slightly, so read-your-own-writes needs care.
Shard: split data across primaries by a partition key (Sharding).

app = service "App Servers" @0,1
cache = cache "Redis" @1,0
primary = store "Primary" @1,1
r1 = store "Replica" @2,0.5
r2 = store "Replica" @2,1.5
app -> cache : hot reads
app -> primary : writes
app --> r1, r2 : reads
primary -> r1, r2 : replication

Replication

Leader-follower (single leader): one node takes writes and streams them to followers. Simple and the most common.
Multi-leader: several nodes accept writes, typically one per region. Writes to the same record can conflict and must be merged.
Leaderless (Dynamo-style, Cassandra): any replica takes writes, and quorums (R + W > N) give consistent reads.

Synchronous replication waits for a replica before confirming a write: no data loss on failover, but slower. Asynchronous replication confirms right away: faster, but the last few writes can be lost if the primary dies. Many systems compromise with one synchronous replica and the rest asynchronous. How this trades off against availability is the subject of CAP Theorem.

Choosing in an interview

[!WARNING] Don't pick NoSQL for "scale" alone "We'll use Cassandra because it scales" without a write-volume estimate sounds like a buzzword. Do the math first: if the numbers fit on one Postgres primary, say so and move on.