TOASTed
Series: jsonb migration · Part 3 of 4
Where is the data
Postgres pages are 8 kB. The engine wants at least four tuples (Note: tuple is the
state of a row) per page. One tuple is 2032 bytes maximum. All this lives in
heaptoast.h.
If we move past that, Postgres will compress the variable-length fields, and if somehow it still does not fit, it will move it to a side tables which will have an 18 byte pointer pointing to it. This whole process is called TOAST: The Oversized Attribute Storage Technique.
If we look at our size:
SELECT relname, relpages,
pg_size_pretty(pg_relation_size(oid)) AS heap,
pg_size_pretty(pg_table_size(oid)) AS heap_plus_toast
FROM pg_class WHERE relname = 'beats';
relname | relpages | heap | heap_plus_toast
---------+----------+-------+-----------------
beats | 2 | 16 kB | 40 MB
We can see that we are a thousand times past the line.
Our table is two pages, and it has a hungred rows (199 MB of JSON), and the heap holds 16 kB of pointers. And! our TOAST is 20,245 chunks of around ~2 kB each, 203 per project.
Why do we care? Well, reading one key means fetching all 203 chunks and decompressing
all of them. Meaning, partial reads don’t exist. That being said, jsonb avoids
reparsing, but it doesn’t avoid fetching and decompressing.
Evan Jones benchmarked this across a million rows: expect
queries 2–10× slower once a row crosses 2 kB. In his numbers jsonb goes from
746 ms inline to 7,624 ms TOASTed for the same logical work.
Indexes on a derived table
SELECT b.id, b.payload->>'title', m->>'label', h#>>'{sound,sample_id}'
FROM beats b
CROSS JOIN LATERAL jsonb_array_elements(b.payload->'mixes') m
CROSS JOIN LATERAL jsonb_array_elements(m->'patterns') p
CROSS JOIN LATERAL jsonb_array_elements(p->'hits') h
WHERE b.id = 7;
-- Execution Time: ~2000 ms
The traversal is measured at 5 ms. The rest is b.payload->>'title' in the SELECT
list, which is re-read and decompressed once per output row…2,484 times.
SELECT b.id, b.title, m->>'label', h#>>'{sound,sample_id}'
-- Execution Time: ~4.9 ms
b.title is just a column which is stored inside the heap tuple, so it never touches
TOAST. Which means, we have ~2000 ms down to ~4.9 ms.
Same effect in miniature on the browse filter. Add a projection from the document and the index stops tying:
| buffers | time | |
|---|---|---|
expression index forced, + payload->>'title' |
1,358 | 22.0 ms |
column + title |
2 | 0.015 ms |
An index avoids de-TOASTing the rows that don’t match. It can do nothing about the rows that do. Paul Ramsey put it plainly, measuring the same thing on 40 kB documents: “There is not a free lunch here, because reapplying the filter after the index scan means the 983 rows that passed the filter still pay the TOAST tax.”
So, for a TOASTed document:
An index makes the search cheap. Only a column makes the read cheap.
Generated columns
I’ve looked at multiple production systems, and most of them do some sort of projection. The only difference between those examples are the granularity and who does the projection.
| mechanism | who keeps it in sync | |
|---|---|---|
| Paul Ramsey | GENERATED ALWAYS AS (...) STORED |
Postgres |
| Marten, “duplicated fields” | an ordinary column | the app, on every save |
| HAPI FHIR | separate index tables | the app, same transaction |
Decompression
lz4 has been available since PostgreSQL 14 and decompresses much faster than
the default pglz:
| per project | read | |
|---|---|---|
pglz |
396 kB | ~92 ms |
lz4 |
405 kB | ~48 ms |
EXTERNAL (uncompressed) |
2148 kB | ~34 ms |
Nearly twice as fast for 2% more disk. We can set it by:
ALTER TABLE beats ALTER COLUMN payload SET COMPRESSION lz4;
It only applies to rows written afterwards - so copying from another jsonb column
doesn’t count as writing them. Just a friendly reminder.
References
- PostgreSQL 16 manual, §8.14 JSON Types — document design and §8.14.4 jsonb Indexing.
- Paul Ramsey, Postgres JSONB Columns and TOAST — the head-to-head on 40 kB documents and the “no free lunch” result.
- Evan Jones, Postgres large JSON value query performance — the 2 kB cliff quantified.
- Marten documentation — “Duplicated Fields” vs “Calculated Indexes”. Its duplicated fields are ordinary columns written by Marten’s own upsert function, not Postgres generated columns.
- HAPI FHIR’s JPA architecture — document body plus separate index tables, in production.
src/include/access/heaptoast.h— where 2032 comes from.
PostgreSQL 16.15 in Docker, 100 projects averaging 2042 kB plus 20,000 inline
documents averaging 1108 bytes, M-series Mac. Medians of seven runs, warm
cache, produced by sql/08 and sql/09 in the companion repo. Sub-millisecond
medians move about 20% between runs; two significant figures is the real
precision. Buffer counts and plans reproduce exactly.