On Dec 3, 2013, at 3:53 PM, Andreas Brandl <[email protected]> wrote:
> John,
>
>> ...
>> 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 during some
>> maintenance cycle (since it exclusively locks the table). However,
>
> you might want to consider setting fillfactor to 100 [1] to completely
> compact the table (before doing a VACUUM FULL).
>
> Though I'm not 100% sure but I assume that VACUUM FULL considers the
> fillfactor when rewriting the table (maybe someone can comment on this?).
In the 9.0 documentation (which we're on) it suggests fillfactor is 100 by
default, and I don't believe the table creation overrode these. This likely
means that incoming data will attempt to re-use pages with deleted rows? It
does seem that our database growth has slowed (as reflected with on-disk
space), but it never decreases, which is why I was hoping to VACUUM FULL.
>> 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 minimizes the
>>> 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 vacuuming to be able to
>> complete? Or does it / can it write the filesystem files in the 1GB
>> chunks stored in /base while removing the new "unused" files at the
>> same time, thus requiring only a few GB of free space?
>
> AFAIK a VACUUM FULL frees the old data after having completely written the
> new version. So the size of the original table is an upper bound for the
> space requirement and it can be much less (in case the original table is
> bloated a lot).
That's what I'm afraid of. Thanks for the anecdote,
~ john
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
