Firebird Pagination: ROWS, FIRST/SKIP, and the Deep Offset Trap

You are building an admin screen that lists articles newest first. The natural instinct is to reach for OFFSET 20 FETCH NEXT 20 ROWS ONLY, the syntax you have used in PostgreSQL or SQL Server. Firebird rejects it. The server does not implement the ANSI OFFSET/FETCH clause, and client libraries or ORMs that generate it will fail unless you configure a Firebird-specific dialect. The useful takeaway: Firebird gives you two long-standing ways to page—FIRST/SKIP and the ROWS clause—but the way you use them determines whether page fifty is instant or painfully slow.

Two dialects, one version caveat

Firebird has supported FIRST and SKIP for a long time. They are straightforward: FIRST limits the result set, and SKIP discards a number of leading rows. They remain perfectly serviceable for top-N queries and legacy code.

Firebird 2.0 introduced the ROWS clause, which aligns more closely with standard SQL and reads more naturally for pagination. It accepts either a single count, such as ROWS 20, or an inclusive range, such as ROWS 21 TO 40. For new development, ROWS is generally preferred because the range syntax makes the intent explicit. If you are maintaining an older server, confirm the version before relying on it: ROWS requires Firebird 2.0 or newer, while older servers only support FIRST/SKIP.

Whichever syntax you choose, an ORDER BY is mandatory for deterministic pages. Without a unique sort key, rows can shift between requests and produce duplicates or gaps.

Worked example: fetching the second page

Imagine an articles table with an id primary key, a title, and a created_at timestamp. To return the second page of twenty rows, sorted newest first, run the following in isql—Firebird's interactive SQL tool—after connecting to your database with an account that has read access to the table:

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
ROWS 21 TO 40;

This query returns rows 21 through 40 of the ordered set—the second page when each page holds twenty rows. The composite sort on created_at and id ensures a stable order even when multiple articles share a timestamp.

To verify the result, count the returned rows. You should see exactly twenty records, and the created_at value of the first row should be older than or equal to the last row of the previous page. If you see fewer than twenty rows, you have reached the end of the table.

Why deep offsets are disproportionately slow

Firebird evaluates row numbering after sorting. When you ask for ROWS 100001 TO 100020, the engine must still traverse, sort, and discard the first 100,000 rows before it can produce the twenty you want. The cost grows linearly with the offset, making deep pagination disproportionately expensive compared with shallow pages.

You can observe how the engine handles this by comparing execution plans. In isql, enable plan output and run the same query with a shallow and a deep range:

SET PLAN ON;

SELECT id, title
FROM articles
ORDER BY created_at DESC, id DESC
ROWS 1 TO 20;

SELECT id, title
FROM articles
ORDER BY created_at DESC, id DESC
ROWS 100001 TO 100020;

Both queries require a sort, but the second one forces the engine to process a much larger working set before the row filter is applied. On a table with only a few thousand rows the difference may be negligible; on a table with millions, the deep query will take noticeably longer. To confirm the behavior on your own data, measure the response time for both queries against the same ordered dataset.

Keyset pagination: the scalable alternative

If your application needs to page deep into large result sets, offset-based paging is the wrong tool. Keyset pagination—also called seek pagination—avoids the discard step entirely. Instead of skipping a fixed number of rows, you filter on the last sort key from the previous page.

Using the same articles table, suppose the last row on the previous page had created_at = :last_created_at and id = :last_id. In your application code, bind those values as parameters and run:

SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
ROWS 20;

The row-value comparison in the WHERE clause lets Firebird seek directly to the correct position in the index, provided a supporting index exists on created_at and id. The engine no longer traverses and discards preceding rows, so the cost stays roughly constant regardless of page depth.

The trade-off is navigation flexibility. Keyset pagination naturally supports "next" and "previous" pages, but jumping to an arbitrary page number requires either an offset query or a separate lookup. For infinite-scroll feeds and API cursors, that restriction is usually acceptable.

Limitations and practical checks

• Version sensitivity: ROWS requires Firebird 2.0 or newer. Verify your server version in your database administration tool or installation metadata before deploying.
• Stable ordering: If your ORDER BY columns are not unique, concurrent inserts or deletes can shift rows between pages. Always append a unique tie-breaker such as the primary key.
• ORM configuration: Object-relational mappers that default to OFFSET/FETCH will generate invalid Firebird SQL unless you explicitly select a Firebird dialect.
• Index support: Keyset pagination performs best when an index matches the WHERE predicate and the ORDER BY. Without it, the engine may still fall back to a full sort.

A practical way to check your setup is to run the worked example in isql against a test table with a few thousand rows. Confirm the row count and ordering match expectations, then compare execution plans with SET PLAN ON to see how sorting and row filtering interact. Finally, measure response time for a shallow range versus a deep range to quantify the offset cost on your own hardware.

Actionable summary

Use ROWS for new Firebird development; its range syntax is clearer than FIRST/SKIP and closer to standard SQL. Reserve FIRST/SKIP for legacy code or simple top-N queries. When your users start requesting page fifty of a million-row table, stop increasing the offset and switch to keyset pagination with a row-value predicate. The change is small, but the performance difference is not.