DataSpell Database Console Pagination with Large OFFSET

Answer

DataSpell’s Database Console does not have a built‑in switch to force server‑side pagination. It simply appends LIMIT and OFFSET clauses to the SQL you write, and the JDBC driver (and ultimately the database) decides how those clauses are executed.

Likely Explanation

When the OFFSET value is large, many drivers will still fetch rows from the start of the result set and skip the requested number before returning the page. This happens because the driver treats the query as a ordinary statement; unless the database can apply the offset using an index‑supported ORDER BY, it must scan and discard the skipped rows. The exact behavior varies:

• PostgreSQL JDBC driver typically pushes the LIMIT/OFFSET to the server, so the database does the skipping.
• MySQL/JDBC can also push LIMIT/OFFSET, but without a supporting index the server still scans the skipped rows.
• Some older or proprietary drivers may materialize the entire result set client‑side before applying the offset.

Confirmed Facts

1. DataSpell’s UI pagination controls always generate a query of the form SELECT … FROM … ORDER BY … LIMIT ? OFFSET ?.
2. No DataSpell setting exists to change this to a keyset (seek) pagination pattern or to enable streaming of results.
3. Performance impact of a large OFFSET depends on the presence of an index that matches the ORDER BY clause.

Steps to Verify and Mitigate

1. Enable SQL logging in DataSpell: View → Tool Windows → Database Console → Log. Run a page with a large OFFSET and check the exact SQL sent to the database.
2. Run EXPLAIN ANALYZE (or the equivalent for your DB) on that logged query to see whether the database is using an index to skip rows or performing a full scan.
3. If the plan shows a costly scan, rewrite the query using keyset pagination:

   SELECT …
   FROM …
   WHERE ordered_col > :last_val
   ORDER BY ordered_col
   LIMIT :page_size

   where ordered_col is a unique, indexed column (or a combination that guarantees uniqueness).
4. Compare execution times of the original LIMIT/OFFSET query and the keyset version for several pages, especially after inserts/updates, to confirm correct ordering and no missing/duplicate rows.

Missing Diagnostic Detail

To give a definitive recommendation, please specify which database and JDBC driver version you are using (e.g., PostgreSQL 15 with pgJDBC 42.7, MySQL 8.0 with Connector/J 8.0). The answer may change if the driver is known to materialize results client‑side.