Re: 8.0.3 pg_autovacuum doesn't clear out stats table?

>You need 100k pages minimum here.
>
>
>
>>I don't actually understand exactly what this is telling me though!
>>
>>
>
>Take a look at
>http://www.pervasivepostgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
>It's a good overview of the FSM, how it works, and how to understand
>vacuum verbose output. Though, I guess my opinion is biased since I
>wrote it... ;)
>
>
OK, I read that and understand now what is happening - thanks!

(1) I think the FSM was a possible cause of the problem - the
pg_statistics table had nowhere to store the space released by
vacuuming. I have fixed that by increasing FSM space by 10x for now and
will monitor vacuumdb -av output periodically to make sure the database
stays within the bounds of the FSM.

(2) I still think that the 8.0 default vacuum base threshold and scale
mean that the pg_statistic table might never trigger the vacuum
threshold and would like to check my understanding is correct:

The process of analyzing my tables causes some or all of the rows in the
pg_statistic table to be updated.
.
Each row update in postgres causes a new tuple to be created to
represent the new row version. So each updated statistic makes the
pg_stat_all_tables.n_tup_upd for pg_statistic climb by 1. It also makes
the pg_class.reltuples for pg_statistic climb by 1 (new row version).

From README.pg_autovacuum (and I think 22.1.4 of the 8.1.x document
says the same thing):

- If the number of (deletes + updates) > VacuumThreshold, then a
vacuum analyze is performed.

VacuumThreshold is equal to:
vacuum_base_value + (vacuum_scaling_factor * "number of tuples in
the table")

In general deletes is 0 for the pg_statistic table so for the default
values we're looking at

n_tup_upd >= 1000 + (2.0 * reltuples)

to trigger a vacuum.

We know for each increment of n_tup_upd, reltuples will receive the same
increment. Thus if the above expression isn't true for any non-zero
starting values of n_tup_upd and reltuples, it won't ever be true:

If:
n_tup_upd < 1000 + (2.0 * reltuples)
Then:
n_tup_upd+X < 1000 + (2.0 * (reltuples+X))

I see that in 8.1.x this has been resolved by defaulting the scale to
0.4. Rightly or wrongly I have set my scale to 0.3.

Thanks for all the help you've offered so far.

Robin