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.marlowe@xxxxxxxxx] Sent: Monday, March 21, 2011 9:13 PM To: Voils, Steven M Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Primary key vs unique index On Fri, Mar 18, 2011 at 6:38 AM, Voils, Steven M <steve@xxxxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
