Pagination at Scale: Why OFFSET Is Killing Your Performance

The Query That Gets Slower Every Page

You've built pagination. Users click through pages and your SQL looks like this:

SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

Page 1 loads in 2ms. Page 500 takes 800ms. Page 5000? Your database is sweating. Same query, same LIMIT -- so why does it get slower?

How OFFSET Actually Works

OFFSET doesn't "skip" rows. It reads and discards them.

The deeper you paginate, the more rows the database scans and throws away. This is O(offset + limit) for every single page request.

Sort  (cost=15234.12..15234.17 rows=20 width=128)
Sort Key: created_at DESC
Sort Method: top-N heapsort  Memory: 42kB
->  Seq Scan on products  (cost=0.00..12834.00 rows=500000 width=128)
Planning Time: 0.09 ms
Execution Time: 842.31 ms

That Seq Scan on products (rows=500000) tells the whole story. The database scanned 500k rows to return 20.

Why This Matters at Scale

In a real application, this creates compounding problems:

• Database CPU spikes as users (or bots) crawl deep pages
• Connection pool exhaustion -- slow queries hold connections longer
• Inconsistent results -- rows inserted between page requests cause items to appear on two pages or disappear entirely

That last one is subtle but critical. If a new row is inserted while a user is on page 5, every subsequent page shifts by one row. Users see duplicates or miss items entirely.

Cursor-Based Pagination

Instead of saying "skip N rows," cursor pagination says "give me rows after this specific point."

-- Instead of OFFSET
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;
 
-- Use a cursor (the last item's created_at from the previous page)
SELECT * FROM products
WHERE created_at < '2026-01-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=0.42..1.28 rows=20 width=128)
->  Index Scan Backward using idx_products_created_at on products
    (cost=0.42..21234.56 rows=250000 width=128)
      Index Cond: (created_at < '2026-01-15 10:30:00+00'::timestamptz)
Planning Time: 0.11 ms
Execution Time: 0.089 ms

0.089ms regardless of how deep into the dataset you are. The Index Scan Backward means the database walked the B-Tree index directly to the right position.

The Cursor Encoding Pattern

In practice, you don't expose raw column values as cursors. You encode them:

// API Response
{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0xNVQxMDozMDowMFoiLCJpZCI6NDU2N30=",
    "has_more": true
  }
}

That cursor is a base64-encoded JSON object containing enough information to resume:

// Decoded cursor
{ "created_at": "2026-01-15T10:30:00Z", "id": 4567 }

When OFFSET Is Still Fine

Cursor pagination isn't always the right answer. OFFSET works well when:

The Hybrid Approach

Some systems use both. GitHub's API, for example:

• OFFSET for the first ~1000 results (user-facing, low depth)
• Cursor for deep pagination and API consumers (automated, potentially deep)

The implementation looks like:

// Accept both pagination styles
interface PaginationParams {
  // OFFSET style
  page?: number;
  per_page?: number;
  // Cursor style
  after?: string;
  first?: number;
}
 
function buildQuery(params: PaginationParams) {
  if (params.after) {
    const cursor = decodeCursor(params.after);
    return sql`
      SELECT * FROM products
      WHERE (created_at, id) < (${cursor.created_at}, ${cursor.id})
      ORDER BY created_at DESC, id DESC
      LIMIT ${params.first || 20}
    `;
  }
 
  // Fallback to OFFSET with a hard cap
  const page = Math.min(params.page || 1, 50); // Cap at page 50
  const offset = (page - 1) * (params.per_page || 20);
  return sql`
    SELECT * FROM products
    ORDER BY created_at DESC
    LIMIT ${params.per_page || 20}
    OFFSET ${offset}
  `;
}

Keyset Pagination: The SQL-Level Pattern

Cursor pagination is the API pattern. Under the hood, it uses keyset pagination -- a SQL technique:

-- Standard keyset pagination (single column)
SELECT * FROM products
WHERE created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;
 
-- Composite keyset (handles duplicate sort values)
SELECT * FROM products
WHERE (created_at, id) < (:last_seen_created_at, :last_seen_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The composite version uses row value comparison -- a SQL standard feature that compares tuples in order. PostgreSQL, MySQL 8+, and SQLite all support this syntax.

Key Takeaway

References

• Use The Index, Luke: Pagination Done the Right Way -- The definitive guide to keyset pagination
• Slack Engineering: Evolving API Pagination -- Real-world cursor pagination at scale
• PostgreSQL Row Value Comparisons -- Official docs on tuple comparison