P-6.5 Indexes, Transactions and Failing Safely

What this is and why it exists

Two mechanisms account for most of the difference between a database that copes and one that does not.

An index makes reads fast and writes slower. The only honest way to decide about one is to read the query plan before and after. Not to guess, and not to add one because a query feels slow.

A transaction makes a group of changes all-or-nothing. That is what stops a failed transfer from taking money out of one account without putting it into the other.

The vocabulary

• Index — an ordered structure beside the table, over one or more columns.
• Scan — reading every row because there is no better route.
• Query plan — the database's own account of how it will answer a query.
• Transaction — a group of statements that succeed or fail together.
• Rollback — undoing a transaction that did not complete.
• Isolation level — the rules about what concurrent sessions can see.

The mental model

An index is a separate ordered structure over one or more columns, usually a tree. It lets the database find rows without scanning the table. And it is the same search tree from the data structures module — same shape, same logarithmic lookup, same dependence on staying balanced. Recognising that is worth more than memorising index syntax.

Before adding one, read the query plan. The database will tell you how it intends to answer a query and what it expects that to cost. This is unusual and valuable, because the system can describe the problem precisely. Adding indexes without reading it is guessing at something you were handed the answer to.

The cost side is real. Every index must be updated on every insert, update and delete touching its columns, and it takes space. Indexing everything is a way of making a database slower while feeling productive. It is common in a system somebody once tried to speed up.

Transactions are the other half. Group the statements, and a failure partway through leaves the data as it was rather than half-changed. This is the guarantee that makes a money transfer safe to write: the deduction and the credit either both happen or neither does. It is also what makes stock adjustments, seat bookings and any multi-step change safe.

And once more than one session is writing, they need rules about what each can see. That is the isolation level, and it is a trade: stricter rules cost throughput. You do not need to memorise the full ladder of them. You do need to know your database's default, because your code is silently relying on it right now.

What you should now be able to explain or do

Say what an index is and connect it to the search tree you already know. Read a query plan and decide from it rather than from a hunch. State what every index costs on writes and in space. Use a transaction to make a multi-step change all-or-nothing, and give an example where that is essential. Say what an isolation level trades away, and name your own database's default.

Check yourself