A list endpoint with LIMIT 20 OFFSET 20000 looks harmless. On a table with a few hundred rows in development it is instant, so nobody notices.
In production the database reads the first 20,000 rows in your sort order, discards them, and returns the next 20. Page 1 touches 20 rows. Page 1000 touches 20,000. If the table grows, the same URL gets slower every month, and the first complaint is usually an export or an admin screen that times out.
What it looks like
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 20000; Run EXPLAIN ANALYZE on this and on the same query with OFFSET 0. The plan shows the row count the scan produced, and the gap is the wasted work.
In Prisma the same thing is skip:
await prisma.order.findMany({ orderBy: { createdAt: 'desc' }, skip: 20000, take: 20 }); Keyset pagination
Remember the last row of the previous page and ask for rows after it:
SELECT * FROM orders WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20; With an index on (created_at, id) the database jumps straight to the position and reads 20 rows, on page 1 and on page 1000. In Prisma:
await prisma.order.findMany({ orderBy: [{ createdAt: 'desc' }, { id: 'desc' }], cursor: { id: lastId }, skip: 1, take: 20, }); A second benefit
With offsets, a row inserted while someone scrolls pushes everything down by one, so the next page repeats an item. A cursor does not shift.
The tradeoff
You cannot jump to page 500, only go forward and back. For feeds, exports, admin lists and APIs that is usually fine. For a numbered pager over a small table, OFFSET is fine too. Small offsets are not the problem.
Catching it in review
Zelsis flags a literal OFFSET 1000 or larger, and .skip(1000) or larger, in a pull request as a low-severity warning. It does not catch an offset computed from page * pageSize, so a human still has to look at those. Run EXPLAIN ANALYZE against production-sized data before you ship a list endpoint.
How do you paginate big tables: cursors, or something else?
