Vibe Engines is free, interactive AI engineering & system design — runs in your browser. Pick a learning path
Database Index Size Estimator
A database index size estimator. Enter the row count, the size of the indexed key, and a fill factor to estimate the on-disk size of a B-tree index — including the row-pointer overhead and internal nodes — so you can anticipate the storage and memory cost of an index before adding it. Compares the cost of several indexes at once.
Leaf ≈ rows × (key + pointer) ÷ fill factor, plus ~1.5% for internal nodes. Roughly 34.3 bytes/row. An order-of-magnitude estimate — exact size depends on the engine, alignment and data. Composite / covering indexes: sum all indexed (and included) columns into the key size.
A B-tree index is a second, sorted copy of your keys, each paired with a pointer back to its row, so its size follows almost directly from the row count. This estimator multiplies the bytes in one index entry by the number of rows, inflates the result for the free space pages keep, and adds a small allowance for the levels above the leaves.
What it computes
bytes_per_row = (key + pointer) ÷ (fill_factor ÷ 100)
leaf = rows × bytes_per_row
per_index = leaf × 1.015
total = per_index × indexes- key is the indexed value in bytes: 4 for a 32-bit integer, 8 for a bigint or timestamp, 16 for a binary UUID. For a composite or covering index, add every indexed and included column.
- pointer is what each entry stores to find its row. PostgreSQL stores a 6-byte tuple ID; InnoDB secondary indexes store the row’s primary key, so a wide primary key makes every secondary index bigger.
- fill_factor is how full leaf pages are on average, clamped to 1–100%. Dividing by it accounts for the space left free for future inserts.
- 1.015 adds 1.5% for the internal pages, the routing levels above the leaves.
- indexes multiplies the result for several indexes with the same key and pointer sizes.
Sizes are formatted by dividing by 1,024 at each step, so the MB and GB labels are binary units (MiB and GiB). Values of 100 or more show no decimals and smaller ones show one.
A worked example
The defaults: 10,000,000 rows, a 16-byte key such as a UUID, an 8-byte pointer, a 70% fill factor and one index.
- Entry:
16 + 8 = 24bytes. - Fill factor:
24 ÷ 0.70 = 34.3bytes per row, the figure shown in the note under the inputs. - Leaf level:
10,000,000 × 34.29 = 342,857,143bytes. - Internal levels:
342,857,143 × 1.015 = 348,000,000bytes. - Display:
348,000,000 ÷ 1,024 ÷ 1,024 = 331.9, shown as 332 MB per index and 332 MB in total.
| Change from the defaults | Bytes per row | Per index | Total |
|---|---|---|---|
| None | 34.3 | 332 MB | 332 MB |
| Fill factor 90% | 26.7 | 258 MB | 258 MB |
| 8-byte key (bigint) | 22.9 | 221 MB | 221 MB |
| 3 indexes | 34.3 | 332 MB | 996 MB |
| 100,000,000 rows | 34.3 | 3.2 GB | 3.2 GB |
Assumptions and limits
- No per-entry or per-page overhead. Real engines add headers and alignment. A PostgreSQL B-tree entry has an 8-byte header that holds the tuple ID, is padded to a multiple of 8 bytes, and needs a 4-byte line pointer in its page. To approximate it, enter 12 as the pointer and round the key up to a multiple of 8: an 8-byte key then costs 20 bytes per entry.
- Fill factor is both a setting and a state. PostgreSQL builds B-tree leaves 90% full by default, but pages that split under random inserts settle nearer 70%, which is why the default here is 70.
- No compression or deduplication. PostgreSQL 13 and later deduplicate repeated keys in B-tree leaves, so an index on a low-cardinality column can be much smaller than this estimate.
- Fixed internal overhead. With hundreds of entries per page, the levels above the leaves are usually under 1% of the leaf pages, so 1.5% is a slightly generous allowance.
- No bloat. Deleted and updated rows leave dead entries until vacuum or a rebuild reclaims them, so a busy index grows past this number.
How to use the result
- Check it fits in memory. An index is fast while its hot pages stay in the buffer pool. Add up the indexes your workload touches and compare with the cache you have.
- Count the write cost. Every index is updated on each insert and on updates to its columns. Three indexes at 332 MB each is close to another gigabyte to write, cache and back up.
- Prefer narrow keys. Halving the key from 16 to 8 bytes cuts a third off the estimate here, and in InnoDB a narrow primary key shrinks every secondary index too.
- Calibrate against reality. After building an index, check its real size (in PostgreSQL,
pg_relation_sizeon the index) and adjust the pointer and fill factor so future estimates match your engine.
How it works
- Bytes per row = (key + pointer) ÷ fill factor.
- Per index = rows × bytes per row × 1.015, the extra 1.5% for internal B-tree levels.
- Total = per-index size × number of indexes; sizes use 1,024-based units.
- An order-of-magnitude estimate: entry headers, alignment and bloat are not modeled.
Frequently asked questions
How do I estimate the size of a database index?
Multiply the row count by the bytes in one index entry, which is the key plus a pointer back to the row, then divide by the fill factor to account for free space in each page. Add a small allowance for the internal levels of the B-tree. With 10 million rows, a 16-byte key, an 8-byte pointer and a 70% fill factor, that is 24 ÷ 0.7 × 1.015 × 10 million, or 348 million bytes, about 332 MB.
How big is an index on a UUID column?
A UUID stored in binary is 16 bytes, twice a bigint, so its index entries are wider. With this tool’s defaults, a UUID key gives about 332 MB per 10 million rows and a bigint about 221 MB. In PostgreSQL, with entry headers and alignment, a UUID entry costs about 28 bytes against 20 for a bigint. Random version 4 UUIDs also insert all over the index, which splits pages and lowers the fill factor, while time-ordered version 7 UUIDs mostly append.
What is the fill factor of an index?
It is how full index pages are on average. Pages are left partly empty so that new entries fit without splitting a page every time. PostgreSQL builds B-tree indexes with leaf pages 90% full by default, but pages that split under random inserts settle nearer 70%, which is this tool’s default. A lower fill factor uses more disk and memory but means fewer page splits on write-heavy tables. The estimate divides the raw entry bytes by the fill factor.
Why do indexes cost more than just disk space?
Every index is updated on each insert, on each delete and on updates to its columns, so it adds write work and log volume to the table. Indexes also compete for memory: an index whose hot pages no longer fit in the buffer pool turns fast lookups into disk reads. An index trades write cost, storage and cache space for read speed, and knowing its size up front tells you whether the trade is worth it and whether it will stay in memory.
Does a composite or covering index change the estimate?
Yes. A composite index stores every indexed column in each entry, so its key size is the sum of those columns. A covering index that also includes extra columns, so queries can be answered from the index alone, is wider again. Add up the bytes of all indexed and included columns and enter the total as the key size. The result shows the storage you pay for avoiding a trip to the table, which is often the deciding question for a covering index.
Why is my real index bigger than the estimate?
The estimate counts only key and pointer bytes. Real engines add a header and alignment padding to each entry, a line pointer per entry and a header per page. Indexes also bloat: deleted and updated rows leave dead entries until vacuum or a rebuild reclaims the space, and random inserts leave pages emptier than the fill factor you set. Measure the real size, then adjust the pointer and fill factor inputs until the tool matches your engine for future estimates.