Understanding Offset and Cursor Pagination: Pros, Cons, and Practical UX Examples

How would you solve infinite scroll? With pagination, right? Okay, so we pick e.g 3 items every time and load the next section of them until we get them all. Done ✔ Classic Offset pagination.

With prisma ( an ORM tool for JS/TS ), you would make the request like so:

const results = await prisma.items.findMany({
  skip: 3,
  take: 3,
})

BUT… what if we want to get data from two completely different tables? And they only have a date in common, and you want them to be sorted? Can we just pick 10 from one and 10 from the other? NO!

Lets think in dates: Imagine getting 3 entries from July only ( first table ) and then 3 entries from July and September mixed! ( second table ). Then we load the next page and get more from July on the first dataset, but already September data on the second dataset. This will result in a date mismatch, with nothing truly sorted. 😭

And dont start with “we can sort in the frontend afterwards”. Imagine having a lot of data and then sorting the whole thing after each fetch… not ideal, right?

So, should we just paginate by passing a date frame instead of a page? Then we might fetch from August to September initially, but what if there's no data in that time frame? To trigger a fetch with infinite scroll, we need to get to the end of the list / screen. And just calling the endpoint until you finally get the desired data does not sound very elegant. Then we might get jagged loading indicators. Meh. 😭

So, what solution would consider all edge cases and work in all scenarios?

Cursor-based pagination! ✨

I hit this exact wall. And i want to help you so you don’t need to undergo the same hell! In this post, I’ll break down offset vs. cursor pagination. When each works, when it breaks, and how cursors quietly fixed my Timeline. Bonus: NestJS + Prisma code you can paste in. Let’s dig in!

1. Introduction: Why this topic matters

This might sound basic to some, but for me it was a total “ohhh, so that’s how it should work” moment. Figured I’d share, so you can skip the headaches I went through. ✍️

You might be thinking: “Why would we even merge two tables with different schemas and creation dates into one endpoint? Who does that?” Well… sometimes you can’t just cram everything into one neat schema. Sure, an audit log with title, message, and date sounds clean. But when the customer wants extra info on certain entries, and you don’t want to dump JSON blobs into a message field, you need a different plan.

JSON columns? Yeah, nah. They’re messy, fragile, and a pain to migrate when schemas change. Keeping separate tables for detailed data and merging them only when needed is usually cleaner.

You might think “But you could just make two different endpoints, and then merge it in the frontend” Well that kills lazy loading and proper pagination.

See, i thought the exact way, but trust me, cursor-based pagination is not that tricky, and soooo easy to implement. Let me show you how!

---

2. What is Offset Pagination?

With offset pagination, you can define the page you want to load, and how many items that page should contain. In Prisma you would use skip and take, like shown below.

Example with NestJS + Prisma:

async getItems(page: number, limit: number) {
  const skip = (page - 1) * limit;
  return this.prisma.item.findMany({
    skip,
    take: limit,
    orderBy: { createdAt: 'desc' },
  });
}

Pros:

• Easy to understand and implement

• Fine for small, static datasets

• You can jump to any page immediately.

• You can paginate in any sort order. For example sorting by first name etc.

Cons:

• Performance drops with high skip values

• Offset pagination does not scale at database level.If you skip 300,000 records, the database STILL needs to traverse the first 300,000 records before returning your desired 10 items. This is bad for performance

Use Case:

• Shallow pagination of a small result set. For example blog posts.

---

3. What is Cursor-Based Pagination?

Cursor-based pagination uses cursor and take to return a limited set of results before or after a given cursor. The cursor can be a ID or a timestamp.

const myCursor = lastId; // Example: 52 
const secondQueryResults = await prisma.post.findMany({
  take: 4,
  skip: 1,
  cursor: {
    id: myCursor,
  },
})

// Example: 1, 3, 4, 52, 10, 7, 100, 89, 62
// With cursor based pagination we would return: 10, 7, 100, 89
// ( since we skipped 52 itself )

• Instead of OFFSET, use a unique field like createdAt or id

• Remembers the "last seen item" (cursor) and continues from there

Pros:

• Infinite scroll with merged datasets

• It scales and is better for performance

Cons:

• Sorting is only possible by cursor, which has to be unique.

• You cannot jump to a specific page, you will need to get all data in order.

Use Case:

• Infinite Scroll!

• Paging through an result set in batches.

---

4. Time-Range Pagination

I thought: “Why not just paginate by day or week?” But what if there’s no data in that range? Right: empty page. Terrible UX. Cursor-based pagination skips empty ranges and shows the next real items instead. Super neat, right?

So don’t do time-ranged pagination, its super weird to test and creates bugs.

---

5. Real-World Example: Merged timeline from multiple tables

Let’s think about one example: Audit logs for social media. You want to see chronologically when a post got created and when comments got in. They are not grouped. You just see a list of: ok there was a post, then 10 comments on that post, then another post, 2 comments on the older post again, 4 on the new one and so on. So → we have two tables. Both tables contain a creationDate.

We want to merge posts and comments in one feed, sorted chronologically. Problem:

• OFFSET grabs 10 posts + 10 comments → what if the first 10 comments we load where created after the first 30 posts? Weird example, BUT it can happen. Then we have a chronological problem. Just like the simple example i showed at the start of this blog!

Cursor solves this:

• Fetch e.g. 10 items per table before a timestamp cursor ( first one is now )

• Merge → sort by date → trim to 10 → save new cursor → repeat

This is a neat example: ( fetching 3 items, and trimming at 3 )

---

6. Code: Cursor Pagination with Prisma & NestJS

This is how the logic could look like for our problem with merging two databases.

Its a special case, but it works!

async getTimeline(cursor?: string, limit = 10) {
  const where = cursor ? { createdAt: { lt: new Date(cursor) } } : {};

  const [posts, comments] = await Promise.all([
    this.prisma.post.findMany({ where, take: limit, orderBy: { createdAt: 'desc' } }),
    this.prisma.comment.findMany({ where, take: limit, orderBy: { createdAt: 'desc' } }),
  ]);

  const merged = [...posts.map(p => ({ ...p, type: 'post' })), ...comments.map(c => ({ ...c, type: 'comment' }))];
  const sorted = merged.sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime());
  const items = sorted.slice(0, limit);
  const nextCursor = items.at(-1)?.createdAt.toISOString() ?? null;

  return { items, nextCursor };
}

---

7. Conclusion

Offset isn’t “wrong” — but often not the right tool. If your use case is more complex than “show me the next 10 items,” cursor-based pagination is worth considering. It’s more robust, more performant, and… a real UX lifesaver. Also, usual infinite scroll with no sorting logic will love this pagination method!

And hey — now you know that pagination is more than just LIMIT and OFFSET. 😉

Write your thoughts in the comments below!

Have a nice day!