Message-ID: <1521952441.2350.4.camel@cybertec.at>
Subject: Re: bloatcheck.sql
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: A System Admin <asysad@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Sun, 25 Mar 2018 06:34:01 +0200
In-Reply-To: 
 <CAKTxCDHOSuV6KEau+vcYjK6C72TVkhoM02sLU-z_Cov7YmGUHA@mail.gmail.com>
References: 
	<CAKTxCDHOSuV6KEau+vcYjK6C72TVkhoM02sLU-z_Cov7YmGUHA@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

A System Admin wrote:
> 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?

Nothing could be farther from the truth.

HOT update ratio has nothing to do with bloat; if anything, bloat
would *increase* the HOT ratio because there is more free space.

A low HOT update ratio could just mean that you have a lot of
indexes, because no HOT update can take place if an indexed column
is modified.

> 3. What specific ongoing/regular step(s) need to occur outside of the hourly and weekly
> commands currently being run on the DB for it to remain in an optimally performant state?

Neither regular reindexing nor VACUUM (FULL) is recommended,
although it cannot harm if you don't mind the down time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com