Re: Concurrent VACUUM and ANALYZE

On Mon, Jul 21, 2008 at 7:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
>> Because we wouldn't want multiple ANALYZEs running on the same table,
>> changing the lock back to an AccessShareLock doesn't sound like a
>> solution.
>
> It flat will not work. We used to do it that way, and it didn't
> (search for "tuple concurrently updated" in the archives).

Agreed.

>> However, what are the thoughts around creating another,
>> more-specific lock? Perhaps something like ShareUpdateAnalysisLock?
>
> The general overhead involved in a whole new lock type is high enough
> that I would resist taking this path. (It's certainly a lot more than
> adding an entry to one enum someplace --- offhand I can name docs and
> grammar as important issues. And no you don't get to have a hidden lock
> type that no one can see.)

Any other suggestions?

> Also, as Alvaro points out, it's far from clear that concurrent VACUUM
> and ANALYZE is as safe as you think --- they both want to write the same
> fields in pg_class.

AFAICS, Alvaro didn't say that at all. At worst, if ANALYZE completed
after VACUUM, its stats wouldn't be as good as those set by VACUUM.
But, as I said in response to Alvaro, that's no different than running
ANALYZE immediately following VACUUM.

The case I'm looking at is a large table which requires a lazy vacuum,
and a zero vacuum cost delay would cause too much I/O. Yet, this
table has enough insert/delete activity during a vacuum, that it
requires a fairly frequent analysis to maintain proper plans. I
patched as mentioned above and didn't run across any unexpected
issues; the only one expected was that mentioned by Alvaro.

-Jonah