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

From: "Nitin Verma" <nitinverma(at)azulsystems(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: What kind of locks does vacuum process hold on the db?
Date: 2007-08-30 04:13:33
Message-ID: 640150C1BB635E4C9F2F617BA3EFF1D102594BE3@XCHMTV1.azulsystems.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

What kind of locks does it hold on the db? Table level / Row level /
AccessExclusiveLock ?
Is there a document that details vacuum process, and its internals?

Use case: I am doing a vacuum {all full analyze} on a postgres 8.1 hosting
two user databases. For this I am using ${pg-home}/bin/vacuumdb, not direct
SQLs. Parallel to this I am running transaction on both user-DBs.
Observation:
Transactions don't pause while vacuum is running. (Odd, how come?)

http://www.postgresql.org/docs/8.1/interactive/sql-vacuum.html
"Plain VACUUM (without FULL) simply reclaims space and makes it available for
re-use. This form of the command can operate in parallel with normal reading
and writing of the table, as an exclusive lock is not obtained. VACUUM FULL
does more extensive processing, including moving of tuples across blocks to
try to compact the table to the minimum number of disk blocks. This form is
much slower and requires an exclusive lock on each table while it is being
processed."

After this I tried two vacuums in parallel but those lock each other.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rodrigo De León 2007-08-30 04:16:18 Re: how to print a schema
Previous Message Joshua D. Drake 2007-08-30 03:38:35 Re: Reliable and fast money transaction design