Databases

A database is an organized collection of structured information held by a database management system (DBMS). The DBMS supplies what an application cannot easily build for itself: durability across a crash, concurrent access without corruption, indexes that turn a full scan into a lookup, and a query language. The data, the DBMS, and the applications around them are together called a database system, usually shortened to "database". It is also the hardest component to change later: servers redeploy in minutes, data has to be migrated.

What the engine does between your query and the disk

A write does not go straight to the table file. The engine appends the change to a write-ahead log, calls fsync so the log record survives a power cut, and only then acknowledges the commit. The table pages themselves are modified in an in-memory buffer pool and flushed later, in the background. That ordering is the whole of durability: if the process dies, recovery replays the log against the data files.

Inside a relational database: buffer pool, write-ahead log, disk, and a lagging replica100%−+FitSVGEsc

Scroll to zoom · drag to pan · 0 fits · Esc closes

Two consequences follow. A read served from the buffer pool costs a main-memory reference (~100 ns) plus the datacenter round trip (~0.5 ms); a read that misses adds an SSD random read (~100 µs), so the ratio of working set to RAM matters more than disk speed. And the follower is always behind the leader by however long the log takes to ship and replay.

That lag is where replica reads go wrong. A user updates their profile, the write commits on the leader, the next page load hits a follower 200 ms behind, and the user sees the old name. Read-your-writes is broken and the user believes the save failed. The fix is to pin reads to the leader for a short window after a user's own write, not to add replicas. Consistency models names the guarantees precisely.

Relational databases

Data is organized into tables of rows (records) and columns (fields), relationships are declared with foreign keys, and SQL both queries and manipulates it. The engine enforces ACID: atomicity, consistency, isolation, durability. Examples are PostgreSQL, MySQL, Oracle, SQL Server, and SQLite. The property worth paying for is not the tables but the transaction: five rows across three tables either land whole or not at all, and the database, not your code, guarantees it.

Note that the C in ACID and the C in CAP are unrelated. ACID-C means a transaction leaves the database's invariants intact (no negative balances, no orphaned foreign keys). CAP-C means every read observes the most recent write. A database can be fully ACID and still serve stale reads from a follower.

NoSQL families

A broad category outside the relational model, trading joins and cross-row transactions for horizontal scale and a looser schema.

• Key-value stores hold data as key-value pairs — Redis, Memcached, DynamoDB. Very fast for simple lookups by a known key, useless for "find all rows where…". This is also the shape most caches take.
• Document stores hold JSON-like documents — MongoDB, Couchbase. Suited to semi-structured data where each record carries its own shape.
• Wide-column stores spread rows across a partition key and clustering columns — Cassandra, HBase. Built for very high write rates and predictable single-partition reads. True columnar analytics engines (ClickHouse, BigQuery) are a different family that stores each column contiguously, so an aggregate reads one column.
• Graph databases store nodes and edges — Neo4j, Neptune. The win is traversal: "friends of friends of friends" is three hops, not three self-joins.

These systems are often described as BASE — basically available, soft state, eventually consistent. SQL versus NoSQL works the choice through.

Do the arithmetic before you choose

Take an e-commerce site with 1 million daily active users making 20 requests each:

1,000,000 DAU × 20 req/day ÷ 86,400 s ≈ 230 QPS average
peak at 3× average           ≈ 700 QPS

A single commodity PostgreSQL box handles roughly 5,000 simple QPS, so one leader has about 7× headroom, before any caching or read replicas. On storage: at 1 KB per order row and 1 million orders a day, that is 1 GB/day, about 365 GB/year — one disk for years.

So the rejected alternative is Cassandra. Choosing it here buys write throughput you will not need for several years, and the price is paid immediately: no joins, no multi-table transactions, and every query pattern has to be known before the table is designed. Reach for it when the arithmetic says one leader cannot hold the write rate or the dataset — where scaling a database starts.

The failure that shows up at 700 QPS is not the storage engine but the connection pool. By Little's Law, concurrency = arrival rate × latency: 700 QPS × 20 ms = 14 connections in flight. Let one query degrade to 200 ms because an index was dropped, and the same traffic needs 700 QPS × 0.2 s = 140 connections. A pool of 100 saturates, requests queue, latency climbs, and the outage looks like "the database is down" when the database is merely slower than the pool was sized for.

In an interview

The interviewer is testing whether you can match a data model to constraints and defend it against the alternative you did not pick. Say the estimate out loud before naming a product: QPS, row size, total volume, and read/write ratio. Then name the store and the one property you are buying.

Follow-ups probe these, in order:

• Data model. Relational, document, key-value, wide-column, or graph — driven by access patterns, not familiarity.
• Consistency versus availability. State it as CAP does: when a network partition occurs, you choose consistency or availability, and partitions are not optional. Extend with PACELC: Else, with no partition, you still trade latency against consistency — the follower-read decision above.
• Scalability. How the store handles more data and more traffic: replicas, then partitioning.
• Security. Protecting data from unauthorized access and modification, at rest and in transit.
• Backup and recovery. An untested restore is not a backup. Give a target: restore a 365 GB dataset inside an hour.

That e-commerce site keeps customers, products, and orders in MySQL, because an order that debits stock and charges a card needs one atomic transaction. A social platform keeps posts and the follow graph in Cassandra, because the write rate is enormous and a post arriving a second late for some readers costs nothing.

The mistake that loses points is naming a technology before naming a number — "I'd use MongoDB because it scales" invites the question you cannot answer. The second mistake is describing CAP as picking two of three; there is no useful system that forgoes partition tolerance, so the only live choice is what to do while partitioned.

Check yourself

A feed service takes 5,000 writes/second at 500 bytes each. Does a single PostgreSQL leader hold it, and what is the yearly storage?

5,000 writes/s is at the ceiling of one commodity box (~5,000 simple QPS) with no headroom for reads, so no — this needs partitioning or a wide-column store. Storage: 5,000 × 500 B = 2.5 MB/s, ×86,400 ≈ 216 GB/day, roughly 79 TB/year. The storage number alone rules out one machine.

Your p99 read latency doubles from 20 ms to 40 ms at a steady 1,000 QPS. Your pool is capped at 50 connections. Are you about to queue?

Little's Law: 1,000 × 0.04 = 40 concurrent, under 50, so not yet — but that is 80% of the pool, with a factor of 1.25 left. Alarm on pool utilisation, not on latency alone.

You add two read replicas and route all GET traffic to them. A user reports that editing their display name "doesn't save". What broke, and what is the smallest fix?

Replication lag broke read-your-writes: the write committed on the leader, the subsequent read hit a follower that had not applied it. The smallest fix is to route that user's reads to the leader for a few seconds after their write, rather than adding replicas or making replication synchronous.