Developer tools · Database sizing
B-tree depth and index size calculator.
How many levels a lookup walks and how big the index is, from row count and key width.
Inputs
Index estimate
Enter the values.
How deep is a B-tree index?
A PostgreSQL B-tree page is 8 kB: 24 bytes of page header, 16 bytes of special space, and entries that each take an 8 byte index tuple header plus the key (rounded up to a multiple of 8) plus a 4 byte line pointer. The default index fillfactor is 90, so leaf pages are filled to 90%. Depth is the number of levels needed until a single root page covers all the pages below it.
Worked example. 1,000,000 rows with a bigint key: each entry is 8 + 8 + 4 = 20 bytes, 366 entries fit per page, so there are 2,733 leaf pages, 8 pages above them and 1 root: depth 3, about 21.4 MiB.
Internal pages are assumed to be as full as leaf pages and key prefix truncation, deduplication and non-unique suffix columns are ignored, so real indexes can differ. Depth grows very slowly: even billions of rows rarely need more than 4 or 5 levels. Compare with pgstatindex() from the pgstattuple extension on a real index.
Frequently asked questions
How many levels does a B-tree with 1 million rows have?
With 8 byte keys and an 8 kB page this tool gives 3 levels: about 2,733 leaf pages, 8 internal pages and a root. A lookup reads 3 pages.
How big is a Postgres index on a bigint column?
Each entry takes 20 bytes including its line pointer, so about 366 entries fit on a page at fillfactor 90. One million rows come to roughly 21 MiB.
Why does a uuid index take more space than a bigint index?
A uuid key is 16 bytes against 8, so each entry is 28 bytes instead of 20 and fewer fit per page. The index is about 40% larger and the tree may be deeper at very large sizes.
What fillfactor should an index use?
PostgreSQL defaults B-tree indexes to 90. Lower values leave room for inserts and updates at the cost of a larger index; 100 packs pages fully but risks page splits on writes.
Sources
Checked
- PostgreSQL docs: Database Page Layout
- PostgreSQL source: itup.h (IndexTupleData)
- PostgreSQL source: nbtree.h (BTPageOpaqueData)
- PostgreSQL docs: CREATE INDEX (B-tree fillfactor 90)
- PostgreSQL source: pg_type.dat (uuid and int8 length and alignment)
The 8 byte index tuple header and 16 byte B-tree special space are taken from the PostgreSQL source files linked above (they are not stated in the user documentation). Internal-page fill and prefix truncation are simplified. Runs entirely in your browser. Nothing you type is sent or stored.
Scope
Planning estimates, not guarantees. Real systems add overhead this model leaves out and behave differently under load. Measure on your own data and load-test before committing to a design or a bill.