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.
An index is not free storage — it is a second copy of your keys, and it is worth knowing how big before you add it. A B-tree index keeps, for every row, the indexed key plus a small pointer back to the row (a tuple ID or primary-key reference, on the order of 6–10 bytes). Multiply that per-row cost by your row count and you already have the bulk of the index: the leaf level.
Two adjustments make the estimate realistic. Pages are not packed completely full — a fill factor of roughly 70–90% leaves room for inserts without constant page splits — so the stored size is the leaf data divided by that factor. And above the leaves sit the internal nodes that route each lookup, a small extra percentage on top. This tool applies that model to give an order-of-magnitude number; the exact figure depends on the engine, alignment and data distribution.
Why bother? Because an index trades write cost and storage for read speed. It must be updated on every write to its columns, and it competes for the buffer pool — an index that no longer fits in RAM turns fast lookups into disk reads. Estimating the size up front tells you whether it will stay in memory and whether the write overhead is justified, and it scales: composite and covering indexes are simply larger keys, so summing their columns into the key size shows their real footprint before you commit.
How it works
- Leaf size ≈ rows × (key + pointer) ÷ fill factor.
- Adds internal-node overhead for the routing levels.
- Pointer overhead ~6–10 bytes per row modeled explicitly.
- Estimate several indexes to see their combined footprint.
Frequently asked questions
How is index size estimated?
A B-tree index stores, for each row, the indexed key plus a pointer to the row (a tuple ID or primary-key reference), roughly 6–10 bytes. The leaf level therefore holds about rows × (key size + pointer size) bytes, divided by a fill factor (pages are not packed 100% full — often ~70%), plus a small percentage for the internal nodes that route lookups. This tool applies that model to give an order-of-magnitude estimate; exact size depends on the engine and data.
What is the fill factor?
Index and table pages are deliberately left partially empty so that new inserts and updates have room without constantly splitting pages. A typical default fill factor is around 70–90%, meaning pages are that full on average. A lower fill factor uses more disk but reduces page splits on write-heavy tables; a higher one packs tighter for read-mostly data. Because it inflates the stored size, the estimate divides by the fill factor.
Why do indexes cost more than just disk?
Every index has to be kept in sync on every insert, update and delete of the indexed columns, which adds write amplification and can slow down writes. Indexes also compete for memory (buffer pool / shared buffers) — an index that does not fit in RAM causes disk reads on lookups. So an index is a read-speed-for-write-cost-and-storage trade. Estimating its size up front helps you judge whether it will fit in memory and whether the write cost is worth the query speedup.
Does a composite or covering index change this?
Yes. A composite index’s key size is the sum of all its columns, so it is larger per entry than a single-column index. A covering index that includes extra non-key columns (so queries are answered from the index alone) is larger still, trading more storage for avoiding table lookups. Add up the total key/included bytes and use that as the key size here to estimate those larger indexes.