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
-1
to10m
(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