Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)

On 11/10/2012 02:21 PM, Jeff Janes wrote:
> On Fri, Nov 9, 2012 at 4:28 PM, Lists <lists@benjamindsmith.com> wrote:
>
>
> ...
>
>> 3) For each of the tables from #2, run the commands
>> REINDEX TABLE $table;
>> VACUUM FULL ANALYZE $table;
>>
>> The end result is a squeaky-clean database server with expected disk usage.
>>
>> NOTES:
> ...
>>
>> 2) It was sheer chance that I discovered the need to reindex prior to vacuum
>> in order to get the disk space back.
> As of 9.0, a "vacuum full" inherently does a reindex, so doing an
> explicit one is neither necessary nor beneficial.
>
> I don't know if your discovery is based on a non-full vacuum, or on an
> older server.

I can only state that merely doing a "vacuum full" or "vacuum full
$tables" sequentially did not free the space, whereas the sequential
reindex $table, each followed immediately by a vacuum full $table) did.
If you'd like I can easily recreate the scenario by simply not "cleaning
up" one of the DB servers until it bloats up and make available (limit
distribution) a binary copy of the database (EG: rsync the
/var/lib/pgsql/ filesystem late at night) in order to help identify why
it didn't work as expected.

>
>> 5) I don't yet know if the "full" option for the vacuum is necessary to free
>> up all space. I will experiment with this and post results if useful.
> The answer to this is mostly non-deterministic.  non-full vacuum can
> only free space from the "end" of the table.
>
> If all of your long-lived objects were created before pg_attribute got
> bloated and so the bloat was due only to short-lived objects, then
> non-full vacuum (if run often enough) should eventually be able to
> return that space as the short-lived objects near the end start to go
> away.  However, if even a single long-live object finds itself at the
> end of the table, then only a vacuum full will ever be able to reclaim
> that space.
>

Since the time period involved (weeks/months) would have included both a
large number of created/destroyed temp tables and occasionally altered
persistent objects it would appear that the full option a very good
idea, at least periodically.

-Ben