From: Jodi Kanter <jkanter@virginia.edu>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: Postgres Admin List <pgsql-admin@postgresql.org>
Subject: Re: VACUUM
Date: Fri, 15 Nov 2002 08:27:06 -0500
Message-ID: <002c01c28caa$b18aae60$de138f80@virginia.edu> (raw)
References: <016e01c28c1d$f9db6340$de138f80@virginia.edu>
<1037310288.4723.94.camel@camel>
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>
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
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: jkanter@virginia.edu, xzilla@users.sourceforge.net
Subject: Re: VACUUM
In-Reply-To: <002c01c28caa$b18aae60$de138f80@virginia.edu>
* 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