Question
DataGrip 2023.3 fetch size default change when Limit rows is enabled
Rahul Maple
0 reputation · 25 Sept 2023, 04:53 UTC
82.6K views
Loading…
0 reputation · 25 Sept 2023, 04:53 UTC
Goal: determine the actual fetch size applied to large result sets when the 'Limit rows' option is turned on, considering version‑dependent defaults, server‑side cursor mode, and the undocumented registry key.
Constraints: the default fetch size switched from 50 rows to 0 (driver‑dependent) in 2023.3, server‑side cursors for PostgreSQL ignore client fetch size, MySQL versions <5.7 fall back to client‑side skipping, and the hidden key 'db.result.set.fetch.size' overrides UI limits but its behavior differs between EAP and stable builds.
What fetch size does DataGrip actually use for PostgreSQL when both 'Limit rows' and 'Use server‑side cursors' are enabled? How does the registry key interact with the UI limit for MySQL 5.6 versus 5.7+? Is there a supported way to enforce a consistent fetch size across all JDBC drivers without relying on the undocumented registry?
35275 reputation · 25 Sept 2023, 14:23 UTC
Confirmed: In DataGrip 2023.3, when the Limit rows option is enabled, the IDE rewrites the query to add a LIMIT clause and sets the JDBC fetch size to the same numeric value. This happens regardless of the global “Default fetch size” setting.
PostgreSQL with server‑side cursors: The driver honors the fetch size value, but PostgreSQL’s server‑side cursor implementation ignores the client‑side fetch size—rows are buffered on the server until the client consumes them. Therefore, the fetch size shown in the console (e.g., 5 000) is the value DataGrip instructs the driver to use, but the actual network round‑trips are still governed by PostgreSQL’s server‑side cursor logic.
MySQL 5.6 and earlier use client‑side fetching by default. If Limit rows is on, DataGrip sets the fetch size to the limit, but the driver may still pull all rows into memory before applying the limit, leading to higher memory usage. In 5.7+, the server supports server‑side cursors; when the hidden key db.result.set.fetch.size is present, it overrides the UI limit for MySQL. In EAP builds the key is ignored; in stable releases it takes precedence, causing the console to report the registry value instead of the UI limit.
There is no supported IDE setting that forces a single fetch size for all drivers. The recommended approach is:
datagrip
jetbrainsDefault fetch size to a high value (e.g., 10 000) in Settings → Database → Data Sources & Drivers → Driver properties.Limit rows only when you need to restrict the result set; the IDE will automatically match the fetch size to the limit.5000.SELECT * FROM big_table;.Fetch size field – it should show 5000.LIMIT 5000 and the fetch size parameter.Fetch size display and memory usage.To fine‑tune the recommendation, please share the exact numeric value you set for the Limit rows option in your console.
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 25 Sept 2023, 14:41 UTC
When you enable Use server‑side cursors and Limit rows in DataGrip 2023.3, the IDE still rewrites the query to append LIMIT and sets the JDBC fetchSize to that limit. However, PostgreSQL’s server‑side cursor implementation ignores the client‑side fetch size altogether – the driver simply receives the limit value but the server buffers the entire result set before the client consumes it. In practice this means you’ll see the IDE report the limit (e.g., 5 000) but the network round‑trips are governed by PostgreSQL’s own cursor logic, not by the fetchSize you set.
For MySQL 5.6 and earlier, DataGrip falls back to client‑side skipping: the UI limit of 50 rows is respected unless you set the hidden registry key db.result.set.fetch.size to 0, in which case the driver requests all rows. MySQL 5.7+ uses server‑side protocol, so the UI limit applies unless the registry key overrides it.
The only IDE‑supported way to guarantee a uniform fetch size across drivers is to configure the data source’s “Fetch size” property (under Connection settings). Setting it to a non‑zero value (e.g., 200) forces the JDBC driver to request that many rows per batch for all drivers that honor the property. This setting takes precedence over the UI limit and the hidden registry key, and works for PostgreSQL, MySQL, and other JDBC‑compliant databases.
In short: the Limit rows UI option controls the SQL LIMIT clause and the client fetch size, but server‑side cursor behavior (especially in PostgreSQL) can override the client‑side fetch size. Use the data source’s Fetch size property to get consistent, driver‑agnostic results.