토론: JSONB columns: what they are good for and when a column is better

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

번역이 없어 원문을 표시합니다. 원문

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.