Anurag’s Read

What gets TOASTed in Postgres.

Explains exactly when Postgres compresses or moves large values and measures pglz, LZ4 and no compression on real queries. You can use the numbers to choose a column setting.
Score
8.6
Verdict
Must read
Blog and date
PlanetScale,
Original length and level
15 min, intermediate
Topics
postgres, storage, compression, performance
Read the original

Context

Postgres stores data in 8 KB pages, and one row cannot span pages. TOAST handles values too large for a row by compressing them or moving them to a side table. Postgres 19 beta changes the default TOAST compressor from pglz to LZ4 for builds with LZ4.

PlanetScale made the same change on Postgres 17 and 18. The post measures when each setting wins.

Architecture

A row has a 24-byte header for MVCC visibility. Variable-length types such as text, jsonb, and bytea carry a length header. It takes 1 byte up to 126 bytes, and 4 bytes above that. TOAST starts only when a row exceeds 2,032 bytes, about a quarter page.

It handles the largest values first and stops when the row falls under toast_tuple_target. It compresses a value in place or moves it to a TOAST table in the pg_toast schema. The table splits a value into chunks of up to 1,996 bytes, with a chunk_id and chunk_seq on each.

The main row keeps an 18-byte pointer to the chunks.

Mechanisms

M1

Storage strategies

PLAIN allows neither compression nor moving. EXTENDED is the default for text, jsonb, and bytea, and it compresses first and then moves. EXTERNAL moves a value and never compresses it. MAIN compresses and tries to keep the value inline, and it is the default for numeric.

ALTER COLUMN SET STORAGE affects only newly written values.

M2

pglz

pglz is the built-in compressor. It replaces repeats with back-references to a 4 KB window. It checks several candidates and keeps the longest match. It gives up early on data without repeats.

It discards a result that saves less than 25 percent.

M3

LZ4

LZ4 uses a 64 KB window and checks one candidate from a hash table. The table overwrites old entries, so LZ4 often misses distant matches. It is much faster than pglz. It keeps a result if it saves more than 2 bytes.

M4

Prefix and offset reads

A prefix such as left(body, 200) needs only the first chunk with pglz. Its 200 characters compress to at most 902 bytes. LZ4 has no such bound, so it fetches every chunk. Uncompressed EXTERNAL chunks have a fixed size of 1,996 bytes. Byte 500,000 of a bytea value is therefore in chunk 250.

This seek works for bytea only, because UTF-8 characters vary from 1 to 4 bytes.

Design decisions

No.ChoseInstead ofWhy
D1LZ4 as the general defaultpglz as the defaultLZ4 compresses and decompresses faster and finds matches in a wider window.
D2EXTERNAL for compressed media and range-read byteaCompression for such dataCompression costs CPU and gives little gain on data that is already compressed.
D3TOAST for values under 1 GBLarge objectsTOAST is automatic, and large objects need explicit calls and manual cleanup with lo_unlink.

Key numbers

  • Small rows stay uncompressed up to 2,032 bytes, and 2,001 repeated characters compress to 26 bytes.
  • With a 500-word vocabulary, pglz gave 105,457 bytes and LZ4 gave 96,022 bytes from 209,999.
  • With a 5,000-word vocabulary, pglz stored 209,999 bytes and LZ4 stored 177,590.
  • 10,000 JSON orders took 103 MB raw, 19 MB with pglz, and 24 MB with LZ4.
  • JSON load times were 0.48 s and 1.09 s for LZ4, and 1.59 s and 1.73 s for pglz.
  • For 100 values of about 1 MB, pglz needed 301 page lookups in about 1 ms. LZ4 needed 6,234 lookups in about 16 ms.
  • A 200-byte read at offset 500,000 took 0.6 ms with EXTERNAL, 43 ms with LZ4, and 116 ms with pglz.

Failures and limits

  • The pglz window of 4 KB misses distant repeats.
  • LZ4 cannot bound prefix reads.
  • Compression does not cut network egress, because Postgres decompresses before it sends.
  • Setting changes do not rewrite existing values.
  • The benchmarks come from one environment with few runs, for example two load-time runs.
  • The zstd compressor is not available for columns, only for WAL and backups.

Lessons you can use

  • Keep LZ4 unless a measured workload shows otherwise.
  • Choose pglz for repetitive JSON when disk size matters most, or for large text read as previews.
  • Choose EXTERNAL for already compressed bytes and for reads by byte range.
  • Measure with page lookups as well as time, because chunk counts drive read cost.

Glossary

TOASTThe Postgres method that compresses large values or moves them to a side table.
Large objectA value stored through explicit calls in pg_largeobject, up to 4 TB.
toast_tuple_targetThe row size, 2,032 bytes by default, that TOAST tries to reach.
WALThe write-ahead log, which records changes before they reach data files.