14 expert PostgreSQL Toast Jsonb answers researched from official documentation. Every answer cites authoritative sources you can verify.
The Oversized-Attribute Storage Technique compresses and/or splits large values (>2KB) into separate TOAST table. Automatic and transparent. Applies to: TEXT, JSONB, BYTEA, arrays. Compression: LZ or LZ4 (configurable). Max size after TOAST: 1GB. Reduces main table size, improves query performance for non-TOAST columns.
Four strategies: (1) PLAIN (no compression/TOAST, max 2KB), (2) EXTENDED (compress then TOAST, default for JSONB), (3) EXTERNAL (TOAST without compression), (4) MAIN (compress but avoid TOAST). Set via: ALTER TABLE t ALTER COLUMN c SET STORAGE strategy. JSONB default: EXTENDED. Choose EXTERNAL for pre-compressed JSON.
The Oversized-Attribute Storage Technique compresses and/or splits large values (>2KB) into separate TOAST table. Automatic and transparent. Applies to: TEXT, JSONB, BYTEA, arrays. Compression: LZ or LZ4 (configurable). Max size after TOAST: 1GB. Reduces main table size, improves query performance for non-TOAST columns.
Four strategies: (1) PLAIN (no compression/TOAST, max 2KB), (2) EXTENDED (compress then TOAST, default for JSONB), (3) EXTERNAL (TOAST without compression), (4) MAIN (compress but avoid TOAST). Set via: ALTER TABLE t ALTER COLUMN c SET STORAGE strategy. JSONB default: EXTENDED. Choose EXTERNAL for pre-compressed JSON.
JSONB >2KB automatically TOASTed (compressed then moved to TOAST table if still >2KB). Compression typically 50-80% for text-heavy JSON. Small JSONB (<2KB) stays inline. Access pattern: JSONB queries requiring full document decompression = slower. Queries using ->> or -> operators may avoid full decompression with GIN indexes.
Compression overhead: 10-30ms per JSONB >2KB (depends on size, CPU). Trade-off: slower writes, faster scans (smaller table). Disable compression: SET STORAGE EXTERNAL (TOAST without compression). Benchmark: INSERT 10K rows with 5KB JSONB: EXTENDED (compressed) ~2.5s, EXTERNAL (uncompressed) ~1.8s. Choose based on read/write ratio.
JSONB >2KB automatically TOASTed (compressed then moved to TOAST table if still >2KB). Compression typically 50-80% for text-heavy JSON. Small JSONB (<2KB) stays inline. Access pattern: JSONB queries requiring full document decompression = slower. Queries using ->> or -> operators may avoid full decompression with GIN indexes.
Compression overhead: 10-30ms per JSONB >2KB (depends on size, CPU). Trade-off: slower writes, faster scans (smaller table). Disable compression: SET STORAGE EXTERNAL (TOAST without compression). Benchmark: INSERT 10K rows with 5KB JSONB: EXTENDED (compressed) ~2.5s, EXTERNAL (uncompressed) ~1.8s. Choose based on read/write ratio.
Check TOAST table size: SELECT pg_total_relation_size('table_name') vs pg_relation_size('table_name'). Query specific: SELECT toastrelid::regclass FROM pg_class WHERE relname = 'table_name'. View TOAST stats: pg_stat_user_tables (n_tup_upd, n_tup_del include TOAST). Alert on: TOAST table > main table (indicates many large values).
Impact depends on operation: Full document access (SELECT data): must decompress, 2-5x slower vs inline. Partial access (data->>'key'): may avoid decompression with GIN index. Aggregations: full decompression required. Updates: entire JSONB re-TOASTed even for small changes. Mitigation: normalize frequently-accessed keys to separate columns, use JSONB subset queries.
Check TOAST table size: SELECT pg_total_relation_size('table_name') vs pg_relation_size('table_name'). Query specific: SELECT toastrelid::regclass FROM pg_class WHERE relname = 'table_name'. View TOAST stats: pg_stat_user_tables (n_tup_upd, n_tup_del include TOAST). Alert on: TOAST table > main table (indicates many large values).
Impact depends on operation: Full document access (SELECT data): must decompress, 2-5x slower vs inline. Partial access (data->>'key'): may avoid decompression with GIN index. Aggregations: full decompression required. Updates: entire JSONB re-TOASTed even for small changes. Mitigation: normalize frequently-accessed keys to separate columns, use JSONB subset queries.
Best practices: (1) Normalize frequently-accessed keys to regular columns, (2) Use EXTENDED (default) for text-heavy JSON, (3) Use EXTERNAL for pre-compressed/binary JSON, (4) Monitor toast_tuple_target (default 2KB), consider increasing for large JSONB to reduce TOAST overhead, (5) Index specific JSONB paths vs full GIN if access patterns known, (6) Vacuum regularly to reclaim TOAST space after updates/deletes.
Best practices: (1) Normalize frequently-accessed keys to regular columns, (2) Use EXTENDED (default) for text-heavy JSON, (3) Use EXTERNAL for pre-compressed/binary JSON, (4) Monitor toast_tuple_target (default 2KB), consider increasing for large JSONB to reduce TOAST overhead, (5) Index specific JSONB paths vs full GIN if access patterns known, (6) Vacuum regularly to reclaim TOAST space after updates/deletes.