PostgreSQL v13 new feature: tuning autovacuum on insert-only tables

www.cybertec-postgresql.com
7 min read
fairly difficult
PostgreSQL v13 introduces autovacuum for insert-only tables. This article tells you why and how to make use of this new feature.
© Laurenz Albe 2020

Most people know that autovacuum is necessary to get rid of dead tuples. These dead tuples are a side effect of PostgreSQL's MVCC implementation. So many people will be confused when they read that from PostgreSQL v13 on, commit b07642dbc adds support for autovacuuming insert-only tables (also known as "append-only tables").

This article explains the reasons behind that and gives some advice on how to best use the new feature. It will also explain how to achieve similar benefits in older PostgreSQL releases.

Note that all that I say here about insert-only tables also applies to insert-mostly tables, which are tables that receive only few updates and deletes.

How insert-triggered autovacuum works

From v13 on, PostgreSQL will gather statistics on how many rows were inserted since a table last received a VACUUM . You can see this new value in the new " n_ins_since_vacuum " column of the pg_stat_all_tables catalog view (and in pg_stat_user_tables and pg_stat_sys_tables ).

Autovacuum runs on a table whenever that count exceeds a certain value. This value is calculated from the two new parameters " autovacuum_vacuum_insert_threshold " (default 1000) and " autovacuum_vacuum_insert_scale_factor " (default 0.2) as follows:

insert_threshold + insert_scale_factor * reltuples

where reltuples is the estimate for the number of rows in the table, taken from the pg_class catalog.

Like other autovacuum parameters, you can override autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor with storage parameters of the same name for individual tables. You can disable the new feature by setting autovacuum_vacuum_insert_threshold to -1.

You can use " toast.autovacuum_vacuum_insert_threshold " and " toast.autovacuum_vacuum_insert_scale_factor " to change the parameters for the associated TOAST table.

Use case 1: "anti-wraparound" vacuum on insert-only tables

Why do insert-only tables need VACUUM ?

PostgreSQL stores transaction IDs in…
Read full article