Optimizing DataGrip Memory Usage with Server-Side Pagination

The Problem: IDE Memory Exhaustion with Large Result Sets

When querying large datasets—particularly in Hibernate-backed environments where schemas can be complex—loading millions of rows into the DataGrip Data Editor can lead to OutOfMemoryError in the IDE's JVM. The goal is to shift the burden of data filtering from the client (DataGrip) to the database server using server-side pagination.

Prerequisites

• DataGrip 2023.3 or later.
• A configured connection to a supported database (e.g., PostgreSQL, MySQL, Oracle, SQL Server).
• The Memory Indicator enabled (View → Appearance → Show Memory Indicator) to verify the impact of changes.

Configuring Result Set Limits

DataGrip provides three primary mechanisms to control how much data is transferred from the server to the IDE. To optimize memory, apply these in the following order:

1. Adjusting the Data Editor Page Size

The Page Size determines how many rows are displayed in the grid at once and how many are requested per page navigation action.

1. Open a table or execute a query to open the Data Editor.
2. In the toolbar above the results grid, locate the Page Size dropdown (often defaulting to 500 or "All").
3. Select a specific limit (e.g., 500). Avoid selecting "All" for tables with unknown or massive row counts, as this forces the IDE to attempt to load the entire set.

2. Tuning the JDBC Fetch Size

While Page Size controls the UI, the Fetch Size controls the JDBC driver's behavior—specifically, how many rows are retrieved in a single network round-trip.

1. Right-click your data source in the Database tool window and select Properties.
2. Navigate to the Options tab.
3. Locate the Fetch size field. Set this to a value that aligns with your Page Size (e.g., 1000).
4. Click OK. This prevents the driver from attempting to buffer too many rows in the local JVM memory before handing them to the editor.

3. Implementing Manual Server-Side Constraints

For ad-hoc queries, relying on IDE settings is less reliable than explicit SQL constraints. This ensures the database engine performs the truncation before sending any data over the wire.

Example Configurations by Dialect:

Database	SQL Syntax Example	Execution Location
PostgreSQL / MySQL	SELECT * FROM users LIMIT 500 OFFSET 0;	Query Console
Oracle (12c+)	SELECT * FROM users FETCH FIRST 500 ROWS ONLY;	Query Console
SQL Server	SELECT TOP 500 * FROM users;	Query Console

Verifying Server-Side Execution

To ensure that DataGrip is not simply filtering a large result set in-memory, you must inspect the actual SQL sent to the server.

1. Open the Services tool window (View → Tool Windows → Services).
2. Select the active connection and view the Output or Log tab.
3. Verify that the executed statement contains the LIMIT, TOP, or FETCH clause. If the log shows a plain SELECT * but the grid only shows 500 rows, the IDE may be handling the limit locally, which is less efficient for memory.
4. Observe the row count indicator in the bottom right of the Data Editor; it should display 500 of ? rows, indicating that the total count is unknown or fetched incrementally.

Limitations and Risks

• BLOBs/CLOBs: Row limits do not limit the size of individual cells. Fetching a few rows containing massive binary objects can still trigger memory issues.
• Conflict: Manually adding a LIMIT clause while having a strict Page Size setting may result in the IDE applying a second limit to the already limited result set.

Rollback Procedure

If the IDE becomes unresponsive or data is missing from your view:

• Reset the Page Size dropdown to All in the Data Editor.
• Clear the Fetch size value in Data Source Properties to return to the JDBC driver's default.
• Remove manual LIMIT/OFFSET clauses from the SQL console.