How PostgreSQL Stores Big Values (TOAST)

The idea: a row must fit in a page

PostgreSQL stores a table's rows as heap tuples in 8 KB pages, and a tuple can never span two pages. Yet a text, bytea or jsonb value may be up to 1 GB. TOAST (The Oversized-Attribute Storage Technique) closes that gap. When a row is too wide, PostgreSQL first compresses its widest values. If that is not enough, it moves them out of line into a separate TOAST table, cut into chunks. The row keeps only a small pointer. The page uses one table, docs (id int, title text, body bytea), and watches what happens to body.

When the toaster runs

On every INSERT and UPDATE, PostgreSQL checks the new tuple's size. If it is over TOAST_TUPLE_THRESHOLD (2,032 B with 8 KB pages, about a quarter of a page), the toaster (heap_toast_insert_or_update) works on the columns until the row is under TOAST_TUPLE_TARGET (also 2,032 B). It tries the cheapest fix first, in four passes, each one picking the widest column first:

1. Compress EXTENDED columns. A column still wider than the target after compression is moved out of line at once.
2. Move EXTENDED and EXTERNAL columns out of line.
3. Compress MAIN columns.
4. Move MAIN columns out of line, only if the row is still over 8,160 B, the most a page holds.

Compression uses pglz by default, or lz4 with default_toast_compression = lz4 or ALTER TABLE … ALTER COLUMN … SET COMPRESSION lz4. pglz gives up when it cannot save at least 25 %, which is why random or already-compressed data (a JPEG, a zip) is stored raw. A small row is never touched at all (Demo: small row stays inline). A 5 KB text value usually compresses well under 2 KB and stays in the heap, compressed (Demo: 5 KB text is compressed inline).

The TOAST table and the 18-byte pointer

Every table with a column that can be toasted gets its own TOAST table, pg_toast.pg_toast_<oid>, with three columns: chunk_id (an OID per value), chunk_seq (0, 1, 2, …) and chunk_data. A value moved out of line gets a new chunk_id and is cut into chunks of at most TOAST_MAX_CHUNK_SIZE (1,996 B with 8 KB pages), so four chunk rows fit on a TOAST page. A unique B-tree index on (chunk_id, chunk_seq) finds them in order.

The heap tuple keeps an 18-byte TOAST pointer instead of the value: a 2-byte header, the raw size, the stored size and compression method, the value's OID and the TOAST table's OID. That is why a 5 KB random value leaves a 52-byte row behind (Demo: 5 KB random bytes go out of line). A value can be both: a 60 KB text is compressed to 3.4 KB, which is still too wide, so the compressed bytes go out of line in 2 chunks (Demo: 60 KB text: compressed, still out of line).

Storage strategies

Each column has a strategy, set with ALTER TABLE docs ALTER COLUMN body SET STORAGE …. It decides which passes may touch the column. Pick one in the storage menu before INSERT.

Strategy	Compress?	Out of line?	Typical use
PLAIN	no	no	fixed-width types (int, timestamp); a wide row fails
MAIN	yes	only as a last resort	keep the value in the heap when possible
EXTERNAL	no	yes	fast substring() on large text or bytea: only the needed chunks are read
EXTENDED	yes	yes	the default for text, bytea, jsonb

Reading: detoasting

A pointer is only followed when the value is needed. SELECT body finds the tuple, sees the pointer, scans the TOAST index for chunk_id in chunk_seq order, fetches each chunk row, glues the pieces together and decompresses them if the value was compressed. SELECT title never looks at the pointer, so it reads 0 TOAST chunks however big the body is (Demo: SELECT body vs SELECT title). This is why SELECT * can be much slower than naming the columns you need: it detoasts every wide value.

UPDATE and TOAST

An UPDATE writes a new heap version of the whole row (see How PostgreSQL Runs a Query). When the toasted column does not change, the new version simply copies the 18-byte pointer: the toaster sees the old and new pointers are equal and writes nothing to the TOAST table. Both versions then point at the same chunks (Demo: UPDATE title reuses the TOAST value). With MAIN or PLAIN and a wide inline value, every update copies the whole value instead. When the toasted column itself changes, a new value with a new chunk_id is written, and the old chunks become dead rows that VACUUM removes later.

Practical consequences

• Name the columns you need instead of SELECT * on tables with large values.
• pg_column_size(body) shows the stored size (compressed or the 18-byte pointer); octet_length(body) shows the full size. pg_column_compression(body) says pglz or lz4.
• pg_total_relation_size() includes the TOAST table; pg_relation_size() does not.
• lz4 compresses and decompresses much faster than pglz at a similar ratio.
• ALTER TABLE … SET (toast_tuple_target = …) changes when the toaster runs (128 B up to 8,160 B).

See also the PostgreSQL heap page (where the tuples live), PostgreSQL shared buffers (TOAST pages are cached like any other) and Huffman coding (how compression finds savings).

What the page leaves out

Alignment padding, the null bitmap and the exact header sizes; the real pglz algorithm (the compressed sizes are fixed per sample value); other columns being toasted (only body is wide here); slicing (substring() on an EXTERNAL value reads only the chunks it needs); TOAST of index entries; the in-memory "expanded" and indirect pointer forms; and VACUUM of dead TOAST rows after a DELETE or an UPDATE of the toasted column.

References

TOAST (PostgreSQL documentation)

ALTER TABLE: SET STORAGE and SET COMPRESSION

heaptoast.c: heap_toast_insert_or_update (PostgreSQL source)

The Internals of PostgreSQL (Hironobu Suzuki)