Re: dead tuples and VACUUM

On Sat, May 31, 2003 at 02:15:04PM -0400, Dima Tkach wrote:
> First of all, it is my understanding that this monthly job will
> double the size of the table (create a copy of every tuple it is
> updating). Is that right?

Sort of.  Read on.

> Now, if I run VACUUM on that table, it is supposed to reclaim those dead
> tuples.. Will it actually? I remember some discussions about 7.2, where
> it was mentioned that plain (not FULL) VACUUM doesn't move rows between
> pages (so, it seems to me, that if every row is updated, plain vacuum is
>  useless). Is it any better in 7.3? Or does it still have to be VACUUM
> FULL?

If you replace every tuple in the table, you probably don't have a
large enough fress space map to track all of that.  So VACUUM FULL is
a good idea.

But another answer is to VACUUM every (say) couple thousand UPDATEs.
That'll keep the table size managable.  You could even run a parallel
VACUUM.  If the whole table is UPDATEd in one transaction, though,
that won't help.

> And finally, if I do *not* run VACUUM, and let it just sit there with
> the doubled number of tuples, what will happen next month, when every
> row gets updated again? Will it be able to reuse the dead tuples then,
> or will it just keep creating the new ones?

It can't use the free space until you've run VACUUM.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110