Michael,
Your complete explanation is very helpful!
I appreciate it
Thank you so much!
Regards!
El jue., 30 abr. 2020 a las 10:52, Michael Lewis (<mlewis@xxxxxxxxxxx>) escribió:
In staging environment we have disabled autovacuum since that it is a testing environment and the database are restored very often.But in production environment it is enabled autovacuum=onThe weird case is that production was slow and staging environment was faster.You haven't specified how you are doing backup and restore, but unless it is a byte-for-byte file copy method, then there would be no bloat on the restored staging environment so no need to vacuum. You would want to ensure you take a new statistics sample with analyze database after restore if you aren't.In your production system, if your configs for autovacuum settings have not been changed from the default parameters, it probably is not keeping up at all if the system is moderately high in terms of update/delete transactions. You can check pg_stat_activity for active vacuums, change the parameter to log autovacuums longer than X to 0 and review the logs, or check pg_stat_user_tables to see how many autovacuums/analyze have been done since you last reset those stats.If you have tables that are in the millions or hundreds or millions of rows, then I would recommend decreasing autovacuum_vacuum_scale_factor from 20% down to 1% or perhaps less and similar for autovacuum_analyze_scale_factor. You can do this on individual tables if you have mostly small tables and just a few large ones. Else, increase the threshold settings as well. The default value for autovacuum_vacuum_cost_delay changed from 20ms to 2ms in PG12 so that may also be prudent to do likewise if you upgraded to PG12 and kept your old settings, assuming your I/O system can handle it.Otherwise, if you have a period of time when the activity is low for your database(s), then a last resort can be a daily scheduled vacuum analyze on all tables. Note- do not do vacuum FULL which requires an exclusive lock on the table to re-write it entirely. You are just looking to mark space re-usable for future transactions, not recover the disk space back to the OS to be consumed again if autovacuum still can't keep up. pg_repack extension would be an option if you need to recover disk space while online.
Moisés López Calderón
Mobile: (+521) 477-752-22-30
Twitter: @moylop260
Twitter: @vauxoo
