I was querying a Postgres table with about 4 million rows and writing some of the fields to a CSV. I knew loading the rows into RAM was unacceptable, so I used the MyBatis class ResultHandler to process the records. The code ran fine locally, but in production we were having memory usage issues.

The Postgres documentation pointed out the problem. By default, Postgres loads all the results in RAM. This behavior is changed by setting the fetchSize to a value other than zero. I did this globally by calling org.apache.ibatis.session.Configuration.setDefaultFetchSize(). You can also specify fetchSize on the query annotation @org.apache.ibatis.annotations.Options.

Note that the Postgres documentation also says it will not use cursors if you are in autocommit mode. All my database queries are executed inside a transaction, but some people only put queries that modify the database in a transaction. Doing so would cause all the results to load into RAM, even if you use a ResultHandler.