Offset Pagination in AdonisJS 6 Lucid: When It Works and When It Breaks

The Problem with Page 1000

You ship a list endpoint using Lucid's paginate(). It works beautifully in development with 50 rows. Six months later, a customer scrolls to page 1,000 and the request times out. The database CPU spikes. The culprit isn't Lucid—it's the OFFSET 20000 ROWS clause that forces PostgreSQL to scan and discard twenty thousand rows before returning twenty.

Offset pagination is the default because it's simple: page and perPage map directly to LIMIT and OFFSET. But that simplicity has a hard performance ceiling. This post walks through where that ceiling sits, how to measure it, and what to do when you hit it.

What Lucid Actually Generates

In AdonisJS 6 with TypeScript and ESM, a typical controller call looks like this:

import User from '#models/user'
import type { HttpContext } from '@adonisjs/core/http'

export default class UsersController {
  async index({ request, inertia }: HttpContext) {
    const page = request.input('page', 1)
    const perPage = 20
    const users = await User.query().paginate(page, perPage)
    return inertia.render('users/index', { users })
  }
}

The paginate() method returns an object containing data (the rows), plus metadata: total, perPage, currentPage, lastPage. Edge templates can call users.links() for ready-made navigation markup. The generated SQL for page 3 with 20 per page:

SELECT * FROM "users" ORDER BY "id" LIMIT 20 OFFSET 40;
SELECT COUNT(*) AS "total" FROM "users";
Two queries: one for the slice, one for the total count. Both run on every request.
Why Deep Pages Get Expensive
Databases don't "jump" to an offset. They scan the index (or table) in order, counting rows until they've skipped the offset, then start returning results. Page 1 scans 0 rows. Page 1,000 scans 20,000 rows before returning anything. The cost grows linearly with page number.
You can see this yourself. Enable query logging in config/database.ts or run EXPLAIN ANALYZE on the generated SQL:
EXPLAIN ANALYZE SELECT * FROM "users" ORDER BY "id" LIMIT 20 OFFSET 20000;
Look for Rows Removed by Filter or the actual time on the Limit node. On a modest 100k-row table with an index on id, page 1 might take 0.3 ms; page 5,000 can take 15–30 ms. On a 10M-row table with a less selective sort, page 10,000 can exceed 500 ms.
The Consistency Trap
Offset pagination has a second problem: if rows are inserted or deleted between requests, the same page number returns different data. A user on page 3 sees a new row inserted at the top—suddenly they see a duplicate from page 2, or miss a row entirely. For admin dashboards this is annoying; for financial ledgers it's a bug.
Cursor Pagination: The Manual Alternative
Lucid doesn't ship a cursorPaginate() method. You build it with where and orderBy on a unique, sequential column (usually id or created_at + id for ties):
async index({ request }: HttpContext) {
  const cursor = request.input('cursor') // opaque string: "id:12345"
  const limit = 20
  
  const query = User.query().orderBy('id', 'asc').limit(limit + 1)
  
  if (cursor) {
    const [, lastId] = cursor.split(':')
    query.where('id', '>', Number(lastId))
  }
  
  const rows = await query
  const hasMore = rows.length > limit
  const data = hasMore ? rows.slice(0, -1) : rows
  
  const nextCursor = hasMore ? `id:${data[data.length - 1].id}` : null
  
  return { data, nextCursor, hasMore }
}
This generates WHERE "id" > 12345 ORDER BY "id" LIMIT 21. The index seek lands directly on the next row—no scan, constant cost regardless of position. The trade-off: you lose random access (no "jump to page 50"), and the client must store the cursor instead of a page number.
Decision Checklist
Use offset pagination when: total rows < 50k, users rarely go past page 20, random access matters (SEO pagination, admin tables with jump-to-page), and occasional duplicates on concurrent writes are acceptable.
Switch to cursor pagination when: tables exceed 100k rows, deep-page latency exceeds your SLA, you need stable ordering under concurrent writes, or you're building infinite-scroll UIs where users only move forward.
Hybrid approach: offset for the first 100 pages (covers 95% of traffic), cursor thereafter. Lucid makes this straightforward—just branch on page <= 100.
How to Verify in Your App
Seed a realistic dataset: node ace db:seed --files=database/seeders/main.ts (run in project root, requires write access to DB).
Hit the endpoint at page 1, 100, 1000 with curl -H "Accept: application/json" "http://localhost:3333/users?page=1000".
Check EXPLAIN ANALYZE output for each. If "actual time" on the Limit node grows with page number, you're paying the offset tax.
Set an alert: if p95 latency for the list endpoint exceeds 200 ms at page > 100, schedule the cursor migration.
No rollback needed—this is read-only diagnostics. The migration to cursor pagination is a new endpoint version; keep the old one behind a feature flag until clients migrate.
Closing Action
Open your slowest list endpoint. Run EXPLAIN ANALYZE on page 1 and page 500. If the difference is under 5 ms, offset is fine—stop optimizing. If it's 50 ms or more, add a cursor-based endpoint this sprint. The code is ~30 lines, the performance gain is constant-time, and your future self won't wake up to a database incident caused by a user scrolling too far.