Cursor vs offset pagination
Your bookstore has grown past the point where you can return the whole catalog in one response. You need pagination. There are two common approaches, and only one of them stays fast as the catalog grows.
SELECT * FROM books
ORDER BY id
OFFSET 10000
LIMIT 20;OFFSET works, but the database reads and skips every row you are paging past.
Read what that actually does. Postgres finds the first row, reads it, discards it. Reads the next row, discards it. Keeps going for 10000 rows. Then finally returns 20. The cost grows with the offset. On page 1 it is instant. On page 500 it is painful. On page 5000 it kills your database.
SELECT * FROM books
WHERE id > 42
ORDER BY id
LIMIT 20;A cursor uses an index to jump straight to the next page.
The cursor version jumps directly to id=42 via the primary key index and reads the next 20 rows. Same cost on page 1 and page 5000. The tradeoff: no direct jump to page 500. Users can scroll forward and backward, but they cannot type a page number. For feed-style interfaces (infinite scroll, timelines, catalogs), that is exactly the tradeoff you want.
export async function getBooksHandler(req: Request, res: Response) {
const cursor = req.query.cursor ? parseInt(req.query.cursor as string, 10) : 0;
const limit = 20;
const items = await db
.select()
.from(books)
.where(gt(books.id, cursor))
.orderBy(asc(books.id))
.limit(limit + 1);
const hasMore = items.length > limit;
const data = hasMore ? items.slice(0, limit) : items;
const nextCursor = hasMore ? data[data.length - 1].id : null;
res.json({ data, nextCursor, hasMore });
}A cursor response includes the next cursor so the client knows what to ask for next.
Quiz: Quiz
Loading practice…