Databases — what every backend interview assumes
📖 Walk me through it — plain English
A database is just an organized place to store data and ask questions about it. A backend (the server code behind an app) leans on it constantly, so interviewers assume you know four things: how to make lookups fast (indexes), what guarantees you get when you change data (ACID), how much two simultaneous users can step on each other (isolation levels), and a few SQL query patterns. SQL ("Structured Query Language") is the language you type to read and write the data. Let's take these one at a time.
Indexes — the textbook-index analogy. Imagine a 900-page book and you want every mention of "photosynthesis." Without the index at the back, you read all 900 pages (slow). With it, you jump to "P", find the word, and it tells you the exact pages. A database index is exactly that: a separate, pre-sorted lookup structure that points to the rows you want, so the engine doesn't scan the whole table. The default kind is a B-tree — picture the book's index always kept in alphabetical order, so finding an entry takes only a handful of steps even in a huge book. That "handful of steps even when the data is enormous" is what O(log n) means: double the data and you add only one more step, not double the work.
Because the values stay sorted, a B-tree also makes range scans cheap (e.g. "all salaries between 50k and 80k" — find the start, then read forward). A hash index is faster for exact matches (O(1), meaning one constant step) but stores values in a scrambled order, so it can't do ranges at all. A composite index on (a, b) is sorted by a first, then b within each a — like a phone book sorted by last name then first name. It helps queries that filter on a (or a and b), but is useless for b alone, just as a phone book can't help you find everyone named "John" regardless of last name. A covering index already contains every column the query asks for, so the engine answers from the index and never opens the actual row. The catch (last line of that box): every index must be updated on each insert/update, so indexes speed up reads but slow down writes — don't add ones you won't use.
ACID — what a "transaction" promises. A transaction is a bundle of changes you want treated as one unit (classic example: move $100 from account X to account Y — two updates that must happen together). ACID is four guarantees. Atomicity: all-or-nothing — if the second update fails, the first is undone, so money never vanishes. Consistency: the change can't break the rules you've declared (e.g. a balance can't go negative if you forbade it). Isolation: two transactions running at the same time don't see each other's half-finished work. Durability: once the database says "committed" (saved), a power loss won't lose it. Memory hook: a bank transfer needs all four, which is why banks use ACID databases.
Isolation levels — a dial for that "I" guarantee. Perfect isolation is expensive, so databases let you pick how strict to be. The table lists three bad things that can happen when two users overlap. A dirty read = you read data another transaction wrote but hasn't committed, and it might get undone (you read a lie). A non-repeatable read = you read a row twice in one transaction and the value changed underneath you. A phantom read = you run the same "count the matching rows" query twice and new rows appeared. Reading the table top to bottom, each level forbids more of these: Read Uncommitted allows all three, and Serializable allows none — it behaves as if transactions ran one-at-a-time in a single line. Stricter is safer but slower (more waiting), which is why most real apps sit in the middle at Read Committed.
The SQL patterns. "Top N per group" answers things like "top 3 earners in each department." ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) is a window function: PARTITION BY dept splits rows into per-department piles, ORDER BY salary DESC sorts each pile high-to-low, and ROW_NUMBER() stamps 1, 2, 3, … down each pile; the outer query then keeps only ranks <= 3. "Latest record per user" uses a lateral join — a join where the inner query is allowed to reference the outer row (u.id), so for each user it grabs just their single newest message. The last snippet is a common gotcha: COUNT(*) counts every row, but COUNT(coupon) silently skips rows where coupon is NULL (NULL means "no value / unknown"), so the two numbers can differ — interviewers love asking why.
- B-tree (default in SQL): O(log n) lookups, range scans cheap. Covers most cases.
- Hash index: O(1) exact match, no ranges.
- Composite (multi-column):
(a, b)helpsWHERE a = ?andWHERE a = ? AND b = ?, NOTWHERE b = ?alone. - Covering index: includes all queried columns → engine never touches the row data.
Indexes cost write performance — every insert/update maintains every index. Don't over-index.
- A — Atomicity: transaction is all-or-nothing.
- C — Consistency: transactions respect constraints (FKs, checks).
- I — Isolation: concurrent txns don't observe each other's in-progress state.
- D — Durability: committed data survives crashes.
| Level | Allows |
|---|---|
| Read Uncommitted | Dirty reads, non-repeatable reads, phantom reads |
| Read Committed | Non-repeatable reads, phantom reads |
| Repeatable Read | Phantom reads only |
| Serializable | Nothing — txns appear to run one at a time |
Most production OLTP runs Read Committed. Repeatable Read for analytical reports. Serializable rarely (slow).
-- Top N per group (window function)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
) WHERE rn <= 3;
-- Latest record per user
SELECT u.id, m.body FROM users u
JOIN LATERAL (
SELECT body FROM messages
WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) m ON true;
-- Count + filter — beware NULLs in COUNT
SELECT COUNT(*) FROM orders; -- counts every row
SELECT COUNT(coupon) FROM orders; -- skips NULLs