Pagination and large result sets
Review a paginated endpoint
The idea in 30 seconds
Paginated endpoints are usually written once and then live for years while the table grows. Review them for what they will do at a million rows: unique ordering, a matching index, a cursor instead of an offset, and a total count only if someone really needs it.
Helps to know first: B-tree indexes, Set operations and advanced expressions
On this page
The situation
The shop is adding an activity log per organization. An AI assistant implements the endpoint.
Review taskActivity log endpoint
Pull request: “GET /orgs/:id/events?page=N with total pages.”
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
org_id integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
payload text NOT NULL
);
-- page N, 20 per page
SELECT id, created_at, payload FROM events
WHERE org_id = $1
ORDER BY created_at DESC
LIMIT 20 OFFSET ($2 - 1) * 20;
-- for "page N of M"
SELECT count(*) FROM events WHERE org_id = $1;Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. It works on a small table and degrades in three ways as the log grows.
1. There is no index for the query. Without one, every page reads all of the organization’s events and sorts them. Add CREATE INDEX ON events (org_id, created_at, id);.
2. The order is not unique. Events created in the same transaction share now(), so many rows can have exactly the same created_at. Tied rows have no defined order, so they can appear on two pages or on none. Order by created_at DESC, id DESC.
3. OFFSET gets slower every page and shifts under new events. We measured this design (with the index) on a million-row table: page 1 touched 6 pages in 0.011 ms, page 4,999 touched 11,859 pages, sorted 5.8 MB on disk and took 45 ms. And an activity log is exactly the kind of table that gets new rows at the top while users page through it: in our test, two new events made page 2 repeat two rows from page 1. Use a keyset cursor, with one statement for the first page and one for every page after it:
-- first page
SELECT id, created_at, payload FROM events
WHERE org_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- every later page: $2, $3 are the last row's created_at and id
SELECT id, created_at, payload FROM events
WHERE org_id = $1 AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;Return the last row’s created_at and id as an opaque next cursor.
Resist folding both into one statement with ($2 IS NULL OR (created_at, id) < ($2, $3)). Most drivers send queries as prepared statements, and after five executions PostgreSQL may switch a prepared statement to a generic plan that cannot assume anything about $2. We tried it: with default settings it switched, the cursor became a filter instead of an index condition, and page 4,999 touched 11,858 pages again, the same as OFFSET.
4. The total count runs on every page. It took 6.9 ms and 496 pages for one organization in our test, on every request, growing with the log. If the interface needs “has more”, fetch 21 rows and check for the 21st. If it needs a total, show an approximate one, or compute it once rather than per page.
Compare it with yours. Did your review catch the main problems?
Checklist for any paginated query
- Is the
ORDER BYunique? If not, add the primary key as the last column. - Is there an index on the filter columns plus the sort columns, in that order?
- Keyset or offset? Keyset for feeds, logs and APIs; offset only where users jump to numbered pages, and cap how deep they can go.
- Does every page really need a total count?
- What happens at 100× today’s rows? Measure page 1 and a deep page with
EXPLAIN (ANALYZE, BUFFERS).
Check yourself
Why can
ORDER BY created_at DESCalone repeat or lose rows between pages?Show answer
Rows with the same
created_athave no defined relative order, so across two queries a tied row can land on either side of the page boundary: shown twice or not at all.How can an endpoint tell the client there are more results without counting all rows?
Show answer
Fetch one row more than the page size. If the extra row exists, there is a next page; drop it before returning.
Why is an activity log a particularly bad fit for
OFFSET?Show answer
It grows forever, so deep pages get slower and slower, and new events keep arriving at the top while users page, shifting positions and causing repeated or skipped rows.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: PREPAREPrimary· PREPARE, Notes
With plan_cache_mode set to auto, the first five executions of a prepared statement use custom plans, after which the server may switch to a generic plan.
- 2.PostgreSQL documentation: 7.6 LIMIT and OFFSETPrimary· §7.6 LIMIT and OFFSET
Skipped rows are still computed; paging needs an ORDER BY that gives a unique order.
- 3.PostgreSQL documentation: 11.4 Indexes and ORDER BYPrimary· §11.4
An index matching the ORDER BY lets the first rows be read directly, without sorting everything.
Go further
What to read next, and why it is worth the time.
- 1.Use The Index, Luke!: We need tool support for keyset paginationReferenceMarkus Winand· location not yet verified
Shows how keyset cursors are exposed in APIs and front-end libraries.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.