Re: Vacuum wait time problem

On Fri, Feb 13, 2009 at 3:34 PM, Tino Schwarze <postgresql(at)tisc(dot)de> wrote:
> On Fri, Feb 13, 2009 at 03:13:28PM -0700, Scott Marlowe wrote:
>
> [...]
>
>> Yeah, that's pretty bad. ~2 Million live rows and ~18 Million dead
>> ones is a pretty badly bloated table.
>>
>> Vacuum full is one way to reclaim that lost space. You can also dump
>> and restore that one table, inside a drop / create restraints in a
>> transaction during maintenance if you can. A Vacuum full is quite
>> intrusive, so avoid it during normal working hours. Dumping and
>> reloading the whole db may be faster than either a vacuum full or a
>> cluster. A common trick is to do something like:
>>
>> begin;
>> select * into ordermydata from bigbloatedtable order by some_field;
>> delete * from bigbloatedtable;
>> insert into bigbloatedtable select * from ordermydata;
>> commit;
>>
>> This will both put your table in some order which might help, and
>> remove the bloat.
>
> Really? Wouldn't that add even more bloat? How does that work? (I'd
> expect a drop table/create table instead of the delete...)
> Note: I suppose that you know a lot more about PG than I do, so I'm just
> curious.

Whoops, meant
truncate bigbloatedtable;
sheesh, long week.