Re: Still recommending daily vacuum...

Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Jim C. Nasby wrote:
> >>FWIW, I normally go with the 8.2 defaults, though I could see dropping
> >>vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds
> >>could be decreased further, maybe divide by 10.
> >
> >How about pushing thresholds all the way down to 0?
>
> As long as it handles small (or zero row) tables ok then yes. The
> base_threshold in the originial contrib autovacuum was just an easy way
> to not vacuum really small tables too often. If a table has only 10
> rows, it's going to get vacuumed every time one row is updated. I guess
> that's not a big problem with a table that small but still seems excessive.

Well, if a table has 10 rows, and we keep the current threshold of 1000
rows, then this table must have 1002 dead tuples (99% dead tuples, 1002
dead + 10 live) before being vacuumed. This seems wasteful because
there are 500 dead tuples on it and only 10 live tuples. So each scan
must wade through all the dead tuples.

Another small table with 100 tuples will be vacuumed on every iteration
as well, even if there are just two dead tuples. So you are right --
maybe dropping it all the way to 0 is too much. But a small value of 10
is reasonable? That will make the 10 tuple table be vacuumed when there
are 10 dead tuples (50% of dead tuples), and the 100 tuple table when
there are 11 (11% of dead tuples). It decreases quickly to the scale
factor (2%, or do we want to decrease it to 1%?)

Does this sound acceptable?

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
(http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)