Postgresql OutOfMemory with Criteria.scroll() in Hibernate with 2M records

Question

I am running a Criteria.scroll() on PostgreSQL on a DB containing 2M records. The memory keeps increasing and finally it generates an OutOfMemoryException. Please can you advice how to fix this.

Postgresql DB version: 8.4 Postgresql Driver Used: postgresql-8.4-701.jdbc4.jar

Is there some known issue with Hibernate scroll() in PostgreSQL?

Appreciate any guidance/suggestions.

Answer 1

I am going to guess that it is a Java OutOfMemoryException based on the way you wrote the name.

Hope this isn't to obvious haha, but you get a ScrollableResults when you do your Criteria.scroll() and I assume you are doing something with that data as you go through it. Are you loading each item into memory?

Or are you getting this error just by getting the ScrollableResults? I would find that odd.

Answer 2

You have to call System.gc() every now and then (for example every 1000 records that you process). Or, don't fetch so many objects at once; use the setMaxResults(int) and setFirstResult(int) methods to fetch smaller data subsets. For example 10K records at a time, then System.gc() and then the next batch, etc.