TOAST: where the wide values really live

TOAST is what happens when a row will not fit in the eight-kilobyte page that has to hold it. Variable-length values are compressed first, and if the row is still too wide the largest of them are cut into chunks and stored in a side relation created for that table, leaving a short pointer in the row. This is automatic, per column, and invisible to SQL. It is also why a table can report a small size while occupying a lot of disk.

Compression comes first, and that is the half people skip

The usual summary stops at “big values go somewhere else”, which loses the step that decides almost everything. A value is compressed in place before anything is moved, so whether a column ends up out of line depends on how compressible it is rather than on how long it is. Two values of identical length, one repetitive and one not, land in completely different places.

Three consequences follow. A query that does not select the wide column never pays for it, because the pointer is what lives in the row and the chunks are only fetched when the value is actually needed. The side table has its own indexes, its own dead rows and its own vacuum, so a table with no visible bloat can be sitting next to a great deal of it. And updating any other column of a row leaves an unchanged wide value where it is; the new row version points at the same chunks, so the expensive part is not rewritten.

Two values, the same length, different fates

One value repeats a single hash over and over; the other is a run of distinct hashes. Both are eighty thousand characters.

CREATE TABLE doc (id int PRIMARY KEY, body text);
INSERT INTO doc SELECT 1, repeat(md5('x'), 2500);
INSERT INTO doc SELECT 2, (SELECT string_agg(md5(i::text), '') FROM generate_series(1, 2500) i);
SELECT id, length(body) AS characters, pg_column_size(body) AS stored_bytes FROM doc ORDER BY id;
SELECT pg_size_pretty(pg_relation_size('doc')) AS heap_only,
       pg_size_pretty(pg_table_size('doc')) AS heap_and_side_table;

CREATE TABLE
INSERT 0 1
INSERT 0 1
 id | characters | stored_bytes 
----+------------+--------------
  1 |      80000 |          960
  2 |      80000 |        80000
(2 rows)

 heap_only  | heap_and_side_table 
------------+---------------------
 8192 bytes | 136 kB
(1 row)

The compressible value shrank enough to stay in the row. The other did not compress at all and went out of line at full width. The last line is the one to remember: the function most monitoring uses to measure a table reports a single page, and the function that includes the side table reports something else entirely.

What to do when a table is the wrong size

Start by checking which of the two numbers above your tooling reports, because a fleet-wide size chart built on the first one is blind to the largest thing in the database. Then treat the side table as a table: it accumulates dead rows from the same updates, and the cleanup rules that apply to it are the ones in autovacuum and table bloat. Where the wide column is updated as often as the narrow ones, the page-level optimisation described in HOT update and fillfactor is worth reading next.