Re: VACUUM - Mailing 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>
>
>
>
>
>
>
>
>
>