Offset vs Keyset Pagination in Bun's SQLite: Which Yields Lower Latency for Large Datasets?

Goal

Determine the most efficient pagination strategy for a table containing millions of rows when using Bun’s built‑in SQLite module.

Constraints

• Data size: ≥1 million rows.
• SQLite accessed via import { Database } from 'bun:sqlite'.
• Pagination must return a fixed number of rows per request.
• Implementation must rely on standard SQL supported by the module.

Uncertainty

While OFFSET‑based queries are straightforward, their performance degrades as the offset increases. Keyset (cursor) pagination can avoid this linear scan, but its correctness depends on a unique, indexed column and may introduce edge cases when ordering columns are not strictly monotonic.

Questions

1. Does OFFSET pagination in Bun’s SQLite exhibit a linear increase in execution time as the offset grows, and if so, to what extent?
2. Can keyset pagination be reliably implemented using a single indexed column in Bun’s SQLite, and what are the conditions that could cause duplicate or missing rows?
3. Are there any undocumented limitations or performance penalties when using keyset pagination with Bun’s SQLite module compared to standard OFFSET/LIMIT queries?