Cursor-Based Pagination for Payment Transaction APIs

Why Offset Pagination Fails for Payments

The classic ?page=3&limit=25 pattern has two fatal problems in payment systems:

• Performance cliff: OFFSET 1000000 forces PostgreSQL to scan and discard a million rows before returning your 25. On a table with 50M+ transactions, page 40,000 takes 8+ seconds.
• Consistency drift: New transactions land constantly. Between fetching page 3 and page 4, new records push existing ones forward — causing duplicates or gaps in the client's view.

I learned this the hard way when a merchant dashboard started timing out on their transaction history. They had 2M+ transactions, and customers scrolling to older records were hitting 10-second queries. The fix wasn't an index — it was rethinking the pagination model entirely.

Cursor Pagination — The Core Idea

Instead of saying "skip N rows," you say "give me rows after this specific point." The cursor encodes the last item's position, so the database can seek directly to it using an index.

The key insight: cursors leverage B-tree indexes. The database doesn't scan from the beginning — it jumps directly to the cursor position and reads forward. Constant time regardless of how deep into the dataset you are.

Designing the Cursor

A good cursor for payment transactions needs two components:

• Timestamp — the primary sort key (usually created_at)
• Unique ID — tiebreaker for transactions created at the same millisecond

Using timestamp alone fails because batch processing can create hundreds of transactions with identical timestamps. The compound cursor (created_at, transaction_id) guarantees deterministic ordering.

// Cursor structure
type Cursor struct {
    Timestamp time.Time
    ID        string
}

// Encode to opaque base64 string
func (c Cursor) Encode() string {
    raw := fmt.Sprintf("%d:%s", c.Timestamp.UnixMicro(), c.ID)
    return base64.URLEncoding.EncodeToString([]byte(raw))
}

// Decode from client-provided string
func DecodeCursor(s string) (Cursor, error) {
    raw, err := base64.URLEncoding.DecodeString(s)
    if err != nil {
        return Cursor{}, ErrInvalidCursor
    }
    parts := strings.SplitN(string(raw), ":", 2)
    if len(parts) != 2 {
        return Cursor{}, ErrInvalidCursor
    }
    ts, err := strconv.ParseInt(parts[0], 10, 64)
    if err != nil {
        return Cursor{}, ErrInvalidCursor
    }
    return Cursor{
        Timestamp: time.UnixMicro(ts),
        ID:        parts[1],
    }, nil
}

Go Implementation

Here's the query pattern that makes cursor pagination fast:

func (r *TxnRepo) ListTransactions(ctx context.Context,
    merchantID string, cursor *Cursor, limit int,
) ([]Transaction, *Cursor, error) {
    query := `
        SELECT id, merchant_id, amount, currency, status, created_at
        FROM transactions
        WHERE merchant_id = $1
    `
    args := []any{merchantID}

    if cursor != nil {
        query += ` AND (created_at, id) < ($2, $3)`
        args = append(args, cursor.Timestamp, cursor.ID)
    }

    query += ` ORDER BY created_at DESC, id DESC LIMIT $` +
        strconv.Itoa(len(args)+1)
    args = append(args, limit+1) // fetch one extra to detect next page

    rows, err := r.db.QueryContext(ctx, query, args...)
    if err != nil {
        return nil, nil, fmt.Errorf("list transactions: %w", err)
    }
    defer rows.Close()

    var txns []Transaction
    for rows.Next() {
        var t Transaction
        if err := rows.Scan(&t.ID, &t.MerchantID, &t.Amount,
            &t.Currency, &t.Status, &t.CreatedAt); err != nil {
            return nil, nil, err
        }
        txns = append(txns, t)
    }

    var nextCursor *Cursor
    if len(txns) > limit {
        last := txns[limit-1]
        nextCursor = &Cursor{Timestamp: last.CreatedAt, ID: last.ID}
        txns = txns[:limit]
    }
    return txns, nextCursor, nil
}

The LIMIT N+1 trick avoids a separate count query — if you get more rows than requested, there's a next page.

Handling Real-Time Inserts

Payment tables grow constantly. The beauty of cursor pagination: new transactions inserted after the cursor position don't affect results. The client always sees a consistent slice moving backward through time.

But there's an edge case: what about transactions that arrive out of order? Late-settling batch transactions might have a created_at in the past. Two strategies:

• Use insertion time, not transaction time — if you cursor on inserted_at (a monotonically increasing timestamp), out-of-order arrival doesn't matter. Trade-off: the list isn't sorted by business time.
• Accept eventual consistency — document that the transaction list may have slight reordering for batch settlements. Most merchants are fine with this.

Comparison: Offset vs Cursor vs Keyset

API Response Format

A clean response envelope for cursor-paginated payment APIs:

{
  "data": [
    {
      "id": "txn_8kx92mf",
      "amount": 4500,
      "currency": "SGD",
      "status": "settled",
      "created_at": "2026-06-22T14:30:00Z"
    }
  ],
  "pagination": {
    "has_more": true,
    "next_cursor": "MTcxOTMzMDIwMDAwMDAwMDp0eG5fOGt4OTJtZg==",
    "limit": 25
  }
}

Key design decisions: has_more is explicit (don't make clients guess), the cursor is a single opaque string, and limit is echoed back so clients know what they asked for.

Production Lessons

1. Index must match sort order exactly. For ORDER BY created_at DESC, id DESC, you need a composite index on (merchant_id, created_at DESC, id DESC). A mismatch means a sort operation instead of an index scan.
2. Set a maximum page size. We cap at 100. Without a limit, a single API call could return 50K rows and OOM your service.
3. Expire cursors gracefully. If a cursor references a partitioned-away partition, return a clear error with a message to restart from the beginning — not a 500.
4. Don't expose total count. COUNT(*) on a 50M-row table is expensive. If the UI needs a count, cache it and update it asynchronously.
5. Test with production-scale data. Cursor pagination that works on 1,000 rows in dev might hit edge cases at scale — especially around timestamp precision and tie-breaking.

References

• Use The Index, Luke — No Offset
• PostgreSQL Documentation — Indexes and ORDER BY
• Stripe API — Pagination
• Slack Engineering — Evolving API Pagination

Disclaimer: This article reflects the author's personal experience and opinions. Product names, logos, and brands are property of their respective owners. Pricing and features mentioned are subject to change — always verify with official documentation.