I show a tip of autovacuum management.

As frequently mentioned, the table bloat problem is one of the most annoying things in managing PostgreSQL. Several things can cause that problem, and Autovacuum is one of them.

One of the conditions when the autovacuum runs are “(the number of dead tuples)” is greater than “autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (the number of tuples)“, where the default of autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor are 50 and 0.2, respectively.

The autovacuum runs when the number of dead tuples is greater than: 250 for 1,000 relations, 20,050 for 100,000 relations, and 20,000,050 for 100,000,000 relations.

It is clear from these examples that the more tuples a table has, the less often autovacuum runs.

The following trick is useful for the tables that you are annoying. By setting the following storage parameters for the tables, the autovacuum will perform the vacuum processing each time it reaches your set the number of dead tuples.

Assume that you’d like to run the autovacuum whenever the number of dead tuples of “pgbench_accounts” reaches 1,000 rows. However, the number of pgbench_accounts is 100,000, so, by default, the autovacuum will not run until the number of dead tuples exceeds 20,050.

postgres=# select count(*) from pgbench_accounts;
 count  
--------
 100000
(1 row)

In that case, all you have to do is run the following commands.

postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE
postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE

Let’s see the trick works. I produce the dead tuples in pgbench_accounts table using UPDATE command.

postgres=# update pgbench_accounts set abalance = 1 where aid < 1002;
UPDATE 1001
postgres=# SELECT relname, last_autovacuum FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts';
     relname      |        last_autovacuum        
------------------+-------------------------------
 pgbench_accounts | 2023-07-01 00:32:23.627931+09
(1 row)

After a few minutes, we can confirm that the autovacuum runs.

postgres=# SELECT relname, last_autovacuum FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts';
     relname      |        last_autovacuum        
------------------+-------------------------------
 pgbench_accounts | 2023-07-01 00:43:24.498598+09
(1 row)