Re: VACUUM

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(at)users(dot)sourceforge(dot)net>
> To: "Jodi Kanter" <jkanter(at)virginia(dot)edu>
> Cc: "Postgres Admin List" <pgsql-admin(at)postgresql(dot)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(at)virginia(dot)edu <mailto:jkanter(at)virginia(dot)edu>
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)