Date: Sat, 20 Feb 2021 21:34:20 +0700
From: Victor Sudakov <vas@sibptus.ru>
To: pgsql-admin@lists.postgresql.org
Subject: Re: index bloat estimation
Message-ID: <YDEd7Jr4CtAX4zwP@admin.sibptus.ru>
References: <YCY7oPIXobbY8oPu@admin.sibptus.ru>
 <CAODZiv6YZj3GJsTv5_dp07VS2FACrFu_JPWsFVi64=1mJD_t0A@mail.gmail.com>
 <YCn8BmEe/4mA0KIb@admin.sibptus.ru>
 <CAODZiv5uVWASjuigUxJhmxmrQAdMoOxRu5vz97CkbuqH2-oxow@mail.gmail.com>
 <YCx/DJkZRw0mzRyr@admin.sibptus.ru>
 <CAODZiv69O=pTg7RMew=9Q1d6xKaVxriSSHZ661tx-YKhbuvmQw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAODZiv69O=pTg7RMew=9Q1d6xKaVxriSSHZ661tx-YKhbuvmQw@mail.gmail.com>
Precedence: bulk

Keith Fiske wrote:

> >
> Actually this could still be an issue, but all really depends on the size
> of the tables involved. Long running transactions on the replica have the
> potential to either delay replication entirely
> (max_standby_archive_delay, max_standby_streaming_delay) or cause more
> bloat than normal on the primary (hot_standby_feedback). The latter is more

As I said earlier, if you have a dedicated replica for OLAP running
continuous recovery from a WAL archive (not from a replication slot),
this is not an issue.  You can do whatever you want with this replica:
stop it, make it a delayed replica, it cannot affect the master in any
way.

> 
> The approximate/quick mode of pgstattuple could certainly help with this
> problem, but as the issue you opened up on the github repo pointed out,
> that skips over scanning toast tables (
> https://github.com/keithf4/pg_bloat_check/issues/22) and also does not work
> against indexes which are more often the problem with bloat and query
> performance. I have seen significant bloat forming in the toast tables
> (hundreds of GB) when the regular table only reports very minimal bloat. So
> I don't recommend relying completely on the approximate check.

I'll mind that, thank you.

-- 
Victor Sudakov,  VAS4-RIPE, VAS47-RIPN
2:5005/49@fidonet http://vas.tomsk.ru/