Re: Full Vacuum/Reindex vs autovacuum

On 11/08/10 10:50 AM, Jason Long wrote:
> I currently have Postgres 9.0 install after an upgrade. My database is
> relatively small, but complex. The dump is about 90MB.
>
> Every night when there is no activity I do a full vacuum, a reindex, and
> then dump a nightly backup.
>
> Is this optimal with regards to performance? autovacuum is set to the
> default.

if you have frequently updated tables that are accessed mostly from
their primary key, it may pay to CLUSTER those tables on said index
rather than doing the full vacuum.

VACUUM FULL is usually not recommended, btw.

Also, if you have tables that get lots of updates that only affect data
and not indexed columns, setting a FILL FACTOR of, say, 70 or 80 (its in
%) might help with performance by better facilitating HOT updates (HOT
is a internal feature added to pg 8.3 to speed up these sorts of updates)