Database Tool Window pagination behavior for large result sets

Question

PhpStorm manages the retrieval of large datasets through the Database tool window by implementing a fetch limit, controlled by the Page Size setting. This mechanism prevents JVM memory exhaustion by bounding the initial query result set.

When working with datasets that exceed the global page size, the IDE allows for manual increases or the use of the 'Fetch All' command. However, the efficiency of this process depends on whether the underlying driver utilizes server-side cursors or relies on client-side buffering.

There is uncertainty regarding the memory impact when transitioning from paginated views to a full dataset fetch for extremely large tables.

Does the IDE implement an internal threshold to warn users before a 'Fetch All' operation triggers a heap memory overflow?
  How does the pagination behavior differ between drivers that support server-side cursors versus those requiring client-side buffering?

Wasi · Answer

Direct Answers
No internal warning threshold exists. PhpStorm does not display a heuristic alert before a Fetch All operation. Memory safety depends entirely on the configured JVM heap (-Xmx) and the JDBC driver's fetchSize behavior.
Pagination differs by driver cursor support. Drivers with server-side cursors (PostgreSQL, SQL Server, Oracle) keep a database cursor open and stream rows on demand. Drivers without server-side cursor support (older MySQL Connector/J, some ODBC bridges) materialize the entire result set in the client JVM before the IDE can page through it.

Confirmed Behavior

Page Size (Data Source → Options → Result set page size) caps the initial JDBC fetchSize. The toolbar shows the current page and total rows fetched so far.
  Fetch All issues an unbounded fetchSize (or iterates to the end of the cursor), loading every row into the Swing table model backing the tool window.
  Server-side cursor drivers send FETCH n batches; the IDE only holds the current page plus a small prefetch buffer.
  Client-side buffering drivers return a fully materialized ResultSet; the JVM heap must accommodate every column of every row simultaneously.

Verification Steps

Open Data Sources and Drivers → select the connection → Options tab → note Result set page size.
  Run a query against a table with >100k rows. Observe the toolbar page counter (e.g., "Rows 1–500 of 500").
  Click Fetch All (toolbar icon or Ctrl+Alt+F). Watch the row count climb to the true total.
  Monitor JVM heap with jcmd  GC.heap_info or VisualVM during step 3. A sharp rise indicates client-side buffering.
  Repeat with a PostgreSQL connection using the same table (or a comparable volume). Heap growth should be incremental.

Practical Guidance

Keep Page Size at 500–2000 for interactive work; larger values increase per-page memory without improving latency.
  For exports, use Dump Data → To File (CSV/JSON/SQL) instead of Fetch All → copy/paste. The dumper streams via the driver and avoids the Swing table model entirely.
  If you must inspect millions of rows in the grid, increase -Xmx in phpstorm.vmoptions (e.g., -Xmx4g) and prefer a server-side cursor driver.

One Diagnostic Detail Needed
Which JDBC driver (vendor + version) and database product/version are you using? The cursor behavior is driver-specific, and some MySQL/PostgreSQL driver versions toggle server-side cursors via connection properties (e.g., useCursorFetch=true for MySQL Connector/J 8.0+).

Database Tool Window pagination behavior for large result sets

1 answer

Direct Answers

Confirmed Behavior

Verification Steps

Practical Guidance

One Diagnostic Detail Needed

Your perspective belongs here.

0 question comments