PostgreSQL stores tables in fixed-size pages — commonly 8 kB — and a row is not allowed to span two pages. That one constraint forces an entire secondary storage system into existence: TOAST, "The Oversized-Attribute Storage Technique," which compresses wide values and, if that's not enough, evicts them into a hidden side table in ~2,000-byte chunks.
Most engineers know TOAST exists. What's less known is that its trigger threshold — about 2 kB of row width — quietly splits your schema into two performance regimes, and the slow one is not the one with the big values. A 500,000-row table of 1,800-character strings can lose a sequential scan to the same table holding 4,096-character strings, because the shorter strings are just small enough to stay inline and bloat every page the scan has to read.
What actually happens when a row exceeds 2 kB
TOAST only runs when a row is wider than TOAST_TUPLE_THRESHOLD, normally 2 kB. When it does, it processes the row's compressible columns until the row fits under TOAST_TUPLE_TARGET — also 2 kB by default, adjustable per table:
ALTER TABLE tbl SET (toast_tuple_target = 4096);Each column has one of four storage strategies, settable with ALTER TABLE ... ALTER COLUMN ... SET STORAGE:
| Strategy | Compression | Out-of-line | Default for |
|---|---|---|---|
PLAIN | no | no | non-TOASTable types (e.g. integer) |
EXTENDED | yes, tried first | yes, if still too big | most TOASTable types, incl. text |
EXTERNAL | no | yes | — |
MAIN | yes | only as a last resort | — |
Under the default EXTENDED strategy, Postgres tries compression first. The compressor is pglz unless the column was declared with a COMPRESSION option or default_toast_compression says otherwise; lz4 is also supported when the server was built with --with-lz4. Whether compression helps depends entirely on the bytes: in Haki Benita's experiments, a 10 kB string of random characters didn't compress at all, the same string drawn from only three distinct digits compressed to about 3 kB, and a single repeated character compressed so well the value stayed inline in the main table.
If the value is still too big after compression, it goes out of line. Every table with TOASTable columns gets an associated TOAST table (its OID lives in pg_class.reltoastrelid). The oversized value is sliced into chunks of at most TOAST_MAX_CHUNK_SIZE — chosen so four chunk rows fit on a page, about 2,000 bytes — and each chunk becomes a row of (chunk_id, chunk_seq, chunk_data), with a unique index on (chunk_id, chunk_seq) for reassembly.
What remains in the main row is an 18-byte TOAST pointer, regardless of how large the value is. It records the TOAST table's OID, the value's chunk_id, the logical (uncompressed) and physical (stored) sizes, and the compression method. The whole scheme rides on the varlena header format: variable-length values normally carry a 4-byte length word, values under 127 bytes get a 1-byte header, and specific header bit patterns mark "this is compressed" and "this is a pointer, go fetch the chunks." The ceiling for any single TOASTed value is 2^30 − 1 bytes — the 1 GB limit you eventually hit with giant text or jsonb values.
The medium-text trap
Here is the regime split in numbers, from Benita's benchmark of three 500K-row tables:
- small — the string
'small value': 21 MB main table, no TOAST. - medium — random 1,800-character strings: 977 MB main table, no TOAST. Every value sits inline, because 1,800 characters of random (incompressible) text never pushes the row past the 2 kB threshold.
- large — random 4,096-character strings: 25 MB main table, 1,953 MB TOAST table. Every value went out of line, in three chunks.
Now run SELECT FROM t WHERE id = 6000 with no index, forcing a sequential scan. The medium table answered in 173 ms; the large table in 37 ms. The table holding more than twice the data* was dramatically faster, because the scan over the large table only reads the 25 MB of main-table pages full of 18-byte pointers — the 1.9 GB of TOAST chunks is never touched, since the filter is on id and TOASTed values are only fetched when actually needed, typically when the result is sent to the client. The medium table's scan has to wade through all 977 MB.
The same shape appears in index scans that touch many rows: fetching id BETWEEN 0 AND 250000 warm took about 60 ms on small, 70 ms on large, and 284 ms on medium. Single-row index lookups, by contrast, were ~0.6–0.8 ms on all three tables — point reads hide the problem completely, which is exactly why it tends to surface only after a table grows into scan-heavy territory.
The inversion flips back the moment the query must read the text itself. A warm WHERE value LIKE 'foo%' scan took 260 ms on medium but 7.3 seconds on large: now every one of those 1.9 GB of chunks has to be fetched and stitched back together. TOAST is not free storage — it's a bet that most queries don't touch the wide column. The PostgreSQL docs make the same bet explicit with their own measurement: a table of typical HTML pages and URLs took about half the raw data size with TOAST, the main table held about 10% of the data, and runtime was no different from an un-TOASTed table with pages pre-trimmed to 7 kB.
What to do about it
If scans suffer from inline medium values, lower the target so they move out of line sooner:
ALTER TABLE tbl SET (toast_tuple_target = 128);In Benita's test this pushed a ~2,100-character value out of line that the default kept inline (128 is the minimum). The equivalent manual fix — moving the wide column into its own table — does the same thing with more control, and is effectively hand-rolled TOAST.
If you read big values often, or slice them, consider EXTERNAL:
ALTER TABLE tbl ALTER COLUMN body SET STORAGE EXTERNAL;It skips compression, costing disk but making substring() on wide text/bytea faster — Postgres can fetch only the chunks covering the requested slice, which is impossible when the value is compressed as a whole.
Two more behaviors worth knowing. First, an UPDATE that doesn't modify a TOASTed column pays no TOAST cost at all — the new row version keeps the same 18-byte pointer to the same chunks. Wide-but-cold columns are cheap to live alongside. Second, compression choice is per column and can be changed online via ALTER TABLE ... SET COMPRESSION, so adopting lz4 for new writes doesn't require a rewrite of old values.
The general lesson is that text is not one data type in Postgres; it's three. Under ~127 bytes it's a tight inline value with a 1-byte header. Up to ~2 kB it's inline weight that every scan of the table must carry whether it reads the column or not. Beyond that it's a pointer to a side table you only pay for when you dereference it. The 2 kB boundary between the last two is where most of the surprises live — and it's a knob, not a law.