Re: Ineffective autovacuum

Royce Ausburn <royce.ml@inomial.com> writes:
> I have a problem with autovacuum apparently not doing the job I need it to do.

Hm, I wonder whether you're getting bit by bug #5759, which was fixed
after 8.3.12.

> I have a table named datasession that is frequently inserted, updated and deleted from.  Typically the table will
havea few thousand rows in it.  Each row typically survives a few days and is updated every 5 - 10 mins.  The
applicationreceives unreliable, potentially duplicate data from its source, so this table is heavily used for
synchronisingapplication threads as well.  A typical access pattern is: 

> - tx begin
> - SELECT FOR UPDATE on a single row
> - Do some application processing (1 - 100 ms)
> - Possibly UPDATE the row
> - tx commit

Transactions of that form would not interfere with autovacuum.  You'd
need something that wants exclusive lock, like a schema change.

> I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be
cancelledwhen a client requests a lock that auto vacuum is using� My questions: 
> 1) Does it look like I'm affected by the same problem as in the below discussion?

Not unless you're seeing a lot of "canceling autovacuum task" messages
in the postmaster log.

            regards, tom lane