P-6.5 Indexes, Transactions and Failing Safely

Work through these

• What an index is, and why it is a sorted structure beside the table

  An index is a separate ordered structure over one or more columns, usually a tree, that lets the database find rows without scanning. It is the same search tree from the earlier module.

• Reading a query plan before deciding anything

  The database will tell you how it intends to answer a query and what it expects that to cost. Adding indexes without reading this is guessing at a problem the system can describe precisely.

  PostgreSQL Documentation: Tutorial · Docs

• Every index makes writes slower and takes space

  An index must be updated on every insert, update and delete that touches its columns. Indexing everything is a way of making a database slower while feeling productive.

• Transactions: all of it happens or none of it does

  Grouping statements means a failure partway through leaves the data as it was rather than half changed. This is the guarantee that makes money transfers and stock adjustments safe to write.

  NPTEL: Data Base Management System · Course

• Concurrent access, and what isolation levels trade away

  Several sessions changing the same rows need rules about what each can see, and stricter rules cost throughput. Knowing your database's default is the practical half of this.