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

Short answer

Keyset (cursor) pagination will yield lower — and roughly constant — latency on a million-row table, while OFFSET pagination degrades roughly linearly as the offset grows. This is standard SQLite engine behavior, not something specific to Bun: bun:sqlite executes ordinary SQL against SQLite, so the query planner's handling of LIMIT/OFFSET versus an indexed WHERE predicate is what governs performance. The trade-off is that keyset pagination only supports sequential navigation (no "jump to page 47") and requires a unique, indexed ordering column to be correct.

Why OFFSET slows down

SQLite does not store a row-number-to-position index for arbitrary queries. To satisfy LIMIT 20 OFFSET 900000, the engine must produce and discard 900,000 rows before returning the 20 you asked for. Execution time therefore scales with the offset, not the page size. On a single-threaded Bun process, a deep-offset query can also block the event loop for a noticeable interval, which matters if the same process serves HTTP requests.

Why keyset pagination stays fast

With keyset pagination you remember the last key of the previous page and filter on it:

import { Database } from 'bun:sqlite';

const db = new Database('app.db');
const pageSize = 20;

// First page
const first = db.query(
  'SELECT id, name FROM items ORDER BY id LIMIT ?'
).all(pageSize);

// Next page, using the last id seen
const lastId = first[first.length - 1].id;
const next = db.query(
  'SELECT id, name FROM items WHERE id > ? ORDER BY id LIMIT ?'
).all(lastId, pageSize);

Because id is the indexed primary key, the engine seeks directly into the B-tree instead of scanning. Latency is effectively independent of how deep into the table you are.

Correctness conditions for keyset

• Uniqueness: the ordering column must be unique (or you must add a tiebreaker, e.g. ORDER BY created_at, id with a compound WHERE (created_at, id) > (?, ?)). A non-unique sort key can skip or repeat rows across page boundaries.
• Index: the column must be indexed, otherwise the planner may still scan. A primary key or explicit CREATE INDEX covers this.
• Concurrent writes: rows inserted or deleted between page fetches can shift results — an inherent property of cursor pagination, not a bun:sqlite limitation.

Verify it yourself

There are no documented bun:sqlite-specific penalties for either approach, but you should confirm on your own data rather than trust generalizations:

EXPLAIN QUERY PLAN SELECT * FROM items LIMIT 20 OFFSET 900000;
EXPLAIN QUERY PLAN SELECT * FROM items WHERE id > 900000 ORDER BY id LIMIT 20;

The keyset query should report a SEARCH using the index; the deep-offset query will show a scan that walks the index or table from the start. Time both with performance.now() around the calls to quantify the gap on your dataset.

Recommendation

Use keyset pagination for sequential "next page" flows on large tables, anchored on an indexed unique column. Reserve OFFSET for small tables or genuinely random page access, and cap the maximum offset you accept. If your ordering column isn't unique, share the schema and I can suggest the right tiebreaker form.