Any disadvantage to reading using a cursor in Java PostgreSQL?

Question

I wish to read all rows of a large table from PostgreSQL in Java. I am processing the rows one by one in the Java software.

By default the JDBC PostgreSQL driver reads all rows into memory, meaning my program runs out of memory.

The documentation talks of "Getting results based on a cursor" using st.setFetchSize(50); I have implemented that and it works well.

Is there any disadvantage to this approach? If not, I would enable it for all our queries, big and small, or is that a bad idea?

Answer 1

Well, if you have a fetchsize of 50 and you get 1000 results, it will result in 20 round-trips to the database. So no, it's not a good idea to enable it blindly without thinking of the actual queries being run.

A bigger question is why are your ResultSets so big that you run out of memory. Are you only loading data you're going to use and you just don't have a lot of memory, or are there perhaps poorly designed queries that return excessive results.