Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA44543 for ; Mon, 30 Aug 1999 10:20:56 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA05489; Mon, 30 Aug 1999 10:20:19 -0400 (EDT) To: "Steven M. Wheeler" cc: pgsql-sql@hub.org Subject: Re: [SQL] Questions about vacuum analyze In-reply-to: Your message of Mon, 30 Aug 1999 07:36:10 -0500 <37CA7ABA.7CFFC6F5@sabre.com> Date: Mon, 30 Aug 1999 10:20:18 -0400 Message-ID: <5487.936022818@sss.pgh.pa.us> From: Tom Lane "Steven M. Wheeler" writes: > Does anyone have recommendations regarding vacuum analyze? > Specifically: > 1) Should it be run on a system that is quiet or will it run acceptably > with other processes accessing the DB? Vacuum will run fine, but it obtains an exclusive lock on each table while it is working on that table. You are likely to find your other processes waiting for the lock... > 2) How often should it be run? How fast does your database change? I'd doubt that running vacuum analyze, as opposed to a plain vacuum, needs to be done very often --- it's unlikely that the statistics vacuum analyze measures are changing that fast, especially not in million-row tables. The other function of vacuum is reclaiming space from dead tuples, and the need for that depends on how often you update or delete tuples. I'm just guessing here, but a rule of thumb might be that it's worth vacuuming when something like 20% of the tuples in your table are dead, ie, the number of updates/deletes you've done since last vacuum is about 20% of the table row count. 6.5 seems to have some performance problems with vacuuming large indexes, BTW. We are looking into that, but in the meantime you might experiment with dropping indexes on a table, vacuum table, recreating indexes to see if that's faster than a straight vacuum. regards, tom lane