PostgreSQL Bloat: Why Your Database Grows After Deletes

Updated 6 November 2026 · Standard formula, shown below

PostgreSQL bloat is the invisible growth of your database even after you delete rows, because deleted data is not physically removed from disk—it is merely marked as dead and left in place until a future VACUUM reclaims it. This article explains exactly why that happens, how to measure bloat, and what you can do to keep your database lean.

What Is PostgreSQL Bloat?

PostgreSQL uses a multi-version concurrency control (MVCC) system. When you UPDATE or DELETE a row, PostgreSQL does not overwrite the old version. Instead, it creates a new row version (for UPDATE) or simply marks the old one as deleted. The old version, called a “dead tuple,� remains on disk so that other transactions that started earlier can still see the data as it was. Only after all transactions that could see the dead tuple finish can it be safely removed.

This design gives you strong consistency and allows concurrent reads and writes without locking, but it comes at a cost: dead tuples accumulate, and the table files grow. Over time, if VACUUM does not keep up, the database can become much larger than the actual live data, degrading query performance because the database must scan through dead rows.

How VACUUM Reclaims Space

VACUUM is the process that removes dead tuples. It scans the table, marks dead tuples as free space, and updates the free space map. However, VACUUM does not shrink the physical file size; it only makes the space available for reuse by new inserts. So after a large delete, the table file remains the same size on disk until new data fills the reclaimed space.

To actually shrink the file, you need VACUUM FULL, which rewrites the entire table into a new file, or CLUSTER, which sorts and rewrites. These operations take locks and can be disruptive on a production system, so they are typically done during maintenance windows.

Why Does Bloat Accumulate Even with Autovacuum?

Autovacuum runs automatically based on thresholds and cost limits, but it can fall behind for several reasons:

Heavy write activity – If you delete or update rows faster than autovacuum can process, dead tuples pile up.
Long-running transactions – A transaction that stays open for a long time prevents vacuum from removing any dead tuples that it might still see, effectively freezing the cleanup.
Large tables – Autovacuum is cost-based; it throttles itself to avoid interfering with normal operations. On a huge table, a full vacuum may take hours or days.
Misconfigured thresholds – The default thresholds (e.g., 20% of the table) may be too high for tables that experience frequent small deletes.
Replication slots – If you use logical replication, the slot keeps the WAL (write-ahead log) and can also prevent cleanup of old row versions.

Even when autovacuum runs, it only reclaims space for reuse—it does not reduce the file size. So the database file can stay large indefinitely if the freed space is not reused.

How to Measure Bloat

You can estimate bloat with a query that compares the actual table size with the amount of live data. A common approach uses the pgstattuple extension:

SELECT * FROM pgstattuple('your_table');

This returns dead_tuple_count and dead_tuple_len. Alternatively, you can use the pg_stat_user_tables view to see the number of dead tuples and the last autovacuum time:

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

For a more precise estimate, you can use the pgstattuple extension or a query that samples pages. Many DBAs use a query that estimates bloat based on the average row size and the number of dead tuples, but the most reliable way is to run VACUUM VERBOSE and see how many dead tuples were removed.

How to Reduce Bloat

If you already have significant bloat, here are the steps to reclaim space:

Run VACUUM (or VACUUM ANALYZE) – This removes dead tuples and updates statistics. It does not shrink the file, but it makes space available.
Run VACUUM FULL – This rewrites the table and returns unused space to the OS. It takes an ACCESS EXCLUSIVE lock, so plan for downtime.
Use pg_repack – This tool rewrites the table without blocking reads and writes, but it is not included in the standard distribution.
Consider CLUSTER – If you want to physically reorder rows by an index, CLUSTER rewrites the table and also reclaims space.
Adjust autovacuum settings – Increase the frequency or reduce the cost limits for problem tables, e.g., ALTER TABLE your_table SET (autovacuum_vacuum_scale_factor = 0.05);

After a major cleanup, you should also monitor the table size to confirm it has shrunk.

Common Mistakes and Misconceptions

Mistake: Assuming VACUUM shrinks the file. It doesn’t—only VACUUM FULL or rewriting does.
Mistake: Ignoring index bloat. Indexes also accumulate dead tuples and can be even larger than the table. Reindex or use REINDEX INDEX after a big delete.
Mistake: Thinking autovacuum is enough. Autovacuum is reactive and can be overwhelmed by sudden massive deletes.
Misconception: Deleting data frees disk space immediately. It doesn’t; it just marks rows as dead.
Misconception: Bloat is only a disk-space issue. Bloat also slows down sequential scans and index lookups because the database must read more pages.

Preventing Bloat in the Future

Prevention is better than cure. Here are practical steps to keep bloat under control:

Monitor n_dead_tup and set alerts when it grows.
Keep autovacuum enabled and tune it for large or frequently updated tables.
Break up large deletes into smaller batches to give autovacuum a chance to keep up.
Use TRUNCATE instead of DELETE when you need to remove all rows—it doesn’t create dead tuples.
Avoid long-running transactions, especially those that stay open for hours.
If you use logical replication, monitor replication slots and remove them when not needed.

Frequently Asked Questions

Does VACUUM FULL lock the table?

Yes, VACUUM FULL takes an ACCESS EXCLUSIVE lock, which blocks all reads and writes to the table. It is not suitable for high-availability production systems without a maintenance window. Use pg_repack if you need to rebuild a table without blocking.

Why is my table still large after VACUUM?

VACUUM only marks dead tuples as reusable space; it does not return the space to the operating system. The file size stays the same until the space is reused by new rows or you run VACUUM FULL. Check the free space map usage to see if the space is being reused.

How can I tell if a table is bloated?

Compare the table’s actual size on disk with the amount of live data. Use pg_total_relation_size and pgstattuple to see dead tuple bytes. A table with more than 20% dead tuples is often considered bloated, but the threshold depends on your workload.

Does bloat affect query performance?

Yes. Sequential scans must read dead tuples, and index scans may need to fetch heap pages that contain only dead rows. This increases I/O and CPU usage. In extreme cases, bloat can make queries several times slower.

Can I prevent bloat by using DELETE instead of UPDATE?

No. DELETE also creates dead tuples. In fact, UPDATE creates both a dead tuple (the old version) and a new live tuple, so it can cause even more bloat. The only way to avoid dead tuples is to use TRUNCATE (for full-table deletes) or to design your application to minimize row churn.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.