Lab: what a table really costs on disk

Before you start

Use a throwaway database: this lab creates about 200 MB of tables. Every step shows our results from PostgreSQL 18.3 on an Apple Silicon laptop. Sizes depend on the platform’s alignment rules, so yours should match on any 64-bit machine; anything that varies is noted.

1. The cost of a row

One million rows of a single int column hold 4 MB of actual data. How big is the table?

CREATE TABLE ints (n int);
INSERT INTO ints SELECT generate_series(1, 1000000);

SELECT pg_size_pretty(pg_relation_size('ints')) AS on_disk,
       pg_size_pretty(1000000 * 4::bigint)      AS raw_data,
       round(pg_relation_size('ints') / 1000000.0, 1) AS bytes_per_row;

Nine times the data. Each row carries a 23-byte header (rounded up to 24 so the data starts aligned), its 4 bytes of data padded to 8, and a 4-byte line pointer in the page’s directory: 24 + 8 + 4 = 36. Narrow tables are mostly overhead.

2. Column order

Values must start at positions their type requires: an 8-byte bigint at a multiple of 8. A 1-byte boolean in front of one forces 7 bytes of padding.

CREATE TABLE padded  (a boolean, b bigint, c boolean, d bigint, e boolean, f bigint);
CREATE TABLE ordered (b bigint, d bigint, f bigint, a boolean, c boolean, e boolean);
INSERT INTO padded  SELECT true, g, false, g, true, g FROM generate_series(1, 1000000) g;
INSERT INTO ordered SELECT g, g, g, true, false, true FROM generate_series(1, 1000000) g;

SELECT 'padded' AS t, pg_size_pretty(pg_relation_size('padded')) AS size,
       (SELECT pg_column_size(p.*) FROM padded p LIMIT 1) AS row_bytes
UNION ALL
SELECT 'ordered', pg_size_pretty(pg_relation_size('ordered')),
       (SELECT pg_column_size(o.*) FROM ordered o LIMIT 1);

Same columns, same data, 22% less disk. Putting fixed-width columns from largest to smallest is free at design time. Changing it later means rewriting the table, so it is rarely worth doing to an existing one.

3. Large values move out

CREATE TABLE docs (id int, body text);
INSERT INTO docs
SELECT g, (SELECT string_agg(md5(g::text || k::text), '') FROM generate_series(1, 300) k)
FROM generate_series(1, 1000) g;

SELECT pg_size_pretty(pg_relation_size('docs')) AS main_table,
       pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_table
FROM pg_class WHERE relname = 'docs';

Each body is 9,600 characters. Rows wider than about 2 kB have their large values moved to a separate TOAST table, leaving a small pointer in the row. The main table stays tiny, so a query that does not read body never touches those 10 MB.

4. Deleted space stays

SELECT pg_size_pretty(pg_relation_size('ints'));            -- start
DELETE FROM ints WHERE n % 2 = 0;
SELECT pg_size_pretty(pg_relation_size('ints'));            -- after deleting half
VACUUM ints;
SELECT pg_size_pretty(pg_relation_size('ints'));            -- after VACUUM
INSERT INTO ints SELECT generate_series(1000001, 1500000);
SELECT pg_size_pretty(pg_relation_size('ints'));            -- after 500,000 new rows
DELETE FROM ints WHERE n % 2 = 1;
VACUUM FULL ints;
SELECT pg_size_pretty(pg_relation_size('ints'));            -- after VACUUM FULL

The delete only marked rows. VACUUM made their space reusable but kept it in the file, and the next 500,000 inserts filled it without growing the table. Only VACUUM FULL, which writes a whole new copy of the table, gave space back. It also blocks all access to the table while it runs, which Vacuum, autovacuum and bloat covers.

Clean up

DROP TABLE ints, padded, ordered, docs;

Check yourself

1. Where do the 36 bytes per row for a single int column come from?

   Show answer

   A 23-byte row header rounded to 24 for alignment, the 4-byte value padded to 8, and a 4-byte line pointer in the page directory.

   I could explain this without looking

2. Why did reordering the same columns save 22%?

   Show answer

   Each bigint must start at a multiple of 8 bytes. A boolean before it forces 7 bytes of padding; putting the bigints first and the booleans together at the end removes that padding.

   I could explain this without looking

3. After DELETE and VACUUM, the table is the same size. Is the space wasted?

   Show answer

   No. VACUUM marks it as free, and new rows fill it, as the 500,000 inserts did without growing the file. It is only returned to the operating system by VACUUM FULL or when empty pages are at the very end of the table.

   I could explain this without looking