DBeaver Fetch Size: Optimizing Large Result Set Pagination

Decision Guide: Adjusting DBeaver Fetch Size for Large Result Set Pagination

When working with large result sets in DBeaver, the JDBC fetch size property controls how many rows are retrieved per round trip between the DBeaver client and the database server. This setting directly influences two competing factors: the number of network round trips required to fetch the data, and the amount of client memory consumed while the result set is held in memory. Understanding this trade-off is essential for performance-tuned pagination, especially when dealing with tables containing millions of rows or when DBeaver is used in resource-constrained environments.

Constraints and Decision Context

Before adjusting the fetch size, evaluate the primary constraints that shape the decision. Client available memory is the most immediate concern; setting the fetch size too high can cause the Java Virtual Machine to exceed its heap limit and terminate with an OutOfMemoryError, particularly when multiple large result sets are open simultaneously. Network latency between the client and the database is the second factor: each round trip incurs overhead, and a larger fetch size reduces the total number of trips required to retrieve the same number of rows. The JDBC driver in use also matters, as default fetch sizes are driver-dependent and some drivers ignore the property for certain result set types, such as scrollable cursors. Finally, consider the result set type being processed; the fetch size property most reliably affects TYPE_FORWARD_ONLY result sets, while TYPE_SCROLL_INSENSITIVE or holdable cursors may behave differently.

Supported Configuration Options

Each option serves a distinct workflow. The UI property is useful for exploratory queries where a temporary change suffices. The connection URL parameter suits recurring pipelines or shared data sources, because the setting persists across DBeaver sessions once the URL is updated. The driver default is best left unchanged unless a specific performance need arises, as modifying it without understanding driver behavior can introduce unexpected results.

Concrete Implementation: Setting Fetch Size via Connection URL

To set the fetch size persistently for a data source, append the fetchSize parameter to the JDBC connection URL that DBeaver uses. Most JDBC drivers bundled with DBeaver (PostgreSQL MySQL MariaDB Oracle SQL Server and others) accept this parameter in standard URL syntax. For example a PostgreSQL URL with a fetch size of five thousand rows appears as follows:

jdbc:postgresql://dbhost:5432/mydb?fetchSize=5000

After updating the URL open DBeaver navigate to the data source click Edit and reconnect. DBeaver reads the new parameter on the next connection handshake and applies the specified fetch size for all subsequent queries on that connection.

Verification: Checking the Effect of Fetch Size

1. Set the fetch size to a low value such as 100 and execute a query that returns several thousand rows for instance SELECT * FROM large_table LIMIT 5000. Observe the elapsed time recorded in the DBeaver query console and note any memory warnings that appear during or after execution.
2. Increase the fetch size to a higher value such as 5000 and run the identical query. Compare the total execution time; in many cases the higher fetch size reduces the number of round trips and therefore shortens the overall duration. Monitor client memory usage via the operating system process monitor or DBeaver built-in memory indicators expect a proportional increase in resident memory while the result set is being processed.
3. Repeat the test with an even higher fetch size such as 20000 and verify whether the performance gain continues or whether client memory pressure becomes significant. If an OutOfMemoryError appears reduce the fetch size and find a balanced value.

In DBeaver the query execution log displays the number of round trips performed. A practical check is to confirm that increasing the fetch size reduces this count without triggering memory errors. Additionally the Window Preferences General Memory Analyzer path can provide a snapshot of heap usage if detailed analysis is required.

Limitations and Practical Considerations

• Driver compatibility: Not all JDBC drivers honor the fetchSize property identically. Some drivers ignore it for specific result set types or cursor implementations and the setting may have no effect depending on the underlying database protocol.
• Result set type: The property most reliably affects TYPE_FORWARD_ONLY result sets. Scrollable or holdable cursors may handle fetch size differently and in some cases the property is simply ignored.
• Memory pressure: Setting the fetch size too high generally above 100000 rows for most client configurations can cause the Java process to exceed its heap limit especially when multiple large result sets are open concurrently.
• Large object handling: Fetch size does not apply to BLOB or CLOB data. Those are streamed separately by the driver and adjusting the fetch size has no impact on their transfer size.

For most workloads a fetch size between 500 and 10000 rows provides a balanced starting point. Adjust upward if network latency dominates and client memory is ample; adjust downward if the client is memory-constrained or you are processing many concurrent result sets.