OFFSET vs keyset pagination for sequential export of an append-only BigQuery table

Comparison of Pagination Strategies

For a large append-only BigQuery table, the choice between page tokens and keyset pagination depends on the expected duration of the export process and the requirement for stable cost per page.

Page Tokens (Snapshot Consistency)

A page token provides a consistent view of the data because it indexes a materialized result set (the temporary table created by the query job). When you use a page token, BigQuery does not re-evaluate the query or re-scan skipped rows; it simply retrieves the next slice of the already-computed results.

• Consistency: Guaranteed for the lifetime of the result set.
• Cost: Constant per page, as it reads from the cached result.
• Risk: Tokens have a limited lifetime. If the result set expires or the token is evicted before the export completes, the token becomes invalid. You cannot resume from that point; you must restart the query and the export process from the beginning.

Keyset Pagination (Long-term Stability)

Keyset pagination (using WHERE key > last_seen_value ORDER BY key LIMIT n) is the documented method for sequential access that must persist beyond the lifetime of a single query snapshot.

• Consistency: It provides a "moving window." While it is stable for append-only tables, it does not provide a single-point-in-time snapshot across the entire export if new data is being appended during the process.
• Cost: Unlike OFFSET, which requires BigQuery to scan and discard all preceding rows (leading to linearly increasing costs), keyset pagination allows the engine to seek directly to the last seen value.

Maintaining Constant Processed Bytes

Keyset pagination is the only way to keep processed bytes roughly constant for deep sequential reads beyond the token lifetime, but this is conditional on the schema. To achieve constant cost, the ordering key must be a partitioning column or a clustering column. If the key is a standard column, BigQuery may still scan the entire column to evaluate the filter, though it will still be more efficient than OFFSET.

Implementation Steps for Keyset Export

1. Identify a unique, strictly monotonic ordering key (e.g., a timestamp or sequential ID).
2. Fetch the first page: SELECT * FROM table ORDER BY key LIMIT 1000.
3. Store the key of the last row processed in the current batch.
4. Fetch the next page: SELECT * FROM table WHERE key > [last_seen_key] ORDER BY key LIMIT 1000.
5. Repeat until no rows are returned.

Verification

To verify the cost behavior, compare the totalBytesProcessed in the job statistics for a page at the start of the table versus a page near the end. In a keyset approach with clustering, these values should remain relatively stable; with OFFSET, the value will grow significantly.

Diagnostic Detail Needed: Is the ordering key used for pagination a partitioning or clustering column? If not, the "constant processed bytes" benefit may be significantly reduced.