Firebird OFFSET/FETCH pagination and JDBC driver handling of large offsets

Short answer

Yes, Jaybird accepts OFFSET and FETCH values as JDBC parameters; no, row order is not guaranteed without ORDER BY; and no, the driver does not buffer the whole result set — the server scans and discards the offset rows itself, so large offsets cost server-side work proportional to the offset, not client memory.

Parameter binding for OFFSET/FETCH

Firebird 3.0+ implements the SQL-standard OFFSET n ROWS FETCH NEXT m ROWS ONLY syntax, and both the offset and fetch expressions accept parameter markers. With Jaybird 3.x or later (4.x/5.x recommended for Firebird 4.0/5.0), a prepared statement like this works:

String sql = "SELECT id, name FROM items "
    + "ORDER BY id "
    + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, offset);
    ps.setInt(2, pageSize);
    ResultSet rs = ps.executeQuery();
}

Jaybird forwards the statement to the engine unchanged; it does not rewrite parameters into literals or emulate the clause client-side. Two caveats: this only works with the standard syntax (Firebird's older FIRST/SKIP or ROWS variants have their own rules), and Jaybird 2.2.x targeting Firebird 2.5 does not support OFFSET/FETCH at all — check your driver version before assuming a syntax error comes from the SQL.

Ordering without ORDER BY

Without ORDER BY, the result sequence is genuinely undefined. Firebird returns rows in whatever order the chosen access path produces — physical storage order, index order, or something else depending on the plan. It is not "usually insertion order" in any reliable sense: plan changes, garbage collection, or index availability can alter the sequence between executions. For pagination this means pages can overlap or skip rows even on a static table. Always include ORDER BY, and make it deterministic by ending with a unique tiebreaker such as the primary key.

Large-offset performance

The engine evaluates OFFSET on the server by producing the ordered result and discarding the first n rows. Streaming to the client begins only after the skip, so Jaybird never materializes the skipped rows — there is no hidden client-side buffering of the full result set. The cost is server-side I/O and CPU roughly proportional to the offset, plus the sort cost if ORDER BY lacks a supporting index. OFFSET 100 000 on a million-row table with an indexed ORDER BY is noticeably slower than OFFSET 0; with an unindexed sort it can be much worse. The driver's fetch size (setFetchSize or the useFbFetchSize connection property) only tunes network round-trips for the rows actually returned — it does not reduce the offset scan.

Consistency across pages

Each page query reflects the database state when it executes. If rows are inserted, deleted, or reordered between fetches, offset-based pages can miss or duplicate rows. Two mitigations:

• Run the whole pagination session inside a SNAPSHOT (repeatable read) transaction so every page sees the same committed state.
• Better for volatile data: switch to keyset (seek) pagination — WHERE id > :lastSeen ORDER BY id FETCH NEXT ? ROWS ONLY — which is both stable under concurrent change and avoids the offset scan entirely.

Verification

To confirm behavior on your setup: run the parameterized query above against your server and confirm no syntax error; execute the same query twice without ORDER BY and compare sequences; and time OFFSET 0 versus OFFSET 100000 with SET PLAN ON (or isql's plan output) to confirm the ORDER BY uses an index rather than a full sort. Enabling Jaybird logging at FINE level for org.firebirdsql.jdbc will show the statement is sent unchanged.

One open detail that would sharpen the performance advice: the exact Firebird server and Jaybird versions in use, since plan behavior and driver defaults differ across releases.