Roberto Grandi <roberto.grandi@trovaprezzi.it> wrote:
> we are using postgres 8.3.
> my scenario is:
>
> - Delete all products record for a vendor
> - Reload all products record (from new listing) for the same
> vendor.
>
> Obviously we repeat this process continously and table space is
> growing really fast.
>
> Can you suggest me an approach for autovacuum within this
> scenario and, if you want, suggest me an appropriate version of
> postgres that help solving my problem?
At this point I would recommend the latest minor release of 9.2 for
production use. If you were early in a development cycle I would
suggest considering the soon-to-be-released 9.3.0. Be sure to stay
current on minor releases.
http://www.postgresql.org/support/versioning/
If your table space is growing fast with this usage pattern, it
suggests that autovacuum is not configured to be aggressive enough.
My suggestions:
Make sure autovacuum is on.
Decrease autovacuum_naptime to 15s, so that it will notice deletes
sooner.
You could consider reducing autovacuum_scale_factor below the
default of 0.2 so that it triggers based on fewer deletes.
You should probably set autovacuum_vacuum_cost_limit to 400 and
incrementally increase it until autovacuum is able to keep up with
the activity you describe. It defaults to 200 and I have had to
set it to 650 on some systems to allow it to keep up. It wouldn't
be surprising if some systems need a higher setting. Higher
settings may cause autovacuum activity to have a more noticeable
impact on foreground processes; but if it is too low, you will
develop bloat which will harm performance and eat disk space.
If all autovacuum workers are sometimes busy with big tables for
extended periods and you see other tables neglected for too long,
you should boost autovacuum_max_workers until that problem is
solved.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company