Make this Postgres fast: the brief

The whole database in one picture, from the page to the lock. A tuning job is a walk through that map with a stopwatch: this lesson finds where the time goes, and Make this Postgres fast: the fixes and the write-up changes it.

Before you start. This is the capstone of the track, and it uses the lessons before it: reading a plan (The Query Planner, The plan, running), choosing an index (Which shortcut to build), diagnosing from a symptom (The Database That Won't Answer) and writing the SQL (The sentence that says what). You need the Docker bench from Pages & Heap Files, or any Postgres 16, and Python 3. The code is in teaching/code/database-internals/tuning.

The idea in 30 seconds

The advice not to optimize early is half of Knuth’s sentence; the other half says to find the small part that matters and not pass it up, and measuring is how.
Before you tune, write down the pages, their targets and a way to time them. A target is a decision someone makes; here the numbers are made up.
pg_stat_user_tables and pg_stat_user_indexes tell you which tables are read start to end and which indexes are never used, and the counts can be checked against the workload by arithmetic.
In this shop, three pages of five miss their targets: a lower(email) filter, a foreign key with no index behind it, and a dashboard that reads the biggest table in full.
Two passing pages should be left alone, with a written reason, and a scorecard misses what it does not exercise: deleting one order costs 20 ms here and no page shows it.

Read the story · Skip to the numbers