Writing Efficient Pagination Queries in Postgres

Postgres is the most-used database among professional developers for the third year running, with 58.2% adoption in the Stack Overflow Developer Survey (over 49,000 respondents). A huge share of those developers are, right now, paginating a table with OFFSET. Most of them are fine. Some of them are sitting on a slow-motion problem that won't show up until the table, and the page numbers users click on, both get big.

This piece is about that problem: why OFFSET gets slow in a predictable, measurable way, why it can also get things flat-out wrong, and how keyset pagination (sometimes called cursor-based pagination) fixes both. There's also a third tool, server-side cursors, for the cases where neither of the first two is the right fit.

What OFFSET actually does inside Postgres

The mental model most people carry around is that OFFSET works like an array index. Ask for row 200,000 and Postgres just... goes there. Instant.

That is not what happens.

Postgres builds the result set in order, then counts off the first N rows and throws them away, then hands back whatever's left up to your LIMIT. Ask for OFFSET 199,980 LIMIT 20, and Postgres computes and discards 199,980 rows before it gives you the 20 you actually wanted.

An index can help Postgres find where to start scanning. But it still has to walk the index, one entry at a time, counting as it goes, until it reaches your offset. That walk is O(n). It gets longer as the offset grows, with no ceiling.

At large enough offsets, the amount of data Postgres has to hold in memory to do this sorting and counting can exceed work_mem, and the sort spills to disk. One benchmark recorded a 156MB spill to disk on a 10-million-row table at OFFSET 199,980. It's page 10,000 of a perfectly ordinary table. That's page 10,000 of a perfectly ordinary table.

None of this shows up as an error. The query returns the right shape of data. It just gets slower, and nothing in your application logs says why.

How bad the degradation gets, measured against a real table

Numbers make this concrete. A benchmark against a 10-million-row Postgres table found:

• Page 1 (OFFSET 0): 8ms
• Page 50 (OFFSET 980): 45ms
• Page 1,000 (OFFSET 19,980): 890ms
• Page 10,000 (OFFSET 199,980): 8,200ms

Postgres scanned and discarded almost 200,000 rows to hand back 20. Postgres scanned and discarded almost 200,000 rows to hand back 20. By page 50,000, the same table hit 7.1 seconds. By page 100,000, it passed 14 seconds. For one page load.

The pattern holds across different datasets and different tools. A separate benchmark on a million-row dataset ran from 468 microseconds at page 1 up to 87 milliseconds at page 50,000, tracing the same O(n) curve. A 2024 test against the IMDB title table (over 2.5 million rows) went from sub-millisecond response times on early pages to close to half a second on later ones. A Readyset benchmark on a million-row table measured 0.283ms at OFFSET 0, 24.278ms at OFFSET 100,000, and 138.136ms at OFFSET 999,990, a clean linear climb.

Batch jobs make this worse, not better. A system that fetches "all records" by looping through OFFSET pages is issuing a sequence of increasingly expensive, resource-intensive queries back-to-back. Each one stresses the database a little more than the last, in a tight burst.

None of this means OFFSET is always wrong. It's a reasonable choice when:

• The table is small (roughly under 10,000 rows)
• Users almost never click past the first few pages
• The interface genuinely needs random page access, like jumping straight to page 47

Outside those conditions, the clock is ticking on the query, even if nobody's noticed yet.

The correctness problem that slow queries obscure

Speed is the visible problem. There's a quieter one hiding underneath it: OFFSET pagination can silently return wrong results on any table where rows get deleted or inserted between requests.

Walk through it:

• A user requests page 1 and gets rows 1 through 20.
• Row 1 gets deleted, by anyone, for any reason.
• The user requests page 2. But everything has shifted. The row that used to sit at position 21 (the first row of page 2) is now at position 20, which belongs to page 1. It never gets shown to the user, just vanishing. It just vanishes.

The same thing happens in reverse with inserts: a new row shoves everything down by one, and a row the user already saw on page 1 shows up again on page 2.

If the table is read-only or append-only, this risk shrinks quite a bit. But most production tables see deletes and updates constantly. And the failure here doesn't throw an error. It doesn't log anything unusual. A row just disappears from the user's view, and nothing in the system flags it.

This is especially rough for APIs. The consumer on the other end has no way to detect that a row got skipped, and often no reason to even suspect it. They just have incomplete data and full confidence in it.

Why keyset pagination stays fast at any depth

Keyset pagination throws out the idea of counting from the start entirely. Instead of "skip 199,980 rows," it asks "give me everything after the last row I saw." The WHERE clause does the work that OFFSET used to do.

A typical keyset query looks like this:

SELECT * FROM users
WHERE (updated_at, id) > ('2025-01-01', 123)
ORDER BY updated_at ASC, id ASC
LIMIT 20;

Notice the compound key: (updated_at, id), not just updated_at. That's not a stylistic choice, it's a requirement. A single column like updated_at isn't safe on its own if it isn't unique. If 1,000 rows share the exact same updated_at value and your page size is 100, there's no reliable way to know where one page ends and the next begins. Adding the primary key id as a tiebreaker gives every row a unique, unambiguous position in the sort order.

That compound cursor buys three properties:

• Every row has one deterministic position, full stop.
• A row's position only changes if its own sort fields change.
• Other rows getting inserted or deleted elsewhere in the table has zero effect on this row's position.

Because the query always seeks forward from a known point, Postgres can jump straight there using the index. It doesn't count anything. It doesn't discard anything. Page 50,000 costs the same as page 1.

The payoff shows up clearly in benchmarks: at page 50,000, keyset pagination ran 7,889 times faster than OFFSET in one direct comparison. The keyset query held steady at 0.9ms no matter how deep the page number went, while OFFSET kept climbing without any ceiling in sight. One team's production migration reported query times dropping from 5 seconds to 500 milliseconds after switching an infinite-scroll feed from offset to keyset.

Writing the traversal queries: first page, forward, and backward

The pattern has three variants, and they're worth writing out explicitly because small mistakes here cause quiet bugs.

First page (no cursor exists yet):

SELECT * FROM users
ORDER BY updated_at DESC, id DESC
LIMIT 20;

Take the updated_at and id values from the last row returned. That pair becomes the cursor for the next request.

Forward (subsequent pages):

SELECT * FROM users
WHERE (updated_at, id) < (last_updated_at, last_id)
ORDER BY updated_at DESC, id DESC
LIMIT 20;

Backward (reverse traversal):

SELECT * FROM users
WHERE (updated_at, id) > (first_updated_at, first_id)
ORDER BY updated_at ASC, id ASC
LIMIT 20;

Every column in the WHERE clause has to also appear in the ORDER BY, in the same order. Mixing these up, say, filtering on (updated_at, id) but sorting only by updated_at, is a classic way to introduce a bug that doesn't announce itself. The query still runs. It just returns subtly wrong pages.

On the API side, this changes the response shape. Instead of returning a page number, return a cursor token, the sort field values of the last row on the page. The client sends that cursor back on the next call. This does mean the application now has to carry a bit of state between requests (which cursor value it's up to), where OFFSET pagination let the client just do arithmetic on a page number. That's a real trade-off, and it should be designed for up front rather than bolted on later.

The index that makes keyset queries instant

Keyset pagination is only as fast as its index. The index needs to match the query exactly:

CREATE INDEX idx_users_updated_at_id
ON users (updated_at DESC, id DESC);

Column order in that index isn't cosmetic. Postgres can use a prefix of a composite index, so an index on (created_at, id) will serve a query that filters on created_at alone, but it won't help a query that filters only on id. The keyset's columns have to lead the index in the same order they appear in the query.

Sort direction matters too. The index needs to match both the column list and the direction (ASC or DESC) used in the ORDER BY. An ascending index can still serve a descending sort, Postgres will just scan it backward, but that's happening and should be known rather than discovered by accident.

To check whether any of this is actually working, run EXPLAIN ANALYZE on the query and look at the output:

"Index Scan" means Postgres is using the index the way it's supposed to. "Seq Scan" means it isn't. Common causes: the index columns are in the wrong order, the sort direction doesn't match, or the table is small enough that the planner decides scanning it directly is cheaper anyway.

Skip the composite index, and keyset pagination still technically works. It just loses the entire point of using it, the seek becomes a full table scan, and performance degrades back toward OFFSET territory.

Keyset's own edge cases: what it doesn't protect against

Keyset pagination isn't magic. It solves the deletion problem cleanly. If a row gets deleted between requests, it just disappears from the traversal. Nothing shifts. Nothing else is affected.

Where it gets tricky is when a row's own sort key changes mid-traversal. If updated_at increases while a user is paging through results, that row effectively gets bumped forward in the sort order and might show up a second time. If the sort key decreases, the row might slip behind the cursor and never get seen at all.

For a one-time full sweep of a table, there's a straightforward fix: grab the maximum cursor value before starting.

SELECT updated_at, id FROM users
ORDER BY updated_at DESC, id DESC
LIMIT 1;

Then paginate only up to that ceiling. Anything inserted or updated during the sweep falls outside the window and simply doesn't factor in.

Keyset pagination has no concept of random access. There's no way to jump straight to "page 47." Movement is strictly sequential, forward or backward from wherever you currently are.

That rules out numbered pagination UI ("page 3 of 771"). What it's well suited for instead: infinite scroll, "Load more" buttons, or simple Previous/Next links. GitHub's commit history view is a real-world example of sequential pagination, moving forward or backward through history without jumping to an arbitrary point in the middle.

When to use server-side cursors instead of keyset pagination

Keyset pagination is built for stateless, page-by-page access. It's not the right tool for every large-scan problem.

Consider running a plain SELECT against a table with hundreds of millions of rows. By default, most client drivers try to buffer the entire result set into memory before handing back a single row. On a big enough table, that alone can crash the client. A server-side cursor solves this differently: it keeps the query open on the database server and streams rows back in batches, so the client's memory footprint stays flat no matter how big the underlying result set is.

Basic pattern:

BEGIN;
DECLARE big_scan CURSOR FOR
  SELECT id, payload FROM events ORDER BY id;
FETCH 1000 FROM big_scan; -- first thousand rows
FETCH 1000 FROM big_scan; -- next thousand

A few things to know before reaching for this:

Long transactions are the main risk. A cursor keeps its transaction and snapshot open for as long as it's being consumed. Fetch slowly over hours, and Postgres's vacuum process can't clean up dead rows across the whole database in the meantime, the same damage any long-running transaction causes. WITH HOLD cursors survive past the transaction commit, which can be handy when the consuming process needs multiple separate request/response cycles, but this comes with its own resource trade-offs worth understanding before use. The query planner treats cursors differently. Postgres optimizes cursor queries for fast startup, governed by cursor_tuple_fraction (default 0.1), and may pick an index scan over a hash join that would actually finish faster overall. If a cursor's execution plan looks different from the same query run plainly, this is usually why. Driver support varies. In psycopg2 or psycopg3, use named cursors with itersize. In Node, the pg-cursor or pg-query-stream packages handle it. Monitoring gets murkier. Monitoring cursor performance can be murkier than monitoring plain queries, since the cost is spread across multiple FETCH calls rather than a single query execution.

The practical dividing line: server-side cursors need a dedicated database connection and an open transaction for as long as the client is consuming rows. That's a heavy resource cost for a high-concurrency web API serving thousands of simultaneous requests. Keyset pagination fits that world. Server-side cursors fit a different one: single-session ETL jobs, data exports, report generation, the kind of work where one process owns one connection for a while and that's expected.

The total-row-count problem

BI dashboards and admin panels love to show "1,243 results, page 3 of 13." That number requires knowing the exact total row count, and that's a harder problem than it looks.

There's no cheap way to get an exact count. COUNT(*) over a filtered result set has to scan every matching row to produce that number, and on a large or heavily filtered table, that scan is frequently more expensive than every page query combined, especially if users only ever look at the first two or three pages anyway.

A few practical ways teams handle this trade-off:

Run COUNT(*) in parallel with the first page query, accepting the extra cost as a fixed price for showing an exact total. This works fine if the table is small or the filter is selective enough that the count stays cheap. Show an estimate instead of an exact count, using Postgres's own planner statistics (reltuples from pg_class, or EXPLAIN's row estimate) rather than a live scan. Fast, but approximate, and can drift from reality on tables with heavy write activity.

• Drop the total count from the interface entirely. Display "Next" and "Previous" without a page total, the way infinite scroll and load-more patterns already do. This sidesteps the cost altogether, at the price of not being able to say "page 3 of 13."

None of these is free. Which one makes sense depends on how much the interface actually needs an exact number versus a good-enough one, and how large and volatile the table is underneath it. That's the same question that runs through this entire topic: pagination isn't one universal problem with one universal answer, it's a set of trade-offs between speed, correctness, and what the interface is actually asking for.