>> 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.
will give it a try later on, thanks!
>
> 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.
the problem is: the table was far from being bloated, IMO. it was 2 days
old, every record at most 2-3 times updated. the space needed for the table
dropped from 400mb to roughly 200mb after the 1.5hr vacuum full...
i've never had such a long vacuuming time before, even on tables that are
much larger and contains more dead rows. the table uses tsearch2 and a
gin-index, could that be the problem? the gin faq says a drop/create index
would be much faster than a reindex. maybe this is also true when vacuuming
a table with a gin-index?
- thomas
