Storage

TOAST

A row must fit in an 8 kB page. When a row grows past about 2 kB, PostgreSQL compresses its large values and, if that is not enough, slices them into chunks stored in a separate TOAST table.

In plain words

A row has to fit inside one 8 kB page, but a single text, jsonb or bytea value can be up to 1 GB. TOAST ("The Oversized-Attribute Storage Technique") solves this quietly in the background.

When a row is bigger than about 2 kB, PostgreSQL first tries to compress its largest values. If the row is still too big, it cuts those values into chunks of about 2 kB and stores them in a hidden side table. The row itself keeps only a small 18-byte "pointer", like a coat-check ticket.

The nice part: queries that don't ask for the big column never read the side table at all. The price: reading the big value means extra page reads and decompression, and updating it writes a whole new copy.

For DBAs and operators

When TOAST kicks in

The toaster runs when a row is wider than TOAST_TUPLE_THRESHOLD (2032 bytes with 8 kB pages). It works on the largest eligible values until the row is at most toast_tuple_target (also 2032 by default, settable per table with ALTER TABLE … SET (toast_tuple_target = N), 128 to 8160). Out-of-line values are cut into chunks of 1996 bytes and stored as rows (chunk_id, chunk_seq, chunk_data) in pg_toast.pg_toast_<oid>, which has a unique index on (chunk_id, chunk_seq).

Storage strategies (per column)

Strategy	Compress	Out of line	Default for
`PLAIN`	no	no	fixed-length types (int, timestamp)
`EXTENDED`	yes	yes	most varlena types (text, jsonb, bytea)
`EXTERNAL`	no	yes	-; good for fast substring() on big text/bytea
`MAIN`	yes	only as a last resort	numeric

ALTER TABLE docs ALTER COLUMN body SET STORAGE EXTERNAL;
ALTER TABLE docs ALTER COLUMN body SET COMPRESSION lz4;

SELECT id, pg_column_size(body) AS stored_bytes,
       pg_column_compression(body) AS method,
       octet_length(body) AS raw_bytes
  FROM docs LIMIT 5;

Compression

default_toast_compression is pglz unless changed; lz4 (since PostgreSQL 14, if the build supports it) is much faster to compress and decompress with similar ratios. The method is recorded per value, so changing the setting only affects newly written values. pglz gives up if it finds no repetition in the first 1 kB and requires a 25% saving; already-compressed data (images, gzip) is stored raw.

What to watch for

A row that cannot be made small enough fails with ERROR: row is too big: size 12832, maximum size 8160, typically on a PLAIN column or a table with hundreds of columns.
TOAST tables have their own autovacuum, freeze age and bloat. Find them: SELECT relname, reltoastrelid::regclass, pg_size_pretty(pg_relation_size(reltoastrelid)) FROM pg_class WHERE reltoastrelid <> 0 ORDER BY pg_relation_size(reltoastrelid) DESC;
Updating any other column of a row does not copy the toasted value: the new row version reuses the same pointer. Updating the big value itself writes all new chunks and leaves the old ones dead.
Missing chunks indicate corruption: ERROR: missing chunk number 0 for toast value 16461 in pg_toast_16456. pg_amcheck (which checks TOAST pointers by default) and verify_heapam(…, check_toast => true) help locate them.
SELECT * on a table with large values detoasts everything sent to the client. Select only the columns you need.

For developers: inside the source

Where it lives

heaptoast.c: heap_toast_insert_or_update(), heap_toast_delete(), heap_fetch_toast_slice().
toast_helper.c: ToastTupleContext and the steps toast_tuple_init(), toast_tuple_find_biggest_attribute(), toast_tuple_try_compression(), toast_tuple_externalize().
toast_internals.c (toast_compress_datum(), toast_save_datum(), toast_delete_datum()), detoast.c and toast_compression.c (pglz and lz4 wrappers).
varatt.h: the varlena header bits, varatt_external and macros like VARATT_IS_EXTERNAL_ONDISK() and VARATT_IS_COMPRESSED(); heaptoast.h defines TOAST_TUPLE_THRESHOLD, TOAST_TUPLE_TARGET_MAIN and TOAST_MAX_CHUNK_SIZE.
toasting.c: create_toast_table() builds pg_toast_<oid> and its index.

The toaster's four passes

heap_prepare_insert() and heap_update() call heap_toast_insert_or_update() when t_len > TOAST_TUPLE_THRESHOLD or the tuple already has external values. The target is toast_tuple_target minus the header. Each loop works on the biggest remaining attribute:

Compress EXTENDED values; one that is still bigger than the target on its own is moved out at once.
Move EXTENDED and EXTERNAL values out of line.
Compress MAIN values.
Move MAIN values out only if the row still exceeds TOAST_TUPLE_TARGET_MAIN.

toast_save_datum() gets a new value OID with GetNewOidWithIndex(), slices the data into TOAST_MAX_CHUNK_SIZE pieces, heap_insert()s each chunk and index_insert()s its key. It returns a varatt_external (va_rawsize, va_extinfo = stored size plus a 2-bit compression method, va_valueid, va_toastrelid) behind a 2-byte external header: TOAST_POINTER_SIZE = 18.

Reading it back

Functions call PG_DETOAST_DATUM() → pg_detoast_datum() → detoast_attr(). An on-disk pointer goes through toast_fetch_datum() → table_relation_fetch_toast_slice() → heap_fetch_toast_slice(), an ordered index scan on (chunk_id, chunk_seq) with get_toast_snapshot(); a compressed value then goes through toast_decompress_datum(). PG_DETOAST_DATUM_SLICE() / detoast_attr_slice() fetch only the chunks needed, which is why EXTERNAL makes substr() cheap.

For extension authors

The same header scheme carries in-memory pointers: VARTAG_INDIRECT and expanded objects (VARTAG_EXPANDED_RW). Always detoast before touching varlena bytes.
A table AM opts in with the relation_needs_toast_table, relation_toast_am and relation_fetch_toast_slice callbacks of TableAmRoutine.
Inspect with pg_column_size(), pg_column_compression(), pg_column_toast_chunk_id() (17+) and pageinspect's tuple_data_split(). Regression tests: src/test/regress/sql/compression.sql, indirect_toast.sql.

Parameters that shape this

default_toast_compression block_size