DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type
	:content-transfer-encoding:content-disposition;
	b=k5HcGtY5kToJIsPtrbpFTedyucBA4CYjIAm/fCp0J7LP+2tQCPIX58fU6k2tZnpWY8
	kdP6ErD59ofMXyxJyolhkKIr0mSf3eRHSzQ1mBpV/HaPqA5eq/HHfh1kehWFiUCiFZuJ
	I02LMnDSGgW5Xx4Uu1ZTGUztffBtbNBttnsz8=
Message-ID: <8a547c840810170905m7323dbd8x90de7dd004abc7d4@mail.gmail.com>
Date: Fri, 17 Oct 2008 09:05:26 -0700
From: "Tory M Blue" <tmblue@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Index bloat, reindex weekly, suggestions etc?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Good day,

So I've been running 8.3 for a few months now and things seem good.

I also note there are some bug fixes and you are up to 8.3.4 now, but
reading it I don't see that I'm being affected by anything, but please
tell me if i should plan upgrades to 8.3.4..

The real issue is my index growth and my requirement for weekly
re-indexing  (which blocks and therefore is more or less a manual
process in a live production environment (fail over, changing vips
etc).

After Reindex

My data
3.1G    /data/cls

My Indexes
1.5G    /data/clsindex

After 2 weeks

My data
3.4G    /data/cls

My indexes
6.8G    /data/clsindex

This is 2 weeks. and throughout those  2 weeks I can see performance
degrading. In fact I just bumped the memory footprint of my server to
32gigs from 8gigs as my entire PostgreSQL DB is now 15gb, this has
helped tremendously, everything ends up in memory and there is almost
zero read access from the DB (that's great), however as my DB grows I
can't expect to manage it by adding Terabytes of memory to the system,
so something is not being done efficiently.

We are testing with fill factor, but that just seems to allocate space
and doesn't really decrease my index size on disk. It does however
seem to help performance a bit, but not a ton.

Where should I start looking, what type of information should I
provide in order for me to help you, help me?

This is a transactional DB with over 800K updates happening each day.
tons of updates and deletes. autovac is on and I do a analyze each
night after the dump

Thanks
Tory