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.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.