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

On Fri, Nov 9, 2012 at 4:28 PM, Lists <lists(at)benjamindsmith(dot)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.

> 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.

Cheers,

Jeff