Thomas H. wrote:
>>> this somehow sounds buggy:
>>
>> vacuum full absolutely *will* bloat your index, if run on a
>> heavily-modified table. I do not think it will bloat pg_xlog by itself
>> however; are you sure you don't have some other open transactions?
>
> well yes, as the system is "live", users are browsing the website. but
> all queries that try to access the table in question are stalled at the
> moment. when querying server status i'm seeing lots of queries that are
> waiting for access to the table.
>
> would vacuum freeze be faster?
Vacuum freeze won't move tuples so it won't reclaim any more space than
a normal vacuum. Cluster, however, rewrites the whole table and compacts
the space, and runs faster than vacuum full on a badly bloated table. It
will also recreate all indexes.
In the future, instead of updating a whole table with UPDATE, you should
consider doing a SELECT INTO to create a new table, dropping the old
table and renaming the new one in place of the old one.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
