TOAST and large values

A page is 8 KiB and a tuple cannot span pages. A text column accepts values up to 1 GB. Both statements are true, and TOAST — The Oversized Attribute Storage Technique — is the entire reconciliation.

It matters operationally for one blunt reason: TOAST moves your data into a relation with a different name and a different OID, and the size function most people reach for does not count it.

The mechanism

When a row will not fit, PostgreSQL works through the row’s variable length attributes, largest first, and does two things in whatever combination the column’s strategy permits:

1. Compress the value in place.
2. Move it out of line into the table’s TOAST relation, replacing it in the row with an 18-byte pointer.

The out-of-line copy is split into fixed-size chunks. Each chunk is an ordinary row in an ordinary table, which is why a value of any size can be stored: it becomes many small rows that individually fit in a page.

$ psql -U postgres -c "SELECT chunk_id, count(*) AS chunks, sum(length(chunk_data)) AS total_bytes, max(length(chunk_data)) AS max_chunk_size FROM pg_toast.pg_toast_18507 GROUP BY chunk_id ORDER BY chunk_id"

 chunk_id | chunks | total_bytes | max_chunk_size
----------+--------+-------------+----------------
  18512 |     11 |       20480 |           1996
  18513 |      1 |         248 |            248
(2 rows)

A 20,480-byte value became 11 chunks of at most 1,996 bytes. That chunk size is not arbitrary: it is chosen so four chunks plus their row overhead fill a page exactly.

The TOAST relation has its own name in the pg_toast schema, its own OID, and its own index on (chunk_id, chunk_seq). It is a real table. It does not appear in \dt, and it is not something you should write to, but every tool that measures tables can measure it if you ask it to.

Finding the TOAST relation for a table

SELECT c.relname AS table_name,
       t.relname AS toast_relation,
       pg_size_pretty(pg_relation_size(c.oid))  AS main_fork,
       pg_size_pretty(pg_total_relation_size(t.oid)) AS toast_size
  FROM pg_class c
  LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
 WHERE c.relname = 'orders';

A reltoastrelid of 0 means the table has no TOAST relation, which means it has no column type that could ever need one.

The threshold

Toasting is triggered by the size of the whole row, not by the size of one column. The documented threshold, TOAST_TUPLE_THRESHOLD, is just under 2 kB. Below that the row is stored as it is; above it, the toaster runs until the row fits under the target.

$ psql -U postgres -c "SELECT n AS requested, length(v) AS chars, pg_column_size(v) AS stored_bytes FROM toast_threshold ORDER BY n"

 requested | chars | stored_bytes
-----------+-------+--------------
     500 |   512 |          516
    1500 |  1504 |         1508
    1900 |  1920 |         1924
    2000 |  2016 |         2016
    2100 |  2112 |         2112
    4000 |  4000 |         4000
(6 rows)

Read the third column against the second. The first three values report four bytes more than their length: that is the varlena header of a value stored inline. The last three report exactly their length, because they are stored out of line and the reported size is the size of the external datum.

The count confirms it rather than leaving it to inference:

$ psql -U postgres -c "SELECT count(DISTINCT chunk_id) AS toasted_values, count(*) AS chunks, sum(length(chunk_data)) AS bytes FROM pg_toast.pg_toast_18560"

 toasted_values | chunks | bytes
----------------+--------+-------
            3 |      7 |  8128
(1 row)

Three values, and 2016 + 2112 + 4000 = 8128 bytes exactly. The transition sits between the 1,920-character value and the 2,016-character one.

That the trigger is row width and not column width has a consequence worth holding on to: adding a column can push existing values out of line. A table of 1,900-byte rows that gains a 200-byte column starts toasting on the next rewrite of each row, and the main fork shrinks while the total grows.

The four storage strategies

Every variable-length column has a strategy, visible in pg_attribute.attstorage.

SELECT attname,
       CASE attstorage WHEN 'p' THEN 'plain' WHEN 'e' THEN 'external'
                       WHEN 'm' THEN 'main'  WHEN 'x' THEN 'extended' END AS storage
  FROM pg_attribute
 WHERE attrelid = 'orders'::regclass AND attnum > 0
 ORDER BY attnum;

What each one does to the same value

One 20,480-character compressible value, written into three columns of one row that differ only in strategy:

$ psql -U postgres -c "SELECT length(s_extended) AS input_chars, pg_column_size(s_extended) AS extended_bytes, pg_column_size(s_external) AS external_bytes, pg_column_size(s_main) AS main_bytes FROM toast_strategy"

 input_chars | extended_bytes | external_bytes | main_bytes
-------------+----------------+----------------+------------
     20480 |            248 |          20480 |        252
(1 row)

extended and main both compressed the value by a factor of about 80. external stored all 20,480 bytes, because external means out of line, uncompressed.

PLAIN is a trap on a variable-length column

The fourth strategy did not appear in that capture because the insert failed.

$ psql -U postgres -c "CREATE TABLE toast_plain_only (id int, s text)" -c "ALTER TABLE toast_plain_only ALTER COLUMN s SET STORAGE PLAIN" -c "INSERT INTO toast_plain_only VALUES (1, repeat('abcdefgh',2560))"

CREATE TABLE
ALTER TABLE
ERROR:  row is too big: size 20512, maximum size 8160

plain forbids both compression and out-of-line storage, so a value that does not fit in a page cannot be stored at all.

The dangerous part is the sequencing. The ALTER TABLE succeeds. The table is now one that rejects rows above a certain width, and nothing about it announces that until a real value arrives — typically in production, typically from the one customer whose data is larger than everyone else’s.

The advice about EXTERNAL, tested

A recommendation you will meet often is that SET STORAGE EXTERNAL makes substring operations on large values faster, because there is no compressed value to decompress before the substring can be taken.

That claim was tested directly rather than repeated. Two tables, 200 rows each, one highly compressible ~1 MB document per row, identical contents, differing only in strategy.

$ psql -U postgres -c "SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total FROM pg_class WHERE relname IN ('sub_extd','sub_ext') ORDER BY relname"

 relname  |  total
----------+---------
sub_ext  | 205 MB
sub_extd | 2528 kB
(2 rows)

Eighty-three times the storage. Now the speed the storage was supposed to buy. Three runs of each, warm cache:

extended was faster in all three patterns, including the one the advice is specifically about, while using one eighty-third of the space.

The obvious objection is that this document compresses unusually well. So the same comparison was run on weakly compressible data:

$ psql -U postgres -c "SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total FROM pg_class WHERE relname IN ('wk_extd','wk_ext') ORDER BY relname"

 relname | total
---------+-------
wk_ext  | 25 MB
wk_extd | 25 MB
(2 rows)

-- substring near the end of the value, three runs each
-- EXTENDED : 62.318 ms / 62.025 ms / 62.021 ms
-- EXTERNAL : 61.957 ms / 61.638 ms / 61.590 ms

Identical size and identical time, because the compressor detects that the data does not compress and stores it uncompressed anyway. extended is not paying for compression it cannot recover.

Choosing a compression algorithm

PostgreSQL 18 supports pglz and lz4, selectable per column or globally via default_toast_compression. On this cluster the default is pglz.

$ psql -U postgres -c "SELECT pg_column_size(p) AS pglz_bytes, pg_column_size(l) AS lz4_bytes, pg_column_compression(p) AS p_alg, pg_column_compression(l) AS l_alg, length(p) AS chars FROM lz4_test"

 pglz_bytes | lz4_bytes | p_alg | l_alg |  chars
------------+-----------+-------+-------+---------
    11903 |      4119 | pglz  | lz4   | 1035000
(1 row)

Less than half the size on this corpus. Read time over 200 such documents was 610.1 / 605.4 / 602.5 ms for pglz against 615.6 / 609.5 / 612.9 ms for lz4 — the same, within noise.

So on this workload lz4 halved storage and cost nothing to read. That is a favourable result but a narrow one: it is one corpus on a warm cache, and the measured time is dominated by chunk reassembly rather than by the decompression itself, so the equal timings should not be read as a claim about the algorithms in isolation.

What to take from this

• TOAST is a separate relation. Measure with pg_total_relation_size or pg_table_size; pg_relation_size will understate a TOAST-heavy table by orders of magnitude.
• The threshold applies to the row, not the column, so adding columns changes toasting behaviour for values you did not touch.
• extended is the default because it is the right answer almost always. main is the setting that means “prefer inline”. plain on a variable-length column is a latent insert failure.
• Detoasting is paid on access to the column, not on reading the row. SELECT * on a table with large TOASTed columns is expensive in a way that SELECT id, status is not.
• lz4 is worth considering for new data on compressible columns, and changing the setting does not convert what is already stored.

Cross-course references

• Linux for Production Sysadmins — Part XLI (Storage Performance) covers measuring the extra I/O a detoast costs, and Part XVIII (Enterprise Storage) covers compression done in the storage layer, which is a different decision from the one made here.
• Ceph & Distributed Storage — Part XXXV (RBD architecture) covers object sizing underneath a database, which interacts with the chunk size TOAST writes.

Quiz