Why are my indexes bigger than my table?

It happens on narrow rows. Each index stores its key plus a row pointer for every row, so several indexes on a small row easily exceed the row data itself.

Why is the projection so much larger than I expected?

Because growth compounds. Five percent a month is about a factor of three over two years, not the sixty percent that adding five twenty four times would suggest.

Technologyinfrastructure

Database Sizing Calculator

Storage for a table and its indexes, projected forward at a growth rate. Indexes are the part people leave out of the estimate, and on a narrow table with several of them they can exceed the data.

Also called: database storage calculator, table size calculator.

Storage needed

3.64

3.64 GB today, growing to 11.74 GB in 24 months. 4 indexes add 0.8 GB, which is 29% of the data before overhead. Each index stores a key and a row pointer for every row, so on narrow rows the indexes can exceed the table. At 5% monthly growth the table reaches 11.74 GB in 24 months, a factor of 3.2. Growth compounds, which is why the projection is so much larger than adding the monthly rate 24 times would suggest.

Table data

Indexes

0.8

Overhead and bloat

0.84

Projected size

11.74

Projected rows

1,61,25,500

Share taken by indexes

28.6%

On indexes

4 indexes add 0.8 GB, which is 29% of the data before overhead. Each index stores a key and a row pointer for every row, so on narrow rows the indexes can exceed the table.

On growth

At 5% monthly growth the table reaches 11.74 GB in 24 months, a factor of 3.2. Growth compounds, which is why the projection is so much larger than adding the monthly rate 24 times would suggest.

How this is calculated

Projected size

Hover or drag for values

Growth over time

Month	Rows	GB
Month 0	50,00,000	3.64
Month 2	55,12,500	4.01
Month 4	60,77,531	4.42
Month 6	67,00,478	4.88
Month 8	73,87,277	5.38
Month 10	81,44,473	5.93

Method and background

How this is calculated

Row count times row size gives the table, and each index adds one entry per row, so four indexes on a four hundred byte row add roughly forty percent again. Page overhead, fill factor and bloat from updates then inflate the whole thing, and thirty percent is a conservative working figure for a database under normal write load. Growth compounds monthly, which is what makes a two year projection so much larger than intuition suggests: five percent a month is a factor of three over twenty four months, not a fifth again.

rows times row size, plus one index entry per row per index, all inflated by page overhead and free space

n: Rows
b: Bytes a row
k: Indexes
o: Overhead

Worked examples

Each of these is asserted on every build. If a change to the engine ever moved one of these answers, the build would fail before the page could print it.

five million rows with four indexes

Rows in the main table: 50,00,000
Average row size: 400 B
Indexes on the table: 4
Average index entry: 40 B
Monthly row growth: 5%
Months to project: 24
Page overhead, bloat and free space: 30%

Storage needed3.64

5e6 x 400 bytes, plus 4 index entries a row, inflated 30%

Open this example

compounding over two years

Rows in the main table: 50,00,000
Average row size: 400 B
Indexes on the table: 4
Average index entry: 40 B
Monthly row growth: 5%
Months to project: 24
Page overhead, bloat and free space: 30%

Storage needed3.64

boundary: 1.05^24 is 3.2, not 2.2

Open this example

Method and limits

What it assumes

Uniform row sizes, where variable-length columns make real rows vary widely.

What it deliberately does not model

Compression, column types and page fill factor change the result substantially and are engine-specific.
This sizes the table and its indexes, not the write-ahead log, temporary space, replicas or backups.

Formula version 1.0.0 · definition 1.0.0 · India · Report a problem with this calculator

Frequently asked questions

Why are my indexes bigger than my table?: It happens on narrow rows. Each index stores its key plus a row pointer for every row, so several indexes on a small row easily exceed the row data itself.
Why is the projection so much larger than I expected?: Because growth compounds. Five percent a month is about a factor of three over two years, not the sixty percent that adding five twenty four times would suggest.