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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
