TOAST — Where Big Values Go

A tuple has to fit on an 8 KB page, so when a row grows too wide Postgres compresses its widest compressible values and, if that is still not enough, relocates them into a side table and leaves an 18-byte pointer in their place. The mechanism is called TOAST, it happens whether or not you asked for it, and it is the reason a text column can hold a gigabyte in a database whose page is eight kilobytes.

Cartwheel meets it through products.attributes, a jsonb document that averages 3 KB across the catalogue. Nothing in the schema says anything about TOAST and nothing was configured, and yet the storage of that table is split across two relations with different sizes, different vacuum behaviour and different read costs. Until you measure it, none of that is visible.

The Threshold and the Algorithm

The TOAST machinery is triggered only when a row about to be stored is wider than TOAST_TUPLE_THRESHOLD, normally 2 KB — roughly a quarter of a page. Once triggered, it compresses and moves values out of line until the row is shorter than TOAST_TUPLE_TARGET, also normally 2 KB, or until no further gains are available. That target is adjustable per table with the toast_tuple_target storage parameter, anywhere from 128 bytes up to the block size minus its header; the default is picked so that at least four tuples fit in a block, which works out at 2,040 bytes.

The order matters more than the numbers. Compression is attempted first, relocation second, and the whole process stops the instant the row drops under the target. A 3 KB attributes document that compresses below the target therefore stays in the row, compressed, and is decompressed on every read. One that does not compress far enough is moved out of the row entirely, leaving a pointer that records the TOAST table's OID, the value's chunk id, and its logical and stored sizes. Two rows in the same table can be stored in completely different ways depending on how compressible their JSON happened to be.

The Four Storage Strategies

Each column carries a storage strategy that decides which halves of that algorithm are allowed. PLAIN permits neither compression nor relocation and is the only option for fixed-length types such as integer. EXTENDED permits both and is the default for almost every type that supports anything else, including text, jsonb and arrays. EXTERNAL permits relocation but not compression. MAIN permits compression and treats relocation as a last resort, keeping the value in the row wherever it can.

ALTER TABLE products ALTER COLUMN attributes SET STORAGE EXTERNAL;
ALTER TABLE products ALTER COLUMN attributes SET COMPRESSION lz4;

-- \d+ products shows the strategy per column
   Column   |     Type      | Storage  | Compression
------------+---------------+----------+-------------
 sku        | text          | extended |
 name       | text          | extended |
 price      | numeric(10,2) | main     |
 attributes | jsonb         | external | lz4

Switch to EXTERNAL when the bytes are already compressed — images, gzipped payloads, anything with high entropy — because Postgres will otherwise burn CPU on every write trying to compress incompressible data, and when the value is read by slices, because substring operations on large uncompressed text and bytea run faster: the server fetches only the chunks it needs instead of the whole value. Neither statement moves any existing data. SET STORAGE sets the strategy for future updates and changes nothing in the table today, and SET COMPRESSION applies to values inserted from now on while old values keep the algorithm they were written with. pg_column_compression() reports the method of an individual value.

The TOAST Table Itself

Any table with a TOAST-able column gets a companion relation, whose OID sits in that table's pg_class.reltoastrelid. It lives in the pg_toast schema, it has three columns — chunk_id, chunk_seq, chunk_data — and a unique index on the first two. Out-of-line values are split, after compression, into chunks sized so that four chunk rows fit on a page, which comes to about 2,000 bytes each. Nothing about it is special: it is an ordinary heap table with ordinary pages, tuples and line pointers, so everything in the previous two topics applies to it unchanged.

SELECT c.reltoastrelid::regclass                        AS toast_table,
       pg_size_pretty(pg_relation_size(c.oid))          AS main_fork,
       pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_fork
  FROM pg_class c
 WHERE c.relname = 'products';

       toast_table        | main_fork | toast_fork
--------------------------+-----------+------------
 pg_toast.pg_toast_16412  | 18 MB     | 214 MB

Twelve times as much of products lives outside products as inside it. That is a genuinely good trade for the queries that never touch attributes: the main fork became small enough to scan quickly. It is a bad surprise for anything that reads the column in a loop, and it is invisible to a monitoring query that only ever asks for the size of products. A 3 KB document that ends up out of line becomes two chunk rows plus two index lookups on every read that touches the column, and the main tuple it left behind is around sixty bytes instead of three thousand.

pglz and LZ4

default_toast_compression is pglz, the built-in algorithm that has always been there, and lz4 is the alternative, available when the server was built with --with-lz4. LZ4 compresses somewhat less than pglz but compresses and decompresses several times faster, and for a document column written on every product update that trade is a straight throughput win — CPU is what a busy primary runs out of first, and disk is cheap.

The method is recorded per value, not per column, which has a practical consequence worth knowing before a migration review asks about it: changing the column setting does not rewrite anything. New and updated rows use the new method, existing rows keep the one they were written with, and the table reads correctly throughout with a mixture of both. A pg_restore rewrites everything with the configured method, so the switch completes on its own the next time the table is reloaded, or immediately if you rewrite it on purpose.

What It Costs at Read Time

The main tuple staying small is the benefit: every scan that does not touch the big column reads fewer pages, and on products that is most of them. The cost lands on every access that does touch it — the chunk lookups through the TOAST index, the reassembly, and the decompression, all before the value reaches the query. So SELECT * from an ORM on a table with a large jsonb column is a measurable I/O decision, and the fix is to name the columns the code actually uses.

The reverse claim deserves stating too, because it is where the chapter's opening observation comes from. TOAST moves bytes out of the row; it does not make row width stop mattering, and it does not make reading the relocated bytes free. SELECT id, price FROM products reads far more disk than eight bytes plus a numeric per row can explain, and TOAST is not what rescues it: the main fork still holds sku, name, tags and the pointers, and every page of it is read to reach the two columns you asked for.

Where TOAST Bloat Hides

The TOAST relation is a heap table, so it behaves like one. Updating a single key in a 3 KB document does not patch the document: the whole value is re-compressed and re-written as new chunk rows, the previous chunks become dead tuples, and the WAL generated is wildly out of proportion to the size of the change. A high-frequency update on one jsonb key is therefore one of the more effective ways to bloat a Postgres database. The TOAST table needs vacuum exactly like any other, and it has its own autovacuum settings through the toast. storage parameters when the parent table's defaults are wrong for it.

SELECT pg_size_pretty(pg_relation_size('products'))       AS main_fork,
       pg_size_pretty(pg_table_size('products'))          AS with_toast,
       pg_size_pretty(pg_total_relation_size('products')) AS with_indexes;

 main_fork | with_toast | with_indexes
-----------+------------+--------------
 18 MB     | 232 MB     | 261 MB

Those are three different questions and it is worth being precise about which one you asked. pg_relation_size() reports one fork of the relation itself and knows nothing about TOAST. pg_table_size() adds the TOAST relation together with the free space and visibility maps. pg_total_relation_size() adds the indexes on top. A capacity dashboard built on the first of the three will report products as an 18 MB table indefinitely while the relation behind it grows to forty gigabytes.