Pagination

Offset pages are simple but shift when rows are inserted; cursor pages name the last row seen and stay correct.

SyntaxSELECT * FROM posts WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3

A collection endpoint must not return every row. Pagination returns a slice with a way to ask for the next one. There are two ways to do it, and they behave differently under writes.

Offset

?page=3&limit=20 becomes LIMIT 20 OFFSET 40. It is simple, it can jump straight to any page, and it has two problems. The database has to walk and discard the skipped rows, so page 5000 is slow. Worse, the slice is defined by position: insert one row at the top between two requests and every later row shifts down one, so page 2 repeats a row that page 1 already showed.

Cursor

A cursor names the last row you saw instead of a count. The next request asks for rows after that row in a fixed ordering:

WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3

Inserts and deletes elsewhere in the table cannot move that boundary, so nothing repeats and nothing is skipped. Order by a column pair that is unique, with id as the tiebreaker, or two rows sharing a timestamp can straddle the boundary.

Encode the cursor as one opaque string, for example base64url, so clients treat it as a token rather than something to build themselves. Ask the database for limit + 1 rows: if the extra row comes back there is another page, and you return a nextCursor. Otherwise return null.

Reading the limit

Clamp it. Number(q.get('limit')) || 20 and Number(q.get('limit')) ?? 20 are not the same: || falls back on 0 and NaN, ?? does not.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Page an activity feed where new rows arrive while the reader scrolls.
  • Replace a LIMIT/OFFSET endpoint that slows down on later pages.
  • Keep a public list endpoint from returning the whole table when limit is omitted.
  • Give a mobile client an infinite-scroll endpoint with no duplicate rows.

More examples

A cursor page against PostgreSQL

The limit + 1 trick answers "is there another page" without a second COUNT query over the whole table. An index on `(created_at DESC, id DESC)` makes the WHERE clause a seek straight to the boundary row, which is why cursor pages stay the same speed on page 5000 as on page 1. Decoding failures become a 400, because a client echoing back a cursor you gave it should never produce a 500.

Example · javascript
function encodeCursor(row) {
  return Buffer.from(JSON.stringify([row.created_at.toISOString(), row.id])).toString('base64url');
}

function decodeCursor(cursor) {
  try {
    const [createdAt, id] = JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
    return { createdAt, id };
  } catch {
    // A cursor is opaque to clients, so a broken one is bad input, not a 500.
    throw new ApiError(400, 'invalid_cursor', 'cursor is not valid');
  }
}

async function listPosts({ limit, cursor }) {
  // Ask for one extra row: its presence is how we know another page exists.
  const sql = cursor
    ? `SELECT id, title, created_at FROM posts
       WHERE (created_at, id) < ($1, $2)
       ORDER BY created_at DESC, id DESC LIMIT $3`
    : `SELECT id, title, created_at FROM posts
       ORDER BY created_at DESC, id DESC LIMIT $1`;

  const c = cursor && decodeCursor(cursor);
  const params = cursor ? [c.createdAt, c.id, limit + 1] : [limit + 1];
  const { rows } = await pool.query(sql, params);

  const hasMore = rows.length > limit;
  const items = hasMore ? rows.slice(0, limit) : rows;
  return { items, nextCursor: hasMore ? encodeCursor(items[items.length - 1]) : null };
}

Offset pagination, where it is the right answer

Offset is right when the UI needs numbered pages and a total count, the table is small, and the data is not changing under the reader: an admin list, a report, an export. A cursor cannot give you "page 7 of 12", because it does not know how many rows came before. Keep the total query out of high-traffic public endpoints, where it becomes the slowest part of the request.

Example · javascript
async function listUsersAdmin({ page, limit }) {
  const offset = (page - 1) * limit;
  const { rows } = await pool.query(
    'SELECT id, email FROM users ORDER BY email ASC LIMIT $1 OFFSET $2',
    [limit, offset],
  );
  // A total is only affordable on a small table; it is a second full scan.
  const { rows: counted } = await pool.query('SELECT count(*)::int AS total FROM users');
  return {
    items: rows,
    page,
    totalPages: Math.ceil(counted[0].total / limit),
  };
}

Parsing the pagination query, and the response shape

Returning the next cursor rather than a page number means the client never constructs one, so you can change what a cursor contains without breaking anyone. Clamping to a maximum of 100 caps the work a single request can ask for. Putting `null` in the field, instead of leaving it out, gives clients one condition to check for the end of the list.

Example · javascript
function readPageQuery(searchParams) {
  const raw = searchParams.get('limit'); // null when absent
  // Number(null) and Number('') are both 0, not NaN, so a missing or empty
  // value must be caught before converting, or the default of 20 is never used
  // and ?limit= quietly becomes a one-row page.
  const n = raw === null || raw.trim() === '' ? NaN : Number(raw);
  // Number.isInteger rejects the NaN from a missing or non-numeric value.
  const limit = Number.isInteger(n) ? Math.min(Math.max(n, 1), 100) : 20;
  const cursor = searchParams.get('cursor'); // null when absent
  return { limit, cursor };
}

// GET /posts?limit=2 responds with:
// {
//   "items": [ { "id": 9 }, { "id": 7 } ],
//   "nextCursor": "MjAyNC0wNS0wM3w3"
// }
// The client's next request is simply GET /posts?limit=2&cursor=MjAyNC0wNS0wM3w3,
// and a nextCursor of null means this was the last page.

Related

node db queriesnode api jsonurl urlsearchparams getbuffer tostringnode api routing

Discussion

  • Be the first to comment on this lesson.