In this lesson, you will learn how to balance memory overhead and network throughput by fine-tuning how your database driver buffers incoming result sets.
The Buffering Dilemma
When you execute a query that returns millions of rows, your database driver must decide how to handle that data before handing it to your application. By default, many drivers use eager buffering (or "fetch-all"), where the driver attempts to pull the entire result set into the application's RAM immediately.
While this makes the data available for rapid processing, it creates a massive memory footprint that can trigger OutOfMemory errors or crash your container during high-volume retrieval. Conversely, streaming (or "cursor-based") buffering fetches rows in small batches, keeping memory usage stable but increasing the overhead of multiple network round-trips.
Controlling the Flow
To manage this, you must configure the fetch size—the number of rows the driver retrieves in a single network round-trip. Think of it as a sliding scale between "fast access/high memory" and "slow access/stable memory."
For example, in many JDBC-based environments, you control this via the statement object:
// Setting the fetch size to 100 rows per batch
statement.setFetchSize(100);
ResultSet rs = statement.executeQuery("SELECT * FROM massive_log_table");
By setting a smaller fetchSize, you force the driver to act as a buffer gatekeeper. It keeps only 100 rows in memory at a time. As your code iterates through the ResultSet, the driver periodically transparently fetches the next batch from the database.
When to Adjust
- Use small buffers (low fetch size) when processing large exports, data migrations, or analytical tasks where memory is constrained. This ensures the application remains responsive and stable.
- Use large buffers (or the default) when your result sets are small, or when you are performing latency-sensitive operations where the cost of network round-trips outweighs the cost of RAM.
Monitoring your application's heap usage during data retrieval is the best way to determine the optimal balance for your specific data volumes.
Key Takeaway: Tuning the driver’s fetch size is your primary mechanism for preventing memory exhaustion, allowing you to process infinite result sets by streaming them through a small, manageable memory buffer.