1

We use check_postgres to monitor our DB. We get this warning:

POSTGRES_BLOAT WARNING: 
DB "foo" (db foo) index foo_text_index_id_uniq rows:? 
pages:485435 shouldbe:330673 (1.5X) wasted bytes:1267810304 (1 GB) 

According to the docs a vacuum should help, but the autovacuum does not help.

What can I do to get rid of the bloat?

  • Is it causing anything bad? If you have a working autovacuum setup, the table should not grow much. – dezso Mar 16 '16 at 9:10
  • Do you have any sessions that are "idle in transaction"? Open transactions that have touched that table will prevent (auto)vacuum to clean up properly. – a_horse_with_no_name Mar 16 '16 at 11:48
  • I think you should raise your critical values as 1.5X is a fairly reasonnable value concerning bloat. On my side, I use 2X. – KookieMonster Mar 16 '16 at 11:58
  • @dezso good question: No, it does not do any harm. But I want to get our monitoring logs clean. For me clean is binary: it is clean or not. That's why I want to solve this. OK, an other solution would be to stop doing this test .... :-) – guettli Mar 16 '16 at 11:59
  • @a_horse_with_no_name, not there are no idle session. – guettli Mar 16 '16 at 12:00
1

Vacuuming will not remove the existing bloat. It will free up that space for future use. Until that future use has happened, it will still look bloated.

To free it up now, you would have to do something drastic like VACUUM FULL, CLUSTER, or use some external tool like pg_repack. That is a lot of churn to put your database through just to fix a problem that is not actually a problem.

I would just do as @KookieMonster said, and increase the threshold. Then if things start to get worse, eventually you will start getting warnings again. (Actually, I wouldn't run check_postgres in the first place, but if I were, then...see above.)

  • you don't use check_postgres. Interesting. Don't you monitor your db? I guess you do. How do you do it? – guettli Mar 16 '16 at 19:22
  • @guettli In addition to being the DBA, I'm also a power "end-user" of my database. So if there are performance problems, I experience them firsthand and learn about them that way. I am also constantly forking the database, both to get a fresh test/dev environment, and to have confidence in the backup/restore procedures. So if it were getting bloated for space I would notice the problem through that avenue. I don't recommend that other people avoid check_postgres, I just never felt the need myself. – jjanes Mar 16 '16 at 21:12

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.