Are temporary tables auto vacuumed in PostgreSQL?

转载

wx5bcd90b347f01 2021-10-25 15:19:04

文章标签 Postgresql temp table auto vacuum 文章分类 代码人生

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 …