Offset pagination versus keyset pagination for deep-page queries in Knex.js

Direct answer

When the ordering column is not unique, choose keyset pagination and append a unique column—almost always the primary key—as a final tie-breaker. Offset pagination cannot be made fast for deep pages; the database must scan and discard every skipped row, and concurrent inserts can shift rows across page boundaries, producing duplicates or omissions. The good news is that the tie-breaker complexity can be hidden inside one small helper, so your endpoint code stays nearly as simple as the offset version.

Why offset fails at depth

.limit(pageSize).offset(page * pageSize) has two problems. First, cost grows linearly with the offset: page 10,000 means the database reads and throws away hundreds of thousands of rows. Second, offset pagination is not stable under concurrent writes—a row inserted on page 1 pushes a row from page 1 onto page 2, so a client walking pages sees it twice (or never). Keyset pagination replaces the offset with a WHERE predicate on the sort key, which an index can seek directly, giving roughly constant query time and a stable position in the ordering.

Handling a non-unique sort column

Order by (sort_col, id) and compare the pair. In Knex.js, encapsulate it once:

function applyCursor(qb, cursor, pageSize) {
  if (cursor) {
    // Portable form; works on dialects without row-value comparison
    qb.where(function () {
      this.where('created_at', '>', cursor.sort)
        .orWhere(function () {
          this.where('created_at', cursor.sort)
            .andWhere('id', '>', cursor.id);
        });
    });
  }
  return qb
    .orderBy([{ column: 'created_at' }, { column: 'id' }])
    .limit(pageSize);
}

On PostgreSQL (and MySQL 8) you can shorten the predicate to a row-value comparison: .whereRaw('(created_at, id) > (?, ?)', [cursor.sort, cursor.id]). Verify your target dialect supports it before relying on the shorter form. For descending order, flip both the comparison operator (<) and the ORDER BY direction—if they disagree, pages will be wrong.

The endpoint then reads a cursor from the query string (e.g., the last row's created_at and id, encoded as opaque base64 JSON) and passes it to the helper. That is the entire added complexity over the offset version.

Prerequisites and caveats

• Create a composite index on (sort_col, id) in the same order as the ORDER BY; without it, keyset queries may still scan.
• Nullable sort columns break simple > comparisons because NULL never satisfies the predicate—exclude NULLs or add explicit NULL handling.
• Keyset pagination cannot jump to an arbitrary page number. If the UI needs "go to page 47," keep offset pagination for shallow UI browsing and use keyset for deep iteration or infinite scroll.

Verification

1. Run EXPLAIN (or EXPLAIN ANALYZE) on the offset query at a deep offset (e.g., 100,000) and the keyset equivalent; compare rows scanned and execution time.
2. Seed duplicate values in the sort column, paginate through the whole table with a page size smaller than the duplicate group, and assert every row appears exactly once.
3. Insert rows between fetching consecutive pages and confirm the keyset walk matches a single unpaginated snapshot.
4. Confirm the composite index appears in the keyset query plan.

One missing detail that changes the recommendation: whether your API consumers need random page access. If they do, a pure keyset design is not sufficient and you will need a hybrid approach.