"Thomas H." <me (AT) alternize (DOT) com> writes:
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?

Killing it should not corrupt anything; if it does that IS a serious bug
and will be met with a full alarm response...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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?

after "a while" it eventually finished just in time before the hd was filled
up :-)

2006-11-27 07:18:04 [2096] LOG: duration: 6093332.000 ms statement: VACUUM
FULL VERBOSE ANALYZE forum.posts

thanks,
- thomas



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

will give it a try later on, thanks!

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

Thomas H. wrote:
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