On Tue, Dec 3, 2013 at 2:04 PM, john gale <john@smadness.com> wrote:
>
> Due to running low on disk space, we have recently removed a majority of rows from a table to an archival DB.
>
> Although VACUUM allows disk space to be re-used, VACUUM FULL is the only one that actively reclaims disk space for
useby the OS. http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html
>
> For a variety of reasons I would prefer disk usage to be as low as possible, thus I would like to run a VACUUM FULL
duringsome maintenance cycle (since it exclusively locks the table). However, given the details of VACUUM FULL:
>
>> VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This
minimizesthe size of the table, but can take a long time. It also requires extra disk space for the new copy of the
table,until the operation completes.
>
> Does this suggest that VACUUM FULL needs free disk space on the order of the full size of the table that it's
vacuumingto be able to complete? Or does it / can it write the filesystem files in the 1GB chunks stored in /base
whileremoving the new "unused" files at the same time, thus requiring only a few GB of free space?
Yes, starting with pgsql 9.0 vacuum full writes a new copy of the
table out while holding the old copy locked. If the new copy is going
to be small then it's not too big of a problem. If it will be large
then you need the space to write it out. Note that sometimes making a
tablespace on another drive with enough room for the table to fit on
twice and using that can be helpful.
