Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in a Postgres 8.2.15 database. The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB. A few weeks later and it's back up to 3.5GB and climbing.

This is not a table that has frequent deletes, so I'm at a loss as to what is causing the bloat.

This only happens to a single table. I have a separate, structurally identical database, serving the same software; the table in that database has not shown any bloat.

Any ideas?

share|improve this question

migrated from stackoverflow.com Feb 2 '12 at 21:43

This question came from our site for professional and enthusiast programmers.

2  
Updates also cause "bloat" - they are combination of delete and insert. –  Milen A. Radev Feb 2 '12 at 19:15
3  
since you have two machines, I would suggest upgrading to at least 8.4.x –  wildplasser Feb 2 '12 at 19:22
    
@Milen A. Radev Thanks, I'll see if I'm doing more updates here than in other places, but the bloat is pretty significant (1000% in a few weeks) - could that be just from updates? –  yohaas Feb 2 '12 at 19:42
    
@wildplasser They are actually on the same machine. Any reason to think that 8.4 would be better for bloat or just generally a good idea? –  yohaas Feb 2 '12 at 19:43
3  
Do consider updating. The vacuum implementation got a lot better in the last 6 years –  a_horse_with_no_name Feb 2 '12 at 22:10
show 1 more comment

2 Answers 2

up vote 7 down vote accepted

There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version 9.1.

1.) As has been mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2.

2.) You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs as @Milen already commented. A new feature has been introduced with version 8.3, that counters this problem at its root: Heap-Only Tuples, I quote the release notes:

Heap-Only Tuples (HOT) accelerate space reuse for most UPDATEs and DELETEs (Pavan Deolasee, with ideas from many others)

UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs. Previously only VACUUM could reclaim space taken by dead tuples. With HOT dead tuple space can be automatically reclaimed at the time of INSERT or UPDATE if no changes are made to indexed columns. This allows for more consistent performance. Also, HOT avoids adding duplicate index entries.

Emphasis mine.

3.) PostgreSQL 8.2 has reached end of life in Dec. 2011. No more security updates. Upgrade to a more recent version as soon as possible.


To find out whether there are, in fact, a lot of UPDATEs launched, use the configuration parameter log_statement and check your database log. Maybe a misconfigured app or a trigger launches a lot more UPDATEs than you think?

If you SET the parameter in a session it only changes for this session:

SET log_statement = 'all';

So, rather change it in your postgresql.conf:

log_statement = all

and reload. For instance with pg_ctl reload. But don't forget to change it back (and reload) or your log files may grow huge. Then check the log files to see what's actually going on.

share|improve this answer
add comment

You're probably not running autovacuum. You might want to enable that. But more importantly, you should consider upgrading to the 9.1.x branch.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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