Cursor-Based vs Offset-Based Pagination: A quick overview

Cursor vs Offset Pagination — Which One Should You Use?

Pagination is one of those things that looks easy until your database starts sweating at 3 AM.

Let's break it down simply — no PhD required.

---

The Restaurant Analogy

Imagine you're at a buffet with 1,000 dishes lined up.

Offset is like saying:

"Skip the first 500 dishes, then hand me the next 10."

The waiter has to walk past every single dish — counting one by one — just to get to dish #501. The further you go, the more work they do. Page 1 is instant. Page 5,000 is painful.

Cursor is like saying:

"Start from the dish right after the Chicken Tikka Masala."

The waiter walks directly to that dish and grabs the next 10. Doesn't matter if you're at dish 10 or dish 10,000 — same effort, same speed.

---

The Problem with Offset

-- Page 1: fast
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 0;

-- Page 1,000: database is not happy
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 9990;

PostgreSQL has to scan through 9,990 rows just to throw them away. The bigger the offset, the worse it gets.

Page	Offset	Query Time
1	0	~0.07ms
100	990	~1.3ms
10,000	99,990	~128ms

That's 1,800× slower from page 1 to page 10,000. And it keeps getting worse.

It Also Lies to Your Users

Say a user is on page 1. A new record gets inserted. They click "Next Page." Suddenly a row appears twice — because all positions shifted by one.

Or a row gets deleted and a row silently disappears between pages.

Offset doesn't paginate over data — it paginates over positions. Positions are unstable.

---

How Cursor Pagination Works

Instead of "skip N rows", you say "give me rows after this specific row."

-- First page (no cursor needed)
SELECT user_id, email, created_at
FROM users
ORDER BY created_at DESC, user_id DESC
LIMIT 10;

-- Next page (cursor = last row you saw)
SELECT user_id, email, created_at
FROM users
WHERE
  created_at < '2024-01-15 10:30:00'
  OR (created_at = '2024-01-15 10:30:00' AND user_id < 'abc-123')
ORDER BY created_at DESC, user_id DESC
LIMIT 10;

PostgreSQL hits the index directly and fetches only what it needs. Every page is as fast as the first.

Page	Offset Time	Cursor Time
1	0.07ms	0.07ms
100	1.3ms	0.08ms
10,000	128ms	0.08ms

Consistent. Every time.

---

One Rule: Use a Composite Cursor

Never cursor on just a timestamp. If two rows share the same created_at, you'll skip rows silently.

Always combine the timestamp with a unique ID:

-- ❌ Risky — ties in created_at will cause skipped rows
WHERE created_at < $1

-- ✅ Safe — (created_at, user_id) is always unique
WHERE created_at < $1
  OR (created_at = $1 AND user_id < $2)
ORDER BY created_at DESC, user_id DESC

And make sure you have a matching composite index:

CREATE INDEX idx_users_cursor ON users (created_at DESC, user_id DESC);

Without that index, you lose all the performance benefits.

---

Quick Drizzle ORM Example

async function getUsers(cursor?: { createdAt: Date; userId: string }) {
  let query = db
    .select()
    .from(users)
    .orderBy(desc(users.createdAt), desc(users.userId))
    .limit(11); // fetch one extra to detect hasNextPage

  if (cursor) {
    query = query.where(
      or(
        lt(users.createdAt, cursor.createdAt),
        and(
          eq(users.createdAt, cursor.createdAt),
          lt(users.userId, cursor.userId)
        )
      )
    );
  }

  const rows = await query;
  const hasNextPage = rows.length > 10;
  const data = hasNextPage ? rows.slice(0, 10) : rows;

  return {
    data,
    nextCursor: hasNextPage
      ? { createdAt: data.at(-1)!.createdAt, userId: data.at(-1)!.userId }
      : null,
    hasNextPage,
  };
}

Encode the cursor as base64url when sending it over the API so it's opaque to clients.

---

Which One Should You Pick?

Use Case	Recommendation
Admin dashboard with page numbers	Offset is fine
Small table (< 10k rows)	Offset is fine
Public API	Cursor
Mobile infinite scroll	Cursor
Large dataset	Cursor
Real-time feed	Cursor

Short version: If you're building anything public-facing or with a large dataset, use cursor pagination. If it's an internal admin panel with a small table, offset is perfectly acceptable.

---

TL;DR

• Offset = "skip N rows" → gets slower the deeper you go, can show duplicate/missing rows
• Cursor = "start after this row" → always fast, always consistent
• Use a composite cursor (timestamp + unique ID) to avoid ties
• Add a composite index that matches your ORDER BY clause

Pagination isn't exciting — but getting it right means your users never notice it. That's the goal.

Happy building!