Josh Berkus <josh@agliodbs.com> writes:
> I've been trying to diagnose in a production database why certain tables
> never get autovacuumed despite having a substantial % of updates. The
> obvious reason is locks blocking autovacuum from vacuuming the table ...
> but the trick is we don't log such blocking behavior, at all. This
> means that there is no possible way for a user to figure out *why* his
> tables aren't getting autovacuumed.
> And yes, this is a common problem. A quick survey on IRC found 3 active
> users on channel (out of 20 or so) who'd encountered it. The current
> case I'm looking at is a table with over 70% bloat which hasn't been
> autovacuumed since the database was upgraded a month ago.
> What I'd like to do is add some logging code to autovacuum.c so that if
> log_autovacuum is any value other than -1, failure to vacuum due to
> locks gets logged. Does this make sense?
It's hard to tell, because you're just handwaving about what it is you
think isn't being logged; nor is it clear whether you have any evidence
that locks are the problem. Offhand I'd think it at least as likely
that autovacuum thinks it doesn't need to do anything, perhaps because
of a statistics issue. There *is* an elog(DEBUG3) in autovacuum.c
that reports whether autovac thinks a table needs vacuumed/analyzed ...
maybe that needs to be a tad more user-accessible.
regards, tom lane