Relational databases (SQL)
Defend a relational store with arithmetic — index depth, join cost, isolation level — and name the write pattern that oversells the last unit of stock.
A relational database stores data in tables (relations) built from rows (records or tuples) and columns (attributes), with relationships between tables declared using primary keys and foreign keys. It is queried with SQL (Structured Query Language), a standardised language for defining, manipulating and querying data, with minor variations between implementations. What you are actually buying is not the tables: it is a transaction that spans several of them and either lands whole or not at all, enforced by the engine rather than by your code. Examples are PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, and SQLite for mobile and embedded use.
The schema is a contract the engine enforces
Relational stores suit data with a predefined schema — the tables, columns, data types, and constraints declared upfront. Normalization is the discipline of splitting that data into multiple related tables so each fact is stored once, which removes redundancy and protects integrity. A product name lives in products; order_items references it by id. Renaming a product is one UPDATE.
The rejected alternative is to denormalize immediately — copy the product name into every order line so a page render needs no join. At 1 million orders a day and three lines each, a year of history is roughly 1 billion rows, so that rename stops being an UPDATE and becomes a batch job with a consistency window in the middle of it. Denormalize when a measured read is too slow, not in advance.
The exception, which interviewers do probe: copy the price into the order line while still referencing the product row. A price at purchase time is a historical fact, not a redundant copy, and it must not move when the catalogue does.
ACID, and what each letter costs
- Atomicity: transactions are all-or-nothing. The engine can undo a half-finished transaction because it wrote the change to a log first.
- Consistency: a transaction moves the database from one valid state to another — foreign keys, unique constraints and check constraints all hold at commit.
- Isolation: concurrent transactions do not interfere with each other.
- Durability: a committed transaction survives a crash, because the log record was flushed with
fsyncbefore the commit was acknowledged.
The C in ACID and the C in CAP are unrelated. ACID-C means the transaction preserved the database's invariants; CAP-C means every read observes the most recent write. A fully ACID database still serves stale reads from a follower, and that is where replica reads break read-your-writes: a user saves their profile, the next page load hits a follower 200 ms behind, and the user believes the save failed. Under a partition a single-leader deployment either refuses writes (choosing consistency) or promotes a follower and risks divergence (choosing availability). Partitions are not optional, so that choice cannot be dodged. Consistency models names the guarantees precisely.
Isolation is a dial, and the default is not serializable
Isolation is the letter people assume they have and do not. PostgreSQL defaults to READ COMMITTED, which permits this:
Both transactions were isolated exactly as the level promises, and the shop still sold one unit twice. The bug is the read-modify-write done in application code: the decrement was computed from a value that went stale in a variable.
| Level | Still allows | What it costs |
|---|---|---|
| Read committed (Postgres default) | non-repeatable reads, phantoms, the lost update above | least locking, highest throughput |
| Repeatable read (snapshot isolation) | write skew across different rows | serialization failures your code must catch and retry |
| Serializable | nothing above | more retries under contention, lower throughput |
The smallest fix is not a higher isolation level. It is one atomic statement — UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty > 0 — treating zero rows affected as sold out, so the engine re-evaluates the condition under the lock. SELECT ... FOR UPDATE at read time works too, at the price of holding the lock longer. Distributed locking covers the case where the contended resource is not a single row.
Index arithmetic
Take a users table of 10 million rows at 1 KB each, so about 10 GB of heap. A lookup by email with no index scans all 10 GB; assume roughly 500 MB/s of sequential SSD throughput and that is about 20 seconds, per query.
Add a B-tree. At a fanout of around 100 keys per page the depth is 4 levels, because 100³ = 1 million is too few and 100⁴ = 100 million is enough. The upper levels stay resident in the buffer pool, so the real cost is one or two SSD random reads at ~100 µs plus the heap fetch — under a millisecond, against 20 seconds. That factor of roughly 20,000 is why "is there an index on that column" is the first question about any slow query.
Indexes are not free on the write side. Each one is maintained on every insert, so a table carrying four secondary indexes turns one row insert into five page updates and their log records. Adding an index to fix a read has a measurable write cost, not zero.
When one box is enough
Work the numbers before naming a product. 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. Storage at 1 KB per order row and 1 million orders a day is 1 GB/day, about 365 GB/year — one disk for years. Nothing here argues for sharding or for a NoSQL store.
The failure that does show up at 700 QPS is the connection pool, and transactions are what fill it. By Little's Law, concurrency = arrival rate × latency: 700 QPS × 20 ms = 14 connections in flight. Open a transaction, call a payment provider inside it, and the transaction now lasts 200 ms — 700 × 0.2 s = 140 connections, so a pool of 100 saturates, requests queue, and the outage reads as "the database is down" while the database is idle and holding locks. Never hold a transaction open across a network call.
The genuine limits are the ones arithmetic finds: a write rate one leader cannot absorb, a dataset one disk cannot hold, or a schema so heterogeneous that half the columns are null. SQL versus NoSQL works that choice through. Until then the relational fit is the common one — transactional systems such as financial and order processing, strong consistency requirements, complex and well-defined relationships, and reporting built on relational warehouses.
In an interview
Understand the core concepts of tables, rows, columns, keys, and ACID. Know when a relational database is a suitable choice: structured data, and consistency requirements the engine can enforce for you.
The interviewer is testing whether you can defend the default. Relational is the right answer more often than candidates expect, and defending it with a number beats reaching for something distributed. State the estimate first — QPS, row size, yearly volume, read/write ratio — then name the store and the property you are buying, usually the multi-table transaction.
Three mistakes lose points. Saying "SQL does not scale" when one box absorbs 5,000 QPS and the design needs 700. Treating ACID as a promise that every read sees the latest write, which forgets both follower lag and the ACID-C versus CAP-C distinction. And describing CAP as picking two of three — partition tolerance is not optional, so the only live question is what the system does while partitioned.
Check yourself
A users table holds 50 million rows at 1 KB each and a hot query filters on email, unindexed. Estimate the cost before and after adding a B-tree, and name the cost of adding it.
Before: 50M × 1 KB = 50 GB scanned per query; at ~500 MB/s that is about 100 seconds. After: fanout 100 gives depth 4 (100⁴ = 100 million ≥ 50 million), upper levels cached, so one or two SSD random reads at ~100 µs plus the heap fetch — under a millisecond. The cost lands on writes: every insert, delete, and update of
Checkout reads stock, subtracts one in application code, and writes the result, then inserts the order. Two customers hit the last unit within the same millisecond at READ COMMITTED. What happens, and what is the smallest fix?
The second write overwrites the first with a value computed before it committed — a lost update, and one unit sells twice. The smallest fix is a single atomic statement,
UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty > 0, treating zero rows affected as sold out. Serializable also works, at the price of retries you do not need here.
A team wants to migrate to a wide-column store because "SQL does not scale". The product has 2 million daily active users making 15 requests each. What do you tell them?
2,000,000 × 15 = 30 million requests/day ÷ 86,400 s ≈ 350 QPS average, roughly 1,050 QPS at 3× peak — about 5× under the ~5,000 QPS one commodity leader handles, before caching removes read traffic. The migration buys throughput that is years away and pays immediately in lost joins and lost multi-table transactions. Revisit when write rate or dataset genuinely exceeds one leader.