Databases
Make slow queries fast, and know why they were slow
How a database stores rows, finds them through indexes, plans a query and keeps transactions apart. Every example runs on a real PostgreSQL in your browser, against a sample shop with 100,000 orders, and every plan shown on these pages came from a real run.
New and still in draft: the pages say so until someone other than the author has reviewed them.
Start here
How data is stored and found
How a table is stored
Pages, row addresses, row versions, VACUUM and the write-ahead log: what a table is on disk.
5 min readPart 03B-tree indexes
How the default index finds one row among millions in three or four page reads, and what it costs.
7 min readPart 04Designing indexes
Column order, covering, partial and expression indexes, and when an index is ignored or hurts.
9 min readPart 05Scan types
Sequential, index, bitmap and index-only scans, and how the planner chooses between them.
6 min readReading query plans
Reading EXPLAIN
Read a query plan line by line, spot the red flags, and use the plan to fix a slow query.
12 min readPart 07Join algorithms
Nested loop, hash and merge joins: how each works, what it costs, and when the planner picks it.
7 min readPart 08Planner statistics
What ANALYZE records, how estimates are made, and why they go wrong.
7 min readWriting faster SQL
Pagination and N+1
Why OFFSET gets slower every page, keyset pagination, and replacing many small queries with one.
6 min readPart 10Common table expressions
WITH for readable steps, inlined or materialized CTEs, recursive queries and data changes in one statement.
4 min readPart 11Window functions and materialized views
Rankings, top N per group, running totals and previous-row comparisons; storing an expensive result.
4 min readPart 12Views, functions and triggers
Logic inside the database: what views, functions, procedures and triggers are good for, and what they cost.
4 min readConcurrency
Transactions
ACID, savepoints, and the lost update that transactions alone do not prevent.
4 min readPart 14Isolation levels and MVCC
What each isolation level lets a transaction see, shown with recordings from a real server.
5 min readPart 15Locks and deadlocks
Lock modes, waiting, the lock queue behind a migration, deadlocks, SKIP LOCKED and advisory locks.
7 min readPart 16Connection pooling
Why connections are expensive, what a pool does, PgBouncer modes and how to size a pool.
3 min readScaling a table
Practice
Slow query checklist
A method for any slow query, and a table from what the plan shows to the likely fix.
3 min readPart 20Database interview questions
Eleven questions interviewers ask about indexes, transactions and scale, with answers to compare against yours.
4 min readPart 21Query-tuning exercises
Twelve slow queries from a real-looking shop. Fix each one; the database checks your plan.
5 min read