TOAST: How PostgreSQL Stores Oversized Values

The one thing to understand first

A tuple must fit within a single 8KB page — PostgreSQL does not span a row across blocks. So how does a text column hold a megabyte? The answer is TOAST (The Oversized-Attribute Storage Technique), implemented in src/backend/access/common/toast_internals.c and detoast.c. When a row would be too big, TOAST compresses and/or moves large field values out of the main tuple.

Your wide columns may not live in your table at all — they live in a hidden side table, fetched only when you touch them. Once you know that, a "small" table with a huge on-disk footprint, a slow SELECT * that only needed an integer, and mysterious TOAST bloat all stop being mysteries.

varlena: the variable-length header

Variable-length types (text, bytea, jsonb, arrays, numeric) begin with a varlena header. The header encodes the total length and a flag indicating whether the value is stored inline, compressed, or as an out-of-line pointer. Reading such a value always passes through detoast_attr(), which transparently fetches and decompresses as needed — callers just see the full value.

The threshold and the four strategies

When a tuple's size approaches TOAST_TUPLE_THRESHOLD (~2KB, a quarter page), the TOAST machinery acts on the largest TOAST-able attributes according to each column's storage strategy:

• PLAIN — never TOASTed (used by fixed-length types).
• EXTENDED (default for most varlena) — try compression first, then move out-of-line if still too big.
• EXTERNAL — move out-of-line without compression (faster substring access on large bytea).
• MAIN — compress but keep inline if at all possible.

-- Inspect and change a column's storage strategy
SELECT attname, attstorage FROM pg_attribute
WHERE  attrelid = 'documents'::regclass AND attnum > 0;

ALTER TABLE documents ALTER COLUMN body SET STORAGE EXTERNAL;

attname  | attstorage 
----------+------------
 id       | p
 body     | x
 metadata | x
(3 rows)

The TOAST relation

Out-of-line values do not live in the main table. Each table with TOAST-able columns gets a companion TOAST table (named pg_toast.pg_toast_) plus its own index. A large value is sliced into TOAST_MAX_CHUNK_SIZE pieces, each stored as a row in the TOAST table keyed by a value OID and chunk sequence number. The main tuple keeps only a small TOAST pointer (varatt_external) recording the OID, total size, and the owning TOAST relation.

-- Find a table's TOAST relation and its size
SELECT c.relname AS table,
       t.relname AS toast_table,
       pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
FROM   pg_class c
JOIN   pg_class t ON c.reltoastrelid = t.oid
WHERE  c.relname = 'documents';

table   |  toast_table   | toast_size 
-----------+----------------+------------
 documents | pg_toast_18364 | 0 bytes
(1 row)

Compression: pglz and LZ4

Historically TOAST used the built-in pglz algorithm. Modern PostgreSQL also supports LZ4 (and the WAL can use it too), typically faster with comparable ratios. The default is set by default_toast_compression; per-column overrides are possible:

ALTER TABLE documents ALTER COLUMN body SET COMPRESSION lz4;

Layer 3 — Watch it happen on your own database

-- Compare main-table size to its TOAST table size
SELECT c.relname AS table,
       pg_size_pretty(pg_relation_size(c.oid))            AS main_size,
       t.relname AS toast_table,
       pg_size_pretty(pg_relation_size(t.oid))            AS toast_size
FROM   pg_class c
JOIN   pg_class t ON c.reltoastrelid = t.oid
WHERE  c.relname = 'documents';

-- See each column's storage strategy
SELECT attname, attstorage
FROM   pg_attribute
WHERE  attrelid = 'documents'::regclass AND attnum > 0;

table   | main_size |  toast_table   | toast_size 
-----------+-----------+----------------+------------
 documents | 304 kB    | pg_toast_18364 | 0 bytes
(1 row)

 attname  | attstorage 
----------+------------
 id       | p
 body     | e
 metadata | x
(3 rows)

Insert a few rows with a multi-kilobyte text value and re-run the first query: the main table barely grows while the TOAST table balloons — proof the value was moved out-of-line. The attstorage codes (p=plain, e=external, x=extended, m=main) tell you exactly how each column will be handled.

Layer 4 — The levers this hands you

• **Avoid SELECT * on wide tables.** Detoasting costs extra TOAST-table reads and decompression; project only the columns you need.
• Choose a storage strategy per column. SET STORAGE EXTERNAL skips compression for large binaries you read in ranges; MAIN keeps compressible values inline.
• Pick a compression codec. ALTER ... SET COMPRESSION lz4 (or default_toast_compression = lz4) is usually faster than pglz with comparable ratios.
• Vacuum the TOAST table too. Heavy updates to large values churn the TOAST relation; it has its own autovacuum needs and can bloat independently.
• Model hot fields as real columns. A whole-document jsonb update rewrites the entire value; pull frequently-updated keys out into ordinary columns.

Layer 5 — What an Oracle DBA should expect vs what they get

TOAST is PostgreSQL's answer to the same problem Oracle solves with LOBs and row chaining:

• No LOB locators, no separate LOB API. Oracle stores large objects as BLOB/CLOB with their own storage clauses and locators. In Postgres a text/bytea/jsonb column just works; TOAST handles out-of-line storage transparently via detoast_attr() — there is no separate LOB type to manage.
• Out-of-line, not row chaining. Oracle chains or migrates oversized rows across blocks; Postgres never spans a tuple across pages — it slices the big attribute into chunks in a hidden TOAST table. The diagnosis ("why is this table huge / slow") is different as a result.
• Compression is built in and per-column. Oracle Advanced Compression is a licensed option; TOAST compression (pglz/LZ4) is free and default, with per-column overrides.
• The 2KB threshold is automatic. There is no PCTFREE-style tuning for when to externalise — TOAST triggers near TOAST_TUPLE_THRESHOLD automatically based on the storage strategy.

Key takeaway

When a tuple approaches ~2KB, TOAST compresses and/or moves its largest varlena attributes into a companion pg_toast.* table, leaving only a small pointer in the main row; reads transparently reassemble the value through detoast_attr(). This is why wide columns inflate a hidden table, why SELECT * can be slow even when the row "looks" small, and why TOAST tables need their own vacuuming. Control it with storage strategy, compression codec, and disciplined column projection.