Re: Impact of vacuum full...

On Fri, 2006-07-21 at 11:40, Erik Jones wrote:
> Scott Marlowe wrote:
> > On Fri, 2006-07-21 at 10:13, Erik Jones wrote:
> >
> >> Hello, I was wondering if someone could enlighten me as to the impact to
> >> the entire database of running VACUUM FULL against a single table. The
> >> reason I ask is that at company we work for we have a very large number
> >> of queue type tables that fill up and empty out on a regular basis
> >>
> >
> > HOLD ON! Do you empty them by doing something like
> >
> > delete from table
> >
> > with no where clause?
> >
> > If so, then try truncating the table. That will clean it completely and
> > reclaim all the dead space, plus it's faster than delete anyway.
> >
> > If that doesn't help, look at scheduling more aggressive plain vacuums
> > (no just autovacuum, but cron job vacuum on specific tables that you
> > know have a high turnover).
> >
> > Vacuum full is basically admitting your regular vacuum schedule isn't /
> > can't be aggressive enough.
> >
> No!!! The table is filled and entries are deleted one at a time, or in
> groups, but definitely not all at once. So, then what is the
> difference between scheduling regular vacuum on specific tables v.
> scheduling vacuum full on specific tables? Basically, what I want to do
> is to ensure that when I clean out a table row or rows at a time, the
> space is immediately freed up.

Oh, ok. Misunderstood based on your description there.

With regular vacuum, postgresql marks the freed tuples as available, and
the next time someone writes to the table it uses the freed up space.
Eventually, the table should stop growing and reach a kind of "stable
state" where it averages some percentage free (20 to 50% is generally
considered optimal).

If the space used by your table continues to grow, this points to a
possible problem with not having a large enough free space map.

Since regular vacuums are MUCH cheaper in terms of locking and such, it
might be practical to schedule a plain vacuum at the end of any large
deletes that you currently run.

I'd use regular cronned vacuums on the tables that you know grown a lot
(or just hit the whole db and not worry about it) and run occasional
vacuum verbose / vacuum full verbose by hand to see if you have problems
with your Free Space Map being too small.