We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
This idea carries a full system design question on its own. Each walks through the full answer.
System Design Masterclass · Database Fundamentals
Free preview: the first 2 parts of this 8-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Database Fundamentals
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
The indexing lesson said the tree is shallow. The delete lessons said a lookup reads three or four pages. Both were assertions inferred from timings, and PostgreSQL ships a tool that reads the actual index pages instead.

pageinspect and pgstattuple are contrib modules that ship with PostgreSQL. Creating them takes one statement each.
Timings tell you an index is fast and never tell you why, or when it stops being. Reading the structure answers both.

Nothing in this capture is inferred from how long a query took. The tools are bt_metap, bt_page_stats and pgstatindex.

The last bar is ten times the rows of the one before it and the same height. The index grew from 21.4 MB to 214.2 and got no deeper.

The fanout was never a design parameter somebody chose. It falls out of the page size divided by the size of a key plus its pointer.

This is the measured fanout extended, not a separate claim. Each level multiplies the rows the tree can hold by several hundred.
That is why nobody worries about index depth. The interesting questions about a B-tree are all about how full its pages are and how much of it fits in memory.