Read Ten Million Database Rows Efficiently with Java

Loading ten million rows into a Java List is rarely safe. It can create a long transaction, exhaust heap memory, and overload the database. First decide whether the job is an online query, offline export, or migration, then choose streaming or partitioned processing.

Avoid deep OFFSET pagination

LIMIT offset, size becomes expensive when the offset is large because the database still scans or sorts skipped rows. Prefer keyset pagination based on a stable indexed column:

SELECT *
FROM source_table
WHERE id > ?
ORDER BY id
LIMIT 1000;

Store the last processed id and use it for the next batch.

Stream rows with a JDBC cursor

For MySQL Connector/J, enable cursor fetching and set a bounded fetch size:

jdbc:mysql://host:3306/test?useUnicode=true&characterEncoding=UTF-8&useCursorFetch=true&defaultFetchSize=1000

• useCursorFetch=true enables cursor-based retrieval.
• defaultFetchSize=1000 controls the number of rows fetched per round trip.

Keep autocommit and transaction settings compatible with the driver version. Process each row immediately instead of accumulating the complete result in memory.

Spring Batch

When using JdbcCursorItemReader, some drivers do not report cursor positions as Spring Batch expects. In that case:

reader.setVerifyCursorPosition(false);

Use chunk-oriented writes, checkpoints, idempotent consumers, and retry rules. Monitor database load, Redis throughput, failed records, and restart behavior before running the full migration.