Deleting rows from a DB table doesn't reduce disk allocation


Customer has received a warning of low space left on disk. To remedy this, they decided to delete a large number of rows from a log table in their PostgreSQL database, but have not seen an increase in the availability of space.


This is intended PostgreSQL behavior. The reason is that the files that are physically allocated on disk for the database contents remain in place regardless they are internally populated with relevant contents or not. When you delete table rows, the space is simply marked by the database as unallocated and is never sent back to the operating system as free for other applications. 

In order to fully recover the disk space you should call VACUUM FULL <table>; for the table which you deleted data from. This causes PostgreSQL to write out a fresh copy of the table, with no empty regions. It does require an exclusive lock while it's running, and while it shouldn't take more than a minute or so (at worst, depending on the volume being released), it's adviseable to run the vacuuming during a scheduled maintenance window, to avoid any undesirable disruption to your business.

Complete information on VACUUM can be found at following link:




Article is closed for comments.