Cursor vs Offset Pagination: The 17x Performance Gap at Scale

OFFSET 10000 forces PostgreSQL to scan and discard 10,000 rows before returning results. At page 1 (OFFSET 0), your query takes 10ms. At page 1,000 (OFFSET 10,000), the same query on a 1-million-row table can take 5,000ms. Benchmarks show cursor pagination is 17x faster than offset for deep pages on large datasets. I hit this wall when building an ERP transaction history endpoint — page 500 of an invoice list was timing out. Switching to cursor pagination fixed it in an afternoon.

Why Offset Pagination Is Slow

SQL's OFFSET clause tells the database to skip N rows before returning results. To do this, PostgreSQL must identify the first N rows (which requires scanning or indexing), throw them away, and then return the next M rows. As OFFSET grows, so does the work. This is O(OFFSET) — the cost grows linearly with how deep into the dataset you go. For a table with a B-tree index on the sort column, the index scan is fast for small offsets. For large offsets, the database still must traverse the index from the beginning to reach the offset position.

The Data Consistency Problem

Offset pagination has a second problem: data consistency. If you're on page 5 of an invoice list (OFFSET 40, LIMIT 10) and a new invoice is inserted at the top of the sort order, when you request page 6, you'll either see a duplicate of the last invoice from page 5 or skip an invoice entirely. This is because OFFSET is a positional operation — it doesn't know about records, only row positions. In high-write systems like an ERP, this means users paginating through transaction history can get inconsistent views.

How Cursor Pagination Works

Cursor pagination replaces OFFSET with a WHERE clause that filters by the last seen value. Instead of 'give me rows 10,001 to 10,010', you say 'give me the 10 rows where created_at < $lastCursor'. This is a range query on an indexed column — O(log N) regardless of how many pages deep you are. The cursor is an opaque token (typically a base64-encoded value of the sort key, or a combination of sort key + primary key for stability) that the client sends with each request. The server decodes it, uses it in the WHERE clause, and returns the next page along with the next cursor.

Offset Pagination Performance (1M row table):
─────────────────────────────────────────────────
Page 1    (OFFSET 0):        ~10ms  ✓
Page 10   (OFFSET 90):       ~12ms  ✓
Page 100  (OFFSET 900):      ~40ms  ⚠
Page 1000 (OFFSET 9,000):    ~400ms ✗
Page 5000 (OFFSET 49,000):  ~2000ms ✗ TIMEOUT RISK

Cursor Pagination Performance (1M row table):
─────────────────────────────────────────────────
Any page depth:               ~5ms  ✓ O(log N)
Page 1:   WHERE TRUE LIMIT 10           → index scan start
Page 1000: WHERE (created_at, id) < ($1, $2) LIMIT 10
           → index seek to cursor position, then 10 rows

SQL comparison:
  Offset:  SELECT * FROM invoices ORDER BY created_at DESC LIMIT 10 OFFSET 9900
           ↑ must traverse 9,900 rows to find start

  Cursor:  SELECT * FROM invoices
           WHERE (created_at, id) < ('2024-03-15 10:23:44', 1234)
           ORDER BY created_at DESC, id DESC LIMIT 10
           ↑ seeks directly to cursor position in index

Implementing Cursor Pagination in NestJS

The NestJS implementation involves a cursor encoder/decoder utility, a modified query builder, and a standardized response shape. The response returns items (the current page), nextCursor (null if no more pages), and hasMore (boolean). The client stores the cursor and sends it on the next request. For admin interfaces that need jump-to-page functionality, you can offer offset pagination alongside cursor pagination — use cursor for the 'next page' button and offset only for the page number input.

Cursor Encoding

The cursor value should be opaque to the client — it's an implementation detail. Encode the raw values as base64 JSON: `Buffer.from(JSON.stringify({created_at: lastItem.createdAt, id: lastItem.id})).toString('base64url')`. The `base64url` variant avoids padding characters that can cause issues in query strings. Decode and validate on the server before using in a SQL query — always validate cursor values to prevent SQL injection and handle the case where the cursor points to a deleted record (return an empty page gracefully).

// cursor-pagination.util.ts
export function encodeCursor(createdAt: Date, id: number): string {
  return Buffer.from(
    JSON.stringify({ created_at: createdAt.toISOString(), id })
  ).toString('base64url')
}

export function decodeCursor(cursor: string): { created_at: string; id: number } {
  try {
    return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf-8'))
  } catch {
    throw new Error('Invalid cursor')
  }
}

// invoices.service.ts — NestJS cursor pagination
@Injectable()
export class InvoicesService {
  constructor(private readonly dataSource: DataSource) {}

  async findAll(limit: number, cursor?: string) {
    const take = Math.min(limit, 100) // cap page size
    let cursorWhere = ''
    const params: unknown[] = [take + 1] // fetch +1 to detect hasMore

    if (cursor) {
      const { created_at, id } = decodeCursor(cursor)
      cursorWhere = 'AND (created_at, id) < ($2, $3)'
      params.push(created_at, id)
    }

    const rows = await this.dataSource.query(
      `SELECT id, invoice_number, amount, created_at
       FROM invoices
       WHERE 1=1 ${cursorWhere}
       ORDER BY created_at DESC, id DESC
       LIMIT $1`,
      params
    )

    const hasMore = rows.length > take
    const items = hasMore ? rows.slice(0, take) : rows
    const lastItem = items[items.length - 1]

    return {
      items,
      hasMore,
      nextCursor: hasMore && lastItem
        ? encodeCursor(new Date(lastItem.created_at), lastItem.id)
        : null,
    }
  }
}

Bi-directional Cursors

Standard cursor pagination is unidirectional — you can only go forward. For a 'previous page' button, implement bi-directional cursors: maintain both a nextCursor and a prevCursor in each response. The prevCursor is derived from the first item in the current page rather than the last. The previous page query reverses the sort order and cursor direction. This doubles the complexity of your implementation but enables full prev/next navigation while preserving O(log N) performance in both directions.

Cursor Pagination in GraphQL

GraphQL's Relay Connection Specification defines a standardized cursor pagination pattern: a `Connection` type with `edges` (array of `{node, cursor}`), `pageInfo` ({hasPreviousPage, hasNextPage, startCursor, endCursor}), and `totalCount`. Implementing this in NestJS with @nestjs/graphql gives your API a standard interface that any GraphQL client can work with without knowing your cursor implementation. Many GraphQL client libraries (Apollo, Relay) have built-in support for Relay connections, providing automatic pagination state management.

Database Index Design for Cursor Queries

Cursor pagination is fast only if your sort column has an index. For `ORDER BY created_at DESC, id DESC`, create a composite index: `CREATE INDEX idx_invoices_created_id ON invoices (created_at DESC, id DESC)`. PostgreSQL can use this index for both the ORDER BY and the WHERE clause cursor filter, eliminating sequential scans entirely. If you sort by multiple columns dynamically (user can choose sort field), you'll need indexes on each sortable column — or accept that some sort combinations will fall back to sequential scans on large tables.

Sources & Further Reading

• Milan Jovanovic — Understanding Cursor Pagination and Why It's So Fast — https://www.milanjovanovic.tech/blog/understanding-cursor-pagination-and-why-its-so-fast-deep-dive
• Gusto — A Developer's Guide to API Pagination: Offset vs Cursor — https://embedded.gusto.com/blog/api-pagination/
• Contentful — Why Contentful is Moving to Cursor-Based Pagination — https://www.contentful.com/blog/cursor-based-pagination/