Bug #4735
Updated by Benoît PECCATTE over 9 years ago
On Postgresql 8.3 (the versio of SLES11), the database seems to be always growing, without means to constraint it.
It appears the indexes are the culprit (for a 38 GB table, there are 20 GB extra indexes), as they bloat very fast.
bloating is caused by deletion of rows (which we do often), and vacuum full (which we don't do)
Apparently, there need to be specific tuning in the vacuum strategy on 8.3 to prevent bloating ( see http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=11&ved=0CCcQFjAAOAo&url=http%3A%2F%2Fwww.nws.noaa.gov%2Foh%2Fhrl%2Fhseb%2FpostgreSQL%2FVACUUM.pdf&ei=QGs-U8aVDc_y7AbAtYCwDQ&usg=AFQjCNEOsYpQ3a5rbSi7CIkbxGf4WvsFNw&sig2=bRCkHteUw0MslhatY2mvug&bvm=bv.64125504,d.ZGU&cad=rja ), with the general recommendation to upgrade to 8.4, as it is more easy
Bloat can be mesured with the following script:
http://wiki.postgresql.org/wiki/Show_database_bloat
An easy step can be to reindex the index, to clean them.
More info can also be found here:
http://gkoenig.wordpress.com/2009/11/24/postgres-and-bloated-table/
http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html
It seems version 9.x are completely safe of this problem, and 8.4 suffers from it, but to a much lesser extent
It seem that we have the same behavior with postgresql 9.1
We have seen 25GB of index for 66GB of data on postgresql 9.1