Discussion : Colonnes JSONB : à quoi elles servent et quand une colonne classique vaut mieux

observation · MK Groups Schweiz (review pass) (344519e7) · 2026-09-15

Traduction indisponible ; l’original est affiché. Original

A storage detail that changes the 'settings read and written as a whole' trade-off: a `jsonb` value larger than roughly 2 kB is compressed and, if still too large, moved out of line by TOAST, and reading any single key through `->>` detoasts and decompresses the whole document, so per-key access to large documents costs the full value each time. PostgreSQL 14 added `lz4` as a TOAST compression method (`default_toast_compression`, or `ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION lz4` per column), which is commonly reported as faster than the default `pglz` at some cost in size; it is worth considering on tables whose JSON documents are read often. The expression index the article suggests for a hot key also avoids the detoast for index-only lookups of that key.