What this usually means
TOAST (The Oversized-Attribute Storage Technique) tables store large column values that don't fit in regular heap pages. When rows with TOAST values are updated or deleted, old TOAST tuples can become dead but remain in place if autovacuum fails to keep up. Unlike main table bloat, TOAST bloat often goes unnoticed because TOAST tables are opaque and not shown in standard bloat queries. The root cause is usually a combination of long-running transactions preventing cleanup, insufficient autovacuum frequency for TOAST tables, or application patterns that repeatedly update large column values (e.g., JSON blobs, large text). Once dead tuples accumulate, they fragment the TOAST heap, increasing I/O for every large column access.
The first ten minutes — establish facts before touching code.
- 1Run: SELECT schemaname, tablename, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE schemaname NOT IN ('pg_catalog','information_schema') ORDER BY n_dead_tup DESC LIMIT 10;
- 2Check TOAST table size: SELECT relname, pg_size_pretty(pg_total_relation_size(reltoastrelid)) FROM pg_class WHERE reltoastrelid != 0 ORDER BY pg_total_relation_size(reltoastrelid) DESC LIMIT 10;
- 3Compute TOAST bloat ratio: SELECT (pg_total_relation_size(reltoastrelid) - pg_relation_size(reltoastrelid, 'main')) / NULLIF(pg_total_relation_size(reltoastrelid), 0) * 100 AS bloat_pct FROM pg_class WHERE reltoastrelid != 0 AND relkind='r' ORDER BY bloat_pct DESC;
- 4Check if autovacuum is running on TOAST: SELECT schemaname, relname, last_autovacuum, last_vacuum FROM pg_stat_all_tables WHERE relname LIKE 'pg_toast%';
- 5Look for long-running transactions: SELECT pid, age(now(), xact_start)::text, state, query FROM pg_stat_activity WHERE state != 'idle' AND xact_start < now() - interval '5 minutes';
The specific files, logs, configs, and dashboards that usually own this bug.
- searchpg_stat_user_tables – dead tuple counts and last vacuum timestamps
- searchpg_class.reltoastrelid – map main tables to their TOAST tables
- searchpg_stat_all_tables with relname LIKE 'pg_toast%' – TOAST-specific stats
- searchpg_locks – check if VACUUM is blocked by conflicting locks
- searchpg_stat_activity – identify long-running transactions that block cleanup
- searchpgstattuple extension: pgstatindex, pgstattuple – precise bloat measurement
- searchAutovacuum log entries – look for 'skipping vacuum of toast table' warnings
Practical causes, not theory. These are the things you will actually find.
- warningAutovacuum scale factor too high for TOAST tables (default 0.2 for main, but TOAST inherits the same, not tuned)
- warningLong-running idle-in-transaction sessions preventing VACUUM from removing dead tuples
- warningApplication pattern: frequent UPDATE of large columns (e.g., JSON documents) without actual content change
- warningManual VACUUM issued without VERBOSE, masking TOAST bloat from standard reports
- warningpg_toast tables excluded from monitoring dashboards (common oversight)
Concrete fix directions. Pick the one that matches your root cause.
- buildLower autovacuum_vacuum_scale_factor for the specific table's TOAST: ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01);
- buildVACUUM (VERBOSE, INDEX_CLEANUP ON) the TOAST table directly: VACUUM VERBOSE pg_toast.pg_toast_<oid>;
- buildFor severe bloat, use pg_repack or pg_squeeze to rebuild the TOAST table online (no exclusive lock)
- buildForce aggressive vacuum with FREEZE option: VACUUM (FREEZE, VERBOSE) pg_toast.pg_toast_<oid>; – use with caution
- buildTune autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay to increase throughput
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter vacuum, re-run bloat ratio query and confirm bloat_pct dropped below 10%
- verifiedCheck pg_stat_all_tables for TOAST table: last_autovacuum updated and n_dead_tup near zero
- verifiedRun pgstattuple on TOAST table: SELECT * FROM pgstattuple('pg_toast.pg_toast_<oid>'); verify free_space < 20%
- verifiedBenchmark query that was slow: same query now completes in expected time
- verifiedMonitor disk usage: du -sh $PGDATA/base/*/ – expect size reduction
Things that make this bug worse or harder to find.
- warningRunning VACUUM FULL on TOAST table during peak hours – it takes an exclusive lock and can cause downtime
- warningIgnoring TOAST bloat because main table looks healthy – TOAST can be 10x the main table size
- warningSetting autovacuum_naptime too low globally – causes CPU overhead; target specific tables instead
- warningAssuming VACUUM alone will reclaim disk space – it only marks space reusable; need VACUUM FULL or pg_repack to return to OS
- warningNot checking TOAST bloat after a bulk UPDATE that didn't change large columns – UPDATE always creates new TOAST tuples
The Case of the 50GB TOAST Table on a 10GB Main Table
Timeline
- 09:15Alert: disk usage on primary DB crosses 85% (170GB used of 200GB).
- 09:20Check pg_total_relation_size: 'orders' main table is 10GB, but its TOAST table is 50GB.
- 09:25Run pgstattuple on TOAST table: dead_tuple_count = 4.2M, dead_tuple_percent = 68%.
- 09:30Check pg_stat_activity: a long-running session 'COPY FROM' has been idle-in-transaction for 4 hours.
- 09:35Terminate the idle-in-transaction session: SELECT pg_terminate_backend(pid);
- 09:40Run VACUUM VERBOSE pg_toast.pg_toast_<oid>; – it finishes in 12 minutes, removes 4M dead tuples.
- 09:55Check disk usage: still 85% – VACUUM only marked space reusable, didn't return to OS.
- 10:00Schedule pg_repack during maintenance window: pg_repack -t pg_toast.pg_toast_<oid> --no-kill-backend
- 11:30Repack completes, disk usage drops to 55% (110GB). Query performance on 'orders' returns to normal.
- 11:45Set autovacuum_vacuum_scale_factor = 0.01 for the orders table's TOAST via ALTER TABLE.
I got paged on a Tuesday morning for disk usage on our primary PostgreSQL database. The main table 'orders' was only 10GB, but pg_total_relation_size revealed its TOAST table was 50GB. That was our first clue – TOAST bloat that had been building for weeks. The application stored large JSON payloads in a text column, and every status update created a new TOAST entry.
I suspected a long-running transaction was blocking vacuum. Sure enough, a COPY FROM command had been idling for 4 hours. After killing it, VACUUM cleared the dead tuples quickly. But disk didn't shrink – because VACUUM only marks space reusable inside the TOAST file, it doesn't release it to the OS. We needed pg_repack.
During the next maintenance window, I ran pg_repack on the TOAST table. It rebuilt the TOAST index and heap, freeing 40GB. We also set aggressive autovacuum parameters for that table. Lesson: always monitor TOAST size separately, and don't assume VACUUM reclaims disk. The application was also changed to avoid unnecessary updates on the JSON column.
Root cause
A long-running idle-in-transaction prevented autovacuum from cleaning dead TOAST tuples, and the application pattern of updating large JSON columns on every status change generated excessive TOAST churn.
The fix
Terminated the blocking session, ran VACUUM to clean dead tuples, then used pg_repack to return disk space. Tuned autovacuum for the TOAST table and modified application to skip updates when JSON content didn't change.
The lesson
TOAST bloat is invisible to standard table size monitoring. Always compare main table size vs TOAST size. Aggressive vacuum settings and application-level deduplication of large column updates are essential.
PostgreSQL stores large column values (> ~2KB) in a separate TOAST table. Each TOAST tuple is a chunk (default 2KB) chained together. When you UPDATE a row with a TOAST attribute, the old TOAST tuples become dead, and new ones are inserted. Unlike heap tuples, TOAST tuples are not updated in place because they can span multiple pages.
The TOAST table is a heap with its own indexes (chunk_id, chunk_seq). Dead tuples accumulate when autovacuum doesn't run often enough or is blocked. Because TOAST tables are not shown in pg_stat_user_tables by default, bloat can grow unnoticed until disk runs out.
Use the pgstattuple extension for exact dead tuple percentages. Query: SELECT * FROM pgstattuple('pg_toast.pg_toast_oid'); Look at 'dead_tuple_count' and 'dead_tuple_percent'. Values above 20% indicate significant bloat.
For a quick estimate without extension: compare pg_total_relation_size(reltoastrelid) with pg_relation_size(reltoastrelid, 'main'). The difference is the TOAST table size. But this doesn't separate live vs dead. Better: use pgstattuple.
Autovacuum parameters for TOAST tables are inherited from the parent table but can be set independently via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...). I recommend setting scale_factor to 0.01 for tables with high UPDATE frequency on large columns.
Also consider autovacuum_vacuum_threshold for TOAST: default 50 is often too high for large TOAST tables. Set it to 1000. Monitor dead tuples with a cron that queries pg_stat_all_tables for TOAST tables and alerts if dead_tuple_ratio > 20%.
VACUUM FULL is the nuclear option: it rebuilds the table with an ACCESS EXCLUSIVE lock, blocking all reads and writes. For TOAST tables, this can take minutes to hours. Avoid it during business hours.
Instead, use pg_repack (or pg_squeeze) which works online: it creates a new table, copies live tuples, and swaps via triggers. pg_repack requires an INDEX on the TOAST table (which exists by default on (chunk_id, chunk_seq)). Run: pg_repack -t pg_toast.pg_toast_oid --no-kill-backend. It takes a brief ACCESS EXCLUSIVE lock at the end for the swap, typically sub-second.
If your application frequently updates large columns, consider using JSONB instead of TEXT with TOAST? Actually JSONB can still be toastable. The real fix: avoid UPDATE if the content hasn't changed. Use triggers or application logic to compare old and new values.
Another pattern: store large blobs in object storage (S3) and keep only a reference in the DB. This eliminates TOAST entirely for those columns. For logging tables, consider partitioning and dropping old partitions instead of DELETE.
Frequently asked questions
How do I find the TOAST table OID for a given table?
SELECT reltoastrelid FROM pg_class WHERE relname = 'your_table_name'; Then query pg_class with that OID to get the TOAST table name: SELECT relname FROM pg_class WHERE oid = <reltoastrelid>; The TOAST table name is usually pg_toast.pg_toast_<parent_oid>.
Can I VACUUM only the TOAST table?
Yes. Run VACUUM pg_toast.pg_toast_<oid>; You can specify VERBOSE to see progress. This does not lock the parent table, but it may still be blocked by long-running transactions on the parent.
Why did my VACUUM not reduce disk space?
VACUUM (without FULL) marks dead tuple space as reusable within the file but does not shrink the file. Only VACUUM FULL, CLUSTER, or pg_repack returns space to the OS. Use pgstattuple to see how much space is reusable (free_space).
Does TOAST bloat affect performance even if disk isn't full?
Yes. More dead tuples mean more pages to scan during sequential scans. Also, the TOAST index may become bloated, slowing down chunk lookups. Query performance for large column reads degrades proportionally to the bloat percentage.
How often should I check for TOAST bloat?
At least weekly for tables with high UPDATE rates on large columns. I set up a monitoring query that alerts when TOAST size exceeds 2x main table size or dead_tuple_percent > 20%. Use pg_stat_all_tables and pgstattuple.