Re: Full Vacuum/Reindex vs autovacuum

On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote:
> On Mon, Nov 8, 2010 at 11:50 AM, Jason Long <jason(at)octgsoftware(dot)com> wrote:
> > I currently have Postgres 9.0 install after an upgrade. My database is
> > relatively small, but complex. The dump is about 90MB.
> >
> > Every night when there is no activity I do a full vacuum, a reindex,
>
> One question, why?
>
> > and then dump a nightly backup.
>
> Good idea.
>
> > Is this optimal with regards to performance? autovacuum is set to the
> > default.
>
> that depends very much on your answer to the question of why are you
> doing it and what you're trying to gain / work around with vacuum full
> / reindex every night.
>

Sorry I am not bumping this. I meant to send this to the list as well.

I have been doing this for several years. Since my database is small
and it takes little time to do a full vacuum. I am doing the reindex
because I thought that was recommended after a full vacuum.

As the data has grown the system is slowing down. Right now I am
looking at ways to improve performance without getting into the queries
themselves because I am swamped with new development.

Is doing the full vacuum and reindex hurting or helping anything?

Any other quick fixes that I can try?

--
Thank you for your time,

Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.octgsoftware.com
HJBug Founder and President
http://www.hjbug.com