Re: Primary key vs unique index

Thanks for the help. I think we're only going to support pg 8.4 onwards. I'll turn off the application's manual vacuuming and just let autovac do its thing.

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Monday, March 21, 2011 9:13 PM
To: Voils, Steven M
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Primary key vs unique index

On Fri, Mar 18, 2011 at 6: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?

Until 8.3 autovacuum was more of a proof of concept rather than
production ready code. By 8.3 two things had happened, vacuum
costing, which is important so you can tune vacuuming / autovacuuming
to your hardware and usage patterns, and multi-threaded autovacuuming
daemon, which meant that autovac could now handle the scenario where
one or more table would take a long (sometimes very long) time to
vacuum, especially with costing factors slowing it down, and another
table would get bloated while waiting its turn. With a server with
LOTS of random IO capability you can run quite a few threads at once,
since each one is only a small impact against the maximum IO of the
drive array. If you've got 1,000 tables and a couple dozen big ones
that can take 30 minutes or more to vacuum, it's a good thing to be
able to run autovac on more than one at a time.

The next HUGE improvement came with 8.4, which took the free space map
and put it on the drives, removing the need to constantly monitor and
adjust free space map to prevent blowout. If you've got a well tuned
<= pg 8.3 you're ok. If you need to tune an older version, it's often
easier AND safer to migrate to 8.4 or above.