log_autovacuum_min_duration

A configuration parameter for logging autovacuum operations exceeding a certain 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:

Change history

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

Categories

Logging, Storage parameter (relopt), Vacuuming

See also

track_io_timing