Re: detection of VACUUM in progress

On Wed, 2005-08-31 at 07:18, Bohdan Linda wrote:
> >
> > From the postgresql manual
> > http://www.postgresql.org/docs/8.0/interactive/maintenance.html :
> > " The standard form of VACUUM is best used with the goal of maintaining
> > a fairly level steady-state usage of disk space. If you need to return
> > disk space to the operating system you can use VACUUM FULL ? but what's
> > the point of releasing disk space that will only have to be allocated
> > again soon? Moderately frequent standard VACUUM runs are a better
> > approach than infrequent VACUUM FULL runs for maintaining
> > heavily-updated tables."
> >
> > From this I conclude that an ordinary VACUUM is sufficent to your
> > purpose cause you insert/delete almost the same amount of data daily.
> >
> > But then again I can be mistaken so if anyone can back me up here or
> > throw the manual on me will be nice ;P
>
>
> If I vacuum only the table, the records may be used by new lines, that is
> fine. Problem is, that when creating select on such table, it takes more
> pages to be read from the IO (it will read laso deleted rows) thus the
> select will last a bit longer.

It really depends on what percentage of rows you're updating.

If you are updating 240,000 rows a day, and have a database with 100M
rows, then that's not too bad. Regular vacuums once a day would be
plenty.

If you're updating 240,000 rows a day, spread out over the day, and you
have a table that has 10,000 rows, then you will need to run vacuum far
more often to keep the table at a steady state of 10,000 to 20,000 rows.

If you're doing the 240,000 updates all at once on a small table, then
you might well be a candidate for a vacuum full.

So, again, it's about percentages really. If 240k rows represents 1% of
your table, then daily, regular vacuums will do fine.

Personally, I just install pg_autovacuum and check on it once a week or
so to make sure it's doing its job.