Stop 09 · API DesignTopics · 3/12

Pagination

In short

Your products table has 2 lakh rows. Sending all of them at once would freeze the phone and hammer your database. Pagination sends one small page and a way to ask for the next.

Splitting large result sets into pages so clients don't have to fetch (and you don't have to serve) thousands of rows at once — offset-based or cursor-based.

The blob character slips a red ribbon bookmark into a huge ledger.

Think of it like this

A bookmark in a fat register

The college clerk never reads the whole admissions register aloud. She reads one page, slips a bookmark in, and continues from there next time. Offset pagination says 'skip 40 rows'. Cursor pagination is the bookmark: 'continue right after this entry'.

Watch it happen

Cursor pagination over orders sorted by id

101

102

103

104

105

106

107

108

109

110

1/5Page 1: GET /orders?limit=3. The server returns the first three orders.

The big ideas

What you need to know

Offset is simple

LIMIT 20 OFFSET 40 is easy and allows 'jump to page 5'. It slows down on deep pages and shifts when rows change.

Cursor is stable

WHERE id > :lastId LIMIT 20 uses the index, stays fast at any depth and never skips or repeats rows.

Always cap the limit

Accept ?limit= but clamp it, say to 100, so nobody can ask for a million rows in one go.

Return paging info

Send items plus nextCursor (or total and page) so the client knows whether to ask again.

See it in code

Walk through the code

1app.get("/orders", async (req, res) => {2  const limit = Math.min(Number(req.query.limit) || 20, 100);3  const after = Number(req.query.after) || 0;4 5  const { rows } = await pool.query(6    "SELECT id, total FROM orders WHERE id > $1 ORDER BY id LIMIT $2",7    [after, limit + 1]8  );9 10  const hasMore = rows.length > limit;11  const items = rows.slice(0, limit);12  res.json({ items, nextCursor: hasMore ? items[items.length - 1].id : null });13});

1/3Read the limit and cursor from the query string. Clamp limit to 100 so nobody asks for everything.

pool is a pg Pool. Fetching limit + 1 rows is a cheap trick to know whether another page exists, without a COUNT query.

Save yourself the pain

Common mistakes

Oops

Using OFFSET 50000 for infinite scroll on a big, busy table.

Do this instead

The database still walks past 50,000 rows. Use a cursor on an indexed column for feeds and infinite scroll.

Oops

Paginating without an ORDER BY.

Do this instead

Without a fixed order, a row can show up on two pages or on none. Always sort by a unique column like id.

Your mission

Build a cursor feed

~30 min

Quick check

Why does cursor pagination stay fast on page 5,000 while OFFSET gets slow?

Go deeper

1Implement offset/limit pagination on a listing endpoint.
2Implement cursor-based pagination and compare its consistency guarantees to offset-based.
3Return pagination metadata (total count, next cursor) in a consistent response shape.