Hi
I have a table about 17G in size. I did a pg_relation_size before deleting 2 millions rows. I waited a few hours(actually overnight) and then checked the table size with pg_relation_size. The size reported was exactly the same.
My autovacuum is set to run every 50 minutes.
Settings:
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 3000 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 10000 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 1500 # min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
autovacuum_vacuum_cost_delay = -1
vacuum_cost_delay = 200 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 50
work_mem = 802400 # min 64, size in KB
maintenance_work_mem = 102400 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 2000000
Why doesn’t pg_relation_size take the autovacuum maintenance into effect