Toast: Where PostgreSQL hides big values
PostgreSQL utilizes a mechanism called TOAST to manage oversized attributes within its strict 8KB page limit. When a tuple exceeds a 2KB threshold, PostgreSQL begins a process to compress and relocate the data to ensure efficient storage. The article outlines the various storage strategies and the steps involved in the shrinking procedure for variable-length attributes.
- ▪TOAST stands for Oversized-Attribute Storage Technique and is essential for handling large data within PostgreSQL's 8KB page limit.
- ▪The threshold for triggering TOAST is set at 2KB, prompting PostgreSQL to start compressing and relocating attributes to fit within the page.
- ▪There are four storage strategies available for variable-length columns, including EXTENDED, EXTERNAL, MAIN, and PLAIN, each with specific use cases.
Opening excerpt (first ~120 words) tap to expand
Table of Contents The 2KB threshold The four storage strategies The shrinking procedure Watching it happen What the heap tuple looks like Compression: pglz vs lz4 What it costs you Limits Following a tuple through the toaster Keeping the page invariant safe In earlier posts in this series we established that every heap tuple lives inside a strict 8KB page. Everything else is built on top of that hard limit: MVCC, HOT updates, and indexes that point at (page, line_pointer). And yet this still works: CREATE TABLE docs (id int PRIMARY KEY, body jsonb); INSERT INTO docs VALUES (1, (SELECT jsonb_agg(g) FROM generate_series(1, 100000) g)); That body value is somewhere north of half a megabyte. The heap page is still 8KB.
…
Excerpt limited to ~120 words for fair-use compliance. The full article is at boringSQL | Supercharge your SQL & PostgreSQL powers.
Discussion
0 comments
