JDBC Fetch Size for Reading Large Result Sets Safely
Read large JDBC result sets without running out of memory: setFetchSize, streaming cursors in PostgreSQL and MySQL, and transaction needs.

A query that returns 5 million rows can fill the heap before your loop sees the first one. The driver's default behaviour is to fetch everything, and each database has its own way to stream instead.
The problem
try (PreparedStatement ps = c.prepareStatement("select id, payload from event")) {
try (ResultSet rs = ps.executeQuery()) { // PostgreSQL driver reads all rows here
while (rs.next()) handle(rs);
}
}With the PostgreSQL driver in its default mode, executeQuery retrieves the entire result into memory, then lets you iterate. The result is Java heap space on a large table.
PostgreSQL: three conditions
c.setAutoCommit(false); // 1: cursors need a transaction
try (PreparedStatement ps = c.prepareStatement(
"select id, payload from event",
ResultSet.TYPE_FORWARD_ONLY, // 2: forward only
ResultSet.CONCUR_READ_ONLY)) {
ps.setFetchSize(1_000); // 3: a fetch size
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) handle(rs);
}
}
c.commit();With all three, the driver uses a server-side cursor and fetches 1,000 rows at a time. If auto commit is on, the fetch size is ignored and everything loads. The connection stays in a transaction during the read, so keep the loop fast, and be aware of the lock and snapshot held for the duration.
MySQL and MariaDB
Connector/J buffers all rows by default. Two ways to stream:
ps.setFetchSize(Integer.MIN_VALUE); // row-by-row streamingor add useCursorFetch=true to the URL and use a positive fetch size, which uses a server-side cursor. With row streaming, you must read all rows or close the result before running another statement on the same connection. MariaDB's driver supports setFetchSize(1) style streaming.
Other databases
Oracle's driver fetches 10 rows at a time by default and honours setFetchSize directly, so raising it to 100 to 1000 reduces round trips. SQL Server's driver uses adaptive buffering. Check your driver's documentation, as defaults differ, and test with a table large enough to notice.
How big
Larger fetch sizes mean fewer round trips and more memory per batch. A few hundred to a few thousand rows is typical. Measure with your row size: 1,000 rows of 10 KB is 10 MB in flight.
Work with the stream
try (Stream<Event> events = repository.streamAll()) { // Spring Data JPA, read-only transaction
events.forEach(this::handle);
}Spring Data and jOOQ expose the same idea as a Stream that must be closed. In JPA, add the hint HINT_FETCH_SIZE and clear the persistence context periodically, because managed entities accumulate otherwise, see Hibernate batch processing.
Things that break streaming
- Calling
rs.last()orrs.getRow()on a forward-only result set. - Using
TYPE_SCROLL_INSENSITIVE, which makes some drivers load everything. - Running other statements on the same connection while streaming (MySQL).
- Collecting all rows into a list in your own code, which defeats the purpose.
- A pool or proxy with a short statement or idle timeout that cuts a long read. See pool settings.
Pagination as an alternative
If you can stop after a page, keyset pagination (where id > ? order by id limit ?) reads in short transactions and works with any driver. It is the approach in cursor pagination, and it suits exports that may run for a long time and restart.
Reproduce the out-of-memory error and the fix
The default behaviour of the PostgreSQL driver is to read the entire result into memory before next() returns the first row. The test table is one million rows of orders, about 61 MB in the database. The program reads them all and sums a column, with the heap limited to 96 MB, on pgjdbc 42.7.7 against PostgreSQL 17.
try (Connection c = DriverManager.getConnection(url)) {
c.setAutoCommit(autoCommit);
try (PreparedStatement ps = c.prepareStatement(
"select id, customer_id, status, total, created_at from orders")) {
ps.setFetchSize(fetchSize);
long rows = 0; double sum = 0;
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) { rows++; sum += rs.getDouble("total"); }
}
System.out.printf("fetchSize=%d autoCommit=%s: %,d rows%n", fetchSize, autoCommit, rows);
}
}java -Xmx96m -cp out:postgresql-42.7.7.jar Fetch 0 true # default fetch size
java -Xmx96m -cp out:postgresql-42.7.7.jar Fetch 1000 true # fetch size set, autocommit on
java -Xmx96m -cp out:postgresql-42.7.7.jar Fetch 1000 false # fetch size set, autocommit offfetchSize=0 autoCommit=true : Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at org.postgresql.core.v3.QueryExecutorImpl.processResults(...)
fetchSize=1000 autoCommit=true : Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
fetchSize=1000 autoCommit=false: first row available after 6 ms
fetchSize=1000 autoCommit=false: 1,000,000 rows in 581 ms, heap in use after 14 MBThe second row is the trap, and it is the reason this post lists three conditions for PostgreSQL. Setting a fetch size with auto-commit on still loaded everything and ran out of memory, with no warning. The driver uses a server-side cursor only when the statement runs inside a transaction (auto-commit off), the statement is forward-only, and the fetch size is positive. Under those conditions the first row arrived after 6 ms and all one million rows were read in 581 ms with 14 MB of heap in use. Each of the three conditions is a way to lose the benefit without seeing an error.
Make the transaction explicit in Spring
In a Spring application, a read-only transaction turns auto-commit off for the connection and gives the cursor its scope. JdbcTemplate can set the fetch size for a query, and a stream keeps the result open for as long as you read it.
@Transactional(readOnly = true)
public void exportOrders(Consumer<Order> sink) {
jdbc.setFetchSize(1_000);
jdbc.query("select id, customer_id, status, total from orders", rs -> {
sink.accept(new Order(rs.getLong("id"), rs.getInt("customer_id"), rs.getString("status"), rs.getBigDecimal("total")));
});
}With Spring Data JPA the equivalent is a Stream<Order> return type with a query hint, which must be consumed and closed inside the transaction:
@QueryHints(@QueryHint(name = "org.hibernate.fetchSize", value = "1000"))
@Query("select o from Order o")
Stream<Order> streamAll();try (Stream<Order> orders = repository.streamAll()) {
orders.forEach(o -> { writer.write(o); entityManager.detach(o); }); // detach, or the persistence context grows
}The detach call matters. Hibernate keeps every loaded entity in the persistence context, so streaming a million entities still fills memory unless you detach each one or clear the context periodically. For a pure export, a projection or JdbcTemplate avoids the entity machinery altogether.
Choosing the size
The fetch size is a trade between round trips and memory. A value of 1,000 means one network round trip per 1,000 rows. Larger values reduce round trips and use proportionally more memory per batch, and the gain flattens quickly because the cost per row dominates. Measure with three values, for instance 100, 1,000 and 10,000, on your data; the right choice is the smallest one after which the time stops improving. Rows with wide columns, such as JSON or text blobs, need smaller sizes than narrow rows.
Know the limits of streaming
Streaming holds a transaction and a connection for as long as you read. A slow consumer, for instance one that writes to a remote service per row, holds a pooled connection for minutes and risks the pool running dry. Read quickly into a bounded queue and process from the queue, or process in pages. Also check that the database's idle_in_transaction_session_timeout and any proxy or load balancer timeout is longer than your longest export, or the cursor disappears halfway.
Size the batch for the workload
Two numbers decide whether streaming helps: how many rows come back and how wide each row is. A million narrow rows at a fetch size of 1,000 cost 1,000 round trips and a few megabytes of heap, as the run above shows. A hundred thousand rows with a large text column can be heavier than the million, and a fetch size of 1,000 may then hold hundreds of megabytes at once. Estimate the batch memory as the fetch size times the average row size, and keep it under a few percent of the heap. If rows are variable, pick the fetch size for the worst realistic row, or select only the columns the export needs, because trimming a column often beats tuning the batch.
Where this does not apply
Small results do not need any of this. For writes, the matching tool is batching. The reference is the PostgreSQL driver page on cursors, and the MySQL Connector/J documentation covers streaming.


