pg_toast Table Bloat

Scenario

A DBA investigates a 50GB table that should hold at most 5GB of JSON data. pg_relation_size() returns 4GB but pg_total_relation_size() returns 50GB. The difference — 46GB — is hidden in the table’s TOAST relation, filled with dead versions of large JSON columns from frequent updates.

How to Identify

Conditions:

• pg_total_relation_size(table) >> pg_relation_size(table) — large gap unexplained by indexes
• Table has TEXT, JSONB, BYTEA, or other large-value columns that are frequently updated
• pg_stat_user_tables shows high n_dead_tup on the TOAST table
• pgstattuple on the TOAST table shows high dead tuple ratio

Analysis Steps

-- 1. Find tables where TOAST is the majority of total size
SELECT
    c.relname                                               AS table_name,
    pg_size_pretty(pg_relation_size(c.oid))                 AS heap_size,
    pg_size_pretty(pg_total_relation_size(c.oid)
        - pg_relation_size(c.oid))                          AS toast_and_index_size,
    pg_size_pretty(pg_total_relation_size(c.oid))           AS total_size
FROM pg_class c
WHERE c.relkind = 'r'
  AND pg_total_relation_size(c.oid) > 100 * 1024 * 1024  -- tables > 100MB
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

-- 2. Find the TOAST table for a specific table
SELECT
    c.relname         AS main_table,
    t.relname         AS toast_table,
    pg_size_pretty(pg_relation_size(t.oid))           AS toast_heap_size,
    pg_size_pretty(pg_total_relation_size(t.oid))     AS toast_total_size
FROM pg_class c
JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relname = 'your_table_name';

-- 3. Check dead tuples in TOAST table
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables
WHERE  relname LIKE 'pg_toast_%'
ORDER  BY n_dead_tup DESC
LIMIT  10;

Pitfalls

• Do not VACUUM FULL a TOAST table directly — always vacuum through the parent table. VACUUM FULL orders also rebuilds pg_toast_.
• TOAST tables have their own separate autovacuum tracking. A TOAST table can accumulate bloat even if the main table is vacuumed regularly, if the TOAST table’s own autovacuum threshold is not met.
• pg_repack performs online TOAST rebuilding without ACCESS EXCLUSIVE lock — use it in production instead of VACUUM FULL.
• TOAST bloat is only from columns that exceed the TOAST threshold (~2KB). Small values are stored inline and not affected.

Resolution Approach

VACUUM on the parent table also vacuums its TOAST table, removing dead TOAST tuples. For size reclamation (not just dead tuple removal), VACUUM FULL or pg_repack is needed to physically compact the TOAST table.