1


I'm running a PgSQL 8.1 on a CentOS 4.4 system (not upgradable unfortunately).
There's a Java app running on top of the PgSQL daemon and we got to reindex the database every 2 months or so. Also important: the database isn't growing.

It looks like the bloat is now coming faster than before and this tends to increase.
My config is available here, autovacuum daemon is enabled and running quite often: pastebin.com/RytNj7dK

You can also find the output of this query wiki.postgresql.org/wiki/Show_database_bloat

3 hours after running reindex: http://pastebin.com/raw.php?i=75fybKyd
72 hours after running reindex: http://pastebin.com/raw.php?i=89VKd7PC

Does anyone have any idea what should I tweak to get rid of that growing bloat?

Thanks for your help

PS: due to antispam prevention system, I had to remove the first 2 http:// prefixes for my two first links.

Improve this question
3
  • Also important: the database isn't growing. -- Is it shrinking or changing in any way? Static databases will never have index bloat. Databases that only grow should have negligible bloat. Databases that change or shrink (large numbers of updates/deletes that move/change keys) will cause bloat... – voretaq7 Mar 19 '12 at 15:59
  • I believe it has a lot of updates, do you have any idea how to deal with this? – Henry-Nicolas Tourneur Mar 20 '12 at 7:52
  • Are you serious about version 8.1 or is that a typo? For 8.1 I have quite an obvious idea what you should do: upgrade to a more recent version, preferably the current version 9.1. 8.1 has reached end of live in 2010, there have been countless improvements. – Erwin Brandstetter Jun 5 '12 at 3:49
2

Unfortunately if you have an active database (one with lots of inserts/updates/deletes) you will experience index bloat -- It's just a fact of database life. The best you can do is hope to slow the bloat down to the point where your reindex intervals are reasonable.

The best advice I can give you in that regard is to upgrade to a newer version of Postgres (8.3 or later): This is when Postgres introduced Heap-Only Tuples support.
Right now on your (8.1) system ANY update to a row is the equivalent of a delete/insert as far as the index is concerned, hence the index bloat. 8.3 and later don't touch the index unless they have to ("If the row still fits on the page it's in").

After upgrading to a version of Postgres that with HOT support you may still experience index bloat if your UPDATEs are touching indexed columns, or if your UPDATEs substantially increase the size of a row so that it has to be moved to a new page, but these situations should be relatively infrequent provided your indexing strategy is sane and your rows are relatively static in size, so the index bloat issue should be less of a problem.

Some additional general strategies for dealing with index bloat:

  1. Primary Key Indexes
    You'r pretty much out of luck here - you need to REINDEX and take the table lock.
  2. Other Indexes
    • Option 1: DROP and re-CREATE Non-Critical Indexes
      This has the advantage of not locking the table, but the disadvantage of taking away the index while it's being rebuilt.
    • Option 2: Index-Shuffle for Critical Indexes
      Rather than the Drop/Create procedure above, first create a new index, then when that is done drop the old one and rename the new one to take its place.
      This has the advantage of not locking the table, and the advantage of leaving the original index working (though bloated). The primary disadvantage is that you've got to rename an index to keep naming conventions sane -- an extra manual step.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.