Autovacuum in Postgresql database

Question

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

• user1822: Those are accumulated numbers since Postgres was started. The interesting question is how many rows have been changed/deleted/inserted since the last vacuum?
• Gabriele D'Onufrio: I got 5421563 rows from this : select count(id_document_state) from document_state where date_created > '2017年11月10日' or date_modified > '2017年11月10日' 5421563 rows
• Gabriele D'Onufrio: can anyone answer? please

Answer 1

Autovacuum only runs when necessary:

• if too many rows have been changed or deleted sine the last VACUUM run

• if there are rows old enough that they should be “frozen” to prevent data loss due to transaction counter wraparound.

See the documentation for details.

If autovacuum never ran on some tables, that could also be because there was no reason for it to run yet.

• Gabriele D'Onufrio: can you explain me frozen please? is there a command to do that?
• Laurenz Albe: "Frozen" is a flag on a row version that means that the row is unconditionally visible, and xmin and xmax should be ignored. To figure out if your table is due for autovacuum, look at the number of dead tuples in the table's entry in pg_stat_user_tables.