log_autovacuum_min_duration
log_autovacuum_min_duration is a configuration parameter determining the interval which should elapse before autovacuum operations are logged
log_autovacuum_min_duration was added in PostgreSQL 8.3.
Usage
If set to -1 (the default in PostgreSQL 14 and earlier), no autovacuum operations will be logged.
If set to 0, all autovacuum operations will be logged.
Otherwise autovacuum operations exceeding the specified interval will be logged.
log_autovacuum_min_duration can be set as a server-wide parameter or as a storage parameter for individual relations (PostgreSQL 9.2 and later).
Note that track_io_timing must be set to on for I/O timing statisics to be recorded.
Default value
The default value for log_autovacuum_min_duration is:
- 10m (PostgreSQL 15 ~)
- -1 (PostgreSQL 8.3 ~ PostgreSQL 14)
Change history
- PostgreSQL 15
- default value changed from
-1to10m(commit 64da07c4)
- default value changed from
- PostgreSQL 9.2:
- additional I/O stats added to generated log output (commit 9d3b5024)
- can be set as a storage parameter (commit 9d3b5024)
- PostgreSQL 8.3
- added (commit ef23a774)
Examples
To demonstrate the effect of log_autovacuum_min_duration, create a table and with storage_parameters set to trigger an autovacuum on a small number of inserts:
postgres=# CREATE TABLE foo (id INT) WITH ( log_autovacuum_min_duration = 0, autovacuum_vacuum_insert_threshold = 5); CREATE TABLE postgres=# INSERT INTO foo VALUES (generate_series(1,10)); INSERT 0 10
This (assuming the autovauum daemon is not otherwise occupied) result within a short time in an autovacuum operation, which will be logged like this (with track_io_timing set to the default off):
[2021-12-14 08:13:26 UTC] LOG: 00000: automatic vacuum of table "postgres.public.foo": index scans: 0
pages: 0 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 10 remain, 0 are dead but not yet removable, oldest xmin: 740
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 27.174 MB/s, avg write rate: 27.174 MB/s
buffer usage: 55 hits, 4 misses, 4 dirtied
WAL usage: 5 records, 4 full page images, 33264 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
With [track_io_timing|]] set to on:
[2021-12-14 08:17:34 UTC] LOG: 00000: automatic vacuum of table "postgres.public.foo": index scans: 0
pages: 0 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 30 remain, 0 are dead but not yet removable, oldest xmin: 751
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 135.281 MB/s
buffer usage: 31 hits, 0 misses, 4 dirtied
WAL usage: 5 records, 5 full page images, 34920 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
References
- PostgreSQL documentation: log_autovacuum_min_duration