1

I have an autovacuum (to prevent wraparound) happening on one of my tables for almost 4 days. I'm wondering if there is any way for me to tell if it's not keeping up with the amount of updates/deletes that are happening.

For reference, I am using Postgres 9.6 on Google CloudSQL. They have a known issue where pg_stat_progress_vacuum returns null for everything.

I've also been checking pg_stat_all_tables and have noticed that n_dead_tup for my table is going up instead of down, but I'm not sure if this number would be aware of the autovacuuming that is going on.

Improve this question

1 Answer 1

Reset to default
2

Bad luck. If you have a hosted database, and the monitoring view is broken, there's nothing you can do.

To speed up autovacuum as much as possible, increase maintenance_work_mem and lower autovacuum_vacuum_cost_delay. But that won't have an effect on an already running autovacuum worker.

Improve this answer
5
  • Thanks for your quick reply. Do you know if looking at n_dead_tup in pg_stat_all_tables is a reliable way of telling if the autovacuum won't complete? My value keeps going up, but I'm not sure if it's because it's not aware of the vacuuming taking place.
    – Mr. Snrub
    Jun 10, 2020 at 7:52
  • @Mr.Snrub, check if there any connection status show in idle in transaction if you find any then better to kill them . And also if your tables are larger then consider to decrease autovacuum_vacuum_scale_factor. and the final solution VACCUM FULL or do pg_pack.
    – Rj_N
    Jun 10, 2020 at 8:14
  • @Rj_N Killing sessions that could block autovacuum is alright. Decreasing the scale factor or running VACUUM (FULL) are bad advice and will harm rather than help.
    – Laurenz Albe
    Jun 10, 2020 at 8:16
  • @LaurenzAlbe @Rj_N: do either of you know if n_dead_tup in view pg_stat_all_tables is a sign that the vacuum is removing dead rows slower than new ones are being added?
    – Mr. Snrub
    Jun 10, 2020 at 8:38
  • No, n_dead_tup is only updated when VACUUM is done.
    – Laurenz Albe
    Jun 10, 2020 at 8:48

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.