1

I am using using the Heroku command as described here to detect bloat in tables. One of our tables reported a bloat size of around 7GB, but running vacuum on it, then running the same bloat command, reports close to 21GB of "waste". I have no idea how this can happen. I understand the various conditions under which a table can bloat, even this dramatically, but not after a vacuum, I have never seen such a case.

Any ideas why this might happen? The table in question does have a few TOAST tuples, perhaps that might be it. I also can't tell if that query from Heroku accounts for TOAST waste, but my understanding is that vacuum would take care of those as well. Local tests show the "waste" column from that bloat query staying pretty stable for small sizes and decreasing for larger values, but never an increase, which makes this rather strange.

0

All these queries that estimate bloat are not precise, but rely on heuristics.

If you want to find out the truth, install the pgstattuple extension and run the pgstattuple function on the table in question. That will tell you how much space is occupied by user data, dead tuples and empty space, respectively.

4
  • Indeed, I know about this option but never used it, will try it, thanks.
    – m_w_k
    Dec 8 '19 at 19:36
  • Actually, while I have you Laurenz, even if this is heuristic, can it be that off? I mean, it's no longer possible to use this query safely if the delta is that big.
    – m_w_k
    Dec 8 '19 at 19:47
  • I didn't look at the query in question. These queries are usually good as a hint that you should maybe have a second look at the query. If you really suffer from bloat, you usually know because your database size is increasing out of proportion to the increase in data. Dec 8 '19 at 19:50
  • Yep, that's the case, but this bloat increase (more than 8 fold) happened after a vacuum, which tells me to not rely on this query anymore. Will see what the extension reports.
    – m_w_k
    Dec 8 '19 at 19:59

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.