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

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

Right now it's just default, can you please point me to a document that
elaborates on calculation of FSM for a given load (or to say averaged load)

I found ( http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp ) but
does not have details.

# - Free Space Map -

#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes
each
#max_fsm_relations = 1000 # min 100, ~70 bytes each

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Thursday, August 30, 2007 4:07 PM
To: Nitin Verma
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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.