Re: Making autovacuum logs indicate if insert-based threshold was the triggering condition

On Sat, Aug 06, 2022 at 01:03:57PM -0700, Peter Geoghegan wrote:
> thresholds. It may be far from obvious which triggering condition
> autovacuum.c must have applied to trigger any given autovacuum, since
> that information isn't currently passed down to lazyvacuum.c. This
> seems like a problem to me; how are users supposed to tune
> autovacuum's thresholds without even basic feedback about how the
> thresholds get applied?

+1

This sounded familiar, and it seems like I anticipated that it might be an
issue. Here, I was advocating for the new insert-based GUCs to default to -1,
to have insert-based autovacuum fall back to the thresholds specified by the
pre-existing GUCs (20% + 50), which would (in my proposal) remain be the normal
way to tune any type of vacuum.

https://www.postgresql.org/message-id/20200317233218.GD26184@telsasoft.com

I haven't heard of anyone who had trouble setting the necessary GUC, but I'm
not surprised if most postgres installations are running versions before 13.

> Note that a VACUUM that is an "automatic vacuum for inserted tuples" cannot
> [...] also be a "regular" autovacuum/VACUUM

Why not ?

$ ./tmp_install/usr/local/pgsql/bin/postgres -D src/test/regress/tmp_check/data -c log_min_messages=debug3 -c autovacuum_naptime=9s&
DROP TABLE t; CREATE TABLE t (i int); INSERT INTO t SELECT generate_series(1,99999); DELETE FROM t; INSERT INTO t SELECT generate_series(1,99999);

2022-08-06 16:47:47.674 CDT autovacuum worker[12707] DEBUG: t: vac: 99999 (threshold 50), ins: 99999 (threshold 1000), anl: 199998 (threshold 50)

--
Justin