Re: Autovacuum help..

On Tue, Aug 01, 2006 at 08:02:59AM -0700, Sundar Narayanaswamy wrote:
> I need your help/suggestions with a problem I am facing related to autovacuum.
>
> I am using PostgreSQL 8.1.2 through a JDBC connection. The connection is
> long lived (established when the application starts up and is closed only when
> the application is shutdown).

<snip>

> On digging in further, I noticed that the reltuples (in pg_catalog.pg_class)
> for the relevant table keeps increasing continually. I also noticed a large
> number of dead unremovable rows when I ran the vacuum analyze command.

Well, you need to work out why they are unremovable. For example, do
you have any really long lived open transactions. These are generally a
bad idea, for all sorts of reasons. If you don't commit occasionally,
none of your changes will be visible to anyone else.

> My question is, is autovacuum expected to work in situations like this where I
> have a long lived connection to the database ? After I shutdown my application,

It's got nothing to do with connections and everything to do with open
transactions. I'd suggest you run a:

select * from pg_stat_activity ;

And look for messages like: IDLE in transaction.

> why am required to run the "analyze" command before the dead rows are removed
> (autovacuum is not able to remove the dead rows until I run the "analyze"
> command) ?

The stats arn't updated until the rows are actually removed. Eventually
autovacuum would have done the analyze for you...

Hope this helps,

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.