Re: Does VACUUM ever free up any disk space?

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> On Thu, 11 Sep 2003, Chris Miles wrote:
>> I've read a lot where people recommend using VACUUM FULL
>> to free up disk space, especially after many updates/inserts.

> A regular vacuum since 7.2 never actually frees up space, it simply marks
> the space in the file as available for reuse. Note that in many
> circumstances this is actually better than freeing up the space, as it
> allows the database to store date without having to extend and possibly
> fragment the table.

Regular vacuum *will* shorten the table's file if (a) there are some
completely-empty pages at the end, and (b) it can get an exclusive lock
on the table without blocking. This might be a relatively rare
condition in a heavily-used table. But "never actually frees up space"
is incorrect.

You're correct that regular vacuum is designed around the idea of
maintaining a steady-state file size rather than trying very hard to
give space back to the OS.

regards, tom lane