[100% Off] Database Internals Masterclass: Indexes, Mvcc, And Bloat
Learn how a database stores, changes and finds data, and why your index gets ignored. Every demo runs on PostgreSQL.
What you’ll learn
- Explain how a table is laid out on disk and why reading pages in order is so much cheaper than jumping between them,Predict when a database will choose a full table scan over an index
- and explain why that is often the right call,Picture how a B-tree index is shaped and trace a lookup from the root down to the row in the table,Estimate what a single INSERT or UPDATE really costs once every index on the table has to be kept up to date,Explain how row versioning leaves dead rows behind and how that affects index size
- speed and maintenance,Design multi-column indexes by reasoning about how the tree is sorted
- instead of memorizing the leftmost-prefix rule,Read EXPLAIN ANALYZE output line by line: estimated vs actual rows
- loops
- rows removed
- and buffer hits vs reads,Diagnose why the planner ignores an index: low selectivity
- stale statistics
- functions on columns
- LIKE patterns
- type mismatches,Use partial
- expression
- covering and unique indexes where they pay off
- and build indexes without blocking writes,Find bloated and unused indexes in production and decide which ones to rebuild
- keep or drop,Debug slow queries the way you would on the job
- including queries generated by an ORM,Carry the same principles to MySQL
- SQL Server and other relational databases
- knowing what questions to ask
Requirements
- Comfortable writing basic SQL: SELECT
- WHERE
- ORDER BY and simple JOINs,No database internals or DBA background needed. Every term is explained the first time it appears,You don’t need to use PostgreSQL at work. The ideas apply to any relational database
Description
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 villain
Why indexes exist, and why a B-tree is shaped the way it is
What a single write really costs, and what changing rows leave behind
Indexes in practice: equality, ranges, sorting, multi-column and covering indexes
How the planner decides, and how to read EXPLAIN ANALYZE to see its reasoning
Indexing strategy and production maintenance: bloat, VACUUM, reindexing, unused indexes
Real-world debugging cases, including queries generated by an ORM
How 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.
