Pagination at Depth: Why OFFSET Falls Over and How to Build Cursors That Don’t

Posted on

Pagination looks like the least interesting problem in backend engineering — until the page that took 30 ms at depth 1 takes 2.4 seconds at depth 50, or users report seeing the same item twice on a live feed. Both bugs come from the same default choice: LIMIT and OFFSET. This post covers why offset pagination degrades and drifts, how keyset (cursor) pagination fixes it, what the cursor token should contain, and the edge cases — NULLs, filters, sorting — that decide whether your implementation is correct or just fast. Examples use PostgreSQL and Go.

How OFFSET actually executes

Offset pagination asks the database for a window of rows by position:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

The semantics hide the execution model. PostgreSQL does not “seek” to row 100,001 — it produces the ordered stream from the top and discards the first 100,000 rows. With an index that supports the ORDER BY, each of those discarded rows still costs an index traversal plus heap fetch; without one, every page request re-sorts the table. Page cost grows linearly with depth, so the endpoint that benchmarked fine in development (page 2 of 200 rows) falls over in production (page 500 of 2 million). The LIMIT and OFFSET documentation is explicit that offset rows are only skipped, never cheaply located.

Worse than the cost is the drift. Rows are ordered by position, and position shifts every time a row is inserted or deleted ahead of the cursor. A user on page 3 who hasn’t moved their browser can silently see a different set of rows after a background job inserts a batch — items slide between pages, and some get skipped entirely if the user advances pages between inserts. On feeds where new rows arrive constantly, offset pagination is not merely slow; it is incorrect.

The third cost is the total. Offset UIs want “page 3 of 771”, and that requires SELECT count(*) over the same filter — a full scan that gets slower as the table grows, executed on every page view, to produce a number that is stale before it renders.

Keyset pagination: seek instead of skip

Keyset pagination replaces “skip N rows” with “give me everything after the last row I saw”. Instead of a numeric offset, the API returns an opaque cursor encoding the sort key of the final row, and the next request filters on it:

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Two details make this correct, and skipping either one is the most common keyset bug:

  • The tiebreaker column. created_at is not unique — two posts can share a timestamp, and then WHERE created_at < $1 skips rows on the boundary forever. The row-value comparison (created_at, id) < ($1, $2) is lexicographic: it means created_at < $1 OR (created_at = $1 AND id < $2), which places every row exactly once in a total order. PostgreSQL supports row constructors natively here.
  • The matching index. The seek is only fast if an index serves the same order the query sorts by: CREATE INDEX ON posts (created_at DESC, id DESC). With that in place, the cursor condition becomes a direct index seek, and page 50,000 costs the same as page 1. Note that because PostgreSQL B-tree indexes can be scanned backward, a default ascending index on (created_at, id) also serves this query — mixed-direction sorts (some columns ASC, some DESC) are where explicit per-column ordering starts to matter.

The performance difference is not subtle. Offset walks and discards O(offset) index entries; keyset seeks directly to the cursor position in O(log n) plus page size. A table that makes depth-50 offset pages unusable serves unbounded keyset pages in constant time.

Designing the cursor token

Never expose raw database values as the cursor and never let clients construct one. Expose an opaque string the server encodes and decodes. A JSON payload with the sort keys, URL-safe base64-encoded, is the common pattern. The first page omits the cursor entirely, so the data access layer branches on its presence:

import (
	"encoding/base64"
	"encoding/json"
	"time"
)

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

func encodeCursor(c Cursor) (string, error) {
	b, err := json.Marshal(c)
	if err != nil {
		return "", err
	}
	return base64.RawURLEncoding.EncodeToString(b), nil
}

func decodeCursor(s string) (Cursor, error) {
	var c Cursor
	b, err := base64.RawURLEncoding.DecodeString(s)
	if err != nil {
		return c, err
	}
	err = json.Unmarshal(b, &c)
	return c, err
}

The query side takes the cursor, or uses an unfiltered first-page query when it is absent:

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

const firstPageSQL = `
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT $1`

const nextPageSQL = `
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3`

func listPosts(ctx context.Context, db *pgxpool.Pool, cur string, limit int) ([]Post, string, error) {
	var (
		rows pgx.Rows
		err  error
	)
	if cur == "" {
		rows, err = db.Query(ctx, firstPageSQL, limit+1)
	} else {
		c, derr := decodeCursor(cur)
		if derr != nil {
			return nil, "", fmt.Errorf("invalid cursor: %w", derr)
		}
		rows, err = db.Query(ctx, nextPageSQL, c.CreatedAt, c.ID, limit+1)
	}
	if err != nil {
		return nil, "", err
	}
	defer rows.Close()

	var posts []Post
	for rows.Next() {
		var p Post
		if err := rows.Scan(&p.ID, &p.Title, &p.CreatedAt); err != nil {
			return nil, "", err
		}
		posts = append(posts, p)
	}
	if err := rows.Err(); err != nil {
		return nil, "", err
	}

	var next string
	if len(posts) > limit {
		last := posts[limit-1]
		posts = posts[:limit]
		c := Cursor{CreatedAt: last.CreatedAt, ID: last.ID}
		if next, err = encodeCursor(c); err != nil {
			return nil, "", err
		}
	}
	return posts, next, nil
}

Two implementation notes on this pattern. First, fetch limit+1 rows to compute next: if the extra row exists, another page follows, and you trim it before returning. This replaces count(*) entirely — clients know “there is more” without knowing how much more. Second, the next cursor is built from the last returned row, after trimming, which is why the slice arithmetic matters; getting that line wrong silently duplicates or drops a boundary row.

If you also want to detect client-side cursor tampering (or expire cursors), wrap the encoded string in an HMAC before returning it and verify on the way back — a standard trick borrowed from token design, and cheap because cursors are small. The full example above uses pgx, the PostgreSQL driver most Go services standardize on.

Edge cases that decide correctness

  • NULLs in sort columns. In PostgreSQL’s default null ordering, NULLs sort first in a DESC sequence — before every real value — and a row-value comparison against them behaves unintuitively. The clean fix is at the schema level: make sort columns NOT NULL, or use a generated/derived column that cannot be null. If you must sort nullable columns, pin the ordering explicitly with NULLS LAST and verify your comparison logic against actual NULL rows, not just happy-path data.
  • Filters. Keyset only seeks as fast as the index for the filtered query. A cursor over (created_at, id) does nothing for WHERE author_id = $4 — you need composite indexes per filter family, e.g. (author_id, created_at DESC, id DESC). Each new filterable axis is an index decision, not just a WHERE clause.
  • Sortable-by-anything UIs. A cursor is only valid for the sort it was minted from. If users can switch to sort by title, the client must request a fresh first page — encode the sort key inside the cursor and reject cursors whose sort does not match the request, rather than applying an created_at cursor to a title ordering.
  • Jump to page N. Keyset cannot do it, by design — there is no position to seek to without iterating. Admin tables with page-number navigation can keep offset pagination, because their datasets are small and read-heavy; they are the legitimate habitat of OFFSET. For anything user-facing and large, infinite-scroll or next/prev navigation is the honest interface.
  • Deletes mid-scroll. Keyset tolerates concurrent inserts and deletes of rows the client has already passed — the cursor anchors on values, not positions. Deleting the row the cursor points to is still safe with the row-value form, because the comparison does not need the row to exist; it only needs the values to be comparable.

When offset is still fine

Offset pagination is not wrong everywhere — it is wrong at scale and under mutation. Small tables (thousands of rows, not millions), internal admin screens, and endpoints where users genuinely need numbered pages all justify LIMIT/OFFSET, and the simplicity is real. The failure mode to avoid is shipping offset as the default everywhere and discovering the cliff in production, where the fix requires an API change: cursor migration is far harder to retrofit once clients have cached page URLs. If an endpoint is public, unbounded, or fed by live data, start with keyset. Public APIs that did — GitHub’s REST API being the widely used example — made cursors their primary scheme for exactly these reasons, as their API documentation describes.

Wrapping up

Offset pagination skips rows at linear cost and anchors on positions that shift under writes; keyset pagination seeks by value at constant cost and stays correct under concurrent mutation. The implementation requirements are short: a unique tiebreaker in the sort, a composite index matching the sort order, an opaque signed cursor, and the limit+1 trick instead of a count(*). Everything else — filters, nullable columns, sort switching — is index and validation work. Audit your endpoints: anywhere a cursor can’t be added later without breaking clients is a place the decision matters today.

Leave a Reply

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