PostgreSQL TOAST table bloat: detection and recovery for large columns

Unexplained disk growth on a PostgreSQL instance often traces to TOAST bloat. A table with large JSONB, text, or bytea columns grows far beyond live data size, and queries that materialize those columns slow down. A plain VACUUM does not shrink the on-disk footprint because the dead tuples are in the companion pg_toast table, not the main heap.

TOAST (The Oversized-Attribute Storage Technique) moves values exceeding the TOAST_TUPLE_TARGET out of the main row and into a companion pg_toast.NNNN table. PostgreSQL compresses the value and splits it into roughly 2 KB chunks, each stored as a separate row with a unique index on (chunk_id, chunk_seq). Updates or deletes to the row leave dead chunks in the TOAST table. Because operators often monitor only the parent table, TOAST bloat grows silently until it dominates disk usage.

flowchart TD
    A[Row value exceeds ~2KB] -->|compress and chunk| B[pg_toast table]
    B --> C[chunk_id + chunk_seq + chunk_data]
    C --> D[TOAST index]
    E[UPDATE or DELETE] -->|old chunks become dead tuples| F[Dead TOAST chunks]
    F --> G[VACUUM reclaims space]
    G -->|if blocked or too slow| H[TOAST bloat accumulates]

Common causes

Quick checks

Run these read-only queries to confirm whether TOAST bloat is the culprit.

Find the TOAST relation and compare sizes:

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       t.relname AS toast_relation,
       pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
       pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relname = 'my_table';

Check dead tuples and vacuum history for the TOAST relation:

-- Replace pg_toast.pg_toast_NNNN with your actual TOAST relation name
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE relid = 'pg_toast.pg_toast_NNNN'::regclass;

Measure exact bloat with pgstattuple. This reads the entire relation and can be I/O intensive on large tables. You must target the pg_toast relation directly; invoking pgstattuple on the parent does not scan the TOAST table:

-- Requires the pgstattuple extension
SELECT table_len, dead_tuple_count, dead_tuple_len,
       dead_tuple_percent, free_space, free_percent
FROM pgstattuple('pg_toast.pg_toast_NNNN');

Identify sessions that may prevent vacuum progress:

SELECT pid, usename, state,
       age(now(), xact_start) AS xact_age,
       left(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state IN ('idle in transaction', 'active')
ORDER BY xact_start;

Diagnosis

1. Map the TOAST relation. Use the pg_class query to find the companion pg_toast table.
2. Compare sizes. If pg_relation_size(toastrelid) is more than double the expected live data volume, suspect TOAST bloat.
3. Measure bloat. Run pgstattuple on the TOAST relation. dead_tuple_percent above 20% indicates significant bloat; 50% or higher is common when autovacuum falls behind on high-churn workloads.
4. Check vacuum health. Query pg_stat_user_tables for the TOAST relid. last_autovacuum stale by more than a few hours on an active table means vacuum is not keeping up.
5. Find blockers. Inspect pg_stat_activity for long-running transactions and pg_replication_slots for inactive slots pinning old snapshots.
6. Correlate with workload. Look for recent bulk updates, ETL loads, or batch jobs that modified large jsonb, text, or bytea columns. These generate dead TOAST chunks rapidly.

Metrics and signals to monitor

Fixes

Clear blockers first

Before running manual vacuum, remove anything that prevents dead tuple reclamation.

• Terminate idle-in-transaction sessions. This kills connections and aborts transactions.

  -- WARNING: disruptive. Kills the client connection and its transaction.
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE state = 'idle in transaction'
    AND state_change < NOW() - INTERVAL '5 minutes';
  

• Drop inactive replication slots that retain old snapshots and prevent cleanup.

Run targeted VACUUM

A plain VACUUM is safe and online. It processes the TOAST table alongside the parent:

VACUUM (ANALYZE, VERBOSE) my_table;

Plain VACUUM marks dead TOAST space as reusable within the pg_toast relation but does not return space to the operating system. Use this to stop bloat from worsening without taking heavy locks.

Reclaim space with VACUUM FULL

If the OS must recover disk space, VACUUM FULL rewrites the table including its TOAST storage and returns space to the OS. Be aware of the tradeoffs:

• Requires an ACCESS EXCLUSIVE lock for the duration. No reads or writes are possible.
• On large tables with severe TOAST bloat, it can take hours.
• Requires roughly twice the table size in available disk space during the rewrite.
• Rewrites all tuples, which may apply current compression settings to TOAST data.

Only use VACUUM FULL during a maintenance window when prolonged locking is acceptable.

Use pg_repack for online rewrite

For production systems where prolonged locking is not acceptable, use the pg_repack extension. It rewrites the table online, holding an exclusive lock only briefly during the final catalog swap. It handles TOAST storage correctly. Requirements: a primary key or unique NOT NULL index, superuser or owner privileges, and roughly twice the table size in free disk space.

Reduce future TOAST overhead

On PostgreSQL 14 and later, consider LZ4 compression for large columns. It trades a small reduction in compression ratio for significantly lower CPU overhead during detoasting:

ALTER TABLE my_table
ALTER COLUMN my_jsonb_column SET COMPRESSION lz4;

This affects new data and data rewritten by VACUUM FULL or CLUSTER.

Prevention

• Tune autovacuum aggressively for tables with large-column churn. Lower autovacuum_vacuum_scale_factor and increase autovacuum_work_mem (or maintenance_work_mem if autovacuum_work_mem is -1) so vacuum can process dead TOAST tuples efficiently.
• After bulk deletes or updates on wide columns, run manual VACUUM ANALYZE rather than waiting for autovacuum thresholds.
• Avoid SELECT * against tables with large JSONB or text columns. Select only the columns the application needs to avoid lazy detoasting overhead and reduce pressure on the TOAST subsystem.
• Monitor TOAST relation sizes explicitly. Most monitoring queries target only the parent table and hide pg_toast growth.

How Netdata helps

• Tracks PostgreSQL table and TOAST size metrics to flag when pg_toast growth diverges from live tuple counts.
• Alerts on long-running transactions and idle in transaction sessions that stall vacuum.
• Monitors autovacuum activity and disk usage trends to catch bloat before the volume fills.
• Correlates query latency spikes with heavy write activity on large-column tables.

Related guides

• How PostgreSQL actually works in production: a mental model for operators
• PostgreSQL ALTER TABLE blocked: zero-downtime DDL patterns
• PostgreSQL autovacuum blocked by long-running transaction: detection and fix
• PostgreSQL autovacuum not running: detection, causes, and fixes
• PostgreSQL autovacuum tuning: per-table thresholds for high-churn workloads
• PostgreSQL blocking queries: finding the root blocker in a lock cascade
• PostgreSQL checkpoint storms: detection, causes, and tuning
• PostgreSQL: checkpoints are occurring too frequently – what to tune
• PostgreSQL connection exhaustion: detection, diagnosis, and prevention
• PostgreSQL connection refused: pg_hba, listen_addresses, and TCP diagnosis
• PostgreSQL: database is not accepting commands to avoid wraparound data loss
• PostgreSQL dead tuples piling up: why autovacuum can’t keep up