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
v1-0002-Add-log_autovacuum_analyze_min_duration.patch
Description: Binary data
v1-0001-Rename-log_autovacuum_min_duration.patch
Description: Binary data
