Choosing the Right Pagination Strategy in DBeaver for Massive Result Sets

When a Query Returns Millions of Rows, DBeaver Can Freeze or OOM

Analytical workloads that return millions of rows often hit two hard limits in a desktop client: the Java heap used by DBeaver and the number of round‑trips required to fetch data from the database. If you run a query like SELECT * FROM sales ORDER BY sale_date on a table with 10 million rows, you’ll see the editor either become unresponsive or throw an OutOfMemoryError. The question is: how do you tell DBeaver to fetch data in a way that keeps the UI responsive and the client memory bounded?

Decision: Pick a Pagination / Fetch Strategy

There are three supported ways to control how DBeaver pulls data:

• Fetch on Demand (Editor Preference) – the editor loads rows page by page as the user scrolls.
• JDBC fetchSize (Connection Property) – the driver streams rows in a buffer, reducing the number of round‑trips.
• Manual LIMIT/OFFSET (SQL) – you add pagination logic to the query itself.

Each option has constraints: client memory, network latency, and database cursor support. The following table summarizes the trade‑offs.

Strategy	Pros	Cons	Typical Use‑Case
Fetch on Demand	Minimal memory, UI stays responsive, works with any JDBC driver.	Increases round‑trips; server cursor may stay open, possibly holding locks or temp resources.	Interactive reporting where the user scrolls gradually.
JDBC fetchSize	Streaming buffer reduces round‑trips; lower latency for large result sets.	Driver may ignore the hint; some databases need server‑side cursor support.	Batch analytics where you need to process all rows quickly.
LIMIT/OFFSET	Portable SQL; no driver quirks.	Deep offsets are expensive; the database must scan rows up to the offset; can be slow on very large tables.	When you need a specific page (e.g., page 10 of 50) and can afford the cost.

Practical Constraints to Consider

• Client Heap – DBeaver’s default maximum heap is 1 GB. For 10 million rows of 200 bytes each, that’s 2 GB, which will exceed the limit.
• Network Latency – On a 100 ms round‑trip, fetching 10 k rows per request can add noticeable delay.
• Cursor Support – MySQL, PostgreSQL, and SQL Server support server‑side cursors; some embedded drivers do not.

Concrete Implementation in DBeaver

1. Enable Fetch on Demand (Editor Preference)

1. Open Window > Preferences (Windows/Linux) or DBeaver > Preferences (macOS).
2. Navigate to Database > Result Sets.
3. Check Fetch rows on demand and set Maximum rows to fetch (e.g., 10 000).
4. Apply and close.

Result: As you scroll, DBeaver will issue SELECT ... LIMIT 10000 OFFSET 10000 style requests automatically.

2. Configure JDBC fetchSize (Connection Property)

1. Open a connection editor: Data Source Properties > Driver Properties.
2. Add a new property defaultFetchSize (key) with a numeric value (e.g., 5000).
3. Check Use server-side cursor if the driver supports it.
4. Save the connection and reconnect.

When you run a query, the driver will stream 5 000 rows at a time, keeping the client heap low and reducing the number of round‑trips.

3. Use LIMIT/OFFSET in the SQL Editor

SELECT * FROM sales
ORDER BY sale_date
LIMIT 10000 OFFSET 20000;

Replace the numbers to navigate pages. This approach is portable but can be slow for large offsets.

Validating the Strategy

• Memory Check – Open Help > Show Log or use a system monitor to watch the Java heap while scrolling. With Fetch on Demand, the heap should stay under the set maximum.
• Round‑Trip Count – Enable the Show SQL console (View > SQL Console) and watch the number of SELECT statements issued. Fetch on Demand should show multiple statements; JDBC fetchSize should show a single statement with a streaming cursor.
• Row Count Consistency – Export the result set to CSV using Export Result Set > CSV and count the rows. They should match the expected count from the original query.

Limitations and Caveats

• Some JDBC drivers ignore fetchSize unless you set it per statement. Test with conn.setFetchSize(5000) in a custom script if necessary.
• Server‑side cursors can keep locks or temporary tables open on the database. If you notice long‑running transactions, consider setting a timeout on the cursor or closing the connection after the query.
• UI labels for fetch controls differ between DBeaver releases. Always check the Result Sets preference page for the exact option names.

Takeaway

For most analytical workloads, enable Fetch on Demand and set a reasonable maximum rows per fetch. If you need to process all rows quickly, configure a fetchSize with server‑side cursors. Use LIMIT/OFFSET only when you need explicit page navigation and the database can handle deep offsets efficiently.