Indexes and transactions

Make queries fast with indexes, and keep data correct with transactions and ACID guarantees.

Indexes: the book's index

To find "garbage collection" in a 900-page book, you don't read every page — you use the index at the back. Databases work the same way.

Without an index, SELECT * FROM users WHERE email = 'ada@example.com' must check every row (a full table scan). With an index on email, the database keeps a sorted structure (usually a B-tree) mapping emails to rows, and finds the match in a handful of steps — even among hundreds of millions of rows.

CREATE INDEX ix_users_email ON users (email);
CREATE UNIQUE INDEX ux_users_email ON users (email);   -- also enforces uniqueness
CREATE INDEX ix_orders_customer_date ON orders (customer_id, ordered_at);   -- composite

Primary keys are indexed automatically. Good candidates for extra indexes are columns you often use in WHERE, JOIN … ON and ORDER BY. A composite index on (customer_id, ordered_at) helps queries filtering by customer, or by customer and date — but not by date alone (column order matters).

Indexes aren't free: they take disk space, and every write must update them too. A table with ten indexes makes inserts noticeably slower.

Seeing the query plan

Databases tell you how they'll execute a query. Run these in order and compare the plans — SCAN reads the whole table, SEARCH … USING INDEX jumps straight to matching rows:

Interactive

SQL sandbox

A real SQLite database running in your browser — a small online shop

Loading editor…

Ctrl + Enter to run

In PostgreSQL it's EXPLAIN ANALYZE, in SQL Server the "execution plan". Reading plans is how you diagnose slow queries.

The N+1 problem

The most common performance bug isn't a missing index — it's running a query inside a loop: one query for 100 orders, then 100 more for each order's customer. Fetch related data in one query with a JOIN. You'll see how ORMs cause and fix this in Track 10.

Transactions: all or nothing

Moving money from A to B takes two updates. If the server crashes between them, money vanishes. A transaction groups statements so they succeed or fail together:

BEGIN TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
 
COMMIT;      -- make both changes permanent
-- or ROLLBACK;  to undo everything since BEGIN

ACID

Relational databases guarantee four properties for transactions:

	Property	Meaning
A	Atomicity	All changes happen, or none do
C	Consistency	Constraints (keys, `NOT NULL`, `CHECK`) are never violated
I	Isolation	Concurrent transactions don't see each other's unfinished work
D	Durability	Once committed, data survives crashes and power loss

Isolation and concurrency

Perfect isolation (as if transactions ran one at a time) is expensive, so databases offer isolation levels. The default in most databases is Read Committed: you never see uncommitted data, but if you read the same row twice, another transaction might have changed it in between. Stricter levels (Repeatable Read, Serializable) prevent more anomalies at the cost of more locking or retries.

A classic concurrency bug is the lost update: two requests read stock = 1, both sell the last item, both write stock = 0. Fixes:

Do it in one atomic statement: UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock > 0; and check how many rows changed.
Optimistic concurrency: store a version number and update only WHERE version = @expected — EF Core supports this with concurrency tokens.

Key takeaways

Indexes turn full table scans into fast lookups, at the cost of storage and slower writes.
Use EXPLAIN to see whether a query uses an index.
Transactions make multi-step changes atomic: BEGIN … COMMIT / ROLLBACK.
ACID = atomicity, consistency, isolation, durability; watch out for lost updates under concurrency.