Mastering PostgreSQL TOAST & Vacuum
Keep Your Large Tables Fast and Lean
Have your PostgreSQL queries slowed down despite all the right indexes and optimizations?
If your table has large TEXT, JSONB, or BYTEA columns, the culprit might be silently hiding under the radar: TOAST.
What is TOAST in PostgreSQL?
TOAST stands for The Oversized-Attribute Storage Technique. PostgreSQL’s smart approach to handling large column values involves compressing and storing them outside the main table.
TOAST Storage Strategies:
– PLAIN: No compression, stored inline.
– EXTENDED: Compress + store large values in a TOAST table (default).
– MAIN: Compress but keep values inline.
– EXTERNAL: No compression, but store in TOAST table.
Why TOAST Improves Performance
1. Reduces Table Bloat
2. Faster Sequential & Index Scans
3. Minimized Page Splits
4. Better Use of Cache
How to Control TOAST for Better Performance
Monitor TOAST size:
SELECT relname, pg_relation_size(relname::regclass)
FROM pg_class
WHERE relname LIKE ‘pg_toast%’;
Choose the right storage strategy:
ALTER TABLE my_table ALTER COLUMN col SET STORAGE MAIN;
ALTER TABLE my_table ALTER COLUMN col SET STORAGE EXTERNAL;
Tune compression (PostgreSQL 14+):
ALTER DATABASE your_db SET default_toast_compression = ‘lz4’;
Don’t Ignore Vacuuming TOAST Tables
Run a full cleanup:
VACUUM FULL pg_toast.pg_toast_[table_oid];
VACUUM FULL ANALYZE;
Improve autovacuum sensitivity:
ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.01);
Autovacuum Still Not Enough?
Remove delay:
ALTER TABLE my_table SET (autovacuum_vacuum_cost_delay = 0);
Let autovacuum do more before pausing:
ALTER TABLE my_table SET (autovacuum_vacuum_cost_limit = 10000);
Increase workers (postgresql.conf):
autovacuum_max_workers = 10
Bonus Tips
– Use VARCHAR(n) if length is known.
– Use partial indexes.
– Store infrequently accessed large data externally (e.g., S3).
Final Thoughts
PostgreSQL’s TOAST is brilliant — but only if you understand it.
Maintain it well, choose the right strategy, and tune the vacuum.
