Efficient Pagination in Knex.js: Limit/Offset with Count Optimization

Use limit/offset for small pages; switch to keyset pagination for deep pages

Knex.js builds standard SQL LIMIT and OFFSET clauses when you chain .limit(n).offset(m). For the first few pages this is simple and fast. As the offset grows the database must scan and discard the preceding rows, which degrades performance. The reliable pattern is:

1. Fetch the page with .limit(pageSize).offset((page-1)*pageSize).orderBy('id').
2. Run a separate count('id') query to get the total row count for UI pagination controls.
3. When the page number exceeds a few hundred, replace offset with a keyset query: .where('id', '>', lastSeenId).limit(pageSize).orderBy('id').

Worked example: page 3 of a users table

// knexfile.js (assumes Knex 2.x, Node 18+)
const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL
});

const page = 3;
const pageSize = 10;
const offset = (page - 1) * pageSize;

// 1️⃣ Page query – deterministic ordering is mandatory
const pageQuery = knex('users')
  .select('id', 'email', 'created_at')
  .orderBy('id')               // unique column prevents duplicate/missing rows
  .limit(pageSize)
  .offset(offset);

// 2️⃣ Count query – returns { count: '42' } as a string
const countQuery = knex('users').count('id as total');

// Execute both in parallel (requires read permission on users table)
const [rows, [{ total }]] = await Promise.all([
  pageQuery,
  countQuery
]);

// Parse the string count
const totalRows = Number(total);
console.log({ page, pageSize, totalRows, rows });

Verification steps you can run locally

• Inspect generated SQL – run pageQuery.toString() in a Node REPL or script. You should see select "id", "email", "created_at" from "users" order by "id" limit 10 offset 20.
• Cross‑check count – execute await knex('users').limit(5).offset(0) and compare the returned row count with totalRows for a small dataset.
• Test keyset pagination – after the last row of page 3 capture lastId = rows[rows.length-1].id then run knex('users').where('id', '>', lastId).limit(pageSize).orderBy('id'). Verify the next 10 rows match the offset‑based page 4.

Limits and common mistakes

Deep offset performance

Large OFFSET values force the database to scan and discard rows before the window. An index on the ordered column (e.g., id) reduces but does not eliminate the cost. For page numbers beyond ~500, prefer keyset pagination.

Non‑deterministic ordering

Without a unique ORDER BY the database may return rows in any order that satisfies the query plan. This causes skipped or duplicated rows across pages. Always order by a unique, immutable column (primary key or a unique timestamp + id composite).

Count returns a string

knex('users').count('id') yields [{ count: '42' }] (or total if aliased). Forgetting to coerce with Number() leads to string concatenation bugs in pagination math.

Transaction isolation side‑effects

If the page and count queries run inside a transaction, the isolation level (e.g., REPEATABLE READ) can lock rows or present a snapshot that diverges from concurrent writes. For read‑only pagination, run queries outside a transaction or use READ COMMITTED.

Practical checklist

• ✅ Add .orderBy('id') (or another unique column) to every paginated query.
• ✅ Parse count result with Number() before using it in calculations.
• ✅ Monitor query latency; if OFFSET > 5000 rows, migrate to keyset pagination.
• ✅ Verify generated SQL with .toString() during development.
• ✅ Run the cross‑check (small limit vs. total count) in CI to catch schema changes.

When to roll back

Pagination queries are read‑only; they do not modify state, so no rollback is required. Only the surrounding transaction (if any) would need rollback on error.