Index Bloat Problem

From: Strahinja Kustudić <strahinjak(at)nordeus(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index Bloat Problem
Date: 2012-08-10 22:15:11
Message-ID: CADKbJJW0aCD_jp6Dy1j-VUP-zHSEvUkyfJ0Az3T1NnLX8tu_vw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

We have PostgreSQL 9.1 running on Centos 5 on two SSDs, one for indices and
one for data. The database is extremely active with reads and writes. We
have autovacuum enabled, but we didn't tweak it's aggressiveness. The
problem is that after some time the database grows even more than 100% on
the file system and most of the growth is because the indices are a few
times bigger than they should be, and when this happens, the performance of
the DB drops.

For example, yesterday when I checked the database size on the production
server it was 30GB, and the restored dump of that database was only 17GB.
The most interesting thing is that the data wasn't bloated that much, but
the indices were. Some of them were a few times bigger than they should be.
For example an index on the production db is 440MB, while that same index
after dump/restore is 17MB, and there are many indices with that high
difference. We could fix the problem if we reindex the DB, but that makes
our DB go offline and it's not possible to do in the production enviroment.

Is there a way to make the autovacuum daemon more aggressive, since I'm not
exactly sure how to do that in this case? Would that even help? Is there
another way to remove this index bloat?

Thanks in advance,
Strahinja

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message hubert depesz lubaczewski 2012-08-11 09:30:42 Re: Index Bloat Problem
Previous Message Jeff Janes 2012-08-10 18:19:56 Re: query overhead