Re: bloatcheck.sql

> On Mar 24, 2018, at 9:07 PM, A System Admin <asysad(at)gmail(dot)com> wrote:
>
> Hi PostgreSQL admins,
>
> (PostgreSQL v9.5.9)
>
> The author of this SQL indicates that any output for the hot_update_ratio that falls below 0.95 indicates that action needs to be taken over and above the autovacuuming that is setup for this DB based on its levels of bloat.
>
> My questions are:
>
> 1. Is this an accurate statement in your opinion for this and all PostgreSQL v9.5.9 DB's?
>

No. I think bloat is a quite misunderstood topic; especially, in the Postgres world and unfortunately perpetuated as Postgres’s achilles heel. Performance is also a relative term.

What performance benefit would removing bloat solve? In a well designed transactional system… none. For reporting with large table scans sure.

Systems should come to an equilibrium and each system will be different thus the bloat factor will be different. It’s better to understand your system rather than rely on folklore. Work to identify real performance problems rather than trying to fix perceived phantom performance issues.

Also, if you know the equilibrium of your system; then you’ll understand when it gets out of wack due to a bad long running query throwing off the equilibrium. Long running queries, can prevent vacuum from doing it’s job.

As an example, if you database consists of heavy updates and long running queries then given boat query would continue to state that there is something wrong; however, it just the nature of your given system.