Subject: Re: VACUUM
From: Robert Treat <xzilla@users.sourceforge.net>
To: Jodi Kanter <jkanter@virginia.edu>
Cc: Postgres Admin List <pgsql-admin@postgresql.org>
In-Reply-To: <002c01c28caa$b18aae60$de138f80@virginia.edu>
References: <016e01c28c1d$f9db6340$de138f80@virginia.edu>
	<1037310288.4723.94.camel@camel>
	<002c01c28caa$b18aae60$de138f80@virginia.edu>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 15 Nov 2002 09:12:19 -0500
Message-Id: <1037369539.7319.3.camel@camel>
Mime-Version: 1.0

http://groups.google.com/groups?q=tom+lane+index+group:comp.databases.postgresql.general&hl=en&lr=&ie=UTF-8&selm=23812.1037210966%40sss.pgh.pa.us&rnum=6

hows that for a URL?

Robert Treat

On Fri, 2002-11-15 at 08:27, Jodi Kanter wrote:
> 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" <xzilla@users.sourceforge.net>
> To: "Jodi Kanter" <jkanter@virginia.edu>
> Cc: "Postgres Admin List" <pgsql-admin@postgresql.org>
> 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 <mailto:jkanter@virginia.edu>
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)