Pagination in PostgreSQL: OFFSET and keyset Basic

Problem

Table event_demo, 100,000 rows, id bigint PRIMARY KEY. Two ways to fetch “20 rows starting at position 90,000”:

-- Option 1: OFFSET
SELECT id, message FROM event_demo ORDER BY id LIMIT 20 OFFSET 90000;

-- Option 2: keyset (remember the last id of the previous page)
SELECT id, message FROM event_demo WHERE id > 90000 ORDER BY id LIMIT 20;

Both return exactly 20 rows at the same logical position in the table. Measured on PostgreSQL 17:

Option	Time	Buffers
OFFSET 90000	5.507 ms	821
id > 90000 (keyset)	0.019 ms	3

290 times slower and 273 times more buffers for the same result.

OFFSET walks through 90,000 entries, keyset jumps straight to the target

Enlarge diagram

OFFSET 90000 → Index Scanskip 90,000 rows

◀ Prev▶ PlayNext ▶↺ RestartStep 1/4

OFFSET: the planner does not know where "row 90,000" sits, so it must count from the start of the index.

OFFSET cost grows linearly with depth; keyset stays flat. The events example later on is an illustrative table for multi-column cursors, not event_demo.

Core concepts

OFFSET n tells PostgreSQL: “compute the full sorted result, skip the first n rows, then keep going.” Skipping is not free: PostgreSQL still has to walk each of those n rows through the index (or read and discard them).

WHERE id > last_id says something entirely different: “locate the point right after last_id directly.” For a B-tree this is one comparison, exactly what a B-tree was built for (see B-tree index). There is no “skipped part” to walk; PostgreSQL jumps straight to the start.

How it works

OFFSET: cost grows with depth

Shallow page (OFFSET 20, i.e. page 2):

Limit  (cost=0.94..1.59 rows=20 width=19) (actual time=0.009..0.010 rows=20 loops=1)
  Buffers: local hit=3
  ->  Index Scan using event_demo_pkey on event_demo  (actual time=0.007..0.008 rows=40 loops=1)
Execution Time: 0.019 ms

Deep page (OFFSET 90000, page 4,501):

Limit  (cost=2919.89..2920.54 rows=20 width=19) (actual time=5.498..5.500 rows=20 loops=1)
  Buffers: local hit=821
  ->  Index Scan using event_demo_pkey on event_demo  (actual time=0.006..3.784 rows=90020 loops=1)
Execution Time: 5.507 ms

In both, the Index Scan underneath has to walk exactly OFFSET + LIMIT rows before it stops; the rows=40 and rows=90020 in actual prove it. The deeper the page, the more entries it walks before reaching the part to return.

This is not an implementation bug. By definition, “rows 90,000 to 90,020 in order” can only be determined after knowing all 90,000 rows before them.

Keyset: cost does not change with depth

Same position (id > 90000):

Limit  (cost=0.29..1.00 rows=20 width=19) (actual time=0.014..0.015 rows=20 loops=1)
  Buffers: shared hit=3, local hit=3
  ->  Index Scan using event_demo_pkey on event_demo
        Index Cond: (id > 90000)
Execution Time: 0.019 ms

Even deeper, near the end of the table (id > 99990):

Limit  (cost=0.29..8.47 rows=10 width=19) (actual time=0.003..0.005 rows=10 loops=1)
  Buffers: local hit=3
  ->  Index Scan using event_demo_pkey on event_demo
        Index Cond: (id > 99990)
Execution Time: 0.009 ms

3 data buffers (local hit=3) at both positions, even though one is mid-table and the other near the end.

The shared hit=3 in the first measurement does not belong to the demo table: a TEMP table only uses local buffers, so that shared part is system pages read on the first run.

Index Cond: (id > 90000) gives the B-tree a direct comparison: descend from the root to the leaf holding that value, regardless of how many rows come before it. That is why keyset does not slow down as you go deeper into the table, while OFFSET does.

Required condition: the order must be unique

ORDER BY created_at DESC alone is not enough if many rows share a created_at. PostgreSQL does not guarantee order among ties, so a page boundary can cut through a group of equal values, duplicating or dropping rows between calls.

Always add a unique column (usually the primary key) as a tie-breaker:

CREATE INDEX ON events (created_at DESC, id DESC);

-- The next page uses BOTH values from the end of the previous page
SELECT id, created_at, body FROM events
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Compare the tuple (created_at, id) < (...), not two separate AND conditions, to keep the exact meaning of “less than in the sort order”.

Keyset implementation traps

Trap	What happens	Fix
Mixed sort direction (created_at DESC, id ASC)	Tuple comparison (a, b) < (x, y) always compares in one direction and cannot express it	Write the condition explicitly as a < x OR (a = x AND b > y), or flip a column so both go the same way. An OR condition often uses the index less cleanly than a tuple comparison, so check EXPLAIN
Sort column has NULL	Comparing NULL yields unknown, NULL rows vanish from pages	Make sort columns NOT NULL, or COALESCE to a fixed value in both ORDER BY and the index
Filter (WHERE tenant_id = 42)	If the filter is not the leading index column, keyset has to filter while walking	Index (tenant_id, created_at DESC, id DESC) (column order)
Total pages (“page 3 / 450”)	SELECT count(*) scans the whole matching set, cost proportional to row count; keyset does not solve this	Drop the total, show “more available”, or use an estimate (e.g. reltuples)
Cursor exposes internal columns	The client edits the cursor to probe data	Encode it opaquely (base64 of JSON), better yet sign it with HMAC; always re-validate
Going back a page	The condition flips	Run the query with the reverse ORDER BY and >, then reverse the results in the application
Rows deleted between calls	Keyset stays correct: it continues from the cursor position, no drift as with OFFSET	Nothing to handle

Row comparison is defined left to right, stopping at the first pair that differs or is NULL. That is why both NULLs and sort direction affect the result.

When OFFSET is still fine

Situation	OFFSET	Keyset
Admin page, table under about 10,000 rows	Fine, simpler	Overkill
Need a “go to page N” box	Required	Cannot do it directly
Users rarely go past the first few pages	Fine	Not needed
Infinite feed, public API, large export	Avoid	Recommended
Table of millions of rows, users scroll deep	Slows with depth	Flat cost

Real-world example

Real-world example

An app’s activity feed has 5 million events and only a “load more” button:

1. The API returns nextCursor as base64 of the last row’s {created_at, id}.
2. The next call sends the cursor back; the server decodes it and runs WHERE (created_at, id) < (...).
3. The cost per page stays nearly constant no matter how far the user scrolls.

An admin page with a “go to page 200” box on a small table uses OFFSET; for a large table, keep date markers to jump near the position and then keyset from there.

Trade-offs

Pros

• OFFSET is simple, can show page numbers, and can jump to any page directly
• Keyset keeps the cost nearly constant however deep you go, as long as an index matches the sort order
• Keyset is more stable when new rows are inserted before the reading position: pages do not “drift”

Cons

• Deep OFFSET costs more the deeper it goes: 290 times slower at page 4,501 than at page 2 in the demo
• Keyset cannot jump directly to “page N” without knowing that page’s boundary in advance
• Neither guarantees a consistent snapshot across multiple API calls: data can change between two requests

Common mistakes

Common pitfall

Using a cursor that holds only one non-unique sort column. If many rows share a created_at, the condition created_at < $last can skip the remaining rows with that same value that sit “later” in the group.

Always put the full tie-breaker column (usually the primary key) into the ORDER BY, the index, and the tuple comparison, as in the example above.

🎮 Try it

• Slowing query, step 4: OFFSET 100,000 versus keyset.

Review checklist for a keyset pagination API

1. ORDER BY ends with a unique column (usually the primary key), and the cursor holds every sort column.
2. An index matches the sort order exactly; with a filter such as tenant_id, that column leads the index.
3. The next-page condition uses a tuple comparison (created_at, id) < (...); for mixed sort directions, write an explicit OR condition and check EXPLAIN.
4. Sort columns are NOT NULL, or COALESCE is applied consistently in both ORDER BY and the index.
5. The cursor is opaque (base64, better yet HMAC-signed) and always re-validated on the server.
6. Do not show a total page count via count(*) on a large set; use “more available” or an estimate.

Summary

Key takeaways

• OFFSET walks exactly the number of skipped rows before returning results: cost grows linearly with page depth
• Keyset uses the B-tree to locate the start directly by comparison, independent of how many rows come before
• In the demo: same position (row 90,000), OFFSET took 5.507 ms/821 buffers, keyset 0.019 ms/3 buffers
• Keyset needs: a unique tie-breaker, one sort direction, non-NULL sort columns, an opaque cursor; “total pages” is still an expensive count
• Both approaches require a unique ORDER BY (add the primary key as a tie-breaker) unless you accept duplicated or missing rows
• Choose by UX: “load more” suits keyset; “jump to any page N” needs OFFSET or a separate marker design

References

• PostgreSQL 17 — LIMIT and OFFSET
• PostgreSQL 17 — Indexes and ORDER BY