If you’ve been running PostgreSQL for a while, you’ve heard about autovacuum. Yes, autovacuum, the thing which everybody asks you not to turn off, which is supposed to keep your database clean and re...
Updated Jul 28, 2022
Version 1.0
Blog Post
Thank you samay_sharma, I noticed that auto vacuum threshold was set to 5000 (very less as the tables have around 100K+ transactions per day) and auto vacuum was called often. I updated it so that vacuum does not occur that frequent (not sure where my previous comment disappeared, so writing again) and thus auto vacuum was getting cancelled on that server. Further, adding to that query could you please let me know, how to handle if the database has turned very slow. What are the other things that I have to look into. (Learning PGSQL while checking out things [have a little SQL background])
Now auto vacuum works fine and dead tuples are reasonable.
1. I suspect for bloating as the database size is huge around 220GB, I ran the https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql with following output: (trying to learn and understand about the output parameters)
2. Also the counters are never reset so the huge numbers too. Can I reset the counters using below query? Is it recommended ?
SELECT pg_stat_reset();
Thanks,
Anuya