On Fri, Mar 18, 2011 at 8:38 AM, Voils, Steven M
<steve@sensorswitch.com> wrote:
What are the general guidelines under which autovacuum will trigger? I was unaware it was turned on by default for the newer versions. Would it be worthwhile to leave the manual vacuuming on? Currently it runs immediately after large sections of the tables are deleted. Or would it be expected that autovac would pick these changes up and run anyway?
Autovacuum will tend to run after those types of changes. As described here:
http://www.postgresql.org/docs/8.4/interactive/routine-vacuuming.html#AUTOVACUUMWhat PostgreSQL looks for is a portion of the tuples to be obsoleted. The configuration is essentially "if some portion (percentage) of the table is obsolete, vacuum it" but also has an added scalar (base threshold) which is required on top of that portion (scale factor).
My understanding is that the base threshold is there to prevent small tables from being vacuumed for little or no reason, but for large tables it should be insignificant in comparison to the scale factor. So if your scale factor is .5, when you delete half of your table, you can expect an autovacuum to run on the next iteration of the daemon.
Note that the default scale factor is .2 (20%) and the default base threshold is 50. Both can be modified for the cluster as well as for individual tables.
As someone else already alluded, VACUUM FULL is generally bad for indexes, where VACUUM will help indexes (by reclaiming space the same way as it does for the table). More details on the same page linked above.
Derrick