Vacuum/Autovacuum is one of the critical parts of every PostgreSQL installation. When autovacuum is not configured properly for your workload you’ll suffer from bloat and performance issues sooner or later. Most of the installations we’ve seen run with the defaults just fine, and a lot of people probably never need to deal with adjusting any of the parameters for autovacuum. On the other side there are workloads where the defaults do not work nicely anymore and you need to adjust how autovacuum deals with specific tables. PostgreSQL 18 will come with a new parameter called “autovacuum_vacuum_max_threshold” which gives you one more option to deal with a specific issue.

Before we look at the new parameter lets take a look at when autovacuum kicks in in the default configuration. This is controlled by two parameters:

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

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

What that means is, that approximately 20% of the table (the 0.2 of autovacuum_vacuum_scale_factor) + 50 tuples (autovacuum_vacuum_threshold) need to change before autovacuum is triggered. Given this simple table with one million rows:

postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 

postgres=# select count(*) from t;
  count  
---------
 1000000
(1 row)

… this can easily be triggered by changing more than 20% of the table:

postgres=# select last_autovacuum from pg_stat_all_tables where relname = 't';
       last_autovacuum        
------------------------------
 2025-02-07 07:24:58.40076+01
(1 row)

postgres=# select now();
              now              
-------------------------------
 2025-02-07 07:26:48.333006+01
(1 row)

postgres=# update t set b = 'xxx' where a < 250000;
UPDATE 249999

postgres=# select pg_sleep('60');
 pg_sleep 
----------
 
(1 row)

postgres=# select last_autovacuum from pg_stat_all_tables where relname = 't';
        last_autovacuum        
-------------------------------
 2025-02-07 07:27:58.356337+01
(1 row)

The consequence of this is, that the more rows you have in a table, the longer it takes for autovacuum to kick in. You can deal with this already today by adjusting either “autovacuum_vacuum_threshold” or “autovacuum_vacuum_scale_factor” or both on either the table or globally on the instance level. If, for example, you want autovacuum to kick in after 10’000 rows have been changed in the above table you can do it like this:

postgres=# alter table t set ( autovacuum_vacuum_threshold = 10000 );
ALTER TABLE
postgres=# alter table t set ( autovacuum_vacuum_scale_factor = 0 );
ALTER TABLE

Doing the same test as above but only changing 10001 rows:

postgres=# update t set b = 'aaa' where a < 10002;
UPDATE 10001
postgres=# select now();
              now              
-------------------------------
 2025-02-07 07:54:35.295413+01
(1 row)

postgres=# select last_autovacuum from pg_stat_all_tables where relname = 't';
        last_autovacuum        
-------------------------------
 2025-02-07 07:27:58.356337+01
(1 row)

postgres=# select pg_sleep(60);
 pg_sleep 
----------
 
(1 row)

postgres=# select last_autovacuum from pg_stat_all_tables where relname = 't';
       last_autovacuum        
------------------------------
 2025-02-07 07:54:58.69969+01
(1 row)

The downside of this is, that you need to deal with that manually. With the introduction of “autovacuum_vacuum_max_threshold” PostgreSQL will handle those cases in a more “by default” way. The default for this parameter is quite high:

postgres=# show autovacuum_vacuum_max_threshold;
 autovacuum_vacuum_max_threshold 
---------------------------------
 100000000
(1 row)

To see it in action lets reset the table level settings we did above and set autovacuum_vacuum_max_threshold instead:

postgres=# alter table t reset ( autovacuum_vacuum_scale_factor );
ALTER TABLE
postgres=# alter table t reset ( autovacuum_vacuum_threshold );
ALTER TABLE
postgres=# alter table t set ( autovacuum_vacuum_max_threshold = 10000 );
ALTER TABLE

This will have exactly the same effect:

postgres=# update t set b = 'qqq' where a < 10002;
UPDATE 10001
postgres=# select now();
              now              
-------------------------------
 2025-02-07 08:02:51.582044+01
(1 row)

postgres=# select last_autovacuum from pg_stat_all_tables where relname = 't';
       last_autovacuum        
------------------------------
 2025-02-07 07:54:58.69969+01
(1 row)

postgres=# select pg_sleep(60);
 pg_sleep 
----------
 
(1 row)

postgres=# select last_autovacuum from pg_stat_all_tables where relname = 't';
        last_autovacuum        
-------------------------------
 2025-02-07 08:02:58.809895+01
(1 row)

Nice, and as always, thanks to everyone involved.

Post Views: 464
Thumbnail [60x60]
by
Daniel Westermann