Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 37 ยท Performance and Patterns

Pagination Patterns

Paginate correctly: LIMIT and OFFSET with skip and take, the cost and drift of offset paging, and fast stable keyset (cursor) pagination.

Returning every row of a large table is never acceptable. APIs return one page at a time. There are two main ways to build pages. Offset pagination says "skip the first 40 rows and give me the next 20". Keyset pagination (also called cursor pagination) says "give me the 20 rows after the one with this id". Offset is simpler and lets users jump to page 7; keyset is faster on big tables and never skips or repeats rows when data changes.

The demos use node:sqlite. TypeORM and Sequelize calls are read-only, but they map directly onto the SQL you will see.

Offset pagination

TypeScript
// TypeORM
const [items, total] = await postRepo.findAndCount({
  order: { createdAt: 'DESC', id: 'DESC' },
  skip: (page - 1) * pageSize,   // OFFSET
  take: pageSize,                // LIMIT
});
JavaScript
// Sequelize
const { rows, count } = await Post.findAndCountAll({
  order: [['createdAt', 'DESC'], ['id', 'DESC']],
  offset: (page - 1) * pageSize,
  limit: pageSize,
});

Always include a unique tie-breaker (id) in ORDER BY. If many rows share the same createdAt, the database may return them in a different order on each request, and rows appear on two pages or none.

Node.js Offset pages

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Two problems with offset

  1. Cost. OFFSET 100000 makes the database find and throw away 100,000 rows before returning yours. Deep pages get slower and slower.
  2. Drift. If a new row is inserted while the user is reading, everything shifts by one and the next page repeats a row they have already seen.
Node.js Offset drift when data changes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Keyset pagination

Instead of counting rows to skip, remember where the last page ended and ask for rows after it. For ORDER BY id DESC, the next page is WHERE id < :lastId ORDER BY id DESC LIMIT 20. The database uses the index on id to jump straight there, at the same cost for page 1 and page 10,000.

Node.js Keyset pages are stable

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Sorting by something that is not unique

Real feeds sort by created_at, which is not unique. Use a composite cursor (created_at, id) and compare the pair:

SQL
WHERE (created_at, id) < (:lastCreatedAt, :lastId)
ORDER BY created_at DESC, id DESC
LIMIT 20

Row-value comparison works in PostgreSQL, MySQL 8 and SQLite 3.15+. If your database lacks it, write it out: created_at < :c OR (created_at = :c AND id < :i). Back it with an index on (created_at, id).

Node.js Composite cursor, encoded as an opaque string

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Return the cursor to the client as an opaque string (base64 of the values, as above). Clients should not build cursors themselves, and you stay free to change the internals later. Validate the decoded values on the way back in; a cursor is user input.

Choosing

Offset Keyset
Jump to page N Yes No (next/previous only)
Total count and page numbers Easy (findAndCount) Extra query, optional
Deep pages Slow Constant time
Stable while data changes No Yes
Sort by arbitrary column Easy Needs matching index and tie-breaker

Use offset for small admin tables where "page 3 of 12" matters. Use keyset for feeds, infinite scroll, exports and any big table.

Counting is costly too: SELECT COUNT(*) on a huge table scans it. Cache the count, show "10,000+" or skip it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does OFFSET 100000 get slow?

    Offset cost grows with the number of skipped rows.
  2. 2. What is the main advantage of keyset pagination?

    The cursor says where to continue, so inserts do not shift pages and the index jumps straight there.
  3. 3. When sorting by created_at (not unique), what should the cursor contain?

    The id breaks ties so no row is skipped or repeated.
  4. 4. Why ship cursors as opaque base64 strings?

    An opaque token lets you change the cursor internals without breaking clients.

Summary

  • Offset (skip/take, offset/limit) is simple but slow deep and unstable under inserts.
  • Keyset pagination uses WHERE key < last with an index and returns a cursor.
  • Always order by a unique tie-breaker; use a composite cursor for non-unique sorts.
  • Choose by use case: admin tables with offset, feeds and big tables with keyset.

Next: Repository and Unit of Work