Pagination at Scale: Choosing Between Offset, Keyset, and Cursor Strategies

Posted on

Pagination is one of those API design decisions that seems trivial until your dataset grows. Return everything at once and you’ll OOM your servers. Return too little and clients need dozens of round-trips. Choose the wrong pagination strategy and you’ll deal with duplicate records, skipped items, and performance that degrades as users page deeper.

Most APIs pick a pagination strategy early and live with the consequences. The three dominant approaches — offset, cursor, and keyset — each have distinct trade-offs in performance, consistency, and implementation complexity. Understanding where each one breaks down helps you choose correctly before you’re locked in.

Offset Pagination: The Default That Doesn’t Scale

Offset pagination is what most developers reach for first. You return a page of results starting at a given offset, along with the total count:

GET /api/orders?page=3&limit=20

// Response
{
  "data": [...20 orders...],
  "pagination": {
    "page": 3,
    "limit": 20,
    "total": 1547,
    "total_pages": 78
  }
}

The SQL behind this is straightforward:

SELECT id, customer_id, total, status
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

The problem is that OFFSET 40 doesn’t mean “skip to row 40.” It means the database scans 40 rows and discards them. At page 100, you’re scanning and throwing away 2,000 rows. At page 500, it’s 10,000 rows of wasted work. This is why offset pagination feels fast for the first few pages and then becomes sluggish as users go deeper.

The second issue is data drift. If a new order arrives between page requests, every subsequent page shifts by one. A client paginating through results may see the same order twice or skip one entirely. For admin dashboards and internal tools, this is usually tolerable. For payment systems or audit logs, it’s not.

Keyset Pagination: Fast, Consistent, Rigid

Keyset pagination (also called seek or continuation token) eliminates the offset entirely. Instead of “skip 40 rows,” it says “give me rows after this specific value.” The client passes the last value it saw from the previous page:

GET /api/orders?after=2026-08-04T12:30:00Z&limit=20

// Server translates to:
SELECT id, customer_id, total, status
FROM orders
WHERE created_at < '2026-08-04 12:30:00'
ORDER BY created_at DESC
LIMIT 20;

The database uses the index on created_at to jump directly to the starting position. No rows are scanned and discarded. Page 500 is just as fast as page 1. And because each page is anchored to a specific value, new rows inserted between requests don’t shift existing pages — there’s no data drift.

The trade-off is rigidity. Keyset pagination only works well when sorting by an indexed, monotonically increasing column. You can’t jump to an arbitrary page — there’s no “go to page 5” because there’s no concept of pages, only “next” and “previous.” This makes it unsuitable for UIs with page number navigation.

There’s also a tiebreaker problem. If two rows share the same created_at value, the cursor needs to disambiguate. The standard solution is to include the primary key as a secondary sort:

SELECT id, customer_id, total, status, created_at
FROM orders
WHERE (created_at, id) < ('2026-08-04 12:30:00', 4521)
ORDER BY created_at DESC, id DESC
LIMIT 20;

PostgreSQL handles row-value comparisons natively, making this pattern clean. MySQL requires an equivalent formulation using (created_at <= cursor_time AND id < cursor_id) with careful boundary handling.

Cursor-Based Pagination: The Pragmatic Middle Ground

Cursor pagination is keyset pagination with an opaque token. Instead of exposing the sorting columns to the client, the server encodes them (typically base64) into a cursor string:

type Cursor struct {
    CreatedAt time.Time `json:"created_at"`
    ID        int64     `json:"id"`
}

func encodeCursor(t time.Time, id int64) string {
    data, _ := json.Marshal(Cursor{CreatedAt: t, ID: id})
    return base64.URLEncoding.EncodeToString(data)
}

func decodeCursor(s string) (time.Time, int64, error) {
    data, err := base64.URLEncoding.DecodeString(s)
    if err != nil {
        return time.Time{}, 0, err
    }
    var c Cursor
    if err := json.Unmarshal(data, &c); err != nil {
        return time.Time{}, 0, err
    }
    return c.CreatedAt, c.ID, nil
}

The API becomes stateless and implementation-agnostic:

GET /api/orders?limit=20&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wOC0wNFQxMjozMDowMFoifQ==

// Response
{
  "data": [...20 orders...],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wOC0wNFQxMjowMDowMFoiLCJpZCI6NDUyMX0=",
    "has_more": true
  }
}

The cursor abstracts away the sort column, sort direction, and tiebreaker logic. This means you can change your pagination implementation without breaking clients — the cursor format is entirely server-controlled. You can even embed filter parameters or expiration timestamps inside the cursor for additional flexibility.

Building a Cursor Handler in Go

Here’s a practical Go handler that implements cursor pagination with proper tiebreaker handling:

func ListOrders(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        limit := 20
        if v := r.URL.Query().Get("limit"); v != "" {
            if n, err := strconv.Atoi(v); err == nil && n > 0 && n <= 100 {
                limit = n
            }
        }

        var args []interface{}
        query := `SELECT id, customer_id, total, status, created_at
                  FROM orders`

        if cursor := r.URL.Query().Get("cursor"); cursor != "" {
            createdAt, id, err := decodeCursor(cursor)
            if err != nil {
                http.Error(w, "invalid cursor", http.StatusBadRequest)
                return
            }
            query += ` WHERE (created_at, id) < ($1, $2)`
            args = append(args, createdAt, id)
        }

        // Fetch one extra row to check for has_more
        query += ` ORDER BY created_at DESC, id DESC LIMIT $` +
            strconv.Itoa(len(args)+1)
        args = append(args, limit+1)

        rows, err := db.QueryContext(r.Context(), query, args...)
        if err != nil {
            http.Error(w, "query failed", http.StatusInternalServerError)
            return
        }
        defer rows.Close()

        var orders []Order
        for rows.Next() {
            var o Order
            if err := rows.Scan(&o.ID, &o.CustomerID, &o.Total, &o.Status, &o.CreatedAt); err != nil {
                http.Error(w, "scan failed", http.StatusInternalServerError)
                return
            }
            orders = append(orders, o)
        }

        hasMore := len(orders) > limit
        var nextCursor string
        if hasMore {
            last := orders[limit-1]
            nextCursor = encodeCursor(last.CreatedAt, last.ID)
            orders = orders[:limit]
        }

        json.NewEncoder(w).Encode(map[string]interface{}{
            "data": orders,
            "pagination": map[string]interface{}{
                "next_cursor": nextCursor,
                "has_more":    hasMore,
            },
        })
    }
}

The LIMIT N+1 trick fetches one extra row to determine whether more results exist, then trims it from the response. This avoids a separate COUNT(*) query that would scan the entire filtered result set. The Go database/sql package handles connection pooling and prepared statement caching transparently, so the handler stays simple.

Choosing the Right Strategy

Each strategy has a sweet spot:

  • Offset — Small datasets, admin UIs with page-number navigation, APIs where simplicity matters more than deep-paging performance. Acceptable when total records stay under ~10K.
  • Keyset — Large datasets accessed sequentially (infinite scroll, feed-style UIs, background jobs processing records in batches). Requires indexed sort columns and stable ordering.
  • Cursor — Public APIs where you want implementation flexibility, clients shouldn’t know your sort columns, and you need to support backward pagination. The most common choice for production APIs at scale.

Common Pitfalls

  • Missing composite index — If your cursor uses (created_at, id), you need a composite index on both columns in the same order. A single-column index on created_at forces a sort for the tiebreaker.
  • Floating timestamps — created_at values can collide at millisecond resolution. Always include a unique secondary column (like id) as a tiebreaker.
  • Unbounded count queries — Returning total in every response means running COUNT(*) on every request. For large tables, this is as expensive as the data query itself. Consider approximate counts or omit totals entirely for cursor-based APIs.
  • Unencrypted cursors — Base64 is encoding, not encryption. If your cursor contains sensitive data or you want to prevent clients from crafting their own cursors, sign or encrypt it.

Wrapping Up

Pagination strategy is an architectural decision that’s expensive to change once clients depend on it. Offset pagination works for small datasets and simple UIs, but it degrades catastrophically at scale. Keyset pagination is fast and consistent but inflexible about sort order. Cursor-based pagination abstracts the implementation behind an opaque token, giving you the performance of keyset with the flexibility to evolve your query strategy without breaking clients. For most production APIs, cursor pagination is the right default — the initial implementation cost is modest, and it prevents a class of problems that only surface under load.

Leave a Reply

Your email address will not be published. Required fields are marked *