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@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
