Choosing a Pagination Syntax in Firebird: OFFSET/FETCH, ROWS, and FIRST/SKIP

The pagination decision in Firebird

You need to return a fixed number of rows starting at a specific position, but the pagination clause you choose in Firebird depends on server version, portability requirements, and how deep your users will page. The useful takeaway: for new services on Firebird 4 or later, use standard OFFSET/FETCH with a deterministic ORDER BY; on older servers use Firebird-native ROWS; reserve FIRST/SKIP for legacy top-N queries that need no offset.

Constraints that drive the choice

Before selecting a syntax, confirm three constraints on your target environment:

• Server version and dialect. Firebird has evolved its SQL surface over time. Treat the exact version availability of ROWS and OFFSET/FETCH as assumptions and verify them on the actual server before standardizing.
• Deterministic ordering. Every pagination form requires a unique ORDER BY so that page boundaries remain stable. Include the primary key or another unique tiebreaker, and be explicit about NULLS ordering where applicable.
• Deep-page performance. All three syntaxes still process the rows they skip. A large offset is not free, so keyset pagination is usually faster for deep navigation.

Syntax comparison

Syntax	Availability	Standard	Best for	Key limitation
FIRST/SKIP	Legacy Firebird	Non-standard	Simple top-N without offset	SKIP only works with ORDER BY; least portable
ROWS	Firebird-native, version-sensitive	Non-standard	Concise page ranges	Can be confused with window functions; not composable with OFFSET/FETCH
OFFSET/FETCH	Firebird 4+ (verify)	SQL-standard	Explicit page size and clear intent	Ties you to newer servers

Trade-offs

FIRST/SKIP is the least portable and easiest to misuse because the two clauses are independent and the offset form is not standard SQL. ROWS is concise and reads naturally as a range, but its version constraints and visual similarity to window-function syntax can cause confusion. OFFSET/FETCH is the clearest for readers familiar with standard SQL, but it requires a newer server. Regardless of syntax, deep offsets still scan skipped rows, so keyset pagination is usually faster when users jump to late pages.

Concrete implementations

The examples below assume a table docs with a primary key id and a payload column. Run them in isql or your SQL client. You need read access to the table and an index that supports the ordering.

Standard OFFSET/FETCH (Firebird 4+)

SELECT id, payload
FROM docs
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

This form is explicit about page size and intent. Keep the ORDER BY in the same SELECT as the pagination clause; some client drivers add their own pagination wrappers, so test the exact SQL sent to the server.

Firebird-native ROWS

SELECT id, payload
FROM docs
ORDER BY id
ROWS 21 TO 30;

This is a compact equivalent for servers where ROWS is supported. Do not combine ROWS with OFFSET/FETCH in one SELECT; they are alternative syntaxes, not composable clauses.

Legacy FIRST/SKIP

SELECT id, payload
FROM docs
ORDER BY id
FIRST 10
SKIP 20;

Use this only when maintaining legacy top-N queries. Avoid it in new code intended to be portable beyond Firebird.

Keyset pagination for deep pages

When users page deep into the result set, replace the offset with a seek predicate on an indexed unique column.

SELECT id, payload
FROM docs
WHERE id > :last_id
ORDER BY id
FETCH NEXT 10 ROWS ONLY;

Replace :last_id with the primary key of the last row from the previous page. This avoids processing skipped rows and keeps response time stable as the user advances.

Validation and operational checks

Confirm server support

Check the server version and SQL dialect using a context query such as ENGINE_VERSION, then run each pagination form in isql against that server. Expected check: the statement parses without a syntax error. Risk: assuming support and deploying a query that fails at runtime.

Verify the execution plan

Run SET PLAN ON; in isql (or use EXPLAIN) before the paginated query. Expected check: the plan for ORDER BY uses an index rather than a full sort where possible. Compare the plan for an offset query with the plan for a keyset query to confirm the keyset form uses an index navigation on the unique column.

Test page boundaries and timing

1. Fetch pages sequentially and assert that the last primary key of page N precedes the first primary key of page N+1.
2. While a concurrent inserter adds rows, repeat the boundary test to confirm no duplicate or missing primary keys.
3. Measure elapsed time for the same logical page at offsets 0, 10,000, and 100,000 to confirm whether keyset pagination is needed for your workload.

Limitations to plan around

• Deep offsets are not free. Skipped rows are still processed in all three syntaxes, so response time grows with offset size.
• Version assumptions. Treat exact availability of ROWS and OFFSET/FETCH as assumptions until verified on the target server.
• Non-deterministic ordering. Without a unique tiebreaker in ORDER BY, concurrent inserts can cause rows to duplicate or disappear across pages.
• Driver behavior. Some drivers wrap or rewrite pagination, so validate the exact SQL that reaches the server rather than relying on the client library's abstraction.