0

Autovacuum execution takes a long time on my postgres9.4 instance, and my question is:

  • Can I improve the speed of autovacuum by dropping the index? If I terminate the vacuum during the process, will its results take effect, as I may need to terminate the current vacuum before the drop index.
  • Is there any other way to speed up autovacuum.

I've increased maintenance_work_mem, set vacuum_cost_limit to a higher value and set vacuum_cost_deplay to 0.

Any suggestion or help would by appriciated!

3
  • 2
    Hi, and welcome to dba.se! You're about 5 releases beyond EOL - consider an upgrade. Considerable work has gone into this!
    – Vérace
    Commented Jun 25 at 11:42
  • @Vérace Thank you for the suggestion. We are planning an upgrade, but it may take us several months. I am concerned that the slow speed of the current vacuum may soon cause transaction ID wraparound. Commented Jun 25 at 12:02
  • 1
    Changing vacuum_cost_delay doesn't do anything for autovacuum, unless autovacuum_vacuum_cost_delay is set to -1 (which is not the default).
    – jjanes
    Commented Jun 25 at 18:08

1 Answer 1

0

The best things to speed up autovacuum are

  1. Upgrade.

    PostgreSQL 9.6 has introduced substantial performance improvements for VACUUM, and later releases have improved that even more.

  2. Increase autovacuum_vacuum_cost_limit or reduce autovacuum_vacuum_cost_delay.

    Setting autovacuum_vacuum_cost_delay to 0 will make autovacuum go as fast as it can.

  3. Crank up maintenance_work_mem to 1GB.

  4. On recent releases, crank up max_parallel_maintenance_workers.

Yes, dropping an index will speed up autovacuum.

If you interrupt autovacuum, it may have already done some work that the next run won't have to repeat, but it could also be that you lose everything.

Your Answer

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

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