skip to content

Design a memory-safe path to export millions of rows via a Spring Data JPA repository. Which mechanisms do you combine and why?

level: principalimportance: should knowfreq 25%

answer

  1. Stream + readOnly tx + close
  2. fetchSize hint (MySQL = Integer.MIN_VALUE)
  3. readOnly hint = no snapshots
  4. detach/clear or DTO projection to bound heap
  5. keyset paging as restartable alternative

basics

~10 s

Return a Stream<T>, consume it inside a read-only transaction, and close it with try-with-resources. Add @QueryHints for fetch size and readOnly, and periodically detach/clear entities so the persistence context and heap stay bounded.

solid answer

~40 s

The goal is constant memory regardless of row count. I return `Stream<T>` (a JDBC cursor, not a materialized `List`) and consume it inside a single `@Transactional(readOnly = true)` method so the connection stays open, closing the stream with try-with-resources. I attach `@QueryHints`: `org.hibernate.fetchSize` (e.g. 1000; on MySQL specifically `Integer.MIN_VALUE`) to control round-trip batching, and `org.hibernate.readOnly` so Hibernate keeps no dirty-check snapshots. Even then the persistence context accumulates entities, so I periodically `entityManager.detach(...)` or `clear()` to cap the heap. I keep the transaction long only for the read, stream straight to the output (CSV/HTTP), and avoid buffering. Alternatives I'd weigh: keyset (seek) pagination for restartability and shorter transactions, or a stored procedure with REF_CURSOR if the DB should own the query. Streaming wins when one long sequential pass is acceptable.

code

java · 27 lines
java
public interface OrderRepository extends JpaRepository<Order, Long> {
    @QueryHints({
        @QueryHint(name = "org.hibernate.fetchSize", value = "1000"),
        @QueryHint(name = "org.hibernate.readOnly", value = "true")
    })
    @Query("select o from Order o")
    Stream<Order> streamAll();
}

@Service
class OrderExportService {
    private final OrderRepository repo;
    private final EntityManager em;
    OrderExportService(OrderRepository repo, EntityManager em) { this.repo = repo; this.em = em; }

    @Transactional(readOnly = true) // spans the whole consumption
    public void exportCsv(Writer out) {
        long n = 0;
        try (Stream<Order> orders = repo.streamAll()) { // must close the cursor
            var it = orders.iterator();
            while (it.hasNext()) {
                writeCsvRow(out, it.next());
                if (++n % 1000 == 0) em.clear(); // keep persistence context bounded
            }
        }
    }
}

go deeper

for a junior

Understand that loading everything into a List can OOM; streaming reads lazily.

for a middle

Assemble Stream + readOnly tx + close + fetch-size/readOnly hints correctly.

for a senior

Add persistence-context management (detach/clear or DTO projection) and the MySQL fetch-size caveat.

for a principal

Trade streaming vs keyset paging vs REF_CURSOR vs Spring Batch on transaction duration, pool pressure, restartability, and testability.

## The failure mode to avoid A naive `List<T> findAll()` loads **every** row and every managed entity into memory before you write a byte — for millions of rows that's an `OutOfMemoryError`. The design target is **bounded memory** independent of result size. ## The core recipe (streaming) 1. **Return `Stream<T>`** — Spring Data backs it with a scrollable JDBC cursor, so rows are produced lazily instead of materialized. 2. **Open transaction over consumption** — the cursor/connection must stay open, so the *consuming* method is `@Transactional(readOnly = true)`. Read-only lets Hibernate skip snapshots and enables driver/DB read optimizations. The transaction must span the whole consumption, not just the repository call. 3. **Close the stream** — it holds a cursor + connection; use try-with-resources. `forEach` alone does not close it, so leaking here drains the connection pool. 4. **`@QueryHints`:** - `org.hibernate.fetchSize` — JDBC rows per round trip. Too small = chatty; too large = memory spikes. On **MySQL** true streaming needs `Integer.MIN_VALUE` (a driver quirk); a positive value there buffers the whole result — a classic silent OOM. - `org.hibernate.readOnly` — no dirty-check snapshots for streamed entities → less heap/CPU. 5. **Bound the persistence context** — every managed entity is still added to the `EntityManager`. Periodically `em.detach(entity)` (or `em.clear()` every N rows) so the first-level cache doesn't grow unbounded. Projections/DTOs (interface or class-based) avoid managed entities entirely and are often the cleanest fix. 6. **Stream to the sink** — write each row straight to the CSV/HTTP `OutputStream`/`Writer`; never collect into a `List` first, or you reintroduce the memory problem. ## Why each piece matters (interaction) Streaming without read-only + detach still OOMs on the persistence context. Read-only without fetch size still round-trips inefficiently. On MySQL, all of it fails silently unless fetch size is `Integer.MIN_VALUE`. The mechanisms are complementary, not alternatives. ## Alternatives and trade-offs - **Keyset / seek pagination** (`WHERE id > :last ORDER BY id LIMIT n`): each page is a short, independent transaction — restartable, pool-friendly, no long-held cursor. Better for very long or resumable exports; slightly more code. Preferred over offset pagination, which degrades on deep offsets. - **`Slice`/`Page` with offset**: simple but `OFFSET` gets slow at depth and `Page` adds a count query. - **Stored procedure + `REF_CURSOR`**: push the query into the DB (`@NamedStoredProcedureQuery`) when the DB should own it; costs portability and testability. - **Batch frameworks** (Spring Batch) for chunk-oriented, restartable, monitored jobs at scale. ## Operational concerns a principal weighs - **Long-running read transaction** can hold a connection and, on some DBs, MVCC snapshots for a long time — keyset paging avoids that. - **Connection-pool sizing**: a streaming export ties up one connection for its full duration. - **Timeouts / backpressure**: streaming to a slow HTTP client keeps the DB cursor open; consider server timeouts. - **Testability**: streaming/procedure code is harder to unit test than plain repository methods. ## Bottom line For a single sequential pass with acceptable transaction duration, `Stream<T>` + read-only tx + fetch-size/read-only hints + detach/clear (or DTO projection) gives constant-memory export. For resumability, very long runs, or pool pressure, prefer keyset pagination; for DB-owned logic, a REF_CURSOR procedure.

  • When would you choose keyset pagination over Stream<T> for a large export?
    When you need restartability, shorter transactions, or to avoid holding one connection/cursor (and DB snapshot) open for the whole run. Keyset (WHERE id > :last ORDER BY id LIMIT n) processes independent pages; streaming is a single long-lived cursor.
  • Why is a positive fetchSize dangerous on MySQL for streaming?
    The MySQL JDBC driver buffers the entire result set for positive fetch sizes; only Integer.MIN_VALUE triggers true row-by-row streaming. So a positive value silently reintroduces the OutOfMemoryError you were trying to avoid.
  • How do DTO projections help here?
    Interface/class-based projections return unmanaged objects, so nothing is added to the persistence context — no dirty-check snapshots and no need to detach/clear, cutting memory further than streaming entities.

saying these in an interview costs you the question

  • Collecting the stream into a List before writing (defeats the purpose)
  • Positive fetchSize on MySQL expecting streaming
  • No detach/clear, letting the persistence context grow unbounded
  • Transaction that ends before the stream is fully consumed
  • Using deep OFFSET pagination for millions of rows

context

resolving links…