How to use pagination in an SQL query with a cursor?

Use cursor (keyset) pagination by ordering rows deterministically on indexed column(s), fetching a page with LIMIT, then requesting the next page with a WHERE predicate that filters strictly after the last seen key values. This avoids deep OFFSET scans and remains consistent as data changes before the current page (GitLab pagination guidelines), (Citus: Keyset vs OFFSET).

Why use a cursor instead of OFFSET?

• OFFSET gets slower the deeper you go, because the database must skip PAGE × SIZE rows; large page numbers can degrade heavily (GitLab).
• OFFSET can return inconsistent pages if rows are inserted or deleted between requests; keyset pagination stays consistent relative to the chosen ordering (Citus).
• Keyset pagination requires a stable ORDER BY and appropriate indexes on the ordered columns for good performance (GitLab).

Step-by-step: cursor pagination in PostgreSQL

Example table:

-- Messages ordered by newest first, with id as a tie-breaker
CREATE TABLE messages (
  id          BIGSERIAL PRIMARY KEY,
  created_at  TIMESTAMPTZ NOT NULL,
  body        TEXT NOT NULL
);

-- Index covering the ORDER BY for efficient page reads
CREATE INDEX ON messages (created_at DESC, id DESC);

1) First page

-- Page size: 20
SELECT id, created_at, body
FROM messages
ORDER BY created_at DESC, id DESC
LIMIT 20;

Always specify a deterministic ORDER BY; without it, row order is non-deterministic (GitLab).

2) Build an opaque cursor for the client

Take the last row’s ordered key values. For this example that is the pair (created_at, id). Package them into an opaque token the client can send back:

{
  "created_at": "2026-10-01T12:34:56Z",
  "id": 123456
}

For APIs, prefer emitting a next link via the HTTP Link header rather than asking clients to build URLs themselves (GitLab).

3) Next page (after a cursor)

Request rows strictly after the last seen keys. Because we sort DESC, the next page uses “less than” on the leading key, with an id tie-breaker:

-- Variables $1, $2 from the last row of the previous page
SELECT id, created_at, body
FROM messages
WHERE (
  created_at < $1
) OR (
  created_at = $1 AND id < $2
)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This is the core of keyset pagination. It reads only the next slice via the index instead of skipping prior rows (GitLab), and remains consistent even if earlier rows are inserted or removed (Citus).

Ascending order or single-key variant

If you sort ascending by a single, unique, monotonically increasing key (like id ASC), the next-page predicate simplifies:

-- First page
SELECT * FROM items ORDER BY id ASC LIMIT 50;

-- Next page, given last id = 500
SELECT * FROM items
WHERE id > 500
ORDER BY id ASC
LIMIT 50;

GitLab shows this id-based example and highlights that keyset reads only the needed rows (GitLab).

Handling NULLs and complex orderings

If an ordered column can be NULL, add explicit NULLS FIRST/LAST and adjust the predicate accordingly. With multiple columns and NULLs, predicates get more complex; GitLab illustrates the additional OR branches needed when tie-breakers and NULL handling are involved (GitLab).

Returning links and keeping URLs stable

• Expose only opaque cursors; avoid leaking internal column names in URLs. Parameters are visible to users, so choose ordering columns carefully (GitLab).
• For APIs, include next/prev URLs in the Link header so clients don’t construct URLs by hand and you can evolve URL formats safely (GitLab).
• Related reading: Types of pagination in REST APIs.

Limitations and practical tips

• No page numbers: keyset pagination doesn’t support jump-to-page-N. Prefer next/previous navigation (GitLab).
• Index for your ORDER BY: cover all ordered columns, e.g. (created_at, id), to avoid sorting large sets and to read only the needed rows (GitLab).
• Pick a reasonable page size and cap how far clients can paginate to protect your database and rate limits. See also How to stop 429 Too Many Requests.

Put this into practice

Test the continuation predicate with duplicate sort values before relying on it for an export. Keep sort direction, filters and the unique tie-breaker consistent between the SQL query and the cursor returned to the caller.

Quick checklist: ship cursor pagination safely

• Create a composite index that matches ORDER BY (for example, created_at DESC, id DESC).
• Return an opaque next cursor built from the last row’s ordered keys.
• Use keyset WHERE predicates (strictly after last keys) with LIMIT, no OFFSET.
• Emit next/prev links via the HTTP Link header to keep clients decoupled.
• EXPLAIN ANALYZE your queries to confirm the index scan and stable latency.

FAQ

Can I show an exact total count?
On very large tables, COUNT(*) can be expensive. GitLab recommends avoiding total counts and using capped counts like “1000+” to keep responses fast (GitLab).

Stack & Method is an independent publication. We cite primary docs and keep implementation advice practical for small engineering teams.