Autovacuum does not clear the database.

0

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"

relid               | 16556
schemaname          | public
relname             | spree_datafeed_products
seq_scan            | 20
seq_tup_read        | 365522436
idx_scan            | 962072108
idx_tup_fetch       | 9929276855
n_tup_ins           | 2846455
n_tup_upd           | 35778058
n_tup_del           | 284291955
n_tup_hot_upd       | 0
n_live_tup          | 3546840
n_dead_tup          | 338790851
n_mod_since_analyze | 307930753
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 2022-04-29 13:01:43.985749+00
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 1

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:

datid            | 16404
datname          | milanstyle_production
pid              | 2274
backend_start    | 2022-05-01 19:52:00.066097+00
xact_start       | 2022-05-01 19:52:00.23692+00
query_start      | 2022-05-01 19:52:00.23692+00
state_change     | 2022-05-01 19:52:00.236921+00
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 1301636863
query            | autovacuum: VACUUM ANALYZE public.spree_datafeed_products
backend_type     | autovacuum 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?

  • What is your maintenance_work_mem? It could be that you are hitting cost and memory limits for that size of a table and it is just taking a really long time.

Ahmad
asked 2 years ago240 views
1 Answer
0

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:

profile pictureAWS
SUPPORT ENGINEER
Brandon
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions