13

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

  • 6
    Run SHOW autovacuum;. Or enable autovacuum logging and check the logs. RDS certainly does require vacuum, but I'd be surprised if it wasn't doing pretty aggressive autovacuuming. – Craig Ringer Jul 9 '14 at 3:16
15

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.

| improve this answer | |
4

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.