Postgres TOAST Tuning: Find and Fix Fat Columns

Postgres pages are 8 KB. Anything that does not fit gets moved out of the main row into a side table called TOAST — The Oversized-Attribute Storage Technique. TOAST is invisible to queries that do not touch the affected column, which makes it easy to forget about. It is also where some of the worst unexpected I/O comes from: a SELECT * on a table that stores 200 KB blobs reads every blob, even if the caller only wanted three small columns. This guide is the operator view: what TOAST is, where it bites you, how to spot fat-column tables, and when to split a column off the main row.

How Postgres decides to TOAST a value

Every variable-length column type (text, varchar, bytea, jsonb, numeric, geometry types, arrays) has a storage mode that decides what happens when the row exceeds TOAST_TUPLE_THRESHOLD (typically 2 KB). There are four modes:

• PLAIN — never compressed, never moved. Fixed-width types only. If you see this on a variable-length column you set it manually.
• EXTENDED (default for most variable types) — compress first; if still too big, move the value to the TOAST table.
• EXTERNAL — never compress, but move to TOAST. Cheaper writes and faster substring access for already- compressed payloads (gzipped JSON, images).
• MAIN — try to compress in place; only push to TOAST as a last resort. Use when the value is usually small-to-medium and almost always needed.

Check the storage mode on every column of a table:

SELECT
  a.attname AS column,
  pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
  CASE a.attstorage
    WHEN 'p' THEN 'plain'
    WHEN 'x' THEN 'extended'
    WHEN 'e' THEN 'external'
    WHEN 'm' THEN 'main'
  END AS storage
FROM pg_attribute a
WHERE a.attrelid = 'public.orders'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

Change storage at will — it only affects rows written after the change. Existing rows keep their current TOAST placement until updated or rewritten via VACUUM FULL:

ALTER TABLE orders ALTER COLUMN raw_payload SET STORAGE EXTERNAL;

Why TOAST hurts more than you expect

TOAST works fine as a passive size guard. It starts being a performance problem when the access pattern misaligns with the storage shape. Four common ways this shows up:

• SELECT * on a wide table. Even if the caller only needs id, status, fetching * forces every TOASTed column to be detoasted. Detoast is a join against pg_toast.pg_toast_NNN plus a decompression pass — easily 5–10× slower than the main-table seq scan suggests.
• Sorts and hash joins on fat columns. The executor materializes detoasted values into memory. A query that sorts by a 50 KB jsonb column burns work_mem and spills to disk at trivial result sizes.
• Index-only scan defeat. Even if every referenced column lives in the index, the visibility map might require a heap fetch. With a fat row, that heap fetch now includes detoasting columns the query did not name.
• WAL amplification. An UPDATE on a row whose total tuple size exceeds page free space cannot be a HOT update. Every TOASTed column update writes new chunks in the TOAST table plus the WAL record for them. A one-byte status flip can write 200 KB of WAL.

Finding TOAST-heavy tables

The diagnostic you want is the ratio of TOAST size to main relation size. Anything above ~30% is worth a closer look:

SELECT
  c.relname AS table,
  pg_size_pretty(pg_relation_size(c.oid))                            AS heap,
  pg_size_pretty(pg_relation_size(t.oid))                            AS toast,
  pg_size_pretty(pg_indexes_size(c.oid))                             AS indexes,
  pg_size_pretty(pg_total_relation_size(c.oid))                      AS total,
  round(100.0 * pg_relation_size(t.oid)
              / nullif(pg_total_relation_size(c.oid), 0), 1)          AS toast_pct
FROM pg_class c
JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relkind = 'r' AND c.reltoastrelid <> 0
ORDER BY pg_relation_size(t.oid) DESC
LIMIT 20;

High toast_pct on a table that is rarely sorted or SELECT *'d is benign. High toast_pct on a table the application reads constantly is your priority candidate.

Finding which column is fat

A table-level TOAST count does not name the column. Use pg_column_size on a sample:

SELECT
  avg(pg_column_size(raw_payload)) AS avg_payload_bytes,
  max(pg_column_size(raw_payload)) AS max_payload_bytes,
  avg(pg_column_size(metadata))    AS avg_metadata_bytes,
  max(pg_column_size(metadata))    AS max_metadata_bytes
FROM orders TABLESAMPLE SYSTEM (1);

TABLESAMPLE SYSTEM (1) reads about 1% of pages without scanning the whole table. pg_column_size returns the on-disk size including TOAST overhead, so it is the right number to compare against row width.

Three fixes, in order of cost

1. Stop SELECT *

The cheapest fix: never select a TOASTed column unless the caller needs it. Most ORMs let you specify columns at the query level (SQLAlchemy .options(load_only(...)), Django's .only(), Rails's .select(:id, :status)). If the application code does SELECT * and the column is unused 90% of the time, narrow the query and move on.

2. Adjust storage mode

For already-compressed payloads (gzipped JSON, base64 images, encrypted blobs), switch from EXTENDED to EXTERNAL:

ALTER TABLE orders ALTER COLUMN raw_payload SET STORAGE EXTERNAL;

Postgres no longer wastes CPU trying to compress uncompressible data. Substring access (substring(), slicing JSONB without parsing the whole document) also gets faster because the executor can read just the chunks it needs instead of detoasting the whole value.

For values that almost always fit on the page but occasionally spill, switch from EXTENDED to MAIN — it keeps small values inline and only pushes the rare large one to TOAST.

3. Split the column off the row

When the same column is fat and queried separately from the rest of the row, move it to a sidecar table:

CREATE TABLE orders_payload (
  order_id    bigint PRIMARY KEY REFERENCES orders(id) ON DELETE CASCADE,
  raw_payload bytea NOT NULL
);

INSERT INTO orders_payload (order_id, raw_payload)
SELECT id, raw_payload FROM orders;

ALTER TABLE orders DROP COLUMN raw_payload;

What you have gained: SELECT * FROM orders no longer touches the blob. Index-only scans on orders stay narrow. The blob lives in its own heap with its own vacuum cadence. Reads that need the blob join on the primary key — one B-tree probe.

What you have given up: every blob read now costs a join. Plan for this only when the blob-read share is under ~30% of total queries on the table; above that, the join overhead overwhelms the I/O savings.

tuple_data_split: see exactly what TOAST is storing

The pageinspect extension lets you peek at a single tuple's layout, including which columns were externalized:

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT
  t_xmin, t_xmax,
  array_length(tuple_data_split('orders'::regclass, t_data, t_infomask, t_infomask2, t_bits), 1) AS cols
FROM heap_page_items(get_raw_page('orders', 0))
LIMIT 5;

For each tuple, tuple_data_split returns NULL for columns that were externalized to TOAST. Counting NULLs against non-NULLs tells you the on-page vs in-TOAST split at the individual-row level — useful when one customer has 10× larger payloads than the rest and you want to confirm where their data lives.

Compression: lz4 vs pglz

Postgres 14+ supports two compression algorithms for TOAST:pglz (the old default) and lz4 (faster compress and decompress, slightly worse ratio). Switch the default for new TOAST writes:

-- cluster-wide
ALTER SYSTEM SET default_toast_compression = 'lz4';
SELECT pg_reload_conf();

-- per-column
ALTER TABLE orders ALTER COLUMN raw_payload SET COMPRESSION lz4;

lz4 is usually the right default on read-heavy workloads — decompression CPU dominates the access path, and lz4 decompresses 2–4× faster than pglz. The compression-ratio difference is single-digit percent on most JSON workloads.

Quick checklist

• List TOAST sizes per table; investigate any toast_pct > 30%.
• Sample pg_column_size() to identify which column is fat.
• Stop SELECT * on wide tables before touching storage modes.
• Switch to EXTERNAL for already-compressed payloads to skip pointless compression CPU.
• Split a column to a sidecar table only when it is fat AND read on a minority of queries.
• Default new TOAST writes to lz4 on Postgres 14+; the decompression speed pays for itself.

Related: MVCC and HOT updates · Index bloat · Reading EXPLAIN plans