1

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

Improve this question
3
  • 1
    Those are accumulated numbers since Postgres was started. The interesting question is how many rows have been changed/deleted/inserted since the last vacuum?
    – a_horse_with_no_name
    Nov 27, 2017 at 13:54
  • 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
    Nov 27, 2017 at 16:36
  • can anyone answer? please
    – Gabriele D'Onufrio
    Nov 19, 2018 at 13:25

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • can you explain me frozen please? is there a command to do that?
    – Gabriele D'Onufrio
    Mar 23, 2020 at 11:59
  • "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.
    – Laurenz Albe
    Mar 23, 2020 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.