Hong Life CalculatorsUnderstanding the database table size estimator — concept and principle
Before creating a large table, it helps to know roughly how much disk it will need. This estimator multiplies the number of rows by their average size, allows for the free space left in each storage page, and adds a percentage for indexes. Database administrators and developers use it for capacity planning, choosing instance storage and estimating backup and replication volumes.
How it is calculated
- Data size (GB) = rows × average bytes per row ÷ (fill factor ÷ 100) ÷ 10⁹.
- With indexes (GB) = data size × (1 + index overhead ÷ 100).
The fill factor is the percentage of each page filled with rows; the rest is left free. Sizes are in decimal gigabytes (1 GB = 10⁹ bytes).
The principle
Relational databases store rows in fixed-size pages, for example 8 kB in PostgreSQL and 16 KB by default in MySQL's InnoDB. Pages are often not filled completely, either by design (a fill factor that leaves room for updates) or because of deletions and page splits. If only 90% of each page holds data, the table needs 1 ÷ 0.9 ≈ 1.11 times the raw bytes. Indexes are separate structures, usually B-trees, that store the indexed columns plus a pointer to each row; their size depends on how many indexes you have and how wide their keys are, so a percentage of table size is a practical shortcut.
Worked example
- Inputs: 10,000,000 rows, 200 bytes per row, 50% index overhead, 90% fill factor.
- Raw data: 10,000,000 × 200 = 2,000,000,000 bytes.
- Allowing for fill: 2,000,000,000 ÷ 0.9 ≈ 2,222,222,222 bytes = 2.22 GB.
- With indexes: 2.222 × 1.5 = 3.33 GB.
| Page fill factor (%) | Data size (GB) |
|---|---|
| 22.5 | 8.888888889 |
| 45 | 4.444444444 |
| 67.5 | 2.962962963 |
| 90 | 2.222222222 |
| 112 | 1.777777778 |
| 135 | 1.481481481 |
| 180 | 1.111111111 |
How to read the result
Treat the output as a lower-bound starting point. Real engines add per-row overhead: PostgreSQL stores a tuple header of about 23 bytes plus a 4-byte line pointer for each row, and variable-length columns and alignment padding add more. Plan disk space with headroom for growth, temporary files, write-ahead or redo logs and maintenance operations, which can need extra space while rebuilding tables or indexes.
Common mistakes
- Using the declared maximum column size (such as VARCHAR(255)) instead of the average stored length.
- Forgetting row headers and page headers, which matter for narrow rows.
- Ignoring bloat from updates and deletes in engines that keep old row versions until cleanup.
Frequently asked questions
What fill factor should I use?
PostgreSQL tables default to 100% and B-tree indexes to 90%; a lower table fill factor can help heavily updated tables.
How can I measure the real size?
In PostgreSQL use pg_total_relation_size; in MySQL query information_schema.TABLES for data and index length.
Is 50% index overhead realistic?
It varies widely: a table with one small primary key may need far less, and one with many wide indexes can exceed 100%.
Sources: PostgreSQL Documentation, "Database Page Layout" and CREATE TABLE storage parameters (fillfactor) · MySQL Reference Manual, InnoDB page size (innodb_page_size)