From: Robert Treat <xzilla@users.sourceforge.net>
To: Jodi Kanter <jkanter@virginia.edu>
Cc: Postgres Admin List <pgsql-admin@postgresql.org>
Subject: Re: VACUUM
Date: 14 Nov 2002 16:44:48 -0500
Message-ID: <1037310288.4723.94.camel@camel> (raw)
In-Reply-To: <016e01c28c1d$f9db6340$de138f80@virginia.edu>
References: <016e01c28c1d$f9db6340$de138f80@virginia.edu>
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>
>
>
>
>
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: xzilla@users.sourceforge.net, jkanter@virginia.edu
Subject: Re: VACUUM
In-Reply-To: <1037310288.4723.94.camel@camel>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox