Cursor-Based Pagination in SQL: Beyond OFFSET

Every developer learns LIMIT and OFFSET early. Page 1 is OFFSET 0, page 2 is OFFSET 20, page 50 is OFFSET 980. It looks clean. It works. And then someone navigates to page 500 on a table with 10 million rows, and the database scans and discards 10 million rows just to return 20.

Cursor-based pagination, also called keyset pagination, solves this by remembering where the last page ended and starting the next query from that point. The database jumps directly to the right place using an index, regardless of how deep into the dataset you are.

The OFFSET problem

-- Page 1: fast
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 500: slow
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 9980;

With OFFSET 9980, the database must find all rows, sort them, skip the first 9,980, and return the next 20. The index helps with sorting, but the engine still reads and discards nearly 10,000 rows. Execution time grows linearly with the offset value.

There is also a consistency problem. If a new order is inserted while the user navigates from page 5 to page 6, all subsequent offsets shift by one. The user either misses a row or sees a duplicate.

How cursor pagination works

Instead of telling the database “skip N rows,” you tell it “give me rows after this specific point.”

-- First page: no cursor, just get the first 20
SELECT id, customer_name, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

The last row returned has created_at = '2026-06-15 10:30:00' and id = 4582. For the next page:

-- Next page: start after the last row from the previous page
SELECT id, customer_name, created_at
FROM orders
WHERE (created_at, id) < ('2026-06-15 10:30:00', 4582)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The WHERE clause uses a row-value comparison. PostgreSQL evaluates (created_at, id) < ('2026-06-15 10:30:00', 4582) by first comparing created_at, then id as a tiebreaker. With a composite index on (created_at DESC, id DESC), the database jumps directly to the right position. The query takes the same time whether you are on page 2 or page 5,000.

Why the tiebreaker matters

If you paginate only by created_at, two orders with the same timestamp create ambiguity. The cursor lands between them, and you might skip rows or repeat them. Adding a unique column like id as a secondary sort guarantees a deterministic order.

The general rule: your cursor must be composed of columns that together form a unique ordering. (created_at, id) works because id is unique. (created_at) alone does not, unless timestamps are guaranteed unique.

The right index

For the query above to be fast, you need a composite index that matches the sort order:

CREATE INDEX idx_orders_cursor ON orders (created_at DESC, id DESC);

The database can walk this index forward to produce results in DESC, DESC order. Without this index, the query still works but falls back to a sequential scan and sort.

Verify with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT id, customer_name, created_at
FROM orders
WHERE (created_at, id) < ('2026-06-15 10:30:00', 4582)
ORDER BY created_at DESC, id DESC
LIMIT 20;

You should see an Index Scan or Index Only Scan, not a Seq Scan.

Encoding cursors for APIs

In an API, you do not expose raw column values to the client. Instead, encode the cursor as an opaque string:

// Encode: combine the values and base64 them
function encodeCursor(createdAt, id) {
  const payload = JSON.stringify({ c: createdAt, i: id });
  return Buffer.from(payload).toString('base64url');
}

// Decode: reverse the process
function decodeCursor(cursor) {
  const payload = JSON.parse(
    Buffer.from(cursor, 'base64url').toString('utf8')
  );
  return { createdAt: payload.c, id: payload.i };
}

The API response looks like this:

{
  "data": [ ... ],
  "pagination": {
    "next_cursor": "eyJjIjoiMjAyNi0wNi0xNVQxMDozMDowMFoiLCJpIjo0NTgyfQ",
    "has_more": true
  }
}

The client passes next_cursor back to get the next page. The server decodes it, extracts the values, and builds the WHERE clause. The client never sees or manipulates the underlying column values.

Backward pagination

Forward-only cursors are simple. If you also need a “previous page” button, return both a next_cursor and a prev_cursor:

-- Previous page: reverse the comparison and sort, then re-reverse in the app
SELECT * FROM (
  SELECT id, customer_name, created_at
  FROM orders
  WHERE (created_at, id) > ('2026-06-15 10:30:00', 4582)
  ORDER BY created_at ASC, id ASC
  LIMIT 20
) AS prev_page
ORDER BY created_at DESC, id DESC;

The inner query fetches 20 rows going backward (ascending order after the cursor). The outer query flips them back to descending order so the client receives them in the expected sequence.

Handling multiple sort columns

Users often want to sort by different columns: price, name, popularity. Each sort order needs its own cursor structure.

-- Sorting by price ascending, id as tiebreaker
SELECT id, name, price
FROM products
WHERE (price, id) > (29.99, 1042)
ORDER BY price ASC, id ASC
LIMIT 20;

The cursor for this page encodes { price, id }. If the user switches to sorting by name, the cursor is invalid and the client should start from page 1. Document this clearly in your API.

Each sort variant also needs a matching index:

CREATE INDEX idx_products_price ON products (price ASC, id ASC);
CREATE INDEX idx_products_name ON products (name ASC, id ASC);

Tradeoffs

Cursor pagination cannot jump to an arbitrary page number. There is no “go to page 47.” For most modern interfaces, this is acceptable because infinite scroll and “load more” buttons are the standard UX pattern. For admin dashboards that genuinely need page numbers, OFFSET is simpler, and the datasets are usually small enough that performance is not a concern.

Cursor pagination also requires a stable, unique sort order. If your sort column is not unique, you must add a tiebreaker. If your sort order changes between requests, cursors from the old order are meaningless.

Summary

Aspect	OFFSET	Cursor
Deep page performance	Degrades linearly	Constant
Consistency during writes	Rows shift	Stable
Arbitrary page access	Yes	No
Implementation complexity	Simple	Moderate
Index requirements	Basic	Composite, matching sort

For user-facing APIs with large datasets, cursor pagination is the clear choice. It eliminates the performance cliff that OFFSET creates and keeps results consistent even as new data arrives. The implementation cost is a cursor encoding layer and a composite index, a small price for pagination that stays fast at any depth.