WebStorm DataGrip page-size limit versus PostgreSQL fetch-size streaming

Question

WebStorm's bundled Database Tools (DataGrip engine) presents query results in a table grid bounded by a per-data-source page-size setting under Database Tools | Data Views, commonly defaulting to approximately 500 rows. Grid pagination functions as a client-side window over fetched rows; the IDE does not automatically inject LIMIT/OFFSET into the issued SELECT statement, so explicit server-side pagination predicates remain the developer's responsibility for bounding work on the database server. A per-data-source JDBC fetch size controls how many rows the driver pulls per round trip; for PostgreSQL, cursor-based streaming is only effective with autocommit disabled and a positive fetch size, otherwise the driver may buffer the entire result set in client memory. The unresolved behavior concerns whether navigating to the next page re-executes the original query or continues fetching from an open cursor, an outcome dependent on driver implementation and IDE cursor state. This question matters for profiling large-dataset queries and deciding whether to rely on IDE pagination or implement application-level keyset pagination.

Does grid pagination re-issue the SELECT statement, or does it advance an existing cursor? How does the page-size setting interact with the JDBC fetch size when autocommit is enabled? Can the observed paging behavior be predicted from driver and IDE configuration alone?

Tasadduq Burney · Answer

Direct answer
Grid pagination in WebStorm’s bundled Database Tools/DataGrip engine is normally a client-side grid window: opening the next page commonly re-issues the query with new LIMIT/OFFSET bounds rather than advancing one open JDBC cursor. That is the likely behavior, not a guarantee. It depends on the IDE build, data source, transaction mode, and JDBC driver. The page-size setting does not append LIMIT/OFFSET to a raw SELECT run in a console.
Confirmed facts and likely explanation
Confirmed: the per-data-source page size limits rows materialized in the IDE grid per page. The JDBC fetch size controls how many rows the driver requests per network round trip for an executed statement. They are separate settings.
Likely explanation: DataGrip fetches the first page, closes or releases the statement/transaction, then executes a new bounded query for the next page. If it instead keeps a forward-only cursor open with autocommit disabled and a positive fetch size, page navigation could continue fetching from that cursor. You cannot tell which one is happening from the two settings alone.
SettingControlsPostgreSQL autocommit on
Data editor page sizeRows shown per IDE grid pageClient window only; does not bound the SQL unless the IDE adds LIMIT/OFFSET
JDBC fetch sizeRows per driver round trip for one executed statementWith autocommit on, PostgreSQL JDBC often completes the portal and may transfer the whole result set; fetch size alone is not reliable streaming
AutocommitTransaction boundary for statement executionOn: cursor streaming generally not effective. Off: required but not sufficient; the IDE must keep the statement/transaction open

Steps for this case

Record the Database Tools | Data Views page size and the data source advanced JDBC fetch size. Do not assume they are the same limit.
Observe actual paging before changing settings. Enable the data-source or console SQL log in the IDE, open a table, and move to the next page. If the log shows the same SELECT with new LIMIT/OFFSET values, the IDE is re-executing. If you have server privileges, enable PostgreSQL statement logging for your session, for example SET log_min_duration_statement = 0;, and inspect pg_stat_activity during page navigation.
For large-dataset profiling, do not rely on IDE pagination to bound server work. Add explicit LIMIT/OFFSET for ad-hoc checks, or use keyset pagination in application code, for example WHERE id > :last_id ORDER BY id LIMIT :page_size. Repeated OFFSET paging becomes more expensive as offset grows.

Missing diagnostic detail
Does WebStorm/DataGrip keep the same JDBC statement and transaction open for the data editor between page turns? If yes, with autocommit off and a positive fetch size, continued cursor fetch is plausible. If no, or if autocommit is on, expect re-execution or full result-set buffering. This detail changes whether fetch size can matter across pages.
Assumptions and uncertainty
Assumes recent WebStorm bundled Database Tools/DataGrip engine behavior and PostgreSQL JDBC driver behavior; menu labels and defaults vary by version. Cursor reuse across IDE pages is version-dependent. A positive fetch size with autocommit enabled is not sufficient for PostgreSQL cursor streaming. Verify with logs rather than predicting from configuration alone.

WebStorm DataGrip page-size limit versus PostgreSQL fetch-size streaming

1 answer

Direct answer

Confirmed facts and likely explanation

Steps for this case

Missing diagnostic detail

Assumptions and uncertainty

Your perspective belongs here.

0 question comments

Setting	Controls	PostgreSQL autocommit on
Data editor page size	Rows shown per IDE grid page	Client window only; does not bound the SQL unless the IDE adds LIMIT/OFFSET
JDBC fetch size	Rows per driver round trip for one executed statement	With autocommit on, PostgreSQL JDBC often completes the portal and may transfer the whole result set; fetch size alone is not reliable streaming
Autocommit	Transaction boundary for statement execution	On: cursor streaming generally not effective. Off: required but not sufficient; the IDE must keep the statement/transaction open