Ineffective autovacuum

Hi all,

I have a problem with autovacuum apparently not doing the job I need it to do.

I have a table named datasession that is frequently inserted, updated and deleted from. Typically the table will have a few thousand rows in it. Each row typically survives a few days and is updated every 5 - 10 mins. The application receives unreliable, potentially duplicate data from its source, so this table is heavily used for synchronising application 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

In a few instances of our application we're seeing this table grow obscenely to the point where our monitoring servers get us out of bed to manually vacuum. I like sleep, so I want to fix this =D

I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be cancelled when 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?

2) Are there better solutions to this problem than a periodic task that vacuums/truncates-and-rebuilds the table?

Perhaps relevant info:

# select version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 8.3.12 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)

Auto vacuum and vacuum parameters are set to the factory defaults.

Cheers,

--Royce

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [GENERAL] Vacuum as "easily obtained" locks
> Date: 4 August 2011 1:52:02 AM AEST
> To: Michael Graham <mgraham(at)bloxx(dot)com>
> Cc: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
>
>>> On Wed, 2011-08-03 at 11:40 -0400, Tom Lane wrote:
>>> The other problem is that once autovacuum has gotten the lock, it has
>>> to keep it for long enough to re-scan the truncatable pages (to make
>>> sure they're still empty). And it is set up so that any access to the
>>> table will kick autovacuum off the lock. An access pattern like that
>>> would very likely prevent it from ever truncating, if there are a lot
>>> of pages that need to be truncated. (There's been some discussion of
>>> modifying this behavior, but nothing's been done about it yet.)

> Michael Graham <mgraham(at)bloxx(dot)com> writes:
>> Ah! This looks like it is very much the issue. Since I've got around
>> 150GB of data that should be truncatable and a select every ~2s.
>
>> Just to confirm would postgres write:
>
>> 2011-08-03 16:09:55 BST ERROR: canceling autovacuum task
>> 2011-08-03 16:09:55 BST CONTEXT: automatic vacuum of table
>> "traffic.public.logdata5queue"
>
>> Under those circumstances?
>
> Yup ...
>
> If you do a manual VACUUM, it won't allow itself to get kicked off the
> lock ... but as noted upthread, that will mean your other queries get
> blocked till it's done. Not sure there's any simple fix for this that
> doesn't involve some downtime.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general