Tuning DataGrip result‑set pagination and fetch size for large MySQL/PostgreSQL tables

Keep the page‑size limit on and tune the driver fetch size

When browsing a table that may contain millions of rows, leave DataGrip’s Limit page size to option enabled (the default is roughly 500 rows). This forces the IDE to request only a page of data at a time instead of trying to buffer the whole result set in memory. If page loads feel slow or you need a steady stream of rows, adjust the JDBC fetch size for the data source so the driver can satisfy each page with a bounded network round‑trip.

How DataGrip pages results

With paging enabled, the results grid shows one page. When you scroll to the next page, DataGrip either:

• re‑executes the original query with a LIMIT/OFFSET‑style clause (MySQL, MariaDB), or
• advances a server‑side cursor using the JDBC fetch size (PostgreSQL, Oracle, SQL Server when the driver supports it).

In either case the IDE never silently loads the entire set into the grid; only the rows needed for the visible page are held in memory.

Worked example: MySQL with server‑side cursors

To make MySQL Connector/J use a cursor that honors the fetch size, add useCursorFetch=true to the JDBC URL and set a positive fetch size.

jdbc:mysql://host:3306/mydb?useCursorFetch=true&defaultFetchSize=500

With the page size still set to 500 rows, each time DataGrip requests the next page the driver issues a fetch that returns up to 500 rows from the open cursor, keeping memory usage flat even on tables with tens of millions of rows.

Worked example: PostgreSQL with pgJDBC

For PostgreSQL, set the fetch size either in the data source’s Advanced tab or as a URL parameter:

jdbc:postgresql://host:5432/mydb?defaultRowFetchSize=500

Note that pgJDBC only performs cursor‑based fetching when autocommit is disabled. In DataGrip you can toggle autocommit from the console toolbar; if it remains on, the driver will fetch the whole result set despite the fetch‑size setting.

Limits of the paging feature

• The setting only affects the data grid. Aggregates (COUNT, SUM), ORDER BY without a unique tie‑breaker, and export operations may still process the full result set or a different subset.
• When using MySQL streaming (useCursorFetch=true) the connection stays occupied until the result set is closed, which can block other statements on the same physical connection.
• Exported data may reflect only the currently loaded page in some DataGrip versions; always verify with a small table before exporting a large one.

Common mistakes to avoid

1. Disabling Limit page size to to “see all rows” – this can cause the IDE to attempt to load the entire result set into memory, leading to OutOfMemory errors.
2. Assuming that page N+1 continues exactly where page N left off when the query lacks a deterministic ORDER BY. Without a stable ordering the driver may return different rows on each fetch.
3. Editing cells in a paged grid when the table has no detectable primary key. DataGrip cannot generate a reliable UPDATE statement, so edits may be disabled or may affect unexpected rows if the underlying data changes concurrently.

Practical decision for bulk extracts

If you need to pull the whole table for backup or analysis, prefer DataGrip’s Export wizard or a database‑specific command (mysqldump, pg_dump, COPY TO) rather than paging through the grid. The grid is optimized for interactive browsing and inline edits, not for moving large volumes of data.

Verification steps

• Open Settings → Database → Data Views (or search for “page size”) to confirm the current limit.
• Run a query on a table larger than the page size, then open idea.log or the IDE’s sql.log and look for queries that contain LIMIT/OFFSET or for cursor‑related messages.
• Compare the IDE’s memory usage (View → Memory Indicator) with the limit on versus off while scrolling through a large table.
• Consult the driver documentation: MySQL Connector/J useCursorFetch and defaultFetchSize, pgJDBC defaultRowFetchSize and the autocommit requirement.