Re: Vacuum taking an age

On Jan 3, 2008 6:48 AM, Brian Modra <epailty(at)googlemail(dot)com> wrote:
> Hi,
>
>
> I have a pretty "live" table: rows being inserted and updated more
> than once 1 per second, though far, far more inserts than updates.
>
> There are currently over 3 million rows.
>
> It has not been vacuumed for months.

How many rows per second? 1? all of them? Kinda makes a difference.

If it was 1 a second updated for 3 months that's about 7million dead
rows. If it was all 3million, then that's 7million * 3million dead
rows, also know as a whole bunch of rows.

Either way, you probably have a table so terribly bloated that a
regular vacuum will not help you in terms of speeding it up. Regular
vacuums are like brushing your teeth three times a day. If you've
forgotten for three months, brushing them once isn't likely to fix all
the cavities you've got. Same thing here. You'll either need a
vacuum full or a cluster. Cluster is often faster. Or you can try
selecting everything into a temp table, truncating the real table, and
inserting the data back in. Truncation will remove all rows, dead or
otherwise. The advantage is that it's often faster to truncate /
reload than it is to vacuum full. If you have indexes, you might want
to drop them while re-inserting and then recreated them.