Does amazon rds PostgreSQL require vacuum?

Question

It isn't clear if auto vacuum is running in rds. Thanks!

Answer 1

Run this query to show if/when your tables were last vacuumed.

SELECT * FROM pg_stat_all_tables;

A table won't be vacuumed until the insert/update/delete threshold is reached, which is 20%.

You can tweak the setting in RDS by creating a new DB Parameter Group and change the setting you want.

Answer 2

The RDS Documentation states:

Autovacuum is enabled by default for all new Amazon RDS PostgreSQL DB instances, and the related autovacuum configuration parameters are appropriately set by default. Because our defaults are somewhat generic, you can benefit from tuning parameters to your specific workload.

You can check the activation of autovacuum with this command: SHOW autovacuum; to get a simple on or off answer.

You can get more detailed information with this command: SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';

Example of returned data:

                name                 |  setting  
-------------------------------------+-----------
 autovacuum                          | on
 autovacuum_analyze_scale_factor     | 0.05
 autovacuum_analyze_threshold        | 50
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 autovacuum_naptime                  | 5
 autovacuum_vacuum_cost_delay        | 5
 autovacuum_vacuum_cost_limit        | -1
 autovacuum_vacuum_scale_factor      | 0.1
 autovacuum_vacuum_threshold         | 50
 autovacuum_work_mem                 | -1
 log_autovacuum_min_duration         | -1
 rds.force_autovacuum_logging_level  | disabled

These settings (and further vacuum settings) are documented for Postgres here.