Add log_autovacuum_{vacuum|analyze}_min_duration

From: Shinya Kato <shinya11(dot)kato(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Add log_autovacuum_{vacuum|analyze}_min_duration
Date: 2025-06-03 06:35:20
Message-ID: CAOzEurQtfV4MxJiWT-XDnimEeZAY+rgzVSLe8YsyEKhZcajzSA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I am proposing the introduction of two new GUC parameters,
log_autovacuum_{vacuum|analyze}_min_duration, to replace the existing
log_autovacuum_min_duration.

Motivation:

Currently, log_autovacuum_min_duration controls the logging threshold
for both autovacuum and autoanalyze activities. However, autoanalyze
operations typically have a much shorter duration than autovacuum
operations. Consequently, if log_autovacuum_min_duration is set to a
value appropriate for capturing longer-running autovacuum tasks,
shorter autoanalyze activities are often not logged. This can make it
difficult to monitor and troubleshoot autoanalyze behavior
effectively.

By providing separate GUCs, administrators can set distinct logging
thresholds for autovacuum and autoanalyze, ensuring that both types of
activities can be logged appropriately based on their typical
execution times.

Example Usage:

The following SQL demonstrates how these new parameters would allow
for more granular logging. In this example, autoanalyze is configured
to always log (duration set to 0), while autovacuum is set to log only
if it runs for a very long time.
```
=# CREATE TABLE t (i int, d text) WITH (
-- autoanalyze settings
autovacuum_analyze_threshold = 1,
autovacuum_analyze_scale_factor = 0,
log_autovacuum_analyze_min_duration = 0,
-- autovacuum settings
autovacuum_vacuum_threshold = 1,
autovacuum_vacuum_scale_factor = 0,
log_autovacuum_vacuum_min_duration = 100_000_000
);
=# INSERT INTO t VALUES (1, 'a');
=# DELETE FROM t WHERE i = 1;

2025-06-03 15:15:39.608 JST [401368] LOG: automatic analyze of table
"postgres.public.t"
avg read rate: 18.229 MB/s, avg write rate: 0.000 MB/s
buffer usage: 155 hits, 7 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 530 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
```

Two patches are attached.

In v1-0001-Rename-log_autovacuum_min_duration.patch, just renamed
log_autovacuum_min_duration to log_autovacuum_vacuum_min_duration.

In v1-0002-Add-log_autovacuum_analyze_min_duration.patch, add the new
GUC parameter log_autovacuum_analyze_min_duration.

Do you think?

Best regards,
Shinya Kato
NTT OSS Center

Attachment Content-Type Size
v1-0002-Add-log_autovacuum_analyze_min_duration.patch application/octet-stream 17.3 KB
v1-0001-Rename-log_autovacuum_min_duration.patch application/octet-stream 21.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2025-06-03 06:55:45 Re: Add CHECK_FOR_INTERRUPTS in polling loop code path in XactLockTableWait
Previous Message Ajin Cherian 2025-06-03 06:25:15 Re: Proposal: Filter irrelevant change before reassemble transactions during logical decoding