BUG #15067: Documentation or behaviour bug with autovacuum thresholds? - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15067
Logged by:          Greg Clough
Email address:      greg.clough@ipreo.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