Auto vacuuming high-write, high-update, and mostly read table types

Question

What are good auto vacuum settings (recommendations) for tables like:

• High Write Table Insert load

  Range between 30-10,000 inserts in a day. The table can idle for weeks without load, but can get bursts of inserts at least three times a week.

• High Update Table

  It uses partition table data, is 3-8 times the size of my table from a single insert.

• High Write Table

  A single row gets updated only once, but bursts of unique key updates in a day and needs to be updated it could be 30-10,000 key update.

• High Read Table

  Most tables are high read tables set to fill factor 80 for my data warehouse, housing table that came from the computation of High Update Table

My deletes happen monthly and in batches. Everything relating to the key gets deleted or moved as backup.

Currently my fill factor is set to be 10-20 for high update table.

Using TDS db.t3.large but I switch to db.t3.micro during low traffic.

Also, does setting fill factor really low slow down selects?

Answer 1

That question is too broad, but here are some pointers about configuring autovacuum:

• For tables that receive bulk inserts, run an explicit VACUUM afterwards or use PostgreSQL v13.

• For tables with many updates, a fillfactor of 70 to 90 (depending on row size) is a good thing if no updated columns are indexed. 10 or 20 is just a crazy waste of space.

• There is no need to care about autovacuum for tables that are only read.

• With mass deletes, there is nothing much you can do. If you can use partitioning, that pain may vanish completely.

Of course low fillfactor will have a negative impact on query performance; that is the price you are paying for more efficient data modifications:

• For sequential scans, the impact is clear: you have to read all that empty space too.

• For index scans that read only a single row, there will be no performance impact.

• Index scans that read several rows are somewhere in the middle, since they will have to read more blocks if fillfactor is low, because the rows will be spread across more blocks.

• Don't forget the impact on caching efficiency: If your blocks consist mostly of air, the RAM used for caching will also mostly contain dead space.

Answer 2

There is a document on the internet that discusses AUTOVACUUM in PostgreSQL on RDS titled: Understanding autovacuum in Amazon RDS for PostgreSQL environments

In particular ....

Autovacuum is a daemon that automates the execution of VACUUM and ANALYZE (to gather statistics) commands. Autovacuum checks for bloated tables in the database and reclaims the space for reuse.

Essentially I would recommend running the defaults as AUTOVACUUM will take care of cleaning up the tables and updating the statistics for you.

You will however have to monitor to see if your PostgreSQL RDS instance is doing a good job of keeping up with housekeeping. You can use the script taken from the article I mentioned:

SELECT
relname AS TableName
,n_live_tup AS LiveTuples
,n_dead_tup AS DeadTuples
,last_autovacuum AS Autovacuum
,last_autoanalyze AS Autoanalyze
FROM pg_stat_user_tables;

This will produce something like this:

        tablename         | livetuples | deadtuples |          autovacuum           |          autoanalyze
--------------------------+------------+------------+-------------------------------+-------------------------------
 cfgdateval               |       1666 |          0 | 2020-02-26 12:32:13.851917+00 | 2020-02-26 12:32:13.87854+00
 atcontval                |       2940 |          0 | 2018-06-07 09:53:30.664645+00 | 2019-11-28 15:10:15.256083+00
 cfgintval                |        206 |          0 | 2020-02-26 12:32:13.815353+00 | 2020-02-26 12:32:13.815787+00
 cfgaggval                |         26 |          0 |                               | 2017-07-26 18:56:23.161035+00
 cfgobjval                |       3366 |          0 | 2020-02-26 12:32:13.933712+00 | 2020-02-26 12:32:13.959892+00
 atintval                 |     169080 |          0 |                               | 2018-06-07 09:53:33.821121+00
 atobjval                 |     259728 |          0 |                               | 2018-06-07 09:53:32.557788+00
 cfgstrval                |       1616 |          0 | 2020-02-26 12:32:13.752583+00 | 2020-02-26 12:32:13.803132+00
 ataggval                 |     182790 |          0 |                               | 2018-06-07 09:53:30.566021+00
 coolinking               |      59375 |          0 | 2017-05-05 08:47:09.865774+00 | 2017-05-05 09:36:07.292082+00
 cooobject                |      31791 |         13 |                               | 2017-05-05 09:01:06.672438+00

If you have tables containing lots of dead tuples and no AUTOVACUUM has been run, then you might want to consider tuning the Autovacuum settings:

select category, name,setting,unit,source,min_val,max_val from pg_settings where category = 'autovacuum' ;

Output:

  category |                name                 | setting   | unit |       source       | min_val |  max_val   | boot_val 
------------+-------------------------------------+-----------+------+--------------------+---------+------------+-----------
Autovacuum | autovacuum                          | on        |      | default            |         |            | on
Autovacuum | autovacuum_analyze_scale_factor     | 0.05      |      | configuration file | 0       | 100        | 0.1
Autovacuum | autovacuum_analyze_threshold        | 50        |      | default            | 0       | 2147483647 | 50
Autovacuum | autovacuum_freeze_max_age           | 200000000 |      | default            | 100000  | 2000000000 | 200000000
Autovacuum | autovacuum_max_workers              | 3         |      | default            | 1       | 262143     | 3
Autovacuum | autovacuum_multixact_freeze_max_age | 400000000 |      | default            | 10000   | 2000000000 | 400000000
Autovacuum | autovacuum_naptime                  | 30        | s    | configuration file | 1       | 2147483    | 60
Autovacuum | autovacuum_vacuum_cost_delay        | 20        | ms   | default            | -1      | 100        | 20
Autovacuum | autovacuum_vacuum_cost_limit        | -1        |      | default            | -1      | 10000      | -1
Autovacuum | autovacuum_vacuum_scale_factor      | 0.1       |      | configuration file | 0       | 100        | 0.2
Autovacuum | autovacuum_vacuum_threshold         | 50        |      | default            | 0       | 2147483647 | 50

See A Case Study of Tuning Autovacuum in Amazon RDS for PostgreSQL for more details for when and if you should modify the AUTOVACUUM settings.

Generally PostgreSQL does a good job of keeping with housekeeping.