Re: checking the trigger values for autovacuum tresholds

On Tue, May 24, 2022 at 6:17 PM Dirk Krautschick <
Dirk(dot)Krautschick(at)trivadis(dot)com> wrote:

> Hi,
>
>
>
> I am actually doing some test with pglogical and a trivial bi-directional
> replication setup (no BDR, just cross subscriptions, no conflicts because
> UUID a prim key).
>
> During the tests I am stressing the autovacuum results to simulate a
> customer situation having trouble with files growing way different on two
> replica nodes.
>
>
>
> I am checking for the count of dead tuples with pg_stat_user_tables or the
> extension pgstattuple and I am not sure if any is somehow accurate.
>
>
>
> What is the most accurate way to check for this information except using
> pageinspect and/or freespacemap and count for myself and
>
> what exact value is using by the autovacuum process related to the
> configured thresholds?
>

pg_stat_user_tables is not transactional. The values are reset to zero
upon crash/unclean shutdown, and can also be reset manually. Also, they
are updated over UDP messages, which might get lost under high load. So it
is not necessarily accurate. But it is what autovacuum uses, so if your
question is about what the autovacuum worker is thinking, this is what it
is thinking, even if that is wrong.

pgstattuple actually inspects every tuple, so it is slow but as accurate as
anything that runs on an unlocked table over a non-zero amount of time can
be. It basically is doing the pageinspect for you. The freespacemap only
shows you space that is free. Space occupied by dead tuples is not free.
It is freeable, but not yet free.

Cheers,

Jeff

>