Fazal Mansuri

Posted on May 16

API Pagination - Offset vs Cursor Pagination Explained

#backend #api #softwaredevelopment #performance

Most APIs don’t return all records at once.

Imagine:

Millions of users
Thousands of orders
Endless social media posts

Returning everything in a single response would:

Consume huge memory
Slow down queries
Increase network payload
Crash clients

That’s why APIs use:

Pagination

But here’s where many developers make mistakes:

Not all pagination strategies are equal.

The two most common approaches are:

Offset Pagination
Cursor Pagination

And choosing the wrong one can create:

Slow APIs
Duplicate records
Missing data
Poor user experience

Let’s understand everything properly.

🧠 What is Pagination?

Pagination means:

Splitting large datasets into smaller chunks (pages).

Instead of:

GET /posts

You request:

GET /posts?page=1&limit=10

This improves:

Performance
Scalability
User experience

📦 Offset Pagination

Offset pagination is the most common approach.

Example:

GET /posts?offset=20&limit=10

Meaning:

Skip first 20 rows
Return next 10 rows

⚙️ SQL Behind It

SELECT * FROM posts
ORDER BY id
LIMIT 10 OFFSET 20;

Simple Go Example - Offset Pagination

package main

import (
    "encoding/json"
    "log"
    "net/http"
    "strconv"
)

type Post struct {
    ID    int    `json:"id"`
    Title string `json:"title"`
}

var posts []Post

func init() {
    for i := 1; i <= 100; i++ {
        posts = append(posts, Post{
            ID:    i,
            Title: "Post " + strconv.Itoa(i),
        })
    }
}

func getPosts(w http.ResponseWriter, r *http.Request) {
    query := r.URL.Query()

    offset, _ := strconv.Atoi(query.Get("offset"))
    limit, _ := strconv.Atoi(query.Get("limit"))

    if limit == 0 {
        limit = 10
    }

    end := offset + limit

    if offset > len(posts) {
        offset = len(posts)
    }

    if end > len(posts) {
        end = len(posts)
    }

    result := posts[offset:end]

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(result)
}

func main() {
    http.HandleFunc("/posts", getPosts)

    log.Println("Server running on http://localhost:8080")
    log.Fatal(http.ListenAndServe(":8080", nil))
}

▶️ Run It

go run offset-pagination.go

📡 API Call

curl "http://localhost:8080/posts?offset=20&limit=5"

📦 Response

[
  {
    "id": 21,
    "title": "Post 21"
  },
  {
    "id": 22,
    "title": "Post 22"
  },
  {
    "id": 23,
    "title": "Post 23"
  },
  {
    "id": 24,
    "title": "Post 24"
  },
  {
    "id": 25,
    "title": "Post 25"
  }
]

✅ Advantages of Offset Pagination

Simple to implement
Easy to understand
Supports random page access

Example:

?page=50

❌ Problems with Offset Pagination

This is where things become interesting.

⚠️ Problem 1 — Slow Queries at Scale

Imagine:

LIMIT 10 OFFSET 1000000

Database still scans and skips 1 million rows.

💥 Very expensive.

⚠️ Problem 2 — Duplicate / Missing Records

Suppose user loads:

Page 1

Meanwhile:

New rows inserted
Old rows deleted

Now user loads:

Page 2

Result:

Duplicate rows
Missing rows

📉 Visual Problem

Before:

1 2 3 4 5 6 7 8 9 10

User fetches:
OFFSET 0 LIMIT 5

Gets:
1 2 3 4 5

Now new record inserted at top:

0 1 2 3 4 5 6 7 8 9 10

User fetches:
OFFSET 5 LIMIT 5

Gets:
5 6 7 8 9

⚠️ Duplicate "5"

🚀 Cursor Pagination

Cursor pagination solves these problems.

Instead of:

Skipping rows

We say:

Give records AFTER this specific item.

Example:

GET /posts?cursor=20&limit=10

Meaning:

Give next 10 posts after ID 20

⚙️ SQL Behind It

SELECT * FROM posts
WHERE id > 20
ORDER BY id
LIMIT 10;

Database uses index efficiently.

Simple Go Example — Cursor Pagination

package main

import (
    "encoding/json"
    "log"
    "net/http"
    "strconv"
)

type Post struct {
    ID    int    `json:"id"`
    Title string `json:"title"`
}

var posts []Post

func init() {
    for i := 1; i <= 100; i++ {
        posts = append(posts, Post{
            ID:    i,
            Title: "Post " + strconv.Itoa(i),
        })
    }
}

func getPosts(w http.ResponseWriter, r *http.Request) {
    query := r.URL.Query()

    cursor, _ := strconv.Atoi(query.Get("cursor"))
    limit, _ := strconv.Atoi(query.Get("limit"))

    if limit == 0 {
        limit = 10
    }

    var result []Post

    for _, post := range posts {
        if post.ID > cursor {
            result = append(result, post)

            if len(result) == limit {
                break
            }
        }
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(result)
}

func main() {
    http.HandleFunc("/posts", getPosts)

    log.Println("Server running on http://localhost:8080")
    log.Fatal(http.ListenAndServe(":8080", nil))
}

▶️ Run It

go run cursor-pagination.go

📡 API Call

curl "http://localhost:8080/posts?cursor=10&limit=5"

📦 Response

[
  {
    "id": 11,
    "title": "Post 11"
  },
  {
    "id": 12,
    "title": "Post 12"
  },
  {
    "id": 13,
    "title": "Post 13"
  },
  {
    "id": 14,
    "title": "Post 14"
  },
  {
    "id": 15,
    "title": "Post 15"
  }
]

✅ Advantages of Cursor Pagination

Fast for large datasets
Uses indexes efficiently
No duplicate/missing rows issue
Better for real-time systems

⚠️ Trade-Offs of Cursor Pagination

Not perfect either.

❌ No Random Page Access

You cannot easily jump to:

Page 50

Need sequential navigation.

❌ More Complex Implementation

Need:

Stable sorting
Unique cursor
Cursor encoding sometimes

🧠 What Companies Usually Do

🟢 Offset Pagination

Used for:

Admin panels
Internal dashboards
Small datasets

🟣 Cursor Pagination

Used for:

Social media feeds
Infinite scroll
Messaging apps
Large-scale APIs

🔥 Real-World Example

Instagram / Twitter Feed

Do you think they use:

OFFSET 5000000

❌ No chance.

They use cursor-based pagination.

⚡ Performance Difference

Offset Pagination

OFFSET 1000000

DB scans huge rows ❌

Cursor Pagination

WHERE id > 1000000

Direct index lookup ✅

🔐 Important Cursor Pagination Rule

Cursor field must be:

Indexed
Unique
Stable

Usually:

ID
Created timestamp

⚠️ Common Developer Mistakes

❌ Using OFFSET for infinite scroll

Bad at scale.

❌ Cursor based on non-unique column

Can create duplicates.

❌ Forgetting stable ordering

Pagination becomes inconsistent.

🧠 Key Mental Model

Offset Pagination

“Skip N rows”

Cursor Pagination

“Continue after this row”

📊 Quick Comparison

Feature	Offset Pagination	Cursor Pagination
Easy implementation	✅	❌
Random page access	✅	❌
Large dataset performance	❌	✅
Infinite scroll	❌	✅
Duplicate/missing record safety	❌	✅

🏁 Final Thoughts

Pagination seems simple.

But poor pagination strategy can:

Slow down APIs
Break user experience
Create inconsistent data

🎯 Key Takeaways

Offset pagination is simple but doesn’t scale well
Cursor pagination is efficient and production-friendly
Infinite scroll systems should prefer cursor pagination
Always use indexed & stable cursor fields

Good API design is not just about returning data.

It’s about returning data efficiently — even at massive scale.