1

I have a problem where it appears that some large tables in my database are not getting vacuumed by the autovacuum deamon. Here is what I'm seeing:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC LIMIT 5;
+------------+-----------------------------+------------+------------+-------------------------------+
| schemaname |          relname            | n_live_tup | n_dead_tup |        last_autovacuum        |
+------------+-----------------------------+------------+------------+-------------------------------+
| md         | calculation_log_item        |   35989527 |    3559253 | 2020-07-13 03:41:37.49764-04  |
| audit      | transaction_statement       |     700356 |     557278 | NULL                          |
| audit      | record_history              |     701635 |     438849 | NULL                          |
| md         | program_requirement_state   |     193500 |      29204 | 2020-07-14 03:06:02.339032-04 |
| md         | calculation_log             |     157942 |      11792 | NULL                          |
+------------+-----------------------------+------------+------------+-------------------------------+
(5 rows)

So, it is clear that the daemon is running because there are values last night (note: I'm posting this on 2020-07-14). But, I can't figure out why transaction_statement and record_history are not. They have a lot of dead_tuples compared to live ones.

I'm using PostgreSQL v11. Here is the full version info:

select version();
+---------------------------------------------------------------------------------------------------------------------------------+
|                                                             version                                                             |
+---------------------------------------------------------------------------------------------------------------------------------+
| PostgreSQL 11.8 (Ubuntu 11.8-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit |
+---------------------------------------------------------------------------------------------------------------------------------+
(1 row)

What could be causing this? Please add a comment if you think if my question is vague or needs additional information to answer (such as postgresql.conf settings, etc).

2
  • 1
    What do you get in psql for \d+ audit.transaction_statement? Any non-default storage parameters? What do you get if you manually VACUUM (VERBOSE) audit.transaction_statement;? Jul 14 '20 at 18:30
  • Laurenz. That was it... for some reason, auto vacuum is disabled on those 2 tables. I've scripted it to enable them. If you will add an "answer" I will more it as accepted/correct. Thank you for the suggestion of \d+ ... I normally just do a \d
    – David S
    Jul 14 '20 at 20:33
1

There could be several reasons for autovacuum to not vacuum the table:

  • a table storage parameter is configured different from the default: enable_autovacuum = off, autovacuum_vacuum_scale_factor is absurdly high or similar.

    Find out by looking at the \d+ output for the table in psql.

  • the rate of change on the table is so high that autovacuum just cannot keep up.

    This is the case if you find autovacuum workers running for the table that never finish. The remedy is to lower autovacuum_vacuum_cost_delay.

  • data corruption in the table or its indexes make autovacuum fail with an error, so that it never finishes processing the table. Look at the log file to diagnose that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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