Implementing Deterministic Pagination in Knex.js

The Problem: Inconsistent Page Results

When implementing pagination in Knex.js, the most common failure is returning inconsistent data across pages. If you use .limit() and .offset() without a strict sort order, the database engine does not guarantee the sequence of rows. This leads to "drifting," where a user sees the same record on page one and page two, or misses a record entirely when a new row is inserted during their session.

The solution is to combine .limit() and .offset() with a deterministic .orderBy() clause on a unique column (usually the primary key). This ensures the database always evaluates the row sequence identically before applying the window of results.

Implementing Offset-Based Pagination

In Knex.js, pagination is handled by calculating the starting point (offset) based on the desired page number and the number of records per page. The formula is: (page - 1) * limit.

const getPaginatedUsers = async (page = 1, pageSize = 10) => {
  const offset = (page - 1) * pageSize;

  return knex('users')
    .select('id', 'username', 'email')
    .orderBy('id', 'asc') // Essential for deterministic results
    .limit(pageSize)
    .offset(offset);
};

Execution and Verification

To implement this in a Node.js environment, ensure you have a configured Knex instance and the necessary database permissions to read the target table. Run the function within an async handler. To verify the generated SQL without executing it against the database, use the .toSQL().toNative() method.

const query = knex('users').orderBy('id', 'asc').limit(10).offset(20);
console.log(query.toSQL().toNative());
// Expected output: { sql: 'select * from "users" order by "id" asc limit ? offset ?', bindings: [10, 20] }

Performance Limitations and Risks

While OFFSET is the standard approach for small to medium datasets, it introduces significant performance degradation as the offset value increases. This is because the database must scan through all preceding rows and discard them before returning the requested slice.

• Scan Overhead: An offset of 100,000 requires the database to read 100,000 rows from disk only to throw them away.
• Data Drift: If a record is deleted from page one while a user is navigating to page two, the first record of page two shifts up to page one, causing the user to see a duplicate record on the second page.
• Memory Pressure: Extremely large offsets combined with complex joins can lead to increased memory usage on the database server.

Comparison: Offset vs. Cursor Pagination

For high-scale applications, consider "Cursor-based" (or Keyset) pagination. Instead of skipping rows, you filter for records that come after the last seen ID.

Practical Verification Steps

1. Empty Set Test: Run a query where the offset exceeds the total row count. Knex should return an empty array [], not a null value or an error.
2. Boundary Test: Execute .limit(10).offset(0) and .limit(10).offset(10). Verify that the last record of the first set is immediately followed by the first record of the second set.
3. Ordering Test: Remove the .orderBy() clause and run the query multiple times. If the result order changes, your pagination is non-deterministic and requires a sort key.