From: Gregory Smith <gregsmithpgsql@gmail.com>
Date: Sun, 14 Feb 2021 05:36:47 -0500 (EST)
To: Victor Sudakov <vas@sibptus.ru>
cc: pgsql-admin@lists.postgresql.org
Subject: Re: index bloat estimation
In-Reply-To: <YCY7oPIXobbY8oPu@admin.sibptus.ru>
Message-ID: <8a25e72c-6054-3e1f-b43-8f92111d2acb@fragile>
References: <YCY7oPIXobbY8oPu@admin.sibptus.ru>
MIME-Version: 1.0
Content-Type: multipart/mixed; BOUNDARY="0-148001315-1613298286=:41952"
Content-ID: <197af6d-b82e-21bb-bdb3-ccdadd97c5c8@fragile>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-148001315-1613298286=:41952
Content-Type: text/plain; CHARSET=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8BIT
Content-ID: <b7e8ccc9-cd94-d777-bb1a-3b444c89f365@fragile>

On Fri, 12 Feb 2021, Victor Sudakov wrote:

> Most of the stuff I've looked at is pretty old, much seems unsupported.
> What is the current best practice?

None of the estimate queries ever really worked well.  They just gave a 
bit more information than zero in the days before PG's internal functions 
were really reliable and useful for bloat measurement, something that 
happened in version 9.5 development.

For any modern PG, it's worth the trouble to learn how to directly use 
pgstattuple https://www.postgresql.org/docs/current/pgstattuple.html to do 
this job.  You run and interpret the output from pgstattuple(relation) and 
its faster estimate version pgstattuple_approx.  It's not hard to run some 
simulations with deleted rows to see what bloat looks like when it builds 
up.

There's still some need for manual estimates if you want to account for 
fillfactor in all cases, but I see that as a niche topic, not where people 
should start at.

There are also wrapper scripts built on top of pgstattuple around, like 
the already mentioned https://github.com/keithf4/pg_bloat_check

A good bit of the work done in that script is around handling multiple 
versions of PG and building some long-term idea of bloat state on all 
tables.  Workloads that have a bloat problem are sometimes fixed, but in a 
lot of cases the best you can do is monitor them and rebuild things when 
it gets bad.  That's one context Keith's packaging of this feature aims 
at.

--
Greg Smith  greg.smith@crunchydata.com
Director of Open Source Strategy
Crunchy Data https://www.crunchydata.com/
--0-148001315-1613298286=:41952--