Re: Queries very slow and memory consumption too high

[Thomas Kellerer <spam_eater(at)gmx(dot)net>]

Saurabh Dave, 22.06.2009 07:02:

> With all the other databases our application is working quite fine, but 
> with Postgres after a day queries are becoming extremely slow and it 
> seems to be taking up all the available memory.

It's not clear to me *where* the memory is taken. Is that on the machine running your application (i.e. Hibernate and the connection pool) or is that memory taken on the Postgres server?

If it's on the application server, could this be caused by not properly closed result sets? The Postgres JDBC driver by default buffers all ResultSets in memory before returning to the calling class. If you do not close your result sets properly, maybe this memory is not released by the driver.

As an alternative you might try to configure the connection to use "cursor based fetching" to avoid the buffering in the driver code:

http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

Regards
Thomas