I am trying to set up autovacuum in my postgresql 9.5 database, but table stats shows me no evidence of that.
In postgresql.conf, the autovacuum is already enabled:
autovacuum = on
In settings, plus, I have (select * from pg_settings where category like 'Autovacuum'
)
autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 60
autovacuum_vacuum_cost_delay 20
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
If I execute this query:
SELECT schemaname, relname,
last_vacuum ,
last_autovacuum ,
last_analyze ,
last_autoanalyze
FROM pg_stat_all_tables
where schemaname = 'cdi'
I get this results:
relname last_vacuum last_autovacuum last_analyze last_autoanalyze aa 11/11/2017 05:56 04/11/2017 03:24 13/11/2017 23:25 bb 09/10/2017 15:01 04/11/2017 03:55 09/10/2017 15:01 cc 13/09/2017 13:23 04/11/2017 03:55 11/11/2017 04:12 dd 14/09/2017 10:27 04/11/2017 03:55 12/11/2017 17:09 ee 14/09/2017 10:32 04/11/2017 03:00
ff 11/10/2017 09:20 04/11/2017 03:48 13/11/2017 22:19 gg 04/11/2017 03:55
hh 04/11/2017 03:55
ii 04/11/2017 03:16 11/11/2017 06:51 ll 04/11/2017 03:16
mm 04/11/2017 03:55
nn 04/11/2017 03:55
oo 04/11/2017 03:55
pp 04/11/2017 03:00
It doesn't appear to me that there is an efficient autovacuum
Can someone tell me a method to make autovacuum happen?
this is my table:
Sequential scans 156 Sequential tuples read 5448762930 Index scans 802121339750 Index tuples fetched 800736733206 Tuples inserted 21907385 Tuples updated 6190434 Tuples deleted 190337 Tuples HOT updated 3958397 Live tuples 59641911 Dead tuples 703334 Heap blocks read 13685226418 Heap blocks hit 786803454925 Index blocks read 5104418623 Index blocks hit 3208275818736 Toast blocks read 0 Toast blocks hit 0 Toast index blocks read 0 Toast index blocks hit 0 Last vacuum 2017-10-11 09:20:22.358935+02 Last autovacuum Last analyze 2017-11-04 03:48:49.753917+01 Last autoanalyze 2017-11-13 22:19:08.47618+01 Vacuum counter 1 Autovacuum counter 0 Analyze counter 10 Autoanalyze counter 3 Table size 6778 MB Toast table size 8192 bytes Indexes size 3969 MB