Re: Primary key vs unique index

On Fri, Mar 18, 2011 at 8:38 AM, Voils, Steven M <steve(at)sensorswitch(dot)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#AUTOVACUUM

What 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