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. > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
