Radim Marek: TOAST: Where PostgreSQL hides big values
PostgreSQL employs the TOAST mechanism to manage oversized attributes within its strict 8KB page limit. When a tuple exceeds 2KB, TOAST initiates a series of compression and relocation processes to ensure efficient storage. This article explores the intricacies of TOAST, including its thresholds, storage strategies, and the procedures involved in managing variable-length attributes.
- ▪TOAST stands for Oversized-Attribute Storage Technique and is crucial for handling large data in PostgreSQL.
- ▪The mechanism begins compressing tuples that exceed 2KB to maintain at least four tuples per page.
- ▪PostgreSQL uses four storage strategies for variable-length columns, allowing for different trade-offs in data management.
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
