Autovacuum does not clear the database.

Question

Autovacuum does not clear the database.
Databases middle-db
Postgres 10.18, AWS RDS(vCPU 2, RAM 8Gb, SSD(gp2) 1100Gib)

Table "spree_datafeed_products"

Table and indexes sizes:

```
indexname                           | size  
index_spree_datafeed_products_on_updated_at                  | 48 GB
index_spree_datafeed_products_on_state                       | 35 GB
index_spree_datafeed_products_on_size_variant_field          | 40 GB
index_spree_datafeed_products_on_product_id                  | 32 GB
index_spree_datafeed_products_on_original_id                 | 31 GB
index_spree_datafeed_products_on_datafeed_id                 | 42 GB
index_spree_datafeed_products_on_datafeed_id_and_original_id | 31 GB
index_spree_datafeed_products_on_data_hash                   | 39 GB
spree_datafeed_products_pkey                                 | 18 GB

pg_size_pretty  - 419 GB
```

Worker:

Settings:

```
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 30
autovacuum_vacuum_cost_delay 20
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.1
autovacuum_vacuum_threshold 50
```

The garbage cleaning script has accumulated a lot of deleted entries. We have been waiting for more than a week (autoclearance). What is the problem? Why is the database failing?

Answer

Hi Ahmad,

My name is Brandon and I'm a Database Support Engineer. If I understand your question correctly you're wondering why AutoVacuum is not providing more storage space after it runs.

Simply put, Auto Vacuum generally does not provide more storage space once it is run as this runs standard vacuum not full vacuum on the database. Run Full Vacuum to reclaim this space, just be aware it will lock the tables it is vacuuming and consume significantly more resources than standard vacuum. You can read more about this here - https://www.postgresql.org/docs/current/routine-vacuuming.html

For more information on Vacuuming on RDS see these guides:
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.OtherParms
- https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/

Autovacuum does not clear the database.

Relevant content