Reviewing pg_stat_user_tables will give you an idea of how often autovacuum
is cleaning up those tables that "need" that vacuum full on a quarterly
basis. You can tune individual tables to have a lower threshold ratio of
dead tuples so the system isn't waiting until you have 20% dead rows before
vacuuming a table with millions of rows that occupies a GB or more on disk.
You might consider changing your nightly analyze to a nightly vacuum
analyze, at least for the tables you know can be problematic. The more
dense a table is packed, the better cache_hits and other such metrics. Like
making dinner, cleanup as you go.

One thing that I think is interesting is that the default cost_delay has
been updated with PG12 from 20ms down to 2ms such that all things being
equal, much much more work is done by autovacuum in a given second. It may
be worth taking a look at.

Another great thing coming to you in PG12 is the option to do reindex
concurrently. Then there's no need for pg_repack on indexes.

Good luck sir.

Reply via email to