log_autovacuum_min_duration

• 19
• 18
• 17
• 16
• 15
• 14
• 13
• 12
• 11
• 10
• 9.6
• 9.5
• 9.4
• 9.3
• 9.2
• 9.1
• 9.0
• 8.4
• 8.3

为了演示 log_autovacuum_min_duration 的效果，创建一个表，并设置 storage_parameters 来触发少量插入数据后的自动清理操作。

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

这（假设自动清理守护进程当前未被占用）将在短时间内导致一个自动清理操作，该操作将被记录如下（当 track_io_timing 设置为默认的 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

当 [track_io_timing|]] 设置为 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

• PostgreSQL 文档： log_autovacuum_min_duration