August 30, 2026 57 minutes minutes read Admin

Cursor-Based Pagination vs Offset Pagination

The Problem: Returning Large Amounts of Data

Imagine an API that returns orders:

GET /orders

At first, the implementation seems simple:

SELECT *
FROM orders;

But what happens when the database contains:

10 orders

Easy.

What about:

100,000 orders

Still manageable.

What about:

100 million orders

Returning all of them in one HTTP response is obviously not practical.

The API needs to return the data in smaller chunks.

This is where pagination comes in.

Instead of:

100,000 records
        |
        v
      API
        |
        v
  100,000 records

we want:

100,000 records
        |
        v
      API
        |
        +----> 20 records
        |
        +----> 20 records
        |
        +----> 20 records
        |
        +----> ...

The question is:

How does the API decide which 20 records to return next?

There are two major approaches:

Pagination
    |
    +-- Offset pagination
    |
    +-- Cursor pagination

Understanding the difference is important because pagination is not merely an API design detail.

It directly affects:

  • database performance

  • API consistency

  • scalability

  • memory usage

  • user experience

  • behavior when data changes

  • index design

Offset Pagination

Offset pagination is probably the first pagination technique most developers encounter.

The client tells the API:

Give me N records
after skipping M records.

For example:

GET /orders?offset=0&limit=20

The database query might be:

SELECT *
FROM orders
ORDER BY id
LIMIT 20
OFFSET 0;

The next request:

GET /orders?offset=20&limit=20

becomes:

SELECT *
FROM orders
ORDER BY id
LIMIT 20
OFFSET 20;

And the third:

GET /orders?offset=40&limit=20

becomes:

SELECT *
FROM orders
ORDER BY id
LIMIT 20
OFFSET 40;

Conceptually:

Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
...

Request 1

[1  2  3  4  5]

Request 2

[6  7  8  9  10]

Request 3

[11  12  13  14  15]

The API is navigating through the dataset by position.

Page Number Pagination

Offset pagination is often exposed using page numbers instead of explicit offsets.

For example:

GET /orders?page=1&size=20

The application calculates:

offset = (page - 1) × size

So:

page 1 → offset 0
page 2 → offset 20
page 3 → offset 40
page 4 → offset 60

The SQL becomes:

SELECT *
FROM orders
ORDER BY id
LIMIT 20
OFFSET 40;

This is still offset pagination.

The API is simply hiding the offset calculation from the client.

Why Offset Pagination Is Attractive

Offset pagination has several advantages.

It is simple.

The API is easy to understand:

GET /orders?page=5&size=20

The database query is also straightforward:

LIMIT 20 OFFSET 80

It is also easy to build traditional UI pagination:

← Previous

1  2  3  4  5  ...  100

Next →

You can also calculate:

total records
total pages
current page

For example:

{
  "data": [...],
  "page": 5,
  "size": 20,
  "totalElements": 10000,
  "totalPages": 500
}

This makes offset pagination very useful for:

  • admin dashboards

  • back-office applications

  • reports

  • searchable tables

  • relatively small datasets

  • applications where users need direct access to page numbers

So offset pagination is not inherently bad.

The problem appears when we start dealing with large and frequently changing datasets.

The Performance Problem With Large Offsets

Consider a database containing:

10,000,000 orders

Now the client requests:

GET /orders?offset=9,000,000&limit=20

The SQL is:

SELECT *
FROM orders
ORDER BY id
LIMIT 20
OFFSET 9000000;

The database has to determine where the requested position is.

Conceptually:

1
2
3
4
5
...
8,999,998
8,999,999
9,000,000
9,000,001
9,000,002
...

The database needs to process the preceding rows/index entries to get to the requested position.

The exact execution behavior depends on the database, indexes, query plan, and ordering, but the important engineering principle is:

Large offsets generally become increasingly expensive because the database still has to walk past the rows being skipped.

This means:

OFFSET 0
    ↓
OFFSET 1,000
    ↓
OFFSET 100,000
    ↓
OFFSET 1,000,000
    ↓
OFFSET 9,000,000

can become increasingly expensive.

This is one of the fundamental scalability problems with offset pagination.

The More Serious Problem: Data Changes

Performance isn't the only problem.

Offset pagination can also become inconsistent when the underlying data changes between requests.

Consider this dataset:

1
2
3
4
5
6
7
8
9
10

Suppose the page size is 5.

The client requests page 1:

1
2
3
4
5

The client then requests page 2.

But before the second request executes, a new record is inserted at the beginning:

0
1
2
3
4
5
6
7
8
9
10

The second request is:

SELECT *
FROM orders
ORDER BY id
LIMIT 5
OFFSET 5;

The database now starts after:

0
1
2
3
4

and returns:

5
6
7
8
9

The client already received:

5

on the first page.

So the same record can appear twice.

Conceptually:

Page 1

1
2
3
4
5
        ↑
      seen

New record inserted

Page 2

5
6
7
8
9
↑
duplicate

This is called a pagination drift problem.

Records Can Also Be Skipped

Deletions can cause the opposite problem.

Suppose page 1 contains:

1
2
3
4
5

Then record 2 is deleted.

The dataset becomes:

1
3
4
5
6
7
8
9

Page 2 still uses:

OFFSET 5

But the positions have shifted.

The API can now skip a record that should have been returned.

So offset pagination can suffer from:

Insertions
    ↓
Duplicates

Deletions
    ↓
Missing records

This becomes particularly important for APIs dealing with data that changes continuously.

Examples include:

Orders
Transactions
Messages
Notifications
Events
Activity feeds
Social media posts
Logs

Cursor-Based Pagination

Cursor pagination approaches the problem differently.

Instead of saying:

Give me records after position 1000.

the client says:

Give me records after this particular record.

Suppose the first request returns:

1
2
3
4
5

The API gives the client a cursor representing record 5.

The next request becomes:

GET /orders?cursor=5&limit=5

The database can execute:

SELECT *
FROM orders
WHERE id > 5
ORDER BY id
LIMIT 5;

Now the database isn't asking:

What is record number 6?

It is asking:

Find records whose id is greater than 5.

Conceptually:

1
2
3
4
5 ← cursor
|
v
6
7
8
9
10

The next page is:

6
7
8
9
10

Cursor Pagination Is About Position by Value

This is the fundamental difference.

Offset pagination:

OFFSET 1000

means:

Skip the first 1000 positions.

Cursor pagination:

WHERE id > 1000

means:

Find records after this known key.

This changes how the database can navigate the data.

With an appropriate index, the database can seek into the index around the cursor value.

Conceptually:

Index

1
2
3
...
999,998
999,999
1,000,000 ← seek
1,000,001
1,000,002
1,000,003
...

The database doesn't need to treat the entire preceding result set as the requested page's offset.

This is why cursor/keyset pagination is generally much better suited to deep pagination over large indexed datasets.

Cursor Pagination and Inserts

Now consider the earlier insertion problem.

The first page returns:

1
2
3
4
5

The cursor is:

5

Then a new record is inserted:

0

The database becomes:

0
1
2
3
4
5
6
7
8
9

The next request is still:

SELECT *
FROM orders
WHERE id > 5
ORDER BY id
LIMIT 5;

The result is:

6
7
8
9

The cursor still means:

Start after ID 5.

The insertion of records before the cursor doesn't shift that position.

This is a major advantage of cursor pagination.

Cursor Pagination and Deletes

Suppose:

1
2
3
4
5

was the first page.

Then record 2 is deleted.

The next request remains:

WHERE id > 5

and therefore still starts from:

6
7
8
9
10

The deletion of earlier records doesn't change the meaning of the cursor.

This gives cursor pagination a much more stable traversal model.

But There Is an Important Requirement

Cursor pagination requires a stable ordering.

Consider:

ORDER BY created_at DESC

Suppose several records have the same timestamp:

id     created_at

101    10:00:00
102    10:00:00
103    10:00:00
104    09:59:59

If the cursor contains only:

created_at = 10:00:00

which of these records should the next page start after?

There isn't enough information.

The timestamp isn't unique.

Therefore, cursor pagination usually needs a deterministic ordering.

A common solution is:

ORDER BY created_at DESC, id DESC

Now the ordering is:

created_at      id

10:00:00       103
10:00:00       102
10:00:00       101
09:59:59       104

The id acts as a tie-breaker.

This is extremely important.

The Cursor Can Contain Multiple Values

If the ordering is:

ORDER BY created_at DESC, id DESC

the cursor needs to represent both:

created_at
id

For example:

{
  "createdAt": "2026-08-30T10:00:00Z",
  "id": 103
}

The next query conceptually becomes:

SELECT *
FROM orders
WHERE
    created_at < :createdAt
    OR (
        created_at = :createdAt
        AND id < :id
    )
ORDER BY created_at DESC, id DESC
LIMIT 20;

This is the essence of keyset pagination.

Cursor Pagination vs Keyset Pagination

The terms are often used interchangeably, but there is a useful distinction.

Keyset pagination describes the database technique.

For example:

WHERE id > :lastId

or:

WHERE
    created_at < :createdAt
    OR (
        created_at = :createdAt
        AND id < :id
    )

Cursor pagination describes how the API exposes that position.

Instead of exposing:

GET /orders?afterId=12345

the API can expose:

GET /orders?cursor=eyJpZCI6MTIzNDV9

The cursor might internally contain:

{
  "id": 12345
}

or:

{
  "createdAt": "2026-08-30T10:00:00Z",
  "id": 12345
}

The client doesn't need to know.

This is generally a better API abstraction.

Why Cursors Should Usually Be Opaque

Avoid making clients depend on the internal representation of the cursor.

For example, this isn't ideal:

GET /orders?afterId=12345

because now the client knows that the database's primary key is being used as the pagination mechanism.

Instead:

GET /orders?cursor=eyJpZCI6MTIzNDV9

The server owns the cursor format.

Today it might contain:

{
  "id": 12345
}

Tomorrow you might change the ordering to:

{
  "createdAt": "...",
  "id": 12345
}

The client doesn't need to change.

The cursor is simply:

An opaque continuation token.

A Typical Cursor-Based API

A request:

GET /orders?limit=20

might return:

{
  "data": [
    {
      "id": 101,
      "amount": 500
    },
    {
      "id": 102,
      "amount": 750
    }
  ],
  "pagination": {
    "nextCursor": "eyJpZCI6MTAy...",
    "hasNext": true
  }
}

The client then sends:

GET /orders?limit=20&cursor=eyJpZCI6MTAy...

The server decodes the cursor and performs the appropriate keyset query.

The next response might contain:

{
  "data": [
    {
      "id": 103,
      "amount": 100
    }
  ],
  "pagination": {
    "nextCursor": "eyJpZCI6MTAz...",
    "hasNext": false
  }
}

The client doesn't need to understand what the cursor means.

Why hasNext Is Often Better Than totalPages

With offset pagination, APIs commonly return:

{
  "page": 5,
  "size": 20,
  "totalElements": 10000,
  "totalPages": 500
}

Cursor APIs usually don't need to expose that.

Instead:

{
  "data": [...],
  "pagination": {
    "nextCursor": "...",
    "hasNext": true
  }
}

Why?

Because determining:

How many records exist?

can require an expensive:

SELECT COUNT(*)

For large or complicated queries, the count can itself be expensive.

Cursor pagination is naturally oriented around:

Give me the next chunk.

rather than:

Tell me exactly how many pages exist.

The LIMIT + 1 Technique

A common implementation trick is to request one more record than the client asked for.

Suppose the client requests:

limit = 20

The database query uses:

LIMIT 21

If 21 records are returned:

20 records → return to client
1 record    → proves another page exists

So:

Database

21 records
    |
    +-- first 20 → response
    |
    +-- 21st → hasNext = true

If only 20 or fewer records are returned:

hasNext = false

This avoids requiring a separate count query just to determine whether another page exists.

The Importance of Indexes

Cursor pagination is not automatically fast.

The database still needs an appropriate index.

Suppose we query:

SELECT *
FROM orders
WHERE id > :cursor
ORDER BY id
LIMIT 20;

If id is the primary key, we already have a suitable index.

But consider:

SELECT *
FROM orders
WHERE customer_id = :customerId
  AND id > :cursor
ORDER BY id
LIMIT 20;

A useful index might be:

CREATE INDEX idx_orders_customer_id_id
ON orders(customer_id, id);

The query pattern should influence the index design.

For:

WHERE customer_id = ?
AND id > ?
ORDER BY id
LIMIT 20

the database needs to efficiently navigate:

customer_id
      +
      id

Pagination design and database indexing are therefore closely related.

Composite Cursor Pagination

Real APIs often sort by something other than a simple ID.

For example:

ORDER BY created_at DESC, id DESC

The cursor might represent:

created_at = 2026-08-30T10:15:00Z
id = 500

The next query becomes:

SELECT *
FROM orders
WHERE
    created_at < :createdAt
    OR (
        created_at = :createdAt
        AND id < :id
    )
ORDER BY created_at DESC, id DESC
LIMIT 20;

The important idea is:

Sort fields
     |
     v
Cursor contains enough information
     |
     v
Next query resumes exactly after that position

If the ordering uses three fields, the cursor may need to contain all three.

Why ORDER BY Is Not Optional

Never build pagination around an unordered query such as:

SELECT *
FROM orders
LIMIT 20
OFFSET 20;

without defining the ordering.

A relational database does not promise that rows will naturally appear in a stable order unless an appropriate ORDER BY is specified.

Pagination depends on knowing exactly what:

first
next
previous

mean.

Therefore, pagination should normally look like:

ORDER BY id

or:

ORDER BY created_at DESC, id DESC

not:

SELECT *
FROM orders;

with an assumed natural order.

Previous-Page Pagination

Cursor pagination isn't limited to "next page".

Suppose:

ORDER BY id ASC

and the cursor represents:

id = 100

Next page:

WHERE id > 100
ORDER BY id ASC
LIMIT 20;

For the previous page, the query can reverse the direction:

WHERE id < 100
ORDER BY id DESC
LIMIT 20;

Then reverse the results before returning them.

Conceptually:

Previous

80 81 82 83 84
        ↑
      cursor

Next

101 102 103 104 105

Cursor APIs therefore don't inherently mean:

You can only move forward.

They simply require a more deliberate API design for backward navigation.

Why Cursor Pagination Is Excellent for Infinite Scrolling

Consider a social media feed.

The user sees:

Post 100
Post 99
Post 98
...
Post 81

Then scrolls down.

The browser requests:

GET /posts?cursor=...

The server returns:

Post 80
Post 79
...
Post 61

Then:

GET /posts?cursor=...

and so on.

There is no concept of:

Page 17
Page 18
Page 19

The application simply continues from where it stopped.

This is exactly the problem cursor pagination is designed for.

Offset vs Cursor: The Fundamental Difference

The easiest way to remember the difference is:

OFFSET

"Skip N records."

                    ↓

Position-based

versus:

CURSOR

"Continue after this record."

                    ↓

Key-based

Or:

Offset

dataset
  |
  +-- position 0
  +-- position 20
  +-- position 40
  +-- position 60

Cursor

dataset
  |
  +-- after ID 20
  +-- after ID 40
  +-- after ID 60

The second model is much more stable when the dataset changes.

Performance Comparison

Suppose there are 10 million records.

Offset

LIMIT 20 OFFSET 9000000

The database must work through a huge offset.

Cursor

WHERE id > 9000000
LIMIT 20

With an index on id, the database can seek into the index and scan forward.

Conceptually:

Offset

start
 |
 v
1 → 2 → 3 → 4 → ... → 9,000,000 → 9,000,001
                                           |
                                         result

Cursor

                         index seek
                              |
                              v
1 ... 8,999,999 → 9,000,000 → 9,000,001
                                  |
                                result

The difference becomes increasingly important as datasets grow.

But Cursor Pagination Has Trade-offs

Cursor pagination isn't universally better.

It makes some things harder.

1. Random Page Access

With offset pagination:

GET /orders?page=100

is easy.

With cursor pagination:

Give me page 100

doesn't naturally fit the model.

You need to traverse cursors sequentially or use another mechanism.

2. Total Counts

Offset pagination naturally fits:

Page 10 of 500

Cursor pagination is usually designed around:

Here is your next page.

rather than:

There are exactly 500 pages.

3. Cursor Complexity

Simple:

WHERE id > ?

is easy.

But:

ORDER BY created_at DESC, priority DESC, id DESC

requires a more complicated cursor and query.

4. API Design Is More Complex

You need to think about:

Cursor format
Cursor expiration
Invalid cursors
Sort order
Forward pagination
Backward pagination
Filtering
Indexes

This is more work than:

?page=5&size=20

Cursor and Filtering

This is an important real-world problem.

Suppose the client requests:

GET /orders?status=COMPLETED&cursor=...

The cursor must be meaningful for that query.

If the client changes the filter:

GET /orders?status=PENDING&cursor=...

the previous cursor may no longer make sense.

A robust API should treat the cursor as belonging to a particular query shape.

A cursor may therefore encode information such as:

{
  "position": {
    "createdAt": "2026-08-30T10:00:00Z",
    "id": 500
  },
  "sort": "created_at_desc",
  "filter": {
    "status": "COMPLETED"
  }
}

You don't necessarily have to encode all of this literally, but the server should ensure that a cursor isn't incorrectly reused with incompatible query parameters.

Cursor Encoding

A cursor is commonly encoded.

For example, the internal representation:

{
  "createdAt": "2026-08-30T10:00:00Z",
  "id": 500
}

could become:

eyJjcmVhdGVkQXQiOiIyMDI2LTA4LTMwVDEwOjAwOjAwWiIsImlkIjo1MDB9

The exact encoding isn't important.

The important property is:

The client should treat the cursor as opaque.

It should not attempt to construct or modify it.

Should Cursors Be Encrypted?

Not necessarily.

Encoding and encryption solve different problems.

Base64 encoding:

JSON
 ↓
Base64

is not encryption.

If the cursor contains only:

{
  "id": 500
}

there may be no security problem in exposing that information.

But if the cursor contains sensitive information, or if you don't want clients to manipulate it, you may use:

signed cursor

or:

encrypted cursor

The important thing is to decide what the cursor is allowed to reveal and whether it must be tamper-resistant.

Cursor Expiration

Some systems make cursors short-lived.

For example:

Cursor generated
       |
       v
valid for 15 minutes
       |
       v
expired

This can be useful when cursors contain:

  • temporary state

  • snapshots

  • signed query information

  • server-side references

But cursor expiration isn't inherently required.

A cursor can simply represent a stable database position.

The choice depends on the API's semantics.

Cursor Pagination in Spring Data

Suppose we have:

@Entity
public class Order {

    @Id
    private Long id;

    private Instant createdAt;
}

A simple repository query could be:

@Query("""
    SELECT o
    FROM Order o
    WHERE o.id > :cursor
    ORDER BY o.id ASC
""")
List<Order> findAfter(
        @Param("cursor") Long cursor,
        Pageable pageable);

The service might look conceptually like:

public OrderPage getOrders(Long cursor, int limit) {

    Pageable pageable =
            PageRequest.of(0, limit + 1);

    List<Order> orders;

    if (cursor == null) {
        orders = repository.findFirstPage(pageable);
    } else {
        orders = repository.findAfter(cursor, pageable);
    }

    boolean hasNext = orders.size() > limit;

    if (hasNext) {
        orders = orders.subList(0, limit);
    }

    String nextCursor = hasNext
            ? encodeCursor(orders.getLast().getId())
            : null;

    return new OrderPage(
            orders,
            nextCursor,
            hasNext
    );
}

The important part isn't the Java syntax.

The important flow is:

Request
   |
   v
Decode cursor
   |
   v
Construct keyset query
   |
   v
Fetch limit + 1
   |
   v
Determine hasNext
   |
   v
Return limit records
   |
   v
Create next cursor

A Good REST API Design

A clean cursor-based API might look like:

GET /api/orders?limit=20

Response:

{
  "data": [
    ...
  ],
  "pagination": {
    "nextCursor": "eyJpZCI6MTAy...",
    "hasNext": true
  }
}

Next request:

GET /api/orders?limit=20&cursor=eyJpZCI6MTAy...

This is much more expressive than exposing database offsets.

What About page and cursor Together?

Usually don't do this:

GET /orders?page=5&cursor=...

They represent different pagination models.

Choose one.

Offset:

GET /orders?page=5&size=20

or:

GET /orders?offset=80&limit=20

Cursor:

GET /orders?cursor=...&limit=20

Mixing the two tends to make the API confusing.

What About limit?

A cursor does not replace the page size.

You still generally need:

?cursor=...&limit=20

The cursor answers:

Where should I continue?

The limit answers:

How many should I return?

These are two separate concerns.

What Should the Maximum Limit Be?

Don't blindly allow:

GET /orders?limit=1000000

Otherwise a client can turn a pagination API into a resource-exhaustion mechanism.

A common approach is:

Requested limit
      |
      v
minimum 1
maximum 100

For example:

int effectiveLimit = Math.min(requestedLimit, 100);

The exact maximum depends on the API and workload.

Pagination is partly a resource-control mechanism.

Pagination and Backpressure

This connects pagination to a larger distributed-systems concept.

Suppose a client requests:

1,000,000 records

and the API allows it.

The application may need to:

read huge result set
       |
       v
allocate memory
       |
       v
serialize huge JSON
       |
       v
send huge response

This can increase:

Memory
CPU
Network bandwidth
Database load
Latency

A reasonable page-size limit prevents clients from overwhelming the system.

So pagination isn't merely a UI convenience.

It is also a form of resource management.

Pagination and Database Transactions

Another subtle issue is consistency.

Suppose a client reads:

Page 1
Page 2
Page 3

These requests are usually separate HTTP requests.

Therefore, they aren't automatically part of one database transaction.

The dataset may change between pages.

Cursor pagination provides a stable traversal boundary, but it does not magically create a database snapshot across multiple HTTP requests.

This distinction is important.

Cursor pagination gives you:

Stable continuation position

not necessarily:

A globally consistent snapshot of the database

If an application requires a true snapshot across a long-running traversal, that is a different problem involving database isolation, snapshots, materialized datasets, or other techniques.

Pagination and Distributed Systems

In a distributed system, the problem becomes even more interesting.

Imagine:

API
 |
 +-- Service A
 |
 +-- Service B
 |
 +-- Database

If the API aggregates data from multiple sources, a simple database cursor may not be enough.

For example:

Orders
Payments
Shipments

may each have different ordering and pagination semantics.

The API may need to coordinate multiple cursors:

{
  "ordersCursor": "...",
  "paymentsCursor": "...",
  "shipmentsCursor": "..."
}

This is another reason to treat cursors as an API abstraction rather than simply exposing database internals.

Offset Pagination Is Still the Right Choice Sometimes

There is a tendency to say:

Cursor pagination is better, therefore always use cursor pagination.

That's too simplistic.

Use offset pagination when:

Dataset is relatively small
        +
Users need page numbers
        +
Random page access matters
        +
Total counts are useful

Examples:

Admin dashboard
Product management
User management
Reporting UI
Search results with page navigation

Cursor pagination becomes more attractive when:

Dataset is large
        +
Data changes frequently
        +
Deep pagination is common
        +
Infinite scrolling is required
        +
You want efficient sequential traversal

Examples:

Transaction history
Activity feeds
Message history
Event streams
Notifications
Large order lists
Logs
Social feeds

A Practical Decision Table

Requirement Offset Cursor
Simple implementation Excellent Good
Page numbers Excellent Poor
Random page access Excellent Poor
Total pages Excellent Poor
Small dataset Excellent Good
Huge dataset Poor Excellent
Deep pagination Poor Excellent
Frequently changing data Risky Better
Infinite scrolling Good Excellent
Stable sequential traversal Fair Excellent
Complex ordering Easier More complex
API complexity Low Higher
Database scalability Can degrade Generally better

The Most Important Design Question

When designing pagination, don't start by asking:

Which pagination library should I use?

Start with:

How is this data ordered, and how will clients consume it?

For example:

Traditional administration

User clicks:

Page 1
Page 2
Page 3
...
Page 100

Offset pagination is natural.

Transaction history

Load newest 50
       |
       v
Load next 50
       |
       v
Load next 50

Cursor pagination is usually more appropriate.

Infinite feed

Scroll
  |
  v
cursor
  |
  v
next records
  |
  v
scroll
  |
  v
cursor

Cursor pagination is almost exactly the abstraction we want.

A Common Mistake: Using ID as a Cursor Without Thinking About Ordering

Developers sometimes implement:

WHERE id > :cursor
ORDER BY id

and assume this solves every pagination problem.

It doesn't.

Suppose the business requirement is:

Show newest orders first.

Then:

ORDER BY id

may not represent creation order.

You might need:

ORDER BY created_at DESC, id DESC

The cursor must then represent the ordering position:

(created_at, id)

The cursor is not simply:

Some ID.

It is:

Enough information to resume the ordered traversal.

This is a much more important way to think about cursor design.

Another Common Mistake: Cursor Without a Tie-Breaker

This query looks reasonable:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

But if multiple records have the same timestamp, pagination can become ambiguous.

Prefer:

SELECT *
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

The unique ID gives us deterministic ordering.

A useful rule is:

Your pagination ordering should end with a unique, immutable tie-breaker whenever possible.

For example:

created_at DESC
       +
id DESC

Another Common Mistake: Assuming Cursor Pagination Eliminates All Duplicates

Cursor pagination greatly improves consistency for many common patterns, but it doesn't mean duplicates are impossible in every system.

Consider a sort field that can change after a record has already been returned.

For example:

ORDER BY score DESC

If a record's score changes between requests, its position can move.

The same record could potentially cross the pagination boundary.

Therefore, cursor pagination works best when the ordering fields are stable or when the API's semantics explicitly account for changing ordering.

This is why fields such as:

created_at
id

are common choices.

The Deeper Idea: Pagination Is an Ordering Problem

At first glance, pagination appears to be about:

How many records do I return?

But the deeper problem is:

How do I define a stable position in an ordered dataset?

Offset pagination says:

Position = number of records before it

Cursor pagination says:

Position = values of the ordering keys

This is why cursor pagination scales better conceptually.

The database isn't being asked to repeatedly calculate:

What is the Nth record?

It is being asked:

Continue after this known ordering key.

A Mental Model to Remember

Think of offset pagination like a book.

You say:

Go to page 500.

The system thinks in terms of position.

Cursor pagination is like bookmarking a specific sentence.

You say:

Continue after this bookmark.

The system thinks in terms of position in the ordered content.

If pages are constantly being inserted or removed before your current location, page numbers can shift.

A bookmark is much more stable.

Final Architecture

For a modern API, a good cursor-pagination architecture looks like this:

                  HTTP Client
                       |
                       v
              GET /orders?cursor=...
                       |
                       v
                 API Controller
                       |
                       v
                Cursor Decoder
                       |
                       v
              Pagination Service
                       |
                       v
             Keyset Query Builder
                       |
                       v
                    Database
                       |
                       v
               Indexed Scan/Seek
                       |
                       v
                  N + 1 rows
                       |
                       v
                Has Next?
                  /       \
                Yes        No
                 |          |
                 v          v
             Next Cursor   null
                 \          /
                  \        /
                   v      v
                  API Response

The key pieces are:

1. Deterministic ordering

2. Stable cursor

3. Appropriate database index

4. Bounded page size

5. Opaque cursor representation

6. N + 1 technique for hasNext

7. Clear handling of filters and sorting

Final Takeaway

Offset pagination and cursor pagination solve the same basic problem, but they solve it using fundamentally different models.

Offset pagination asks:

"How many records should I skip?"

Cursor pagination asks:

"Where did I stop?"

Offset pagination is:

position-based

Cursor pagination is:

key-based

Offset pagination is simple and excellent for:

small datasets
traditional page navigation
admin interfaces
random page access

Cursor pagination is generally better for:

large datasets
frequently changing data
deep pagination
infinite scrolling
high-scale APIs

But the most important lesson is not simply:

Use cursor pagination because it is faster.

The real lesson is:

Good pagination starts with a deterministic ordering and a stable way to resume that ordering.

Once you understand that, cursor pagination becomes much easier to reason about.

The complete flow is:

Define ordering
      |
      v
Make ordering deterministic
      |
      v
Choose cursor fields
      |
      v
Create matching database index
      |
      v
Fetch limit + 1
      |
      v
Generate opaque next cursor
      |
      v
Return bounded response
      |
      v
Client sends cursor
      |
      v
Database continues from that position

That is the foundation of scalable pagination for modern APIs.