Firebird pagination with FIRST and SKIP: stable pages and the offset cost

The problem: pages that shift or slow down

You need to show a list of invoices, products, or log entries in chunks of twenty. A naive SELECT returns every matching row, so your application must slice the list in memory. That works for a hundred rows, but not for ten thousand. Firebird offers FIRST and SKIP, clauses that tell the server to return only a window of rows. The catch is that without a stable sort, the window can land on different rows each time you run it, and as the offset grows, the server still has to read past the rows you discard.

What FIRST and SKIP do

FIRST n caps the number of rows sent to the client. SKIP m tells Firebird to discard the first m rows of the ordered result before applying the limit. Together they form a page: FIRST 20 SKIP 0 is page one, FIRST 20 SKIP 20 is page two, and so on. These clauses are Firebird-specific syntax; other databases often use LIMIT and OFFSET instead, so queries do not always port directly.

Firebird does not require a separate count query just to apply FIRST/SKIP, but if you need total page counts you still need COUNT(*) with the same filter criteria.

Why ORDER BY is not optional for pagination

Without ORDER BY, the database can return rows in any physical or access-path order it chooses. That order can change after inserts, updates, deletes, or even a different execution plan. If page one skips zero rows and page two skips twenty, a shifting order means the same row can appear on two pages, or never appear at all. Deterministic pagination requires ordering by a column or combination of columns that uniquely identifies the row sequence, such as a primary key or a timestamp plus tie-breaker.

Worked example: page two of character sets

Run this in isql, Firebird's command-line SQL tool, connected to a database where you have SELECT privileges on system tables:

SELECT rdb$character_set_name
FROM rdb$character_sets
ORDER BY rdb$character_set_name
FIRST 3 SKIP 2;

The query asks for exactly three rows, beginning with the third character set alphabetically. You can verify the result by running the same statement without FIRST/SKIP and confirming that the returned names match positions three through five of the full alphabetical list. Do not assume the output; check it against your installed Firebird version, because reserved-word treatment and syntax details can differ between releases.

The trade-off: large SKIP values get expensive

SKIP does not seek to a position in an index like an application cursor might. Firebird must read and discard rows up to the offset before it can return the requested window. A query with SKIP 0 returns immediately after finding the first n rows. A query with SKIP 5000 may read thousands of rows only to throw them away. As the offset grows, response time and I/O generally grow with it.

To observe this on your system, compare execution plans or timings for the same ordered query with SKIP 0 and SKIP 5000. Use the same table, filter, and ORDER BY so the only variable is the offset. If deep pagination is a regular requirement, consider keyset pagination—filtering on the last seen value of the ordered column instead of skipping rows—or ask whether users truly need page fifty of a result set.

Practical check before you ship it

1. Connect to the target database in isql or a JDBC client with an account that has SELECT on the table.
2. Run your paginated query with ORDER BY, FIRST, and SKIP.
3. Confirm the row count equals FIRST n and that the first returned row matches the expected position in the full ordered set.
4. Repeat the query a few times; if the order is unstable, add more ordering columns until the sequence is deterministic.
5. Time a high-offset variant to decide whether SKIP is acceptable for your deepest expected page.

Because SELECT is read-only, there is no transaction state to roll back. The main risk is logical: returning the wrong rows or slower-than-expected responses. Verify on a copy of production data, or at least on a representative volume, before relying on FIRST/SKIP in user-facing pages.

Closing

Firebird's FIRST and SKIP are a straightforward way to paginate result sets at the database layer. Pair them with a deterministic ORDER BY, treat large offsets as a performance concern, and verify behavior on your actual Firebird version and data. If offsets stay small and ordering is stable, they are often the simplest tool for the job.