A JDBC query over millions of rows OOMs the client before the first row is read. Why, and how do you stream it?

## Why the failure happens before the loop The mental model most developers carry is that `ResultSet` is a lazy stream and `next()` pulls one row from the server. For many drivers in their default configuration that is wrong: `executeQuery()` reads the complete result off the socket into client memory and returns a `ResultSet` that is a cursor over that in-memory buffer. This is a reasonable default — it frees the server-side resources immediately and makes the connection reusable — but it means the memory cost is proportional to the whole result, and the heap dump shows the failure inside `executeQuery`, not inside your row loop. The first diagnostic question is therefore not "how do I process rows more cheaply" but "is the driver materialising everything up front". A thread dump or profile taken during the failure usually answers it immediately. ## Fetch size is a hint, not a switch `Statement.setFetchSize(int)` and `ResultSet.setFetchSize(int)` tell the driver how many rows to fetch per round trip. The specification is explicit that this is a hint; a driver is free to ignore it. In practice each driver defines its own contract: - **PostgreSQL (pgjdbc):** a non-zero fetch size produces a server-side named cursor, but only if auto-commit is off and the `ResultSet` is `TYPE_FORWARD_ONLY`. The cursor lives inside the transaction, which is exactly why auto-commit must be disabled — with auto-commit on, the transaction would end when the statement completes and the cursor would vanish. - **MySQL (Connector/J):** ordinary positive fetch sizes do not change the default buffering. Row-by-row streaming is requested with the sentinel `setFetchSize(Integer.MIN_VALUE)` on a `TYPE_FORWARD_ONLY`, `CONCUR_READ_ONLY` statement. Alternatively the connection property `useCursorFetch=true` enables server-side cursors, and then a positive fetch size takes effect. - **Oracle:** honours `setFetchSize` directly as the array-fetch size, with a small default (10), so tuning it upward is a throughput lever rather than a memory-safety switch. Because the contracts differ, code that must run against more than one database either configures fetching per driver or avoids relying on streaming at all. ## What streaming costs A server-side cursor is not free. The connection is occupied for the entire read — you cannot issue another query on it, and with MySQL's row-by-row streaming the driver requires the `ResultSet` to be consumed or closed before the connection is usable again. In a pooled application that means one pool slot is held for the duration of a job that may run for minutes. The cursor also lives inside a transaction, so a long stream is a long-running transaction: it holds a snapshot open, which on MVCC engines prevents cleanup of superseded row versions, and it inflates the exposure window for lock contention. A streaming export that runs for an hour is a database operations problem, not just an application one. And the per-row work matters more than it looks. If the loop body makes a network call or writes to another system, the stream advances at the speed of that call while the cursor and connection stay open. Read into bounded batches and do the slow work outside the cursor's lifetime. ## The alternative: chunk the query Often the better answer is not to stream at all but to split the query. Order by the primary key and fetch a bounded page at a time, carrying the last key seen forward as the lower bound of the next query. Each query is short, opens and closes its own transaction, holds nothing between chunks, and can be retried or resumed after a failure without redoing everything. The cost is that the data may change between chunks, so the technique fits export and backfill jobs whose correctness tolerates that, better than it fits a point-in-time consistent snapshot — which is precisely where a single streaming cursor earns its cost. Offset-based paging is the trap here: skipping a growing offset makes the database read and discard more rows on every chunk, so the job gets slower the further it goes. ## A checklist Confirm the driver is buffering, rather than guessing. Select only the columns you need — result-set width multiplies whatever buffering exists. Set the fetch size *and* satisfy that driver's preconditions, then verify with a heap profile that memory is now flat. Keep the loop body cheap and free of remote calls. Bound the time a cursor stays open, and prefer key-range chunking for long-running jobs that can tolerate a moving target. Finally, remember the connection is pinned: size the job so it does not starve request-serving traffic of the connections it needs.