Spring Batch JdbcCursorItemReader读取PostgreSQL数据的内部机制及setFetchSize作用

Great question—this is a critical detail for avoiding memory bottlenecks when working with big datasets in Spring Batch, especially with PostgreSQL. Let’s break this down clearly:

1. Does JdbcCursorItemReader load all 1M records into the JVM at once? #

Absolutely not. This is a common misconception, but JdbcCursorItemReader relies on database-side cursors (via JDBC ResultSet) to fetch records incrementally, not in one massive load. Here’s how it works with your chunk size of 1000:

• When the job starts, the reader runs your query and opens a cursor on the PostgreSQL server. The full 1M-record result set stays on the database side initially, not in your JVM.
• For each chunk processing cycle:
  • Spring Batch reads 1000 records from the cursor (using the ResultSet to iterate forward)
  • Processes those 1000 records through your processor/writer
  • Only after the chunk is committed does it move to the next set of records from the cursor.

The chunk size controls how many records are processed in a single transaction/step cycle—not how many are fetched from the database in one go. That’s where setFetchSize comes into play.

2. What does setFetchSize() do? #

The setFetchSize() method configures the JDBC driver’s fetch size, which dictates how many records the driver pulls from the database server to your JVM in a single network round trip.

For PostgreSQL specifically:

• By default, the PostgreSQL JDBC driver uses a fetch size of 0, which means it pulls the entire result set into client memory immediately. This is dangerous for large datasets (like your 1M records) because it can trigger OutOfMemoryErrors.
• When you set a fetch size (e.g., setFetchSize(1000)), the driver switches to using a server-side cursor and only pulls that number of records at a time. With a fetch size of 1000 and chunk size of 1000, the driver fetches 1000 records to your JVM, Spring Batch processes them, then the driver fetches the next 1000, and so on.

Quick Best Practices: #

• Align your fetch size with your chunk size (or a multiple of it) to minimize network overhead and memory usage.
• Never leave the fetch size at the default 0 for large datasets—this is one of the most common pitfalls with JdbcCursorItemReader.
• The reader automatically disables auto-commit for the connection, which is required for PostgreSQL’s server-side cursors to work properly.

内容的提问来源于stack exchange，提问作者Anant Mishra