BUG #15067: Documentation or behaviour bug with autovacuum thresholds?

The following bug has been logged on the website:

Bug reference: 15067
Logged by: Greg Clough
Email address: greg(dot)clough(at)ipreo(dot)com
PostgreSQL version: 9.6.7
Operating system: CentOS v7.4
Description:

I think there is a documentation bug in the Autovacuum section:

https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM

The formula for triggering an autovacuum is listed as:

vacuum threshold = vacuum base threshold + vacuum scale factor *
number of tuples

But in reality I think it needs “+ 1” in there:

vacuum threshold = vacuum base threshold + 1 + vacuum scale factor *
number of tuples

Maybe it has been left out for simplicity, or if not then there’s either a
documentation or implementation bug. I think it has been deliberately
included in the code for safety, so that if both tuning parameters are set
to 0 then it won’t repeatedly vacuum tables with zero changes.

To test this on PostgreSQL v9.6.7 I turned on autovacuum logging, and left
the other parameters set to the defaults:

postgres=# SELECT version();
version


----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# SHOW log_autovacuum_min_duration;
log_autovacuum_min_duration
-----------------------------
0
(1 row)

postgres=# SHOW autovacuum_vacuum_scale_factor;
autovacuum_vacuum_scale_factor
--------------------------------
0.2
(1 row)

postgres=# SHOW autovacuum_vacuum_threshold;
autovacuum_vacuum_threshold
-----------------------------
50
(1 row)

I then created a table with some dummy data, and set
autovacuum_vacuum_scale_factor = 0, and autovacuum_vacuum_threshold = 1.

postgres=# CREATE TABLE autovacuum_threshold_test(id int);
CREATE TABLE
postgres=# INSERT INTO autovacuum_threshold_test(id) (SELECT
generate_series(1,1000));
INSERT 0 1000
postgres=# ALTER TABLE autovacuum_threshold_test SET
(autovacuum_vacuum_scale_factor = 0);
ALTER TABLE
postgres=# ALTER TABLE autovacuum_threshold_test SET
(autovacuum_vacuum_threshold = 1);
ALTER TABLE
postgres=# \d+ autovacuum_threshold_test;
Table "public.autovacuum_threshold_test"
Column | Type | Modifiers | Storage | Stats target | Description

--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Options: autovacuum_vacuum_threshold=1,
autovacuum_vacuum_scale_factor=0

Issuing a single row update does not trigger an autovacuum:

postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id <=
1;
UPDATE 1

Whereas issuing a 2 row update does:

postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id <=
2;
UPDATE 2

< 2018-02-14 14:16:07.531 GMT > LOG: automatic vacuum of table
"postgres.public.autovacuum_threshold_test": index scans: 0
pages: 0 removed, 5 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 2 removed, 1000 remain, 0 are dead but not yet removable
buffer usage: 33 hits, 2 misses, 8 dirtied
avg read rate: 15.038 MB/s, avg write rate: 60.154 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

This behaviour stays constant, even if autovacuum_vacuum_threshold is set to
0, or larger numbers like 500, 1000, etc. It always adds 1:

postgres=# ALTER TABLE autovacuum_threshold_test SET
(autovacuum_vacuum_threshold = 500);
ALTER TABLE
postgres=# \d+ autovacuum_threshold_test;
Table "public.autovacuum_threshold_test"
Column | Type | Modifiers | Storage | Stats target | Description

--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Options: autovacuum_vacuum_scale_factor=0,
autovacuum_vacuum_threshold=500

postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id <=
500;
UPDATE 500
postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id <=
501;
UPDATE 501

< 2018-02-14 14:20:07.583 GMT > LOG: automatic vacuum of table
"postgres.public.autovacuum_threshold_test": index scans: 0
pages: 0 removed, 9 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 501 removed, 1000 remain, 0 are dead but not yet
removable
buffer usage: 43 hits, 0 misses, 5 dirtied
avg read rate: 0.000 MB/s, avg write rate: 15.575 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

Possibly there’s something more unusual happening, because if you run an
update at the *exact* autovacuum_vacuum_threshold, and then shortly after
run the same number of updates again, the autovacuum log only shows that one
set of tuples removed. Shouldn’t it be A + B, as two updates have run? (so
“tuples: 1000 removed” instead of the “tuples: 500 removed” shown below)

postgres=# VACUUM FULL autovacuum_threshold_test;
VACUUM
postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id <=
500;
UPDATE 500
postgres=# SELECT pg_sleep(30);
pg_sleep
----------

(1 row)

postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id
BETWEEN 501 and 1000;
UPDATE 500

< 2018-02-14 14:30:07.690 GMT > LOG: automatic vacuum of table
"postgres.public.autovacuum_threshold_test": index scans: 0
pages: 0 removed, 9 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 500 removed, 1000 remain, 0 are dead but not yet
removable
buffer usage: 41 hits, 2 misses, 4 dirtied
avg read rate: 5.564 MB/s, avg write rate: 11.129 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

Cheers,
Greg Clough
Senior Technology Engineer

Ipreo
Castle House | 37-35 Paul St | London EC2A 4LS