Database Internals Masterclass: Indexes, MVCC, and Bloat

A query is slow. You add an index. It gets faster.

A few months later, another query is slow. You add another index. Nothing changes.

Or it gets worse. So you look it up and find a list of rules: put this column first, don’t wrap the column in a function, avoid this kind of LIKE, don’t add too many indexes. Some of them help.

Some don’t. And you’re never quite sure which one applies to the problem in front of you. That isn’t a knowledge problem.

It’s a model problem. This course teaches databases from first principles. We start from a few facts nobody can argue with.

Data lives on disk in fixed-size pages. Reading pages in order is cheap, and jumping between them is expensive. Every index is one more structure the database has to update on every write.

From there we build up one layer at a time, and every behavior you see later traces back to those facts. Once you can do that, the rules stop being rules. You can work them out yourself, including in the situations nobody wrote a rule for.

Indexes are the lens, not the limit. You can’t really understand an index without understanding the table it points to, what happens to it when rows are updated again and again, and how the database decides whether to use it at all. So along the way you learn how a database stores data, how it changes data, and how it decides what to do with your query.

What we cover:How a database finds data, and why a full table scan isn’t the villainWhy indexes exist, and why a B-tree is shaped the way it isWhat a single write really costs, and what changing rows leave behindIndexes in practice: equality, ranges, sorting, multi-column and covering indexesHow the planner decides, and how to read EXPLAIN ANALYZE to see its reasoningIndexing strategy and production maintenance: bloat, VACUUM, reindexing, unused indexesReal-world debugging cases, including queries generated by an ORMHow the course works:Every lesson starts with a question, not an answer. You’ll be asked to guess before you’re shown. Every query plan and number is from a real run.

Nothing is made up for a slide. Each lesson builds on the one before it, so the best way through is in order. Why PostgreSQL?

We need a real database to test ideas on, and PostgreSQL is where every demo runs. But pages on disk, sequential vs random reads, the write cost of indexes and a cost-based planner aren’t PostgreSQL inventions. In one form or another you’ll find them in almost every relational database.

The details change. The questions you learn to ask stay the same.