Re: Contrib reindex script: - Mailing list pgsql-general
From | Bruce Momjian |
---|---|
Subject | Re: Contrib reindex script: |
Date | |
Msg-id | 200206230330.g5N3UXY11019@candle.pha.pa.us Whole thread Raw |
In response to | Re: Contrib reindex script: (Shaun Thomas <sthomas@townnews.com>) |
List | pgsql-general |
Shaun Thomas wrote: > 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! OK, I have added your reindex script to CVS /contrib. > > 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. Yep, it would be nice. I now realize there isn't even a way to see index wastage. I think Tatsuo was working on such a script for contrib. I have also added information to the SGML manual under maintenance: <para> <productname>PostgreSQL</productname> is unable to reuse btree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinately, even if <command>VACUUM</> is run frequently. </para> <para> As a solution, you can use the <command>REINDEX</> command periodically to discard pages used by deleted rows. There is also <filename>contrib/reindex</> which can reindex an entire database. </para> -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
pgsql-general by date: