Re: Is Autovacuum running?

On Wed, 22 Feb 2023 at 11:28, Brad White <b55white(at)gmail(dot)com> wrote:
> datname | stats_reset | now
> DB | 2023-02-17 14:28:27-06 | 2023-02-21 16:16:34-06
>
> I heard that the system was running slowly on Friday. They may have cycled the service in an attempt to resolve that.

You'll probably want to not do that then. Just in case I wasn't
clear, this'll not only reset the last_vacuumed values, but also
values that autovacuums uses to determine if it needs to do anything
or not. So, say if a table normally gets autovacuumed about once
every 2 days, if you reset the stats each day, then it'll likely just
*never* be autovacuumed, at least, not until an antiwraparound vacuum
is needed. That's not a good situation. That's why the warning in the
docs exists about pg_stat_reset().

If you're resetting the stats to track something like how busy the
database is each week, you should probably just store the current
values and calculate the differences from last week using LAG(...)
OVER (....)

> I went ahead and ran ANALIZE. We'll see if that affects anything.

I think you might want to use that bloat script and get a round of
vacuuming done on any table that looks more bloated than what
autovacuum would trigger on. You may also have a bunch of bloated
indexes that need to be reindexed. I'm not too sure how that bloat
checking script will perform if vacuums have been neglected. You'd at
least want to ANALYZE every table first.

I would make not running pg_stat_reset() ever a priority.

David