On 11/10/2012 02:21 PM, Jeff Janes wrote:
On Fri, Nov 9, 2012 at 4:28 PM, Lists <lists@xxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
