Postgres TOAST: How It Works, Bloats, and Slows Queries

2026-08-07

Postgres TOAST: How It Works, Bloats, and Slows Queries

Postgres can't fit a value larger than about 8 kB into a single table page, so it compresses and chunks oversized values into a side table called TOAST, then reassembles them on read. That mechanism drives storage strategy choices, jsonb query performance, and a specific kind of bloat that hides from normal size checks. Here's exactly how it works and how to keep it under control.

▶ Watch on YouTube: TOAST: How Postgres Stores Your Big Values

Postgres TOAST: How It Works, Bloats, and Slows Queries

The 8 kB problem

Postgres stores table data in fixed-size pages. On a default build that page is 8,192 bytes, set at compile time via BLCKSZ, and a heap tuple cannot span more than one page. That single rule is the reason everything in this article exists.

Do the arithmetic and the problem is obvious. You want to store a 50 kB JSON document in a column. The largest tuple that fits on a page is a little under 8 kB. There is no version of "write it inline" that works. Postgres either refuses the write or finds somewhere else to put the value, and it chose the second option decades ago.

The mechanism is called TOAST, The Oversized-Attribute Storage Technique. The acronym is a joke from the 8.x era and I'll leave it there.

If you want the four-minute whiteboard version, watch the companion video. The rest of this page is the depth the video can't carry: the exact constants, the SQL, and the three operational failure modes that actually page people at 3am.

What TOAST actually does: compress, then chunk

When Postgres is about to write a heap tuple, it measures the assembled row. If the total width exceeds TOAST_TUPLE_THRESHOLD (normally about 2 kB, precisely 2,032 bytes on a default 8 kB-page build), the TOAST machinery runs.

It works in passes, always attacking the widest attribute first:

1. Compress the widest TOAST-able attribute in place. 2. Re-measure. If the row now fits under TOAST_TUPLE_TARGET (same ~2 kB by default), stop. 3. Otherwise take the widest remaining attribute and move it out-of-line entirely. 4. Repeat until the row fits, or until no attribute is left that can help.

Here is the nuance that trips up most people, and it's worth stating plainly: the trigger is total tuple width, not column width.

A 1.5 kB jsonb document sitting in a narrow row with three integers alongside it will never be compressed and never be TOASTed. It sits in the heap, uncompressed, at full width. Put that same 1.5 kB document in a row that also carries a 900-byte description and a 400-byte URL, and suddenly the row crosses 2,032 bytes, TOAST fires, and your jsonb gets compressed.

Same value. Different physical fate. I have watched teams spend an afternoon confused by exactly this, comparing pg_column_size across two tables and concluding Postgres was broken.

Once a value is pushed out-of-line, it is split into chunks of at most TOAST_MAX_CHUNK_SIZE bytes: 1,996 with the default block size. That number is not arbitrary. It is sized so that exactly four chunk rows fit on a TOAST-table page with room for the page header and line pointers.

The anatomy of a TOAST table

Every table with at least one TOAST-able column gets a companion table in the pg_toast schema, named pg_toast.pg_toast_<oid> after the owning table's OID. pg_class.reltoastrelid is the link.

The structure is fixed and boring, which is a virtue:

| column | type | purpose | |---|---|---| | chunk_id | oid | identifies the logical value | | chunk_seq | integer | ordering within the value | | chunk_data | bytea | up to 1,996 bytes of payload |

There is a unique index on (chunk_id, chunk_seq). That index is how Postgres reassembles a value: seek to the first chunk, scan forward, concatenate, decompress.

Back in the main tuple, the wide value is replaced by an 18-byte TOAST pointer holding the chunk_id, the total size, and the raw (uncompressed) size. Eighteen bytes instead of 50 kB. That is the whole trick.

Find any table's TOAST relation and its size:

SELECT
    c.relname                                   AS table_name,
    t.relname                                   AS toast_name,
    pg_size_pretty(pg_relation_size(c.oid))     AS heap_size,
    pg_size_pretty(pg_total_relation_size(t.oid)) AS toast_total
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND c.reltoastrelid <> 0
ORDER BY pg_total_relation_size(t.oid) DESC NULLS LAST;

Note the trap in that query, because it bites people in capacity planning: pg_relation_size() reports only the main fork and excludes the TOAST table. pg_table_size() includes TOAST plus the free space map and visibility map. pg_total_relation_size() adds all indexes on top. If your dashboard graphs pg_relation_size, a 300 GB TOAST table is invisible to you.

The four storage strategies, and when each is right

| Strategy | attstorage | Compress? | Out-of-line? | Use when | |---|---|---|---|---| | PLAIN | p | no | no | Fixed-width types that can't be TOASTed at all (int, timestamp, uuid). Not a choice you make. | | EXTENDED | x | yes | yes | The default for text, jsonb, bytea. Correct roughly 95% of the time. | | EXTERNAL | e | no | yes | Already-compressed payloads, and large text/bytea you read with substring(). | | MAIN | m | yes | prefer not | Rarely. See below. |

EXTENDED is the default for most TOAST-able types and it is the right default. Compress first, push out-of-line only if that wasn't enough. For ordinary text and jsonb it wins on storage, on I/O, and usually on wall-clock time.

EXTERNAL has one genuinely good use case that gets under-reported. Postgres is optimised to fetch only the chunks it needs from an out-of-line value when that value is not compressed. So substring(document, 1, 200) against an EXTERNAL bytea reads one chunk. The same call against an EXTENDED value has to fetch every chunk and decompress the whole thing before it can slice. If you serve previews or range reads over large blobs, EXTERNAL is a real optimisation, not a micro-one.

MAIN is over-prescribed. Blog posts recommend it as "keep it inline for speed," and the recommendation quietly ignores the important part: MAIN is a preference, not a guarantee. If Postgres cannot make the row fit on a page any other way, it pushes the MAIN value out-of-line regardless. You end up with a strategy that behaves like EXTENDED under exactly the conditions you were trying to avoid, plus fatter heap pages the rest of the time, which means fewer rows per page and more I/O on the scans that don't touch the wide column at all. I set MAIN maybe once every two years, usually on a small, well-under-a-kilobyte enum-like text column where I've already measured that it never grows.

If you're reaching for MAIN to solve a performance problem, you've usually misdiagnosed it. Nine times out of ten the culprit is a query filtering on a jsonb key across a million rows, and no storage strategy will fix that. See the read-path section below.

Inspecting what you've actually got

Start with the catalog. On PG14 and later this also shows the per-column compression method:

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

Top 20 TOAST tables across the database, which is the query I run first on any unfamiliar estate:

SELECT
    n.nspname || '.' || c.relname                  AS owning_table,
    t.relname                                      AS toast_table,
    pg_size_pretty(pg_relation_size(t.oid))        AS toast_heap,
    pg_size_pretty(pg_relation_size(ti.indexrelid)) AS toast_index,
    t.reltuples::bigint                            AS approx_chunks
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_class t     ON t.oid = c.reltoastrelid
LEFT JOIN pg_index ti ON ti.indrelid = t.oid
WHERE c.relkind = 'r'
ORDER BY pg_relation_size(t.oid) DESC
LIMIT 20;

Now measure real compression. pg_column_size() gives on-disk bytes including the varlena header and any compression applied. octet_length() gives the logical, uncompressed size. Comparing them is the fastest honest measurement you can take:

SELECT
    id,
    octet_length(metadata)                        AS logical_bytes,
    pg_column_size(metadata)                      AS stored_bytes,
    round(100.0 * pg_column_size(metadata)
          / nullif(octet_length(metadata),0), 1)  AS pct_of_original
FROM events
ORDER BY octet_length(metadata) DESC
LIMIT 5;

A real result from an event-log table I looked at recently:

   id    | logical_bytes | stored_bytes | pct_of_original
---------+---------------+--------------+-----------------
 8814402 |         41203 |        11876 |            28.8
 8814318 |         39885 |        11402 |            28.6
 8813997 |         38610 |        11121 |            28.8

Roughly 3.5x. That is typical for machine-generated JSON with repeated key names. If you see numbers near 100%, your payload is already compressed and EXTENDED is burning CPU for nothing.

Get a rough count of rows that likely went out-of-line (a heuristic based on the threshold, not an exact read of the varlena header, but good enough for triage):

SELECT
    count(*) FILTER (WHERE pg_column_size(metadata) > 2032) AS likely_external,
    count(*) AS total
FROM events;

Changing storage strategy and compression

-- Storage strategy (future writes only)
ALTER TABLE events ALTER COLUMN payload SET STORAGE EXTERNAL;

-- Compression method, PG14+ (future writes only)
ALTER TABLE events ALTER COLUMN metadata SET COMPRESSION lz4;

-- Global default for new columns, PG14+
ALTER SYSTEM SET default_toast_compression = 'lz4';

-- Raise or lower the TOASTing threshold for one table (128..8160)
ALTER TABLE events SET (toast_tuple_target = 4096);

-- Independent autovacuum tuning for the TOAST relation
ALTER TABLE events SET (
    toast.autovacuum_vacuum_scale_factor = 0.02,
    toast.autovacuum_vacuum_cost_limit   = 2000
);

The gotcha, and it is the single most common misunderstanding in this whole topic: SET STORAGE and SET COMPRESSION affect only subsequently written values. Existing rows keep their current physical representation forever, or until something rewrites them.

So switching a 400 GB table to lz4 changes nothing today. You need an actual rewrite:

VACUUM FULL events; — full rewrite, ACCESS EXCLUSIVE lock for the duration, needs free space equal to the new table.
pg_repack — same outcome, brief locks at start and end. This is what I use in production.
A no-op type change like ALTER TABLE events ALTER COLUMN metadata TYPE jsonb USING metadata; — also a full rewrite under ACCESS EXCLUSIVE, no advantage over the above.
An UPDATE events SET metadata = metadata WHERE ... in batches. Slower and generates WAL, but online. Watch the bloat it creates.

Compression: pglz vs lz4

The default pglz has two guardrails worth knowing. It refuses inputs smaller than 32 bytes outright. And it discards the compressed result unless it came out at least 25% smaller than the input. That second rule is why Postgres never bloats poorly-compressing data: it tries, fails to hit the ratio, and stores the original.

The cost of a failed attempt is CPU, and it is not free. If your column holds JPEGs, gzip blobs, or ciphertext from application-side encryption, every single write burns compression cycles to produce nothing. Set those columns to EXTERNAL and move on.

PostgreSQL 14 added LZ4, controlled by default_toast_compression (pglz or lz4) and per-column via SET COMPRESSION. The server must be built --with-lz4; check with SHOW default_toast_compression; and try setting it, because a build without LZ4 will reject the value.

My general position: LZ4 compresses somewhat worse and decompresses several times faster. For jsonb that gets read constantly and written once, that trade is good. On the event table above, moving to lz4 took stored size from 28.8% of logical to about 34%, and cut the detoast portion of a wide scan noticeably. More disk, less CPU. On a read-heavy workload that is the right direction. On a write-heavy table where insert CPU is the bottleneck rather than disk, the same trade is even more clear-cut: less compression work per write, at a modest storage cost.

The read path: what actually triggers a detoast

This is the section that matters most, and the one that explains the majority of TOAST-related incidents I get called into.

Three queries against the same table:

SELECT id, created_at FROM events WHERE id = 42;
-- No detoast. The 18-byte pointer is never followed.

SELECT metadata FROM events WHERE id = 42;
-- Full detoast: index scan on the toast index, N chunk fetches, decompress.

SELECT count(*) FROM events WHERE metadata->>'status' = 'active';
-- Detoast every examined row, one at a time, BEFORE the filter can be evaluated.

The third one is the killer. jsonb operators such as -> and ->> operate on the fully detoasted datum. There is no partial-detoast path for jsonb. To evaluate metadata->>'status', Postgres must reassemble every chunk of that document and decompress the whole thing, for every row the scan touches, only to throw away 95% of the result. The same applies to sorts, hashes, and joins keyed on a toasted column — any operation that needs to compare or materialize the actual value forces a full detoast, not just a read of the pointer.

The evidence shows up plainly in buffers:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM events WHERE metadata->>'status' = 'active';

 Aggregate (actual time=18432.115..18432.116 rows=1 loops=1)
   Buffers: shared hit=1204416 read=88231
   ->  Seq Scan on events (actual time=0.812..18016.443 rows=41288 loops=1)
         Filter: ((metadata ->> 'status'::text) = 'active'::text)
         Rows Removed by Filter: 958712
         Buffers: shared hit=1204416 read=88231
 Planning Time: 0.144 ms
 Execution Time: 18432.190 ms

The heap for that table is about 61,000 pages. The query touched 1.29 million buffers. The difference is TOAST chunk and TOAST index traffic, flowing through shared_buffers like any other heap and index access. pg_statio_all_tables will corroborate it with toast_blks_read and tidx_blks_read climbing in lockstep.

Storage strategy won't help here. What fixes it is an expression index on the extracted key, or a generated column holding status as plain text, so the filter never has to open the document. Don't make every scan pay the detoast tax for a value it's about to throw away.

The write path: update amplification

Two facts sit in tension, and you need both.

First, the good news. An UPDATE that does not modify a toasted column reuses the existing TOAST pointer. No new chunks are written, the old chunks are not duplicated, and the TOAST table sees zero churn. Updating a last_seen_at timestamp on a row carrying a 50 kB document is genuinely cheap on the TOAST side.

Second, the bad news. The moment the toasted column's own value changes, even by one key, Postgres recompresses and re-chunks the entire value. Every old chunk becomes dead. There is no partial rewrite.

Worked example. A 40 kB jsonb document compresses to about 12 kB, which at 1,996 bytes per chunk is 7 chunk rows plus 7 toast index entries. Your service updates one status field 10 times an hour:

10 updates/hour × 24 = 240 rewrites per row per day
240 × 12 kB = 2.9 MB of dead TOAST per row per day
240 × 7 = 1,680 dead chunk rows per row per day

Now multiply by 500 hot rows: roughly 1.4 GB of dead space generated daily in a TOAST table that nobody is monitoring. Autovacuum has to keep up with that, on top of the heap.

The structural fix is to stop storing volatile fields inside the large document. Split the hot keys into their own narrow columns. The document becomes write-once, and update amplification drops to zero.

TOAST bloat: the part that shows up as a disk alert

A TOAST table is an ordinary heap. It bloats like one, and it needs vacuum like one.

What makes it dangerous is invisibility. It lives in a separate schema under a machine-generated name. It is vacuumed by autovacuum as a separate relation with its own statistics, so it competes for autovacuum workers independently of its parent, using the global autovacuum_vacuum_scale_factor by default — a threshold that assumes a much lower churn rate than a hot jsonb column actually produces. And, as noted above, pg_relation_size() on the parent table doesn't show it, so n_dead_tup on the parent can look perfectly healthy while the TOAST relation quietly fills the disk.

The war story: a customer's disk alert fired at 89% on a 2 TB volume. Their largest table by pg_relation_size was 140 GB and the numbers didn't add up. pg_toast_16482 was 610 GB with roughly 70% dead. The parent held a jsonb column updated on every webhook delivery, exactly the amplification pattern above, and autovacuum on the TOAST relation had been starting, hitting the cost limit, and getting preempted for weeks.

What to do:

Monitor TOAST relations by name and size, not just parent tables. See our post on disk capacity planning for Postgres for the alerting thresholds I use.
Tune autovacuum for the TOAST relation directly with toast.autovacuum_vacuum_scale_factor and friends. For a large, churny TOAST table, a scale factor of 0.02 only sounds aggressive until you look at the churn rate — that's what realistic tuning looks like there. More on the general approach in autovacuum tuning that actually holds.
Raise toast_tuple_target if your rows sit just over 2,032 bytes and you'd rather keep them inline. Range is 128 to 8160.
To reclaim: pg_repack on the parent table rebuilds the TOAST table too, which is the option I reach for. VACUUM FULL works and takes an ACCESS EXCLUSIVE lock. If only the toast index is bloated, REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_16482_index is cheap and often enough. Broader treatment in table bloat: measuring it properly.

Edge cases worth knowing before they bite

1 GB per value, hard. Imposed by the varlena length header. You cannot store a 2 GB file in a bytea column, no matter how you configure storage.

Btree can't TOAST. Index tuples may be compressed but can never be stored out-of-line. That is why indexing a wide text column fails with index row size ... exceeds btree version 4 maximum 2704 for index, about a third of a page. The answer is a hash of the value, a prefix index on left(col, 200), or a GIN index if you actually need substring search.

Logical replication and COPY both detoast values on the way out, resolving pointers before data leaves the source. A replica of a TOAST-heavy table costs real CPU on the publisher, and COPY TO of a compressed jsonb column emits the full logical size, not the stored size. Size your network and your dump windows for octet_length, not pg_column_size.

PLAIN-only types exist. Fixed-width types like integer, timestamptz and uuid are marked p and cannot be TOASTed. Trying to SET STORAGE EXTENDED on them is a no-op at best.

A practical checklist

Ten minutes, any estate:

1. Rank TOAST tables by size with the reltoastrelid query above. Anything in your top 10 that you didn't know existed is your starting point. 2. Compare pg_relation_size against pg_table_size for your biggest tables. A large gap is TOAST you weren't accounting for. 3. Sample pg_column_size vs octet_length on every wide column. Ratios near 100% mean you're compressing already-compressed data. Switch those to EXTERNAL. 4. Grep your slow query log for ->> and -> inside WHERE clauses, ORDER BY, or GROUP BY on large tables. Each one is a per-row full detoast. Add an expression index or a generated column. 5. Check pg_stat_all_tables for the pg_toast schema. Look at last_autovacuum and n_dead_tup on the TOAST relations themselves, not just the parents. 6. For any jsonb column updated more than a few times per row per day, calculate the amplification. Compressed size × updates per day × row count. If that number is in gigabytes, restructure the schema.

That checklist is exactly what we automated. MyDBA's free health check walks your catalog and stats, flags TOAST tables that have outgrown their parents or fallen behind on autovacuum, and surfaces the detoast-heavy queries from pg_stat_statements alongside their buffer profiles. Same queries, run continuously, with the thresholds already calibrated.

The physical constraint has not changed since 8 kB pages were chosen. What changes is whether you can see what it's doing to your database.