Pagination

Imagine you have 100,000 blog posts. Returning all of them in a single API response would:

• Increase response size

• Slow down database queries

• Consume more memory

• Create a poor user experience

Instead, we return data in pages:

Page 1 → Posts 1-10
Page 2 → Posts 11-20
Page 3 → Posts 21-30

The two most common approaches are:

1. Offset Pagination

2. Cursor Pagination

Offset pagination

Offset pagination is the simplest and most widely used pagination strategy.

The client tells the server:

• How many records are needed (limit)

• How many records should be skipped (offset)

Let's assume we have 100 posts and we have to show that by using offset pagination and then what we will do every time when a user navigates to the next page initially it will call with offset or number as limit

e.g → /api/post?limit=10&offset=10

{
 "data": [/* posts */], // Actual Data
 "pageInfo": {
 "currentPage": 2,
 "pageSize" : 10,
 "totalItems" : 100, // metadata
 "totalPages" : 10,
 "hasNextPage" : true,
 }
}

so how query works lets say we are in 1st page showing 10 results so now if click on button go to page 2 then we tell backend to that we need 10 posts and we will skip 10 posts and below will be query for that

select id, title from posts limit 10 offset 10; -- for page 2;
select id, title from posts limit 10 offset 20; -- for page 3;

so in backend api service it will look like below

app.get('/posts/offset', (req, res) => {
 const limit = parseInt(req.query.limit) || 10;
 const offset = parseInt(req.query.offset) || 0;
 
 const rows = db.prepare(`
  SELECT * FROM posts
  ORDER BY datetime(created_at) DESC
  LIMIT ? OFFSET ?
 `).all(limit, offset);
 
 const totalItems = db.prepare(`SELECT COUNT(*) as count from posts`).get().count();
 
 res.json(`
 data: rows,
 pageInfo: {
 currentPage: Math.floor(offset / limit) + 1,
 pageSize: limit,
 totalItems,
 totalPages: Math.ceil(totalItems / limit),
 hasNextPage: offset + limit < totalItems,
 hasPreviousPage: offset > 0,
 }
 `)
});

// in ui 
const [page, setPage] = useState(0);
const [posts, setPosts] = useState<Post[]>([]);
const [pageInfo, setPageInfo] = useState<PageInfo | null>(null);

useEffect(() => {
 fetch(`http://localhost:3000/posts/offset?limit=10&offset=${page*10}`)
 .then((res) => res.json())
 .then((data) => {
 setPosts(data.data);
 setPageInfo(data.pageInfo);
 });
}, [page]);

// so on display data and buttons 

so this how for small to medium size database can work in offset based using 2 parameters it is simple and jump to any page but at large datasets we have to scan all rows so it can be slow and there can be duplication if new items are added while we are fetching

Problems with Offset Pagination

Offset pagination works well for small-to-medium datasets, but it starts to show limitations as your application grows.

1) Performance issues

Consider the following query:

SELECT *
FROM posts
LIMIT 10 OFFSET 100000;

Even though only 10 rows are returned, the database still has to scan and skip the first 100,000 rows before returning the next 10. As the offset grows, query performance degrades because more rows need to be processed.

2) Duplicate or missing records

Imagine a user loads page 1 and sees:

• Post 100

• Post 99

• Post 98

Before they navigate to page 2, a new post gets inserted:

• Post 101

• Post 100

• Post 99

• Post 98

Now when the user requests page 2 using OFFSET, some records may be duplicated or skipped because the dataset shifted between requests.

Cursor based pagination

Cursor based pagination effectively retrieves large datasets by breaking them into smaller pages while implementing we need to keep track of the last seen page for this “cursor“ is sent to the frontend with every response which indicates the start of the next page. this “cursor” value can be returned in the subsequent request indicating where the next results page should start.

choice of cursor should be a parameter which is unique and stable in datasets like timestamp, sequential ID, encoded cursor, hashbased cursor, composite cursor

// next page
SELECT id,
 title,
 created_at,
FROM posts
WHERE datetime(created_at) < '2025-08-08 07:08:00'
ORDER BY datetime(created_at) DESC
LIMIT 10;

// previous page
SELECT id,
 title,
 created_at,
FROM posts
WHERE datetime(created_at) > '2025-08-08 07:08:00'
ORDER BY datetime(created_at) ASC
LIMIT 10;

app.get('/posts/cursor', (req,res) => {
 const limit = parseInt(req.query.limit) || 10;
 const after = req.query.after ? decodeCursor(req.query.after) : null;
 
 let rows;
 if(after){
 rows = db.prepare(`
 SELECT * FROM posts
 WHERE datetime(create_at) < datetime(?) 
 ORDER BY datetime(created_at) DESC
 LIMIT ? 
 `).all(after, limit);
 else{
 rows = db.prepare(`
 SELECT * FROM posts
 ORDER BY datetime(created_at) DESC
 LIMIT ? 
 `).all(limit);
 }
 
 const nextCursor = rows.length === limit
 ? encodeCursor(rows[rows.length - 1].created_at)
 : null;
 
 res.json({
 data:rows,
 next_cursor: nextCursor
 })
});

const [cursor, setCursor] = useState(null);
const [posts, setPosts] = useState<Post[]>([]);

const fetchPage = (after = null) => {
 const url = after 
 ? `http://localhost:3000/posts/cursor?limit=10&after=${after}`
 : `http://localhost:3000/posts/cursor?limit=10`;
 
 fetch(url)
 .then((res) => res.json())
 .then((data) => {
 setPosts(data.data);
 setCursor(data.next_cursor);
 });
};

useEffect(() => {
 fetchPage(null);
},[]);

Advantages:

• A good option for infinite scroll.

• Cursor-based pagination provides stable pagination results even when the underlying dataset changes.

Disadvantages:

• Random access of pages is not possible

• Complicated to implement than offset limit pagination.

So pagination is not just ui component it is convention b/w your ui and backend

Which One Should You Use?

Use Offset Pagination when:

• Building admin dashboards

• Users need page numbers

• Dataset is relatively small

• Simplicity matters

Use Cursor Pagination when:

• Building infinite scroll

• Dataset is large

• New records are added frequently

• Performance is critical

Examples:

• Blog archive - Offset

• Ecommerce category pages - Offset

• Admin panel - Offset

• Instagram feed - Cursor

• Twitter/X timeline - Cursor

• Chat messages - Cursor

• Activity logs - Cursor

Check out original article- https://theyashpatel.com/blog/pagination and more https://theyashpatel.com/blog