OFFSET Pagination Will Not Scale. Might I Suggest Keyset?

Good evening. I see you have brought your pagination.

I should make an observation. You have OFFSET pagination in production. I can tell because nearly everyone does. LIMIT 20 OFFSET 40 for page 3. LIMIT 20 OFFSET 199980 for page 10,000. It is the default pagination approach in every tutorial, every ORM, and every API framework you are likely to encounter. It is also, at scale, one of the most quietly expensive patterns in a PostgreSQL database.

The mechanism is easy to understand, which is precisely why it proliferates. "Skip these rows, give me the next batch." Simple. Intuitive. And at page 1, perfectly fast.

The trouble begins at depth. OFFSET pagination works by sorting the entire result set, counting forward N rows, discarding them, and returning the next batch. On page 10,000, PostgreSQL sorts ten million rows and discards 199,980 of them. It is the database equivalent of reading a phone book from the beginning every time you need a number on page 400. One might politely observe that there are more efficient approaches to finding the Smiths.

I should like to show you one such approach. It is called keyset pagination, and it changes the performance characteristics of deep pagination from linear degradation to constant time. The difference, as we shall see, is not subtle.

The anatomy of OFFSET degradation

To understand why keyset pagination matters, one must first understand exactly how OFFSET fails. Not approximately. Precisely.

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

-- Page 100: slower
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1980;
-- 28ms

-- Page 10,000: unacceptable
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 199980;
-- 1,420ms

Three queries against the same table with the same sort order and the same page size. The only difference is the OFFSET value. Page 1 completes in under a millisecond. Page 10,000 takes 1.4 seconds. The relationship is linear — double the OFFSET, double the cost.

The EXPLAIN output reveals the mechanism:

-- EXPLAIN ANALYZE for page 10,000:
Limit  (actual time=1418.2..1418.3 rows=20 loops=1)
  ->  Sort  (actual time=1012.4..1418.2 rows=200000 loops=1)
        Sort Key: created_at DESC
        Sort Method: external merge  Disk: 156MB
        ->  Seq Scan on orders  (actual time=0.01..312.4 rows=10000000 loops=1)

-- PostgreSQL sorted ALL 10 million rows, then skipped 199,980
-- and returned 20. The first 199,980 sorted rows were discarded.

PostgreSQL performed a sequential scan on all 10 million rows, sorted them (spilling 156MB to disk because the working set exceeds work_mem), then discarded 199,980 rows. The 20 rows you wanted are the ones that survived. Every prior row was fully sorted and then thrown away.

I want to be precise about what "thrown away" means here. PostgreSQL did not merely skip over those rows. It read them from disk or shared buffers, compared them during the sort, wrote intermediate results to temporary files when memory was exhausted, and then — having done all of that work — discarded them. The CPU time, the I/O bandwidth, the memory pressure: all real. All paid for. All wasted.

At page 50,000, this query takes 7.1 seconds. At page 100,000 — if your users or API consumers ever reach that depth — it takes over 14 seconds. The cost is unbounded because the work grows without limit.

What is keyset pagination, precisely?

Keyset pagination — also called cursor-based pagination or the seek method — replaces the question "skip N rows" with "give me the rows that come after this specific position." Instead of counting from the beginning every time, it picks up where it left off.

-- Keyset pagination: remember where you left off.
-- First page (same as OFFSET):
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: use the last row's values as a cursor:
SELECT * FROM orders
WHERE (created_at, id) < ('2025-03-01 14:23:07', 9847231)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 0.9ms — regardless of which "page" you're on.

The first page is identical to OFFSET — there is no cursor yet, so you simply request the first N rows. The difference emerges on every subsequent page. Instead of OFFSET 20, you pass the values from the last row of the previous page as a filter: "give me rows where (created_at, id) comes before this point in the sort order."

The critical difference: PostgreSQL uses the composite index to jump directly to the cursor position. There is no sorting, no scanning, no discarding.

-- EXPLAIN ANALYZE for keyset (any depth):
Limit  (actual time=0.04..0.12 rows=20 loops=1)
  ->  Index Scan Backward using idx_orders_created_id on orders
        (actual time=0.03..0.11 rows=20 loops=1)
        Index Cond: (ROW(created_at, id) < ROW('2025-03-01 14:23:07', 9847231))
        Buffers: shared hit=4

-- 4 buffer hits. No sort. No sequential scan.
-- The index jumps directly to the cursor position.

Four buffer hits. No sort node. No sequential scan. The index provides the ordering, and the WHERE clause positions the cursor. Whether you are on page 1 or page 50,000, the query reads exactly the same number of index pages and returns exactly the same number of rows. The cost is constant.

If you will permit me a moment of directness: this is not an optimization. This is a fundamentally different algorithm. OFFSET does O(n) work where n is the offset depth. Keyset does O(k) work where k is the page size. The offset depth has been removed from the equation entirely.

The benchmarks, page by page

Ten million rows. ORDER BY created_at DESC, id DESC. Twenty rows per page. Both approaches use the same composite index. The same hardware. The same PostgreSQL configuration. The only variable is the pagination strategy.

At page 50,000, keyset pagination is 7,889 times faster. The keyset column is unchanging — 0.9ms at every depth. The OFFSET column grows without bound. At page 1, OFFSET is marginally faster (0.8ms vs 0.9ms), because the cursor comparison adds a trivial amount of work when there is nothing to skip. By page 100, keyset is already 31 times faster. The crossover happens almost immediately.

I should note that these benchmarks were conducted with warm caches. Cold-cache performance makes the case even stronger — OFFSET at depth causes massive I/O because it must read and sort rows that ultimately get discarded, thrashing the shared buffer pool in the process. Keyset reads only the index pages and heap pages it actually needs.

The row value comparison, explained

The syntax WHERE (created_at, id) < (...) may look unfamiliar if you have not encountered PostgreSQL's row value comparisons before. A brief clarification is in order.

-- Row value comparison: PostgreSQL handles this natively.
-- These two queries are equivalent:
WHERE (created_at, id) < ('2025-03-01 14:23:07', 9847231)

-- Expanded form (what it means logically):
WHERE created_at < '2025-03-01 14:23:07'
   OR (created_at = '2025-03-01 14:23:07' AND id < 9847231)

-- The row value syntax is cleaner, and PostgreSQL optimizes
-- both forms to use the composite index identically.

Row value comparisons are part of the SQL standard, and PostgreSQL handles them natively. The tuple (created_at, id) < (value1, value2) means: "created_at is less than value1, OR created_at equals value1 and id is less than value2." This is precisely the logic needed for keyset pagination — it implements a lexicographic comparison across the sort columns.

The planner recognizes this pattern and maps it directly to a range scan on the composite index. Both the tuple syntax and the expanded OR form produce the same query plan, but the tuple syntax is cleaner and less error-prone when you have multiple sort columns.

The index that makes it work

-- Keyset pagination requires a composite index matching the ORDER BY:
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

-- The index must cover all columns in your ORDER BY clause,
-- in the same order and direction.

Keyset pagination requires a composite index that matches your ORDER BY clause precisely — same columns, same order, same direction. Without this index, PostgreSQL cannot jump to the cursor position. It falls back to a sort, and you lose the constant-time property that makes keyset pagination worthwhile.

Three requirements, none of which are optional:

1. A deterministic sort order. The ORDER BY must produce a unique ordering. If created_at has duplicate values — and it often does, particularly with timestamp precision — add a tiebreaker column. The primary key id is the natural choice. Without a tiebreaker, rows with identical sort values may appear on either side of a page boundary, producing inconsistent results.
2. A composite index matching the ORDER BY. The index must cover all sort columns in the same order and direction. (created_at DESC, id DESC) in the ORDER BY requires (created_at DESC, id DESC) in the index. PostgreSQL can scan an index backward, so (created_at ASC, id ASC) also works for a DESC query — but only if all columns reverse together. Mixed directions like created_at DESC, id ASC require an index with those exact directions.
3. A cursor from the previous page. The client must send the last-seen values to request the next page. This is a position marker, not a page number. The client does not need to understand the cursor — it simply passes it back.

Multiple sort columns

Keyset pagination extends naturally to any number of sort columns. The principle is the same: the cursor includes all values needed to identify a unique position in the sort order.

-- Keyset pagination with multiple sort columns:
-- Example: orders sorted by priority DESC, created_at DESC, id DESC
CREATE INDEX idx_orders_priority_created_id
  ON orders (priority DESC, created_at DESC, id DESC);

-- First page:
SELECT * FROM orders
ORDER BY priority DESC, created_at DESC, id DESC
LIMIT 20;

-- Next page (using last row's values):
SELECT * FROM orders
WHERE (priority, created_at, id) < (3, '2025-03-01 14:23:07', 9847231)
ORDER BY priority DESC, created_at DESC, id DESC
LIMIT 20;

The composite index must include every column in the ORDER BY, in the same order. The cursor must include every value. And the tiebreaker — typically id — must be the last column, ensuring that no two rows share the same cursor position.

I should note that each additional sort column adds complexity to the cursor and the index. Three columns is perfectly reasonable. Seven columns suggests that the sort order may benefit from simplification — or that a computed sort key (a single column that encodes the desired order) would be more practical.

Navigating backward

A common objection to keyset pagination is that it only moves forward. This is not quite accurate. Bidirectional navigation is entirely possible — it requires one additional query pattern.

-- Bidirectional keyset: previous page support
-- Next page (rows AFTER the cursor — descending order):
SELECT * FROM orders
WHERE (created_at, id) < ('2025-03-01 14:23:07', 9847231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Previous page (rows BEFORE the cursor — ascending order, then reverse):
SELECT * FROM (
  SELECT * FROM orders
  WHERE (created_at, id) > ('2025-02-28 09:15:22', 9846004)
  ORDER BY created_at ASC, id ASC
  LIMIT 20
) subquery
ORDER BY created_at DESC, id DESC;

-- The subquery fetches 20 rows forward from the cursor,
-- then the outer query reverses them to maintain DESC order.

The previous-page query reverses the comparison direction and sort order, fetches the rows, then reverses them back to the expected order. The outer query handles the reversal. The index supports both scan directions natively — a backward index scan on (created_at DESC, id DESC) is simply a forward scan on the equivalent ascending order.

This gives you "next page" and "previous page" navigation. What it does not give you is "jump to page 4,271." That limitation is real, and I shall address it honestly in a moment.

The trade-offs, honestly

I would be a poor waiter indeed if I presented keyset pagination without acknowledging what it cannot do.

No random access. The principal limitation is that keyset pagination is sequential. You can go to the next page or the previous page, but you cannot jump to page 4,271 directly. This is a meaningful constraint for interfaces that display page numbers — the traditional "1 2 3 ... 47 48 49" navigation bar becomes impossible to implement faithfully. For infinite scroll, API pagination, "load more" buttons, and data exports — anywhere the user progresses sequentially through results — this constraint is irrelevant.

Implementation complexity. OFFSET pagination is three words: LIMIT 20 OFFSET 40. Keyset pagination requires cursor management, composite index design, careful handling of sort directions, and slightly more complex API contracts. The implementation is not difficult, but it is not trivial either. For a quick internal tool that will never paginate past page 10, OFFSET's simplicity has genuine value.

Cursor validity. Keyset cursors are stateless — they encode a position, not a pointer to a specific row. This is a strength (no server-side state, no expiration) but also means the cursor position can shift if rows before the cursor are deleted or updated. In practice, this rarely causes visible issues, but it is worth understanding.

What keyset pagination gives you in return is worth the complexity: constant-time performance at any depth, and immunity to the consistency problems that plague OFFSET under concurrent writes.

The concurrent insert problem

This is the trade-off that rarely appears in pagination tutorials, and it is worth examining. OFFSET pagination has a consistency problem that keyset pagination does not.

-- OFFSET pagination under concurrent inserts:

-- User requests page 3 (rows 41-60):
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;
-- Returns rows 41-60. User sees order #9847231.

-- New order inserted (becomes row 1 in sort order).

-- User requests page 4 (rows 61-80):
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 60;
-- Row 60 is now what was previously row 59.
-- Order #9847231 (previously row 41) is now row 42.
-- It was on page 3 before. It's STILL on page 3.
-- But the user already saw page 3. They'll never see order #9847231.

-- With keyset, this cannot happen:
SELECT * FROM orders
WHERE (created_at, id) < ('2025-03-01 14:23:07', 9847231)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- The cursor is anchored. New inserts above the cursor don't affect it.

With OFFSET, every insert or delete shifts the page boundaries. A row that was on page 3 when the user viewed it may still be on page 3 when they advance to page 4 — meaning a different row has been pushed off page 3 and the user will never see it. Alternatively, a row may appear on both page 3 and page 4 if a deletion shifts boundaries the other direction.

For a blog listing or a product catalog with infrequent updates, this rarely matters. For an activity feed, a transaction log, an audit trail, or any dataset where completeness is important — where missing a row or seeing a row twice constitutes a bug — keyset pagination's cursor stability is not a nicety. It is a correctness requirement.

The total count problem

Regardless of which pagination strategy you use, displaying "Page 47 of 2,341" requires knowing the total count. On large tables, COUNT(*) is expensive — PostgreSQL must scan the table (or an index) to produce an exact count, because MVCC means different transactions may see different row counts.

-- Exact count on large tables is expensive:
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 847ms on 10M rows (sequential scan)

-- For "Page X of Y" display, use an estimate instead:
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';
-- 0.1ms — returns the planner's row estimate

-- For filtered counts, use EXPLAIN to get the estimate:
-- Parse the 'rows=' from EXPLAIN output programmatically.
-- This gives you "approximately 50,000 results" instead of
-- scanning the entire table for an exact count.

For many interfaces, an estimate is sufficient. "Approximately 50,000 results" is honest and fast. The pg_class.reltuples statistic gives you the planner's estimate in microseconds. For filtered counts, you can extract the row estimate from EXPLAIN output programmatically.

If you genuinely need an exact count and your table has millions of rows, consider maintaining a counter cache or using a materialized count that refreshes periodically. The count query and the pagination query are separate concerns — choosing keyset pagination does not make counts harder. Both strategies face the same COUNT(*) cost.

Cursor encoding for APIs

For REST or GraphQL APIs, the cursor should be opaque to the client. The client receives a string, passes it back to request the next page, and never needs to parse or understand its contents. This is a deliberate design choice — it allows you to change the cursor format, add columns, or alter the sort order without breaking existing clients.

// Encode cursor as opaque base64 token for the API:
function encodeCursor(row) {
  return Buffer.from(JSON.stringify({
    created_at: row.created_at,
    id: row.id
  })).toString('base64url');
}

function decodeCursor(token) {
  return JSON.parse(Buffer.from(token, 'base64url').toString());
}

// API response:
// {
//   "data": [...],
//   "cursors": {
//     "next": "eyJjcmVhdGVkX2F0IjoiMjAyNS0wMy0wMSIsImlkIjo5ODQ3MjMxfQ",
//     "prev": "eyJjcmVhdGVkX2F0IjoiMjAyNS0wMy0wMSIsImlkIjo5ODQ3MjUwfQ"
//   }
// }

Base64url encoding is the common choice — it produces URL-safe strings without padding characters. The response includes a next cursor (from the last row) and optionally a prev cursor (from the first row) for bidirectional navigation.

A word of caution: do not expose raw column values in the cursor. If your cursor is ?after_id=9847231&after_date=2025-03-01, clients will inevitably hardcode assumptions about the cursor format. Opaque tokens prevent this. They also prevent clients from fabricating cursors to probe data they should not see — though proper authorization should handle that regardless.

GraphQL and the Relay specification

If you are building a GraphQL API, the Relay Connection specification already prescribes cursor-based pagination as the standard pattern. This is not coincidence — Facebook designed Relay after encountering exactly the scaling problems that OFFSET pagination creates.

# GraphQL — Relay-style cursor pagination
# This is the standard pattern for GraphQL APIs.

type Query {
  orders(first: Int, after: String): OrderConnection!
}

type OrderConnection {
  edges: [OrderEdge!]!
  pageInfo: PageInfo!
}

type OrderEdge {
  cursor: String!
  node: Order!
}

type PageInfo {
  hasNextPage: Boolean!
  endCursor: String
}

# The cursor is opaque — clients pass it back without parsing.
# query { orders(first: 20, after: "eyJjcmVhdGV...") { ... } }

The Relay specification provides a well-defined contract: first/after for forward pagination, last/before for backward pagination, edges with per-row cursors, and pageInfo with hasNextPage. If your API follows this specification, keyset pagination is already the expected pattern. Your clients and tooling are designed for it.

How do ORMs handle keyset pagination?

Framework support varies considerably. Some ORMs have native cursor pagination; others require you to write the query yourself. I shall be direct about the state of affairs in each ecosystem.

// Prisma — cursor-based pagination
const page = await prisma.order.findMany({
  take: 20,
  skip: 1,  // skip the cursor row itself
  cursor: { id: lastSeenId },
  orderBy: { createdAt: 'desc' },
});

Prisma's cursor-based pagination is clean and well-documented. The cursor parameter identifies the starting row, skip: 1 excludes the cursor row itself, and take sets the page size. This is the best ORM experience for keyset pagination that I have encountered.

# Django — keyset with django-cursor-pagination or manual:
from myapp.models import Order

last_created = '2025-03-01T14:23:07Z'
last_id = 9847231

orders = Order.objects.filter(
    created_at__lt=last_created
).union(
    Order.objects.filter(
        created_at=last_created, id__lt=last_id
    )
).order_by('-created_at', '-id')[:20]

Django does not include keyset pagination natively. The manual implementation using filter and union works but is verbose. The django-cursor-pagination package provides a cleaner API, though you should verify it generates efficient SQL — some third-party packages construct the query less efficiently than a hand-written version.

# Ruby on Rails — manual keyset pagination
class OrdersController < ApplicationController
  def index
    scope = Order.order(created_at: :desc, id: :desc).limit(20)

    if params[:after]
      cursor = JSON.parse(Base64.urlsafe_decode64(params[:after]))
      scope = scope.where(
        "(created_at, id) < (?, ?)",
        cursor["created_at"], cursor["id"]
      )
    end

    @orders = scope.to_a
    @next_cursor = encode_cursor(@orders.last) if @orders.size == 20
  end

  private

  def encode_cursor(record)
    Base64.urlsafe_encode64({
      created_at: record.created_at.iso8601(6),
      id: record.id
    }.to_json)
  end
end

Rails, like Django, has no built-in keyset pagination. ActiveRecord's where supports tuple comparison syntax, which keeps the implementation clean. The order_query gem and pagy gem's keyset extra provide pre-built solutions.

# SQLAlchemy — keyset with tuple comparison
from sqlalchemy import select, tuple_

stmt = select(Order).order_by(
    Order.created_at.desc(),
    Order.id.desc()
).limit(20)

if cursor:
    stmt = stmt.where(
        tuple_(Order.created_at, Order.id) <
        tuple_(cursor.created_at, cursor.id)
    )

result = await session.execute(stmt)
orders = result.scalars().all()

SQLAlchemy's tuple_ construct maps directly to PostgreSQL's row value comparison syntax. The resulting SQL is clean, and the async session support makes this suitable for FastAPI and other async frameworks.

The common thread: regardless of the framework, the implementation is straightforward once you understand the three requirements — deterministic sort, composite index, cursor from the previous page. The ORM syntax varies, but the SQL pattern is always the same.

When OFFSET is still the right answer

I would not wish to be dogmatic about this. A waiter who insists on the finest china for every occasion is exhausting, not helpful. OFFSET is perfectly appropriate when:

• The result set is small. Paginating through 500 rows with OFFSET is fast at any depth. The degradation only manifests at scale — if your table has fewer than 10,000 rows and is unlikely to grow beyond that, OFFSET will never be your bottleneck.
• Deep pagination is structurally impossible. If your UI caps pagination at page 10 — and enforces this on the server, not just in the UI — then the maximum OFFSET is 180 rows. The cost is bounded and trivial. A search results page with 10 pages of results is a perfectly reasonable candidate for OFFSET.
• Random access is genuinely required. If users need to jump to page 4,271 — and this is a real requirement tested with real users, not a legacy interface assumption — then OFFSET is the only straightforward approach. Consider whether this need is genuine. In my experience, "jump to page N" is frequently a feature that exists in the interface but is used by approximately nobody. But when it is used, it is used with conviction, and keyset cannot serve it.
• The query is already filtered to a small subset. If your WHERE clause reduces 10 million rows to 200, the OFFSET cost is proportional to the filtered result, not the table size. PostgreSQL's optimizer handles this well — it applies filters before sorting. A well-indexed filter that reduces the working set to a few hundred rows makes the pagination strategy nearly irrelevant.

For APIs that serve mobile apps, infinite-scroll interfaces, data exports, webhook event logs, or any context where pagination depth is unbounded — keyset is not an optimization. It is a requirement.

A migration path: OFFSET to keyset

If you have an existing API with OFFSET pagination and would like to migrate, I would suggest the following approach:

1. Add the composite index first. This benefits your OFFSET queries too — PostgreSQL can use the index to avoid a full sort, even with OFFSET. The index is valuable regardless of the pagination strategy.
2. Add cursor support alongside OFFSET. Return cursor tokens in your API responses (next_cursor, prev_cursor) while continuing to support ?page=N. Clients can adopt cursors incrementally.
3. Deprecate OFFSET with a timeline. Once cursor support is available, announce a deprecation date for page-number pagination. This gives existing clients time to migrate.
4. Remove OFFSET support. Or, if backwards compatibility is paramount, cap the maximum OFFSET to a reasonable depth (page 100, say) and return an error with a message directing clients to use cursor pagination for deeper results.

The composite index in step 1 is the only database change. The rest is API versioning — a solved problem in every framework.