Why We Replaced Offset Pagination with Cursor Pagination in Our Go API

Offset pagination feels simple until your SaaS product has 500,000 rows per tenant. Then it becomes the most quietly expensive operation in your API. This is the story of how we moved to cursor pagination in our Go backends and what we learned building it correctly for SaaS clients in Lebanon and the MENA region.

What breaks with OFFSET at scale

The standard LIMIT 20 OFFSET 400 approach seems harmless. The SQL is clear. Every engineer understands it. The problem is what PostgreSQL actually does.

To return page 21 of 20 items, PostgreSQL must read and discard the first 400 rows. It cannot jump directly to row 401. Even with an index on the sort column, the database scans through 400 index entries before arriving at the ones you want. At page 1, this is trivial. At page 1000 with 20 items per page, PostgreSQL is discarding 19,980 rows to return 20.

In a SaaS product where tenants with large datasets are clicking through transaction history, order lists, or audit logs, the API call for page 50 is measurably slower than page 1. Users who paginate deeply notice it. And if your API powers an infinite-scroll feed or a background data export job walking through thousands of records, offset pagination creates a performance cliff.

The second problem is result drift. If a new record is inserted between page 1 and page 2 being fetched, the user sees a duplicate record. If a record is deleted, they skip one silently. For audit logs and financial records, this is unacceptable.

How cursor pagination works

Cursor pagination, also called keyset pagination, uses a marker from the last seen record to anchor the next page query. Instead of telling the database to skip N rows, you tell it to return rows after a specific value.

-- Page 1: no cursor
SELECT id, created_at, amount, status
FROM transactions
WHERE tenant_id = $1
  AND status = 'completed'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Page 2: cursor from last row of page 1
SELECT id, created_at, amount, status
FROM transactions
WHERE tenant_id = $1
  AND status = 'completed'
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The (created_at, id) < ($2, $3) condition uses PostgreSQL's row value comparison, which is a single B-tree range lookup. The database jumps directly to the anchor point without scanning any previous rows. Page 1 and page 5000 have identical query costs.

The cursor value is the sort key tuple from the last row of the current page. In this case, (created_at, id) from the last visible transaction.

Why the sort key needs a tiebreaker

If you paginate only on created_at, two rows with the same timestamp create a problem. You cannot tell which side of the cursor they belong to. Including id as a secondary sort column with DESC ordering resolves ties deterministically because id is unique.

The composite condition (created_at, id) < ($2, $3) in PostgreSQL behaves correctly for row comparison: it returns rows where created_at is strictly less than the anchor, or rows where created_at equals the anchor and id is strictly less than the anchor id. This matches the B-tree structure of a composite index.

For this to be index-efficient, you need a composite index that matches both the filter and the sort:

CREATE INDEX idx_txns_tenant_cursor
  ON transactions(tenant_id, status, created_at DESC, id DESC);

With this index, the cursor query does a single range scan anchored at (tenant_id, status, created_at_anchor, id_anchor) and returns the next 20 rows immediately.

Building the Go implementation

The cursor value needs to be encoded for API transport. Putting raw database values in the URL leaks schema details and makes cursor format hard to change later. Encoding the cursor as a base64 JSON blob is the practical choice.

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

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

func DecodeCursor(s string) (Cursor, error) {
	b, err := base64.URLEncoding.DecodeString(s)
	if err != nil {
		return Cursor{}, fmt.Errorf("invalid cursor: %w", err)
	}
	var c Cursor
	if err := json.Unmarshal(b, &c); err != nil {
		return Cursor{}, fmt.Errorf("invalid cursor: %w", err)
	}
	return c, nil
}

The list handler decodes the cursor if present and passes it to the query:

func (h *Handler) ListTransactions(w http.ResponseWriter, r *http.Request) {
	tenantID := middleware.TenantIDFromContext(r.Context())
	limit := 20

	var cursor *Cursor
	if raw := r.URL.Query().Get("cursor"); raw != "" {
		c, err := DecodeCursor(raw)
		if err != nil {
			http.Error(w, "invalid cursor", http.StatusBadRequest)
			return
		}
		cursor = &c
	}

	txns, err := h.repo.ListTransactions(r.Context(), tenantID, cursor, limit+1)
	if err != nil {
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}

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

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

The pattern of fetching limit+1 items and checking if the extra item exists is cleaner than running a separate count query. If you get 21 rows when you asked for 21, there are more pages. The 21st row is not returned to the client, but its sort key becomes the next cursor.

Repository query with cursor branching

func (r *Repo) ListTransactions(
	ctx context.Context,
	tenantID int64,
	cursor *Cursor,
	limit int,
) ([]Transaction, error) {
	var rows *sql.Rows
	var err error

	if cursor == nil {
		rows, err = r.db.QueryContext(ctx, `
			SELECT id, created_at, amount, status
			FROM transactions
			WHERE tenant_id = $1 AND status = 'completed'
			ORDER BY created_at DESC, id DESC
			LIMIT $2`, tenantID, limit)
	} else {
		rows, err = r.db.QueryContext(ctx, `
			SELECT id, created_at, amount, status
			FROM transactions
			WHERE tenant_id = $1
			  AND status = 'completed'
			  AND (created_at, id) < ($2, $3)
			ORDER BY created_at DESC, id DESC
			LIMIT $4`,
			tenantID, cursor.CreatedAt, cursor.ID, limit)
	}
	// scan rows...
}

This keeps the two code paths separate rather than trying to build a dynamic query with optional WHERE clauses. Dynamic queries are harder to reason about and harder to pair with static analysis tools like sqlc.

Operational considerations in MENA SaaS products

For SaaS products deployed in Lebanon or serving MENA markets, there are a few practical considerations:

Tenants in this region often have large data backlogs from migration periods. A retail client that migrated two years of historical orders into the system will have dense datasets per tenant. Offset pagination would be noticeably slow from day one for any query deep into the history. Cursor pagination handles this transparently.

The cursor approach also makes data export jobs simpler. A background job walking through a full tenant dataset page by page can use the same cursor mechanism as the API without any special handling:

var cursor *Cursor
for {
	page, err := repo.ListTransactions(ctx, tenantID, cursor, 500)
	if err != nil { return err }
	if len(page) == 0 { break }
	processBatch(page)
	last := page[len(page)-1]
	cursor = &Cursor{CreatedAt: last.CreatedAt, ID: last.ID}
}

Limitations to know before switching

Cursor pagination has real constraints. It does not support random access: you cannot jump to page 47 without walking through pages 1 through 46 first. If your product needs "jump to page N" UI, cursor pagination does not fit.

Changing the sort order is also not trivial. A cursor encoded for newest-first pagination cannot be reused for oldest-first. The cursor format must match the sort direction of the query.

For bidirectional pagination, where users can navigate both forward and backward, you need both a "before" and "after" cursor. The SQL uses > for forward and < for backward relative to the anchor. Most SaaS list views only need forward pagination, so this is rarely a real requirement.

Key lessons from production

Cursor pagination is worth the implementation effort for any SaaS product that will grow to millions of rows per tenant. The points that matter most in practice:

• Always include a unique tiebreaker column in the sort key and the cursor value.
• Build the composite index to match both the tenant filter and the full sort key.
• Encode cursors as opaque tokens; never expose raw database values in the API.
• Use limit+1 to detect more pages without a separate count query.
• Keep first-page and paginated queries as separate SQL statements; dynamic query building is harder to maintain.

For Go backends using sqlc, write the two queries as named queries in your schema and let sqlc generate the typed functions. The cursor decode/encode logic sits in a utility package and is shared across all list endpoints.