Pagination: sending data page by page

Why paginate?

An online shop may have 2 lakh orders. Sending them all in one response would be slow for the database, use lots of server memory and burn the user's mobile data. Nobody reads 2 lakh rows anyway. So APIs return a small page, such as 20 items, and let the app ask for more.

LIMIT and OFFSET

LIMIT says how many rows to return. OFFSET says how many rows to skip first. For page number page (starting at 1) and page size pageSize, the maths is: offset = (page - 1) * pageSize, and totalPages = ceil(total / pageSize).

-- Page 3 with 10 items per page: skip 20, take 10
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

Cursor (keyset) pagination

OFFSET has a hidden cost: for OFFSET 100000 the database still reads 100000 rows and throws them away. Deep pages get slower and slower. Keyset pagination remembers the last id the client saw and asks for rows after it. With an index on id, the database jumps straight there, so page 5000 is as fast as page 1.

-- The client saw ids up to 1040 on the last page
SELECT id, name, price
FROM products
WHERE id > 1040
ORDER BY id
LIMIT 20;

What the response should include

// Offset style: GET /api/products?page=2&pageSize=20
{ "items": [ ... ], "page": 2, "pageSize": 20, "total": 134, "totalPages": 7 }

// Cursor style: GET /api/feed?limit=20&cursor=MTA0MA
{ "items": [ ... ], "nextCursor": "MTA2MA", "hasMore": true }

The cursor is often the last id encoded as base64, so clients treat it as an opaque string and do not build their own. When there are no more rows, nextCursor is null.