Database Table Size Calculator (PostgreSQL & MySQL)

About the Database Table Size Calculator (PostgreSQL & MySQL)

Estimate the disk a table will need before you create it. Enter its columns and types — or paste its CREATE TABLE and CREATE INDEX statements — with the expected rows, the monthly growth and the indexes, and get the size of the table, of its long values and of each index, now and over three years.

The estimate follows how the databases lay rows out on disk. For PostgreSQL: 8 kB pages, a 23-byte header on every row, a null bitmap when the row has NULLs, padding so each column starts on its alignment boundary, a 4-byte line pointer per row, the table’s fillfactor, and TOAST, which compresses and moves values out of rows longer than 2,032 bytes. For MySQL and MariaDB (InnoDB): 16 kB pages filled to 15/16, a 5-byte record header, the NULL bitmap and length bytes, the 13 bytes of transaction id and roll pointer every row carries, secondary indexes that store the primary key, and long values moved off-page. The working is shown step by step, and for PostgreSQL the calculator suggests the column order that wastes the least padding.

Without a pass the page shows a free preview: the total size now in full, the first rows of each table and the first step of the working; the other figures, the copy and the CSV unlock with a Pro pass. Everything is worked out in your browser.

Common uses

• Size the disk or the cloud volume for a new service before it launches.
• Compare UUID and bigint keys, or narrower column types, by the space they cost.
• Find the column order that saves padding on a wide PostgreSQL table.
• Plan when a growing table will outgrow its storage.

How a PostgreSQL row is sized

• Every row starts with a 23-byte header. When the row has a NULL, a bitmap of one bit per column follows. The header is padded to 8 bytes, so it is 24 bytes, or 32 with a bitmap of up to 72 columns.
• Each column starts on its type’s alignment boundary: 8 bytes for bigint, double precision, timestamp and timestamptz, 4 for integer, date, real and long text, 2 for smallint, none for boolean, uuid and short text.
• Text, varchar, bytea, numeric and JSON values of up to 126 bytes take a 1-byte header and no alignment; longer ones a 4-byte header. numeric takes 2 bytes per four digits plus 3 bytes.
• The row is padded to a multiple of 8 and needs a 4-byte line pointer on its page. A page holds 8,168 bytes of rows at most (291 rows), less the share the fillfactor reserves for updates.
• A row larger than 2,032 bytes is toasted: long values are compressed and, if still too large, moved to the TOAST table in chunks of 1,996 bytes, four to a page, leaving an 18-byte pointer in the row.
• B-tree entries are an 8-byte header (16 with NULL keys) and the key, padded to 8, with a line pointer; leaf pages are filled to the index fillfactor (90 by default) and inner pages to 70%.

How an InnoDB row is sized

• Each record has a 5-byte header, a NULL bitmap of one bit per nullable column, and one or two length bytes per non-NULL variable-length value; NULL values take no other space.
• Every row of the clustered index (the table) carries a 6-byte transaction id and a 7-byte roll pointer; without a primary key, InnoDB adds a 6-byte row id, unless a unique index on NOT NULL columns can take its place.
• Types take the bytes the manual lists: INT 4, BIGINT 8, DATETIME 5 and TIMESTAMP 4 (plus up to 3 for fractional seconds), DECIMAL 4 bytes per nine digits, VARCHAR the value plus its length bytes; with a multi-byte character set CHAR(N) keeps at least N bytes.
• Pages are 16 kB with 128 bytes of headers and a 2-byte directory slot per 4 to 8 records. Inserts in key order fill pages to about 15/16; random keys leave them between half full and 15/16 full (the estimate uses about 72%).
• Secondary index records hold the index columns and the primary key. A record longer than about half a page moves its longest TEXT, BLOB or long VARCHAR values off-page behind 20-byte pointers, each value in whole 16 kB pages.

Checking the estimate against the real table

Once the table exists and holds data, the check query shows what the database itself reports: in PostgreSQL pg_relation_size, pg_table_size (with TOAST), pg_indexes_size and pg_total_relation_size, and avg(pg_column_size(t.*)) for the real average row; in MySQL data_length and index_length from information_schema.tables after ANALYZE TABLE (these are estimates from statistics too). Measure your real average value sizes with avg(pg_column_size(column)) or AVG(LENGTH(column)) and enter them to tighten the estimate.

Sources

• PostgreSQL manual: Database Page Layout, TOAST, pg_type (typlen and typalign), Numeric Types, CREATE TABLE (fillfactor), CREATE INDEX (B-tree fillfactor and deduplication)
• PostgreSQL source: itup.h (index tuples), nbtree.h (B-tree pages), heaptoast.h (TOAST threshold and chunks)
• MySQL manual: InnoDB Row Formats, Data Type Storage Requirements, The Physical Structure of an InnoDB Index, InnoDB Limits
• MySQL source: page0types.h, page0page.h, fil0types.h (page headers and directory)

Limitations

• These are estimates of a freshly loaded table. Updates and deletes leave dead rows and empty space behind (bloat in PostgreSQL, fragmentation in InnoDB) until VACUUM or OPTIMIZE TABLE reclaims them, so a busy table is usually larger.
• Averages stand in for real values: rows of very different sizes, and how well long values compress, change the result. Enter measured averages where you can.
• PostgreSQL B-tree deduplication (from version 13) shrinks indexes on repeated values; the estimate assumes distinct keys, so it is an upper bound for such indexes. GIN, GiST, BRIN and hash indexes are estimated as B-trees.
• PostgreSQL’s space map and visibility map forks (a few pages each), InnoDB’s extent allocation and the redo and undo logs are not included.
• User-defined types, domains and extension types are not known: enter the built-in type they are based on.

Privacy

On this device: nothing is sent

Everything happens in your browser. What you enter or open here is not uploaded or stored by MySmartCoPilot.