vacuum full taking much longer than dump + restore?

From: Dragan Matic <mlists(at)panforma(dot)co(dot)yu>
To: pgsql-general(at)postgresql(dot)org
Subject: vacuum full taking much longer than dump + restore?
Date: 2006-03-20 09:50:42
Message-ID: 441E7AF2.2050202@panforma.co.yu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have a few larger tables (~3,5 million rows largest, ~ 1 million rows
smallest) on our production database which had at least one column
defined as char(nn) (nn being larger or equal to 60).
I did an alter table where I changed the definition of those columns to
varchar(nn), and after that did a 'UPDATE TABLE some_table SET
column_name = RTRIM(column_name)'.
Since it effectively doubled the number of rows I decided to do a vacuum
full. After some 10 hours I had to stop it since it was monday morning,
and vacuum was blocking the querys. After thad I did a pg_dump and a
restore and it was finished in about an hour and a half, with additional
15-20 minutes of vacuum analyze. I'm guessing that this
dump->restore->analyze has done effectively the same thing what vacuum
full was supposed to do. How is it possible that vacuum full was so
slow, are there some configuration parameters that might be
misconfigured? I am using fedora core 4 with pre-built 8.1.3 rpms.
Server has 2 gb of ram.

Tnx in advance

Dragan Matic

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guido Neitzer 2006-03-20 09:52:36 Difference between "add column" and "add column" with default
Previous Message Rushabh Doshi 2006-03-20 07:40:25 Re: db sever seems to be dropping connections