Обсуждение: autovacuum

i have PostgreSQL 8.1.2



i want to enable autovacuum at my PostgreSQL 8.1.2  from postgresql.conf
i've got 50-60 insert and/or update queries in a second in that case tables
shouldn't be locked
does autovacuum locks tables while vacuuming?

and also i wonder what should be the configuration?

memory: 2gb ddr2
CPU: P4-prescott 3193.495 MHz
cache size      : 2048 KB



Alp UYANIK
Software Engineer

> i have PostgreSQL 8.1.2
>
> i want to enable autovacuum at my PostgreSQL 8.1.2  from postgresql.conf
> i've got 50-60 insert and/or update queries in a second in that case
> tables shouldn't be locked
> does autovacuum locks tables while vacuuming?

Of course it does; any request to access a relation will issue one or
more locks on the relation.

VACUUM issues an AccessShareLock request against each relation that is
vacuumed, which is probably nearly the same lock request your
applications will be requesting, save for the fact that they'll also
be submitting some RowExclusiveLock requests for individual rows of
relations.

http://www.postgresql.org/docs/8.1/interactive/mvcc.html
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/nonrdbms.html
"Bother," said Pooh, as he deleted his root directory.

Christopher Browne <cbbrowne@acm.org> writes:
>> does autovacuum locks tables while vacuuming?

> Of course it does; any request to access a relation will issue one or
> more locks on the relation.

This is correct in general ...

> VACUUM issues an AccessShareLock request against each relation that is
> vacuumed, which is probably nearly the same lock request your
> applications will be requesting, save for the fact that they'll also
> be submitting some RowExclusiveLock requests for individual rows of
> relations.

... but wrong in detail.  Actually VACUUM takes ShareUpdateExclusive
lock, which is a bit stronger than an ordinary reader's AccessShare
lock (it does not block ordinary reads or updates, but it does block
index creation as well as other attempts to VACUUM the table).  Also,
RowExclusiveLock is a table-level lock.  Data-modifying commands such
as UPDATE take RowExclusiveLock on the whole table as a means of letting
other sessions know that someone is changing the table.  They also
take row-level locks on the specific rows they change.

This is all discussed here:
http://www.postgresql.org/docs/8.1/static/explicit-locking.html

            regards, tom lane