Database Index Size Calculator

Index size grows faster than you expect because databases store more than just your data. In PostgreSQL a unique index on one integer column of a million-row table takes about 21.4 MB, and a three-column bigint index on the same table about 38.8 MB. Every entry carries an 8-byte header and a 4-byte line pointer and is padded to a multiple of 8 bytes, so a 4-byte integer key costs 20 bytes per row.

This calculator models B-tree indexes, the default structure in most relational databases. B-trees store data in fixed-size pages (8 KB in PostgreSQL, 16 KB by default in MySQL's InnoDB), with each page containing index keys plus pointers to child pages or table rows. The fill factor sets how full PostgreSQL packs each leaf page when it builds the index; the space left free takes later inserts without splitting pages, at the cost of a bigger index.

Database index size estimation helps prevent storage surprises and performance bottlenecks. A 500 GB table might seem manageable until you discover its indexes consume another 200 GB. Large indexes compete with your application for RAM, and indexes that don't fit in memory turn fast queries into slow disk operations.

Calculate index sizes before building them on production tables, especially for composite indexes or tables expected to grow beyond 100,000 rows. Use these estimates to plan storage capacity and memory allocation for database servers.

This calculator is essential when evaluating index strategies for large datasets. If an index size estimate approaches your available RAM, consider alternatives like a partial index or table partitioning before implementation; a covering index (INCLUDE columns) makes the index bigger, not smaller.

Run calculations during capacity planning for database migrations or when adding indexes to existing large tables. Building an index also sorts the keys, which uses maintenance_work_mem and then temporary files, on top of the finished index.

The biggest mistake is underestimating composite index overhead. Developers often assume that indexing three columns costs three times a single column. The per-entry overhead is fixed instead: one int4 column takes 20 bytes per row and three bigint columns 36, so the three-column index is under twice the size. For small keys most of the index is overhead.

Another common error is ignoring fill factor impact on production systems. Setting fill factor to 100 minimizes storage, but PostgreSQL's documentation advises it only for tables that will never see inserts or updates: otherwise 'even a few updates or inserts will cause a sudden flood of page splits'.

Developers frequently forget that index size directly affects memory requirements. A database might run perfectly with 16GB RAM until index sizes exceed available buffer pool space. At that point, index lookups that miss the cache read from disk, which is much slower.

The core calculation starts with entry size: 8-byte tuple header + (number of columns × average column size), rounded up to a multiple of 8, plus a 4-byte line pointer. A page has 8,192 − 24 (page header) − 16 (B-tree data) = 8,152 usable bytes, and a build leaves 8,192 × (100 − fill factor) ÷ 100 of them free.

Entries per leaf page = floor(free-adjusted usable bytes ÷ entry size). Leaf pages = ceiling(rows ÷ entries per page). Internal pages hold one entry per child page and are filled to 70%, up to a single root, and one metapage is added. Leaf pages dominate for most indexes.

For example: 1 million rows with 3 columns of 8 bytes: 8 + 24 = 32 bytes, plus 4 = 36 bytes per entry. At fill factor 90, 8,152 − 819 = 7,333 bytes hold 203 entries per page, so 4,927 leaf pages; with 33 internal pages and the metapage that is 4,961 pages × 8 KB = 38.8 MB.

The estimate is the size right after CREATE INDEX with unique keys. Inserts in random key order later split pages and leave them partly empty, so a busy index grows past it, while deduplication (PostgreSQL 13+) makes an index with many repeated keys smaller. Measure the real size with SELECT pg_size_pretty(pg_relation_size('index_name')).