Which Page Size setting prevents JVM heap exhaustion for massive result sets?

Question

When executing queries against tables with millions of rows in PhpStorm, the IDE manages memory by implementing a client-side boundary on the retrieved data. This is primarily controlled through the Page Size configuration within the Data Source properties.

While this mechanism prevents the IDE from attempting to load the entire dataset into memory at once, the actual behavior depends on the interaction between the JDBC driver and the IDE's internal result set viewer. There is a potential conflict between the SQL LIMIT clause and the IDE's internal pagination settings when optimizing for JVM heap stability.

Does the Page Size setting strictly override the SQL LIMIT clause during the initial fetch, or do they operate as independent filters? Additionally, how does the IDE handle memory allocation when the Page Size is set higher than the available JVM heap space?

Tasadduq Burney · Answer

Answer
The ‘Limit page size to’ option in PhpStorm’s Data Source → Options does **not** override a SQL LIMIT clause. The two mechanisms work independently: LIMIT restricts the total rows returned by the query, while Page Size only controls how many rows are fetched per JDBC round‑trip.
How to choose a safe Page Size

Determine the IDE’s maximum heap size (the -Xmx value shown in Help → About or via vmoptions).
Estimate the average size of a row in bytes (you can approximate by examining a few rows or checking column types).
Pick a Page Size so that average_row_size × Page Size stays comfortably below a safe fraction of the heap – e.g., ≤25 % of -Xmx. For typical row sizes of a few hundred bytes, values between 500 and 2000 rows are often safe.
In PhpStorm, open Preferences → Data Sources, select your source, go to the Options tab, and set Limit page size to to the calculated value.
Run a query that returns a large result set and watch the memory indicator in the IDE status bar; it should remain well below the heap limit.

What happens if Page Size is too large?
If the Page Size exceeds the available heap, the IDE will try to allocate memory for that many rows at once, which can trigger an OutOfMemoryError or cause the IDE to become unresponsive. Lowering the Page Size reduces the per‑fetch allocation and lets the memory indicator stay stable.
Missing diagnostic detail needed for a precise recommendation: What is the approximate average row size (in bytes) of the result set you are querying?

Which Page Size setting prevents JVM heap exhaustion for massive result sets?

1 answer

Answer

How to choose a safe Page Size

What happens if Page Size is too large?

Your perspective belongs here.

0 question comments