Postgres Dead Tuples: Diagnose and Fix Table Bloat (2026)

Postgres dead tuples are a symptom, not the disease itself, and they signal that your database is actively losing performance and disk space.

Imagine your Postgres table is like a meticulously organized library. When you update a row, you don’t actually change the book on the shelf. Instead, you write a new version of the book and leave the old one there, marking it as "outdated." These "outdated" books are dead tuples. Over time, if they aren’t cleaned up, they clog the aisles, making it harder and slower to find the books you need. This phenomenon is called "table bloat."

Here’s how to diagnose and fix it:

Diagnosing Bloat

The primary tool for understanding bloat is the pgstattuple extension. You’ll need to install it first:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Then, you can query it for a specific table:

SELECT * FROM pgstattuple('your_table_name');

This will give you a wealth of information, but the key metrics for bloat are:

• dead_tuple_count: The number of dead tuples.
• tuple_len_dead: The total disk space occupied by dead tuples.
• avg_width_dead: The average size of a dead tuple.

A high dead_tuple_count or tuple_len_dead relative to the live_tuple_count or tuple_len_live indicates significant bloat. A general rule of thumb is if dead tuples occupy more than 20-30% of the table’s total size, it’s time to act.

You can also get an overview of bloat for all tables in a database with this query:

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
    pg_size_pretty(pg_total_relation_size(C.oid) - pg_total_relation_size(CASE WHEN pg_relation_size(C.oid) <> 0 THEN C.oid ELSE NULL END)) AS bloat_size,
    CASE WHEN pg_total_relation_size(C.oid) <> 0 THEN
        ROUND(
            (pg_total_relation_size(C.oid) - pg_total_relation_size(CASE WHEN pg_relation_size(C.oid) <> 0 THEN C.oid ELSE NULL END)) / pg_total_relation_size(C.oid)::numeric,
        2)
    ELSE 0 END AS bloat_pct
FROM
    pg_class C
LEFT JOIN
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE
    nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND NOT EXISTS(
        SELECT 1
        FROM pg_stat_partitions
        WHERE
            schemaname = N.nspname AND relname = C.relname
    )
ORDER BY
    bloat_size DESC;

Common Causes and Fixes

1. Frequent UPDATE and DELETE Operations: This is the most common culprit. Every UPDATE creates a new version of a row, and DELETE marks the old one for removal.

   • Diagnosis: Use pg_stat_user_tables to check n_live_tup and n_dead_tup for your tables.
   • Fix: VACUUM FULL your_table_name;
     • Why it works: VACUUM FULL rewrites the entire table, discarding all dead tuples and reclaiming disk space. It’s a heavy operation and locks the table, so schedule it during low-traffic periods.
   • Alternative Fix (less locking): TRUNCATE your_table_name;
     • Why it works: TRUNCATE removes all rows from a table and can be faster than VACUUM FULL with less locking, but it also resets the table’s storage and reclaims all space. Be aware that TRUNCATE is not transaction-safe in the same way as DELETE and cannot be rolled back.

2. Autovacuum Not Keeping Up: Postgres has an automatic vacuuming process (autovacuum) designed to clean up dead tuples. If your write/update volume is high, or autovacuum is misconfigured, it might not run often enough.

   • Diagnosis: Check pg_stat_activity for running autovacuum processes. Look at pg_settings for autovacuum_max_workers, autovacuum_naptime, autovacuum_vacuum_threshold, and autovacuum_vacuum_scale_factor.
   • Fix: Adjust autovacuum settings. For example, to make it more aggressive for a specific table:

     ALTER TABLE your_table_name SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.1);
     

     • Why it works: autovacuum_vacuum_threshold is the minimum number of deleted/updated tuples before a vacuum is triggered, and autovacuum_vacuum_scale_factor is a fraction of the table size. Lowering these makes autovacuum run more frequently for tables with high churn.

3. Large Transactions: Very long-running transactions that perform many updates or deletes can prevent dead tuples from being cleaned up, even by autovacuum, because older transactions might still need to see those rows.

   • Diagnosis: Monitor pg_stat_activity for long-running transactions.
   • Fix: Break down large transactions into smaller ones. Ensure your application logic doesn’t hold transactions open longer than necessary.
     • Why it works: Shorter transactions complete faster, allowing VACUUM (both manual and autovacuum) to see and clean up dead tuples sooner.

4. Row Versioning and MVCC: Postgres uses Multi-Version Concurrency Control (MVCC), which inherently means old row versions (dead tuples) exist. This is normal, but excessive churn exacerbates the problem.

   • Diagnosis: This is more of a conceptual cause than something directly diagnosable with a single query. Observe your write patterns.
   • Fix: Optimize queries that cause high churn. Consider using INSERT ... ON CONFLICT DO UPDATE or UPSERT operations carefully, as they still generate new row versions. Sometimes, denormalization or archiving strategies can reduce the need for frequent updates on core tables.
     • Why it works: Reducing the rate at which dead tuples are generated is as important as cleaning them up.

5. Indexes: While indexes are crucial for read performance, they also store dead tuple information. If a table is heavily indexed and experiences high churn, indexes can also become bloated.

   • Diagnosis: Use pgstattuple('your_index_name') on the indexes of a bloated table.
   • Fix: REINDEX TABLE your_table_name; or REINDEX INDEX your_index_name;
     • Why it works: REINDEX rebuilds an index, removing dead tuple entries and reclaiming space within the index. Like VACUUM FULL, it can lock the table. REINDEX CONCURRENTLY is an option that minimizes locking but takes longer.

6. Partitioning Issues: If you’re using table partitioning and not actively managing old partitions (e.g., dropping or archiving them), dead tuples can accumulate within those partitions.

   • Diagnosis: Check bloat on individual partitions if your table is partitioned.
   • Fix: Implement a strategy for detaching, archiving, or dropping old partitions. For example, to detach a partition: ALTER TABLE parent_table DETACH PARTITION child_partition; followed by DROP TABLE child_partition;.
     • Why it works: Removing entire old partitions is the most efficient way to reclaim space and eliminate bloat from historical data that is no longer actively queried.

After performing a VACUUM FULL or REINDEX, you’ll likely need to ANALYZE your table to update statistics for the query planner: ANALYZE your_table_name;.

The next error you’ll hit after fixing table bloat is likely related to index bloat if you only addressed table bloat, or potentially performance degradation on specific queries if your autovacuum settings are still too conservative.