Knex.js pagination performance for deep offset queries

Answer

Use keyset (cursor) pagination instead of .offset/.limit.

Confirmed facts

• Offset pagination forces the database to scan and discard preceding rows, causing linear latency growth.
• Keyset pagination uses a WHERE clause on the last seen key(s) and lets the database seek via an index, giving constant‑time page fetches.
• In Knex there is no built‑in keyset method; you express it with .where(), .orderBy() and .limit().

Likely explanation

The performance decay you see is due to the executor walking through all rows up to the offset before returning the window. Adding an index on the sort column does not change this because the engine still must skip the rows.

Steps to implement keyset pagination in Knex

1. Ensure you have a unique, indexed column (e.g., auto‑increment id) or a combination that is unique and monotonic.
2. When fetching the first page, order by that column and limit: knex('table').orderBy('id').limit(pageSize).
3. Return the last row’s key value(s) to the client as an opaque cursor (e.g., base64‑encoded JSON).
4. For the next page, decode the cursor and build the query:

const { id: lastId } = decodeCursor(cursor);
return knex('table')
  .where('id', '>', lastId)
  .orderBy('id')
  .limit(pageSize);

If you need to sort by multiple columns (e.g., created_at then id), nest the where to handle equality on the leading column:
const { created_at: ts, id: lastId } = decodeCursor(cursor);
return knex('table')
  .where(function () {
    this.where('created_at', '>', ts)
      .orWhere(function () {
        this.where('created_at', '=', ts).where('id', '>', lastId);
      });
  })
  .orderBy('created_at', 'asc')
  .orderBy('id', 'asc')
  .limit(pageSize);

Reverse direction (previous page) uses < and reverses the order.
Always validate the decoded cursor server‑side; never trust raw column values from the client.

Missing diagnostic detail
Do you have a unique, indexed column (such as an auto‑increment id) that can serve as the cursor? If not, you’ll need to add one or create a composite unique key before keyset pagination becomes effective.