Re: What kind of locks does vacuum process hold on the db?

On 8/30/07, Nitin Verma <nitinverma(at)azulsystems(dot)com> wrote:
> > Why are you doing FULL vacuums? Is there some problem that regular vacuums
> aren't solving?
>
> Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M +
> 12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by
> the quota allocated to DB is 100M.
>
> A regular vacuum doesn't stop the database growth, and DB grows beyond 100M.
> Then we have to trigger a script that dump/restores on the live database. For
> that we have a small outage (which is right now automated).
>
> A full vacuum keeps the database below 100M and no outage.

Then you aren't doing regular vacuum often enough and / or don't have
high enough fsm settings.

>
> > Yes, vacuum full takes a hard lock on a table.
>
> That means Table Level AccessExclusiveLock, right?

Not sure which name it is. It definitely blocks writes to the table
while it is vacuuming it. But that's secondary. You're having to do
regular vacuum fulls because of too infrequent regular vacuum and / or
too low fsm setting.