Re: Contrib reindex script:

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!

>
> 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.

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.

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?

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Administrator           |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : www.townnews.com                                             |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+