Why does database disk usage keep growing after we delete rows?

Deleting rows in PostgreSQL usually frees space for future reuse inside the table, not immediate shrinkage of the table file on disk. That is normal MVCC behavior. First confirm whether autovacuum or manual VACUUM is clearing dead tuples, then look for a long-running transaction or snapshot that still needs old row versions. Only after that should you plan a rewrite such as VACUUM FULL, because it takes stronger locks and extra disk.

Why doesn’t DELETE shrink the file right away?

PostgreSQL keeps old row versions long enough for concurrent transactions to see a consistent snapshot. The PostgreSQL routine vacuuming documentation says an UPDATE or DELETE “does not immediately remove the old version of the row,” and that standard VACUUM “marks the space available for future reuse.” That means your table can stop growing as free space gets reused even when the filesystem size barely changes.

This is the key distinction: reusable table space is not the same thing as returned filesystem space. Standard VACUUM is the routine fix because it can run alongside normal SELECT, INSERT, UPDATE, and DELETE. A rewrite operation is different: VACUUM FULL compacts the table into a new copy, needs an ACCESS EXCLUSIVE lock, and requires extra disk while it runs, according to the same documentation.

What can prevent old row versions from being reclaimed?

A long-running transaction is a common reason. If a reporting job, idle transaction, or repeatable snapshot started before the deletes, PostgreSQL may need to keep those old row versions visible to it. In that case, more aggressive vacuuming will not solve the root cause yet.

Use PostgreSQL monitoring statistics documentation for two views with different jobs. pg_stat_activity shows “the exact command currently being executed” and current connections, which helps you find old sessions. pg_stat_all_tables tracks table row counts and “information about vacuum and analyze actions,” which helps confirm whether vacuum is running and whether dead tuples are accumulating.

One practical caution from the monitoring docs: statistics can lag and can be cached for the duration of your current transaction. If you keep querying stats from a long transaction, you can fool yourself with stale numbers.

How do I decide between more vacuum and a rewrite?

Use this checklist:

1. Check whether the table is actually still growing, or whether new writes are reusing freed space.
2. Check pg_stat_all_tables for dead tuples and recent vacuum activity.
3. Check pg_stat_activity for long-lived sessions, especially idle transactions or long reports.
4. If old snapshots exist, end or redesign those workloads first.
5. If routine churn is the pattern, tune autovacuum or schedule plain VACUUM more appropriately.
6. If you truly need the file smaller now, schedule VACUUM FULL in a maintenance window and reserve temporary disk for the rewritten copy.

Worked example: suppose you delete 200 GB from an events table, but nightly analytics keeps one transaction open for hours. Standard vacuum runs, yet the file size does not drop. The right move is not repeated VACUUM FULL attempts during business hours. First fix the analytics transaction boundary so snapshots end promptly. Then let regular vacuum reclaim pages for reuse. Only choose VACUUM FULL if you need that 200 GB returned to the operating system immediately.

Will running vacuum again and again help?

Only if dead tuples are now reclaimable. If an old snapshot is still open, repeated vacuum mostly adds I/O without changing the outcome.

How much extra disk should I plan for reclamation?

For standard VACUUM, think mainly about I/O impact, not a second full copy. For VACUUM FULL, plan for extra space because PostgreSQL writes a complete new version of the table before replacing the old one.

Next step: inspect pg_stat_activity and the target table’s pg_stat_all_tables entry together before scheduling any rewrite.

Reviewed: 2026-09-05