What is the JDBC fetch size, how do you set it for a Hibernate query, and why can it be a hard requirement rather than a tuning knob when streaming a very large result set?
answer
- Rows per network round trip
- hibernate.jdbc.fetch_size / query.setFetchSize
- PostgreSQL: autocommit off + forward-only + size > 0
- MySQL: Integer.MIN_VALUE or useCursorFetch
- Not the same as @BatchSize or fetch strategy
basics
~20 sFetch size is how many rows the JDBC driver pulls from the server per round trip. Hibernate sets it globally with hibernate.jdbc.fetch_size or per query. Some drivers buffer the entire result unless a fetch size (plus driver-specific conditions) makes them open a server-side cursor.
solid answer
~50 sThe JDBC fetch size is a hint to the driver about how many rows to transfer per network round trip while reading a `ResultSet`. In Hibernate you set it globally with the `hibernate.jdbc.fetch_size` property, or per query with `query.setFetchSize(n)` / the `org.hibernate.fetchSize` hint. As a tuning knob it trades round trips against client buffer memory: a default of a handful of rows makes a million-row read chatty, while a very large value wastes memory for little gain. A few hundred is a sane starting point. It becomes a **correctness requirement** for streaming because some drivers materialize the whole result set client-side unless told otherwise. PostgreSQL only uses a server-side cursor when autocommit is off, the result set is forward-only, and fetch size is greater than zero. MySQL Connector/J buffers everything unless you use its streaming mode or cursor-fetch option. Without that, `getResultStream()` is lazy over an already fully buffered result and the heap still blows up. Note it is unrelated to Hibernate's `@BatchSize` or fetch *strategies*.
code
java · 9 linesQuery<Order> q = session.createQuery("from Order", Order.class);
q.setFetchSize(500);
q.setReadOnly(true);
try (ScrollableResults<Order> rows = q.scroll(ScrollMode.FORWARD_ONLY)) {
while (rows.next()) {
export(rows.get());
}
}go deeper
Say what it is — rows pulled per round trip — and that Hibernate exposes it globally and per query.
Add the round-trip-versus-buffer trade, sensible values, and that on some drivers a fetch size is what enables a server-side cursor at all.
Name the concrete driver conditions you rely on, explain how you verify streaming empirically, and connect it to the transaction and connection being pinned for the duration.
Weigh streaming against chunked reads at the system level: fetch size buys throughput but the enabling conditions hold a transaction and a connection open, which shapes pool sizing and job restartability.
## What the fetch size actually controls `java.sql.Statement.setFetchSize(int)` is a hint to the JDBC driver about how many rows it should retrieve from the database server in one go while you iterate a `ResultSet`. It sits at the transport layer: it does not change the SQL, the number of rows returned, or the semantics of the query. It changes how many network round trips happen and how much data the driver holds in client memory at once. The trade is simple. A small fetch size means many round trips — with a default of 10 rows, reading a million rows is 100,000 round trips, and on a link with even 1 ms latency that is 100 seconds of pure waiting. A very large fetch size means fewer round trips but more client-side buffer, and beyond a few hundred to a few thousand rows the returns flatten sharply. Typical guidance for bulk reads is a few hundred to about a thousand. ## Setting it in Hibernate - **Globally**: the configuration property `hibernate.jdbc.fetch_size`. Applied to every statement Hibernate prepares. - **Per query**: `query.setFetchSize(500)` on the Hibernate `Query`, or the JPA-style hint `setHint("org.hibernate.fetchSize", 500)`. Per query is usually the right granularity: your OLTP queries return a handful of rows and gain nothing from a large fetch size, while your one export job wants it raised. ## Why it becomes a hard requirement The JDBC spec calls fetch size a hint, and drivers are free to ignore it — including by fetching *everything*. Two widely used drivers behave in ways that make this decisive: **PostgreSQL.** By default the driver reads the complete result set into client memory before `executeQuery()` returns. It only switches to a server-side cursor (`DECLARE`/`FETCH`) when three conditions all hold: autocommit is off (i.e. you are inside a real transaction), the result set is `TYPE_FORWARD_ONLY`, and the fetch size is greater than zero. Miss any one and your "stream" is a lazy iterator over a fully materialized array — the OutOfMemoryError arrives before your first callback runs. **MySQL (Connector/J).** Also buffers the entire result by default. It streams row-by-row only when the fetch size is set to `Integer.MIN_VALUE` with a forward-only, read-only result set — and while that stream is open, the connection cannot be used for any other statement until the result is fully consumed or the statement is closed. The alternative is the `useCursorFetch=true` connection option combined with a positive fetch size, which uses a server-side cursor instead. **Oracle** goes the other way: it does use a cursor by default, but with a conservative default fetch size (historically 10), so bulk reads are latency-bound until you raise it. The practical rule: fetch size is a *tuning* parameter on some databases and an *enabling* parameter on others. If you are writing a streaming read, verify against your driver's documentation which regime you are in, and confirm empirically — watch heap or connection state on a large result, not just the code. ## Interactions to keep straight - **Fetch size is not Hibernate's fetch strategy.** `FetchType.LAZY`/`EAGER`, `join fetch`, and `@BatchSize` decide *which objects and associations* Hibernate loads and with how many SQL statements. Fetch size decides how rows of one already-issued statement move across the wire. Confusing the two is a classic interview stumble. - **Fetch size does not bound Hibernate's memory.** Even with a perfect server-side cursor, every entity you pull is registered in the persistence context. Constant-memory streaming needs fetch size *and* a read-only load *and* a periodic `session.clear()`. - **Cursors live in the transaction.** Because the streaming regime typically requires autocommit off, the transaction stays open for the entire read. That is a real operational cost for very long jobs — a connection is pinned and the read snapshot is held open for its duration. - **One active streaming result per connection.** With row-by-row streaming on some drivers you cannot interleave other statements on the same connection. If your consumer needs to write rows back as it reads, use a second connection/session for the writes, or chunk the read instead. ## How to demonstrate it A good answer names a concrete experiment: run the export against a table with millions of rows, first without a fetch size, then with one; watch heap usage and time-to-first-row. If the first row arrives only after a long pause and heap spikes to the size of the table, the driver buffered and your fetch size never took effect.
- Is a bigger fetch size always better?No. Beyond a few hundred to a few thousand rows the reduction in round trips flattens while client-side buffer memory keeps growing, and a huge fetch size on a wide row can itself cause memory pressure. It also delays time-to-first-row. Set it per query for bulk reads and leave short OLTP queries alone.
- How is JDBC fetch size different from Hibernate's @BatchSize annotation?They operate at different layers. `@BatchSize` is a Hibernate fetch strategy: when initializing lazy proxies or collections, it groups N of them into one SQL statement with an IN list, reducing statement count. JDBC fetch size does not affect how many statements are issued at all; it controls how many rows of a single result set the driver pulls per round trip.
Fetch size is the size of the bucket you carry to the well. A tiny bucket means endless trips; a huge bucket strains your arms. And some wells hand you the whole reservoir unless you show up with a bucket at all.
saying these in an interview costs you the question
- Treating fetch size as purely cosmetic tuning and being surprised when a stream OOMs on PostgreSQL.
- Confusing JDBC fetch size with Hibernate's @BatchSize or with FetchType/join fetch.
- Setting an enormous fetch size (e.g. 100,000) and calling it streaming.
- Believing fetch size alone gives constant memory, forgetting the persistence context still retains entities.
- Assuming every driver honours the hint identically.