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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
