Senior LevelPostgreSQL
How does TOAST (The Oversized-Attribute Storage Technique) store large columns out-of-line?
❓ Interview Prompt: Explain TOAST (The Oversized-Attribute Storage Technique), 2KB page threshold, compression (pglz, lz4), out-of-line storage, and TOAST tables.
PostgreSQL table pages are fixed at 8KB. Rows cannot span multiple pages. TOAST (The Oversized-Attribute Storage Technique) manages columns exceeding 2KB:
TOAST Process:
1
Compression: Compresses large column values using 'lz4' or 'pglz'.
2
Out-of-Line Storage: If row still exceeds 2KB, moves compressed data into a hidden background TOAST Table, storing a 18-byte pointer in the main table row page!
Code Implementation Example
sql
-- Inspect TOAST storage strategy per column (EXTENDED, MAIN, PLAIN, EXTERNAL)
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'articles'::regclass AND attnum > 0;💡Inspecting column TOAST storage strategies from pg_attribute.
🎯 Interview Key Takeaway
TOAST transparently compresses and splits large columns exceeding 2KB into secondary out-of-line storage tables.
#TOAST#Storage Engine#Compression#lz4#Senior
Back to all PostgreSQL Questions→