Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C49A14759BD for ; Fri, 15 Nov 2002 08:24:10 -0500 (EST) Received: from mail.virginia.edu (mail.Virginia.EDU [128.143.2.9]) by postgresql.org (Postfix) with SMTP id A93B947592C for ; Fri, 15 Nov 2002 08:24:09 -0500 (EST) Received: from smtp.mail.virginia.edu by mail.virginia.edu id aa21160; 15 Nov 2002 8:24 EST Received: from jlk3xpc (d-128-19-222.bootp.Virginia.EDU [128.143.19.222]) by smtp.mail.Virginia.EDU (8.11.6/8.11.6) with SMTP id gAFDO8r14162; Fri, 15 Nov 2002 08:24:09 -0500 Message-ID: <002c01c28caa$b18aae60$de138f80@virginia.edu> From: Jodi Kanter To: Robert Treat Cc: Postgres Admin List References: <016e01c28c1d$f9db6340$de138f80@virginia.edu> <1037310288.4723.94.camel@camel> Subject: Re: VACUUM Date: Fri, 15 Nov 2002 08:27:06 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/162 X-Sequence-Number: 6350 Do you have a URL for that document you mention? I get nothing when I search on pgsql-general. Thanks for the help. Jodi ----- Original Message ----- From: "Robert Treat" To: "Jodi Kanter" Cc: "Postgres Admin List" Sent: Thursday, November 14, 2002 4:44 PM Subject: Re: [ADMIN] VACUUM > Honestly this is very dependent on the type of schema you have and what > your application does with it. If you have tables that get a lot of > updates (or both inserts and deletes) you will want to run vacuum > analyze on those tables more often. How often is debatable, but as a > rule of thumb it good to run them vacuum analyze as least once per > turnover of your table (though running it more wont hurt). As for vacuum > full, with frequently enough "lazy" vacuuming you wont need to vacuum > full very often. If you can afford to lock your tables, then I'd say do > it once a night. If not, you need to decide when is a good time, but I > think the docs recommend doing it at least once every 1 billion > transactions, though more often is definitely better here too. As far > as sequences go, search the archives for pgsql-general, tom lane just > posted a great explanation of why sequences need to be recreated within > the last day or two. Read it and if it applies work that into your crons > accordingly. One final note is to research the free space map setting in > the postgresql.conf. This helps tracks dead tuples in the database, so > you'll want to make this large enough to store your tuple counts between > vacuums. Hope this helps, > > Robert Treat > > On Thu, 2002-11-14 at 15:39, Jodi Kanter wrote: > > I am trying to set up my cron job to run the appropriate vacuums on our > > database. I understand from reading the documentation that regular > > vacuums, vacuum full, and vacuum analyze are all important. Can someone > > assist in telling me what is the most efficient order? I would assume > > that analyze should be done once the inaccessible rows are released > > within the table and then to the OS? > > > > We currently have a small amount of data in our database so my plan was > > to do all vacuums on the entire database. > > > > Also, I read that reindexing was important. Does this need to be done as > > frequently as the vacuums? Is it only done on a per table basis? or can > > you reindex the entire database? > > > > Thanks > > Jodi > > > > _______________________________ > > Jodi L Kanter > > BioInformatics Database Administrator > > University of Virginia > > (434) 924-2846 > > jkanter@virginia.edu > > > > > > > > > > > > > > > > > > > > > >