Every non-trivial system stores state, and the database is usually the hardest part to scale and the easiest to get subtly wrong. This lesson covers the beginner-critical trio: how indexes work, what a transaction guarantees, and what ACID and isolation levels actually mean.
How a B-tree index works
Imagine finding a name in a phone book with no ordering — you would scan every page (a full table scan, O(n)). A B-tree index keeps keys sorted in a shallow, balanced tree, so the database eliminates a large fraction of the rows at each node and finds a match in roughly O(log n) steps — a handful of hops even over billions of rows.
That is why an indexed lookup (WHERE email = 'a@b.com') is milliseconds, while an unindexed one grows with the size of the table.
The catch: reads faster, writes slower
An index is a second sorted structure that must stay in sync. Every INSERT, UPDATE, or DELETE now updates the table and every index on it. So:
- Indexes speed up reads, plus sorts and range scans.
- Indexes slow down writes and cost extra storage.
The tradeoff: index the columns you filter and join on — do not blindly index everything.
Primary vs secondary indexes
- Primary index — built on the primary key; it usually determines how rows are physically stored (the "clustered" order). One per table.
- Secondary index — an extra index on some other column (
email,created_at). It stores that column sorted and points back to the row. You can have many.
Your access patterns decide the secondary indexes: if you query "orders for a customer," index customer_id.
What a transaction is
A transaction groups several operations so they succeed or fail as one unit. The classic example is a bank transfer:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If the second update fails, you must not keep the first — money would simply vanish. A transaction guarantees both happen or neither does.
ACID
Transactions in a relational database promise ACID:
- Atomicity — all steps happen, or none do (no half-finished transfer).
- Consistency — the database moves from one valid state to another; constraints (foreign keys, checks) always hold.
- Isolation — concurrent transactions do not corrupt each other; each behaves as if it ran alone.
- Durability — once committed, the data survives a crash, because it is on disk or in the write-ahead log.
Isolation levels (beginner view)
Perfect isolation is expensive, so databases offer levels that trade a little correctness for more concurrency:
- Read Committed — you only read committed data (no dirty reads), but running the same query twice in one transaction can return different rows if others commit in between.
- Repeatable Read — rows you have already read will not change under you within the transaction, protecting against non-repeatable reads.
- Serializable — the strongest level: the result is as if transactions ran one at a time. Safest, but with the most contention and the lowest throughput.
Higher isolation means fewer anomalies but more locking and aborts. Choose the weakest level that is still correct for your use case.
A quick SQL vs NoSQL pointer
- SQL (relational) — a structured schema, joins, and strong ACID transactions. Great when relationships and correctness matter (payments, orders).
- NoSQL — key-value, document, wide-column, or graph stores that relax schema and/or transactions to scale writes horizontally and handle flexible data.
This is a whole topic on its own — a dedicated SQL vs NoSQL lesson later in this track digs into when each one wins. For now, remember: the access pattern drives the choice.
In the interview
- Justify each index with an access pattern, and mention the write-amplification cost.
- Use "ACID" precisely — do not say "consistency" when you mean "isolation."
- Name an isolation level and why (for example, "Serializable for the ledger, Read Committed for the feed").