toast_tuple_target: Shrinking PostgreSQL Heap Pages for Scan-Heavy Tables

The query was innocent: SELECT id, occurred_at, kind, actor FROM events WHERE tenant_id = 42 AND occurred_at over a day range, feeding a usage dashboard. It ran against a 340 GB table, the index on (tenant_id, occurred_at) existed and was used, and yet EXPLAIN (ANALYZE, BUFFERS) showed 4.1 million buffer hits for a scan that returned ninety thousand rows of narrow columns. The rows, however, were not narrow. Each carried a payload jsonb averaging 1.4 KB — full of base64 blobs that compress terribly — and the dashboard never selected it. PostgreSQL was hauling that payload through shared_buffers on every heap fetch, because it lived inline, inside the same heap page as the columns we actually wanted.

The fix was one table parameter: ALTER TABLE events SET (toast_tuple_target = 128), followed by a rewrite to make it stick. The scan’s buffer count dropped 71 percent, the table’s heap shrank by two-thirds, and vacuum time fell with it. This is how TOAST’s size threshold actually works, when lowering it wins, and what it costs you on the queries that do read the payload.

What does toast_tuple_target actually control?

It controls how hard PostgreSQL tries to squeeze a row before giving up and storing it wide — specifically, the target row length the toaster aims for when deciding which varlena values to compress and push out-of-line into the table’s TOAST relation. The default is 2032 bytes, roughly a quarter of an 8 KB page, and the strategy is greedy from the widest attribute: while the row exceeds the target, take the largest inline-compressible value, compress it; if still too big, move values out to TOAST; stop when the row fits the target or nothing more can move. The subtlety that made our table pathological is that "stop when the row fits" — a 500-byte row of hot columns plus a 1.4 KB payload is about 1.9 KB, already under 2032, so the toaster does nothing at all and the payload sits inline, compressed at best. At 1.9 KB per tuple you get about four rows per heap page. Drop the target to the 128-byte minimum and the payload is forced out-of-line into the TOAST relation, the heap tuple collapses to its 500 hot bytes plus a pointer, and suddenly sixteen rows share a page. The mechanics of out-of-line storage itself — the TOAST table, its index, the chunking — are covered in the TOAST large values notes; toast_tuple_target is the knob that decides what crosses that line.

Why did inline payloads make our scans so expensive?

Because buffer traffic is denominated in pages, and every page spent on payload bytes is a page not spent on rows. The dashboard query fetched ninety thousand heap tuples to read columns totaling under 500 bytes each; at four tuples per page that is about 4.1 million… over the full range the planner chose a scan over much of the tenant’s history, and the page count scaled with the payload nobody read. The same tax showed up everywhere the heap was walked: seq scans for the nightly aggregation, autovacuum’s heap pass, even index visits whose heap fetches dragged in pages that were eighty percent payload. The telling measurement was comparing the heap against what the hot columns alone would occupy:

SELECT pg_relation_size('events')            AS heap_bytes,
       pg_relation_size('events', 'toast')   AS toast_bytes_before,
       avg(pg_column_size(t.*))::int         AS avg_full_row,
       avg(pg_column_size(t.id) + pg_column_size(t.occurred_at)
         + pg_column_size(t.kind) + pg_column_size(t.actor))::int AS avg_hot_columns
FROM events t
WHERE occurred_at > now() - interval '7 days';

Full row averaged 1.9 KB; the four hot columns averaged 480 bytes. Three quarters of every heap page was payload for queries that selected it maybe one request in fifty. That ratio is the entire decision: when the bytes a workload reads are a small fraction of the bytes a row stores, the inline layout is wrong for the workload, and the fix is to move the cold bytes out of the hot pages.

What did toast_tuple_target = 128 change in practice?

It rebalanced the table from "everything inline" to "hot inline, cold out-of-line," and the numbers moved exactly as the page arithmetic predicts. After the change and a rewrite, heap size fell from 340 GB to 118 GB while the TOAST relation grew to hold the evicted payloads; the dashboard query’s buffer count dropped 71 percent; shared_buffers hit ratio for the table climbed past 98 percent because the working set finally fit. The knob is set per table and takes effect on newly written and updated rows — existing rows keep their layout until rewritten, so the change needs a compaction pass to matter. Ours went through the pg_repack online rebuild rather than an ACCESS EXCLUSIVE rewrite, applied in batches over two nights:

ALTER TABLE events SET (toast_tuple_target = 128);

-- Confirm the reloption landed
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'events';

-- After the rewrite, verify the new balance
SELECT pg_size_pretty(pg_relation_size('events'))          AS heap,
       pg_size_pretty(pg_relation_size('events', 'toast')) AS toast;

The costs are real and belong in the decision. Reads that do select payload now pay a TOAST index lookup plus extra page reads per value — our detail-view endpoint got about 15 percent slower, which we accepted because it runs two orders of magnitude less often than the dashboard scan. Inserts and payload updates pay the out-of-line write every time, which interacts with the HOT-update mechanics from the HOT updates and fillfactor notes: a payload change is a large new TOAST chunk set, and the old chunks become dead rows in the TOAST relation that autovacuum must clean, so watch bloat on both relations after the change, with the measurement queries from the table bloat measurement guide.

When should you not touch the knob?

When the wide column is hot, when the table is small, or when the real problem is elsewhere. If most queries select the payload — a document store where the document is the point — pushing it out-of-line adds the TOAST hop to every read and you have made the common case slower; for those tables the question is compression algorithm and chunk geometry, not the target. If the table is a few gigabytes, the page-density win is real but operationally irrelevant, and a rewrite is risk without reward. And if the slow scan is slow because of a missing index, toast_tuple_target is an expensive way to avoid writing one — the four-to-sixteen-tuples-per-page density math only pays when the workload genuinely walks the heap or touches most of an index’s heap side. My shortlist for candidacy: rows with a large rarely-selected text or jsonb attribute, high-entropy content that compresses poorly, and a dominant access pattern over the narrow columns. The events table hit all three. Most tables hit none, which is why the default of 2032 is right almost everywhere and this knob earns its keep as a targeted exception.

Watching page density with MonPG

The symptom that started this — a query reading millions of buffers for ninety thousand rows — is a buffer-profile anomaly before it is a storage decision. MonPG monitors PostgreSQL in production today, and its PostgreSQL monitoring tracks per-statement buffer and latency profiles alongside table growth, so the week the payload crept from 800 bytes to 1.4 KB shows up as the dashboard query’s buffer count climbing while its row count stays flat. Catch the drift on the dashboard, then reach for the reloption with the arithmetic already done.

Related documentation

• PostgreSQL: TOAST storage