Postgres TOAST: How Large Values Are Stored (and Why It Affects Performance)

PostgreSQL stores table rows in fixed-size 8 KB pages, and a row can't span pages. So how does a column hold a 5 MB JSON document or a long article? Through TOAST — "The Oversized-Attribute Storage Technique".

How it works

When a row would be too big — roughly 2 KB (TOAST_TUPLE_THRESHOLD, about a quarter of a page) — Postgres tries, column by column:

1. Compress large values inline.
2. If the row is still too big, move values out of line into the table's TOAST table, split into chunks of about 2 KB, leaving a small pointer (~18 bytes) in the main row.

Each table with any TOAST-able column (text, varchar, bytea, jsonb, arrays…) has a hidden TOAST table in the pg_toast schema, with its own index on (chunk_id, chunk_seq).

Values up to 1 GB can be stored this way.

Storage strategies

Each column has a strategy:

Strategy	Compress	Move out of line
PLAIN	No	No (fixed-size types like integer)
MAIN	Yes	Only as a last resort
EXTERNAL	No	Yes
EXTENDED (default for most variable types)	Yes	Yes

ALTER TABLE documents ALTER COLUMN body SET STORAGE EXTERNAL;

EXTERNAL is useful for large values you'll read partially — substring() on uncompressed out-of-line text can fetch just the needed chunks — or values that don't compress (already-compressed images, encrypted blobs), where trying to compress wastes CPU.

Compression: pglz vs lz4

Since Postgres 14 you can choose the algorithm:

ALTER TABLE events ALTER COLUMN payload SET COMPRESSION lz4;
-- or server-wide:
SET default_toast_compression = 'lz4';

lz4 is much faster to compress and decompress than the default pglz, usually with similar ratios — a cheap win for JSON-heavy tables. Existing values keep their old compression until rewritten. (Postgres JSONB)

Measuring it

-- size of one value as stored (after compression)
SELECT pg_column_size(payload), octet_length(payload::text) FROM events LIMIT 5;

-- how much of a table lives in TOAST
SELECT pg_size_pretty(pg_relation_size('events'))                AS heap,
       pg_size_pretty(pg_relation_size(reltoastrelid))           AS toast,
       pg_size_pretty(pg_total_relation_size('events'))          AS total
FROM pg_class WHERE relname = 'events';

Performance consequences

SELECT * fetches everything

Reading a TOASTed value means extra lookups in the TOAST table plus decompression. SELECT * on a table with large body columns pays that cost even if your code only uses id and title. List the columns you need — this alone can make list endpoints dramatically faster. (ORMs that select whole rows by default are a common culprit.) (The N+1 query problem)

The "fast" path when values aren't touched

An UPDATE that doesn't change a TOASTed column reuses its existing out-of-line data — the big value isn't rewritten. Updating status on a row with a 1 MB document is cheap.

Big JSONB documents and partial updates

The opposite: changing one key inside a large jsonb value rewrites the entire value — decompress, modify, recompress, write new TOAST chunks, leave the old ones as dead tuples for VACUUM. Frequently updated large documents generate heavy WAL and bloat. Options:

• keep frequently changing fields in normal columns,
• split large documents into smaller rows,
• avoid storing ever-growing arrays in one value.

(Postgres VACUUM and bloat, Postgres WAL)

Detoasting repeatedly

A query that references the same TOASTed column several times (in WHERE, SELECT and a function) may detoast it more than once. Expression indexes and generated columns on extracted values avoid repeatedly decompressing big documents. (Postgres index types)

Planner blind spots

The planner doesn't account well for detoasting cost, so plans can look cheaper than they are when wide columns are involved. Check with EXPLAIN (ANALYZE, BUFFERS). (Reading EXPLAIN ANALYZE)

Should large files live in Postgres at all?

For documents and JSON your queries use, yes — TOAST handles them well. For files (images, PDFs, video), usually not: they bloat backups, replication and the buffer cache. Store them in object storage and keep the key in Postgres. (Where should user uploads go?, What is object storage?)

TOAST and VACUUM

TOAST tables are vacuumed along with their parent, and can bloat independently. If a table's TOAST relation is huge relative to the live data, frequent rewrites of large values are the usual reason.

---

EasySpawn gives each app its own Postgres and object storage on the same server, so big files go to buckets and Claude Code can find the SELECT * that's dragging megabytes of TOAST into every list page. See how it works or join the waitlist.

Related: Postgres JSONB · Postgres MVCC Explained · Postgres VACUUM and Table Bloat · Postgres Index Types