Returning free disk space

Usually, administrators are surprised to find that there is no reduction in disk space from using vacuum. There is only one situation where vacuum can actually reduce the size of a table. If the last data page of a table is empty, completely free of rows, and it's possible to obtain an exclusive lock on the table, vacuum executes a special disk release process. It will scan backward from the end, returning all pages it finds to the operating system as newly free space, until it finds a data page that isn't empty.

Accordingly, it's only possible for a regular vacuum to reduce the size of a table if it has a contiguous chunk of free space at the end of the table. One common situation that can produce this pattern of disk usage is when a table includes a fixed time period of data, with new records constantly inserted and older ones periodically deleted. If those deletions are cleaned up properly with vacuum, eventually you can expect that the table size will reach a steady-state.

In most other situations, vacuum will never release disk space. In regular operations, your goal should be for vacuum to run often enough that there is never a large amount of free space in any table to release. However, this is sometimes unavoidable; a large deletion of historical data is one way to end up with a table with lots of free space at its beginning.