paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

Pagination Patterns Explained: Offset vs Cursor vs Keyset (With Real-World Tradeoffs)

A practical breakdown of pagination strategies and when to use each in scalable systems

Introduction

Pagination is one of those problems that looks trivial until the moment your production database starts groaning under the weight of a LIMIT 20 OFFSET 1000000 query. At that point, what seemed like a solved problem suddenly becomes a very active engineering conversation.

Every API that returns lists of data needs a strategy for returning that data in chunks. Whether you're building a social media feed, an admin dashboard, a search interface, or an event log viewer, the pagination approach you choose has real consequences: on database performance, on API consistency, on frontend complexity, and on the correctness of what users actually see. Choosing the wrong strategy for your access patterns can cause silent data corruption (skipped or duplicated rows), catastrophic query plans, or APIs that become unusable at scale.

This article walks through the three dominant pagination strategies - offset-based, cursor-based, and keyset-based - explaining how each works mechanically, where each shines, and where each breaks down. It is written for engineers who already understand relational databases and REST or GraphQL APIs and want to make an informed architectural choice rather than defaulting to whatever their ORM makes easiest.

The Core Problem: Slicing a Moving Target

Before diving into specific techniques, it is worth articulating what makes pagination hard. At its heart, pagination asks a deceptively simple question: "give me the next N records after the ones I already have." The difficulty comes from the fact that the underlying dataset is rarely static.

Records are inserted, updated, and deleted between requests. Rows change their sort position when their sortable fields are updated. Concurrent writers modify the table between your first and second page request. The database itself doesn't inherently track "where you left off" - it only knows the state of the data at the moment of each query. This means every pagination scheme is really a contract between the client and the server about how to navigate a dataset that may be mutating underneath them.

There is also a second, often underappreciated problem: consistency. Ideally, a user paginating through a result set should see each record exactly once - no skips, no duplicates. Achieving this guarantee, especially with concurrent writes, is harder than it sounds and varies significantly across the three approaches we will discuss.

Offset-Based Pagination

How It Works

Offset pagination is the approach almost everyone learns first. The client passes two parameters - a limit (how many records to return) and an offset (how many records to skip from the beginning). The server translates these directly into a SQL LIMIT / OFFSET clause:

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

This returns records 41-60 in the sorted result set. The client increments the offset by the limit for each subsequent page. Simple, universal, and supported by every relational database.

In a REST API, this typically surfaces as query parameters:

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

Or equivalently:

GET /api/articles?limit=20&offset=40

The client can jump to any arbitrary page, which makes it easy to build UIs with numbered page controls. The server is stateless - no session or continuation token needs to be stored between requests.

The Performance Cliff

The core problem with offset pagination is what happens to the query plan as the offset grows. When you issue OFFSET 40, the database does not magically jump to row 41. It scans and discards the first 40 rows, then returns the next 20. At OFFSET 1000000, the database is discarding a million rows on every page request - even though most of that work is thrown away.

This becomes a serious bottleneck in large tables. Even with a covering index on the ORDER BY columns, the database still walks through the index entries for all the rows being skipped. As the table grows and users page deeper, response times degrade predictably and sometimes catastrophically. This pattern has a well-known name in database performance circles: the late row lookup problem combined with offset amplification.

// This looks innocent but becomes O(offset) at the database level
async function getArticlesPage(page: number, limit: number = 20) {
  const offset = (page - 1) * limit;
  
  const { rows } = await db.query(
    `SELECT id, title, created_at 
     FROM articles 
     ORDER BY created_at DESC 
     LIMIT $1 OFFSET $2`,
    [limit, offset]
  );
  
  return rows;
}
// At page 1000 with limit=20, the database discards 19,980 rows
// This query gets slower as page number grows

The Consistency Problem

Beyond performance, offset pagination has a correctness problem when the dataset is mutating. Suppose a user loads page 1 (records 1-20). While they read, a new record is inserted at the top of the sorted order. When they request page 2 with OFFSET 20, the database now sees the new record as position 1, shifting everything down - so the record that was previously at position 20 is now at position 21, and it appears on page 2 again as a duplicate. The inverse happens with deletions: rows fall off pages silently.

For datasets with high write rates and users who page sequentially, this inconsistency is not theoretical - it manifests as visible UX glitches and, in some contexts (financial records, audit logs), as actual data integrity concerns.

When to Use Offset Pagination

Despite its limitations, offset pagination is the right choice in several scenarios. It is appropriate when the total dataset is small enough that deep offsets never occur in practice (fewer than tens of thousands of rows). It is well-suited for admin interfaces with numbered page controls, where users genuinely want to jump to "page 47 of 200." It is also the right choice when the dataset is effectively read-only or infrequently written, making the consistency problem moot. The universality of LIMIT/OFFSET across every SQL database also makes it the pragmatic default when development speed matters more than pagination performance.

Cursor-Based Pagination

How It Works

Cursor-based pagination shifts the mental model from "skip N rows" to "give me records after this specific position." The server encodes the position of the last record returned as an opaque cursor, returns it alongside the results, and the client passes it back on the next request. The server decodes the cursor and uses it to fetch the next page.

This is the approach popularized by the Relay GraphQL specification and widely adopted in APIs from GitHub, Stripe, Twitter, and Shopify. A typical response looks like:

{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJpZCI6IDEyMzQsICJjcmVhdGVkX2F0IjogIjIwMjQtMDEtMTVUMTI6MDA6MDAifQ==",
    "has_next_page": true
  }
}

The cursor is typically a base64-encoded representation of the sort key(s) of the last returned record - enough information to recreate the "position" in the result set. The client treats it as opaque: it doesn't need to decode it or understand its structure.

interface PaginationResult<T> {
  items: T[];
  nextCursor: string | null;
  hasNextPage: boolean;
}

function encodeCursor(id: number, createdAt: Date): string {
  const payload = JSON.stringify({ id, createdAt: createdAt.toISOString() });
  return Buffer.from(payload).toString('base64url');
}

function decodeCursor(cursor: string): { id: number; createdAt: Date } {
  const payload = JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
  return { id: payload.id, createdAt: new Date(payload.createdAt) };
}

async function getArticlesAfterCursor(
  cursor: string | null,
  limit: number = 20
): Promise<PaginationResult<Article>> {
  let query: string;
  let params: unknown[];

  if (!cursor) {
    query = `
      SELECT id, title, created_at 
      FROM articles 
      ORDER BY created_at DESC, id DESC 
      LIMIT $1
    `;
    params = [limit + 1]; // fetch one extra to detect hasNextPage
  } else {
    const { id, createdAt } = decodeCursor(cursor);
    query = `
      SELECT id, title, created_at 
      FROM articles 
      WHERE (created_at, id) < ($2, $3)
      ORDER BY created_at DESC, id DESC 
      LIMIT $1
    `;
    params = [limit + 1, createdAt, id];
  }

  const { rows } = await db.query(query, params);
  const hasNextPage = rows.length > limit;
  const items = hasNextPage ? rows.slice(0, limit) : rows;
  const nextCursor = hasNextPage ? encodeCursor(items[items.length - 1].id, items[items.length - 1].created_at) : null;

  return { items, nextCursor, hasNextPage };
}

Notice the use of a composite sort key (created_at, id) to ensure stable ordering. This is critical: if you only sort by created_at, records with identical timestamps have no deterministic relative order, causing inconsistent pagination.

Relationship to Keyset Pagination

The terms "cursor pagination" and "keyset pagination" are often used interchangeably, but there is a meaningful distinction. Cursor pagination is a broader API design pattern - the server uses a cursor to encode position and the client remains ignorant of the underlying mechanism. Keyset pagination is a specific database query strategy that uses indexed column values as boundary conditions in WHERE clauses rather than OFFSET. A well-implemented cursor-based API should use keyset queries internally, but the two concepts are separate enough to examine independently, which is why this article treats them in successive sections.

Limitations of the Cursor Approach

Cursor pagination enforces strictly sequential navigation. Users can only move forward (and sometimes backward) - they cannot jump to page 47 or to a specific bookmark. For many UIs this is fine: infinite scroll, "load more" buttons, and sequential log viewers are all natural fits. But for traditional paginated UIs with page number controls, cursor pagination is a poor fit.

Another limitation is that the cursor becomes invalid if the dataset changes in ways that affect the sort order of the "anchor" record. If the row referenced by the cursor is deleted, behavior depends on the implementation - some systems return an error, others treat it as "start from the nearest surviving record," and others exhibit undefined behavior. This requires explicit handling in production implementations.

Keyset Pagination

The Underlying Mechanism

Keyset pagination is the database-level strategy that makes cursor-based pagination performant. Instead of OFFSET N, you encode the boundary condition directly in the WHERE clause using the actual values of the sort columns:

-- Page 1: no cursor
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Page 2: cursor from last row of page 1
-- Assume last row had created_at='2024-01-15 12:00:00', id=1234
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2024-01-15 12:00:00', 1234)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The WHERE (created_at, id) < (value1, value2) construct is a row value comparison (supported in PostgreSQL, MySQL 8+, SQLite, and most modern SQL databases). It performs a lexicographic comparison on the tuple: first compare created_at, and only if those are equal, compare id. This precisely captures "records that come after this position in the sort order."

Why Keyset Is Dramatically Faster

The performance advantage of keyset pagination comes from how the database query planner handles the WHERE clause. With an appropriate composite index on (created_at DESC, id DESC), the database can perform an index seek directly to the boundary position and scan forward from there - O(page_size) work regardless of the position in the dataset. With offset, the database always does O(offset + page_size) work.

-- Create the supporting index for keyset pagination
CREATE INDEX idx_articles_cursor ON articles (created_at DESC, id DESC);

-- Now EXPLAIN ANALYZE this query:
EXPLAIN ANALYZE
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2024-01-15 12:00:00', 1234)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Result: Index Scan using idx_articles_cursor - cost is constant regardless of depth

In practice, this makes a significant difference at scale. A keyset query paginating to the "millionth record" executes in roughly the same time as paginating to the tenth record. An offset query paginating to the millionth record is often 100x slower than paginating to page 1.

Compound Sort Keys and Tie-Breaking

The most important rule for correct keyset pagination is that the sort key must uniquely identify a position in the result set. If your sort column is not unique (like created_at), you must add a secondary column - typically the primary key - to break ties deterministically. Without this, records at the boundary with equal sort values can be skipped or duplicated.

In PostgreSQL, using row value comparison syntax handles this cleanly. In databases that don't support row value comparisons, or when the sort direction is mixed (some columns ascending, some descending), you need to expand it manually:

-- Equivalent expansion when row value comparison isn't available
-- For ORDER BY created_at DESC, id DESC
WHERE created_at < $cursor_created_at
   OR (created_at = $cursor_created_at AND id < $cursor_id)

This expanded form is logically identical but verbose. It also becomes unwieldy with three or more sort columns. Row value comparison syntax is strongly preferred where available.

Handling Reverse Navigation

Going backward (to the previous page) with keyset pagination requires either: storing the cursors for each page visited (client-side cursor stack), inverting the sort order and the comparison operator for the reverse query, or maintaining a doubly-linked cursor structure. Most production implementations choose the client-side cursor stack: keep an array of previously seen cursors and pop from it when the user navigates back. This is simple and reliable.

// Client-side cursor stack for bidirectional navigation
class PaginationController {
  private cursorStack: string[] = [];
  private currentCursor: string | null = null;

  async nextPage(fetchFn: (cursor: string | null) => Promise<PaginationResult<unknown>>) {
    if (this.currentCursor) {
      this.cursorStack.push(this.currentCursor);
    }
    const result = await fetchFn(this.currentCursor);
    this.currentCursor = result.nextCursor;
    return result;
  }

  async previousPage(fetchFn: (cursor: string | null) => Promise<PaginationResult<unknown>>) {
    const prevCursor = this.cursorStack.pop() ?? null;
    this.currentCursor = prevCursor;
    return fetchFn(prevCursor);
  }

  get canGoBack(): boolean {
    return this.cursorStack.length > 0;
  }
}

Performance, Consistency, and Tradeoff Analysis

Comparative Performance

At small dataset sizes (under ~10,000 rows), all three approaches perform similarly. The differences become meaningful at scale. Offset pagination degrades linearly with offset depth - a common benchmark finding is that OFFSET 100000 with a well-indexed table is 10-50x slower than OFFSET 0 on large tables. Keyset pagination does not exhibit this degradation: page 5000 takes approximately the same time as page 1.

Total count queries (for "showing 1,240 results, page 4 of 62") are expensive for all strategies because they require a full table scan or index scan. Keyset APIs typically abandon exact counts in favor of "has next page" indicators, which are cheap to compute by fetching one extra record. If exact counts are required, consider maintaining a denormalized count in a separate table updated by triggers, or accepting that count queries run periodically rather than per request.

Consistency Under Concurrent Writes

Each strategy has a different consistency profile when rows are inserted or deleted between page requests:

API Design Implications

The pagination strategy you choose shapes your API contract in ways that are hard to change later. Offset-based APIs can include total counts and support random page access. Cursor-based APIs cannot. Cursor-based APIs enable stable, real-time feeds; offset APIs do not. Consider these characteristics when designing a public API: changing pagination strategy after external consumers have integrated is a breaking change.

For internal APIs or microservices, migrating from offset to cursor pagination mid-stream is feasible but requires a versioned transition period. For public APIs, treat the pagination strategy as part of your API version contract from day one.

Pitfalls and Anti-Patterns

Using Non-Unique Cursor Values

One of the most common mistakes in keyset pagination implementations is using a non-unique column as the sole cursor value - for example, only created_at when multiple rows can have the same timestamp. This produces incorrect results silently: rows at the boundary with equal timestamps may be duplicated or skipped depending on the sort stability of the database.

The fix is always to include a unique tiebreaker, typically the primary key, in the sort key and cursor. This is not optional - it is a correctness requirement.

Exposing Internal IDs in Cursors

Some teams base64-encode their primary keys and call them cursors, effectively leaking internal autoincrement IDs to clients. This creates two problems: clients can infer the total record count and insertion rate from ID gaps, and changing the cursor format later (e.g., switching from integer IDs to UUIDs) becomes a breaking change. The cursor should encode whatever is needed for the query but remain genuinely opaque - signed with an HMAC if tampering is a concern, or structured to avoid leaking sensitive internals.

Forgetting to Index the Cursor Columns

Keyset pagination is only fast when the database can use an index seek for the boundary condition. If the WHERE clause on your cursor columns cannot be satisfied by an index, you get a full table scan on every page request - performance worse than offset pagination because you have both the scan cost and the boundary evaluation cost.

Always run EXPLAIN / EXPLAIN ANALYZE on your keyset queries and verify that an index seek (not scan) is being used. Create composite indexes that cover the exact column order and sort direction of your ORDER BY clause.

Using Mutable Columns as Sort Keys

If your sort key is a column that users can update (like status, priority, or updated_at), a keyset cursor based on that column becomes unstable: records can move past the cursor boundary between requests, causing them to appear on the wrong page or disappear entirely. Prefer immutable sort keys - created_at combined with an autoincrement or UUID primary key is the most robust choice for most use cases.

Mixing Pagination Strategies Within a Product

It is tempting to use offset pagination for some endpoints (where it is easy) and cursor pagination for others (where performance matters). This leads to inconsistent client-side code, varying API contracts, and split engineering knowledge. Standardizing on one strategy - or providing a clear policy for when each is appropriate - pays dividends in maintainability and developer experience.

Best Practices for Production Systems

Always Include a Tiebreaker in the Sort Key

For any keyset or cursor implementation, the sort key must produce a globally unique ordering. In practice this means appending the primary key as the last sort column. For PostgreSQL with UUID primary keys, sort by (created_at DESC, id DESC). For composite natural keys, include enough columns to guarantee uniqueness. Document this explicitly in your codebase - it is easy for a future developer to "simplify" the sort key and introduce a subtle correctness bug.

Make Cursors Opaque and Versioned

Encode cursors as base64 or another opaque format, and include a version field inside the cursor payload. This allows you to change the cursor's internal structure (e.g., adding a new sort field, switching ID format) without invalidating all existing client cursors at once. A version field lets you decode old cursors using legacy logic while producing new cursors going forward.

interface CursorPayload {
  v: number;         // version
  id: string;
  ts: string;        // ISO 8601 timestamp
}

function encodeCursor(id: string, timestamp: Date): string {
  const payload: CursorPayload = {
    v: 2,
    id,
    ts: timestamp.toISOString()
  };
  return Buffer.from(JSON.stringify(payload)).toString('base64url');
}

function decodeCursor(cursor: string): { id: string; timestamp: Date } {
  const payload: CursorPayload = JSON.parse(
    Buffer.from(cursor, 'base64url').toString('utf8')
  );
  
  if (payload.v === 1) {
    // Legacy cursor: id was a number, convert
    return { id: String(payload.id), timestamp: new Date(payload.ts) };
  }
  
  return { id: payload.id, timestamp: new Date(payload.ts) };
}

Set Reasonable Maximum Page Sizes

Always enforce a server-side maximum on limit / page_size. Allowing clients to request unlimited records from a single endpoint is a denial-of-service vector and can exhaust memory. A maximum of 100-500 records per page is typical; some APIs use lower limits (20-50) for resource-intensive queries. Return the actual limit used in the response so clients can detect when their requested limit was capped.

Use FETCH FIRST N+1 ROWS ONLY to Detect End of Data

Rather than running a separate COUNT(*) query (which is expensive), fetch one more record than the requested page size. If N+1 records are returned, there is a next page. If N or fewer are returned, the client has reached the end. Return has_next_page: true/false to the client. This pattern costs one extra row of I/O per query, which is negligible compared to the cost of a count query.

Monitor Slow Queries in Production

Even with the correct indexes, pagination queries can degrade in unexpected ways as data distributions change. Set up query-level performance monitoring (using tools like pg_stat_statements in PostgreSQL, or APM instrumentation in your application layer) and alert on queries that exceed a latency threshold. Deep keyset pages are almost always fast, but changes to query plans as the table grows can introduce surprises. Treat pagination query performance as a first-class production metric.

Key Takeaways

Here are five practical actions engineers can take immediately:

  1. Audit your offset-based APIs for large table offsets. If any endpoint allows OFFSET values that regularly exceed 1,000 rows, evaluate migrating to keyset pagination.

  2. Add a tiebreaker column to every sort key. If your sort key is not unique, add the primary key as the last sort column before shipping any keyset implementation.

  3. Run EXPLAIN ANALYZE on your keyset queries. Verify that the query uses an index seek, not a full scan, before deploying pagination to production.

  4. Treat cursors as a versioned contract. Include a version field in cursor payloads from day one, even if you never need to change the format - the cost is negligible and the future benefit is real.

  5. Document your choice. Add a comment in the codebase explaining why a particular pagination strategy was chosen for a given endpoint, especially if the choice is non-obvious (e.g., offset chosen for a small admin table, keyset chosen for a high-write feed).

Analogies and Mental Models

Offset pagination is like reading a book by counting pages from the beginning every time. You close the book, and next time you want page 100, you flip through 99 pages to get there - even though you were just there. The book doesn't remember your place.

Keyset pagination is like a proper bookmark. You mark the exact word where you stopped, and next time you open to precisely that word immediately. It doesn't matter how long the book is or how many pages came before - you go straight to your bookmark.

Cursor-based pagination is the API design layer that hands you the bookmark in a form you can keep between sessions. The bookmark itself (the keyset boundary) is the mechanism; the cursor is the packaging that makes it portable and opaque.

Another useful mental model: offset pagination gives you an address by row number (fragile, changes when rows are inserted), while keyset pagination gives you an address by content (stable, anchored to actual data values). Just as a URL based on a file path breaks when files are moved but a content-addressed URL remains valid, keyset cursors remain valid even as surrounding rows are inserted or deleted.

The 80/20 Insight

If you remember nothing else from this article, remember this: the 20% of knowledge that produces 80% of the practical improvement is the shift from "skip N rows" to "filter by last-seen value."

Almost all pagination performance problems in production systems trace back to offset amplification: deep offsets on large tables in user-facing APIs. The fix is almost always straightforward - replace OFFSET with a WHERE clause using the sort key of the last row. This change, combined with the correct composite index, eliminates the dominant cost of most pagination queries.

The rest - cursor encoding, bidirectional navigation, count avoidance, cursor versioning - is important for correctness and API quality, but the single largest leverage point is that WHERE (ts, id) < ($1, $2) is almost always faster than OFFSET $3 for any non-trivial table. Make that change first.

Conclusion

Pagination is a foundational pattern that touches almost every data-serving API. The three strategies - offset, cursor, and keyset - are not competitors for the same use case but tools optimized for different problems. Offset pagination trades simplicity and random access for poor performance at depth and consistency problems under write load. Cursor-based pagination provides a clean API contract for sequential traversal with stability under writes. Keyset pagination is the database-level mechanism that makes cursor pagination fast by anchoring queries to data values rather than row counts.

For most modern APIs serving large datasets with real-time updates - social feeds, activity logs, search results, financial records - cursor/keyset pagination is the correct default. For small administrative datasets with infrequent writes and genuine need for random page access, offset pagination remains pragmatic. The critical mistake is applying offset pagination habitually to all endpoints without considering the access patterns, table sizes, and write rates involved.

Understanding the tradeoffs at this level of detail allows you to make the choice consciously, document it for your team, and migrate strategically as your system grows. The right pagination strategy is not the one your ORM makes easiest - it is the one that matches your access patterns, your scale, and your consistency requirements.

References