Are temporary tables auto vacuumed in PostgreSQL?

https://blog.dbi-services.com/are-temporary-tables-auto-vacuumed-in-postgresql/

While doing the EDB quiz at their booth last week at pgconfeu one of the questions was: Are temporary tables auto vacuumed? What do you think? My first thought was yes, but lets see. The first question we need to answer is: How can we check if a table (no matter if temporary or not for now) was auto vacuumed or not? PostgreSQL comes with many views that expose statistical information and one of those is pg_stat_all_tables. Lets have a look …

 

When you describe that view there is column named “last_autovacuum”:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

postgres=# \d pg_stat_all_tables                       View "pg_catalog.pg_stat_all_tables"        Column        |           Type           | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+---------  relid               | oid                      |           |          |  schemaname          | name                     |           |          |  relname             | name                     |           |          |  seq_scan            | bigint                   |           |          |  seq_tup_read        | bigint                   |           |          |  idx_scan            | bigint                   |           |          |  idx_tup_fetch       | bigint                   |           |          |  n_tup_ins           | bigint                   |           |          |  n_tup_upd           | bigint                   |           |          |  n_tup_del           | bigint                   |           |          |  n_tup_hot_upd       | bigint                   |           |          |  n_live_tup          | bigint                   |           |          |  n_dead_tup          | bigint                   |           |          |  n_mod_since_analyze | bigint                   |           |          |  last_vacuum         | timestamp with time zone |           |          |  last_autovacuum     | timestamp with time zone |           |          |  last_analyze        | timestamp with time zone |           |          |  last_autoanalyze    | timestamp with time zone |           |          |  vacuum_count        | bigint                   |           |          |  autovacuum_count    | bigint                   |           |          |  analyze_count       | bigint                   |           |          |  autoanalyze_count   | bigint                   |           |          |

That should give us the time of the last autovacuum, right? Before we begin, here are my autovacuum settings which are all at their defaults:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

postgres=# select name,setting from pg_settings where name like '%autovacuum%' order by 1;                 name                 |  setting  -------------------------------------+-----------  autovacuum                          | on  autovacuum_analyze_scale_factor     | 0.1  autovacuum_analyze_threshold        | 50  autovacuum_freeze_max_age           | 200000000  autovacuum_max_workers              | 3  autovacuum_multixact_freeze_max_age | 400000000  autovacuum_naptime                  | 60  autovacuum_vacuum_cost_delay        | 20  autovacuum_vacuum_cost_limit        | -1  autovacuum_vacuum_scale_factor      | 0.2  autovacuum_vacuum_threshold         | 50  autovacuum_work_mem                 | -1  log_autovacuum_min_duration         | -1 (13 rows)

That means autovacuum should kick in as soon as we change 50 rows in a table because autovacuum_vacuum_threshold is set to 50? The table:

1 2 3 4 5 6 7 8 9

postgres=# create table t1 (a int, b varchar(50)); CREATE TABLE postgres=# insert into t1 (a,b) select a, md5(a::varchar) from generate_series ( 1, 1000000 ) a; INSERT 0 1000000 postgres=# select count(*) from t1;   count  ---------  1000000 (1 row)

As soon as we change 50 or more rows we should see the last_autovacuum column updated in pg_stat_all_tables, so lets check:

1 2 3 4 5 6 7 8 9 10 11 12

postgres=# update t1 set a = a + 1 where a < 1000; UPDATE 999 postgres=# select pg_sleep(10);  pg_sleep ----------    (1 row) postgres=# select relname,last_autovacuum from pg_stat_all_tables where relname = 't1';  relname | last_autovacuum ---------+-----------------  t1      | (1 row)

Hm, not really what was expected. When you check the documentation there is a formula we need to consider for our test, which is

1	vacuum threshold = autovacuum_vacuum_threshold +  autovacuum_vacuum_scale_factor * pg_class.reltuples

In our case that is:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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)   postgres=# select reltuples::int from pg_class where relname = 't1';  reltuples -----------    1000000 (1 row)   postgres=# select 50 + 0.2 * 1000000;  ?column? ----------  200050.0 (1 row)

This means we need to change at least 200050 rows to get autovacuum kicked in?

1 2	postgres=# update t1 set a = a + 1; UPDATE 1000000

That should be fine as we updated all the rows in the table which is way more than 200050:

1 2 3 4 5

postgres=# select relname,last_autovacuum from pg_stat_all_tables where relname = 't1';  relname |        last_autovacuum        ---------+-------------------------------  t1      | 2017-10-31 07:40:56.553194+01 (1 row)

… and here we go. Now, as we know how to check that on a real table we can do the same test on temporary table:

1 2 3 4 5 6 7 8 9

postgres=# create temporary table tt1 as select * from t1; SELECT 1000000 postgres=# update tt1 set a = a + 1; UPDATE 1000000 postgres=# select relname,last_autovacuum from pg_stat_all_tables where relname = 'tt1';  relname | last_autovacuum ---------+-----------------  tt1     | (1 row)

There is one point to consider: There is the parameter autovacuum_naptime which defaults to one minute so it might take some time until the autovacuum really did its work. But even when you wait for 10 minutes you’ll not see the last_autovacuum updated in pg_stat_all_tables for a temporary table. So, the answer is: No. There is no autovacuum on temporary tables but of course you can still do that manually:

1 2 3 4 5

postgres=# select relname,last_autovacuum, last_vacuum from pg_stat_all_tables where relname = 'tt1';  relname | last_autovacuum |          last_vacuum          ---------+-----------------+-------------------------------  tt1     |                 | 2017-10-31 07:50:58.041813+01 (1 row)

The same is true for the statistics used by the planner, you might need to analyze your temporary table manually:

1 2 3 4 5 6 7 8 9 10 11 12 13

postgres=# select last_analyze, last_autoanalyze from pg_stat_all_tables where relname = 'tt1';  last_analyze | last_autoanalyze --------------+------------------               | (1 row)   postgres=# analyze tt1; ANALYZE postgres=# select last_analyze, last_autoanalyze from pg_stat_all_tables where relname = 'tt1';          last_analyze          | last_autoanalyze -------------------------------+------------------  2017-10-31 07:52:27.690117+01 | (1 row)

Btw: This is clearly written in the documentation: “Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via session SQL commands.”

Hope this helps …