Re: Contrib reindex script:

Shaun Thomas <sthomas@townnews.com> writes:

> On 7 May 2002, Jason Earl wrote:
>
> > Hey thanks, I was just wondering if such a thing were available.
>
> Here also is an updated version of the script.  I've cleaned up some
> of the code, heavily commented it, and fixed a bug or two.
> Remember, this is basically just vacuumdb, so it'll take all the
> same parameters (except the obvious ones like -z and -f) and you can
> put it in your postgres bin directory.  I have a line in cron that
> runs it every two hours with the -a option with the same user that
> owns the install.
>
> Works great!

Perfect.  Most of my tables don't get too much turnover, my
application is basically insert-heavy (which is why I went with
PostgreSQL in the first place), so I don't really have problems with
indexes growing out of control.  But with automated scripts to
maintain the database PostgreSQL can be used in a lot more
applications.

> > How has your migration to 7.2?  Are you still have problems with
> > the database growing out of control?
>
> Actually, now that I have this script to basically be a vacuum
> script for indexes to go along with vacuumdb, I've arrested the
> database growth.  The database I was groaning about before is
> standing firm around 87MB instead of the 300MB it would normally be
> by now.

That is good to know.  My own tests have shown that PostgreSQL is now
to the point where I consider it to be more than acceptable for use in
24/7 applications, but I don't have the real-world experience with
high turnover tables that you have.

> So far, 7.2 is fine.  Database load is a non issue, growth is gone
> thanks to the reindex script, and I couldn't be happier.  Now the
> real question is, why can't Postgres have a monitor that does these
> two things (vacuum, reindex) automatically throughout the day?
> Something that just trawls the tables doing a continuous partial
> vacuum, and triggers on deletes and updates to keep the indexes
> consistant.

I believe that this particular issue has come up on hackers a couple
of times.  However, with workable vacuum and reindex scripts this sort
of thing shouldn't be too hard to automate.  There is something to be
said for a more flexible manual solution as it allows the DBA to
vacuum and reindex according to their own needs.  Then again, perhaps
I am just biased due to the fact that I like software with lots of
buttons and levers to play with.

Worse comes to worse a few cron jobs and you pretty much have a
monitor that automatically reindexes and vacuums throughout the day
:).

> If what they say is true about row reuse, then I could get rid of
> both my vacuumdb and reindexdb scripts at that point.
>
> 7.3?

Take Care,
Jason