Re: VACUUM - Mailing list pgsql-admin
From | Robert Treat |
---|---|
Subject | Re: VACUUM |
Date | |
Msg-id | 1037310288.4723.94.camel@camel Whole thread Raw |
In response to | VACUUM (Jodi Kanter <jkanter@virginia.edu>) |
Responses |
Re: VACUUM
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-admin |
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> > > > > > > > > >
pgsql-admin by date: