Pagination, Filtering & Sorting at Scale

In one line

Any list can grow to millions of items. Offset pagination is simple but slow and inconsistent at depth; cursor (keyset) pagination is stable and fast. Filtering and sorting must line up with database indexes.

0/7 · 0%

Think of it like this

Reading a very long book. OFFSET pagination is 'skip to page 5,000' by counting every page from the start. CURSOR pagination is a bookmark: 'continue after this sentence'. The bookmark is instant however far in you are, and it isn't confused if someone inserts pages earlier in the book.

Key ideas

01
OFFSET/LIMIT (?page=500&size=20 → OFFSET 9980 LIMIT 20): easy and allows jumping to page N, but the database still reads and discards all skipped rows (slow at depth), and new inserts shift items between pages so users see duplicates or miss items.
02
CURSOR/KEYSET (?limit=20&after=eyJpZCI6OTAwMX0): the cursor encodes the last item's sort key (e.g. created_at + id), and the query is WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. It uses an index directly, so it's constant-time at any depth and stable under inserts. Trade-off: no 'jump to page 500'. Feeds, timelines, and infinite scroll all use it.
03
Return the next cursor in the response ("nextCursor": "...", or a Link header) and make cursors OPAQUE (base64-encoded) so you can change their format later. Always cap limit on the server.
04
FILTERING and SORTING: only allow filters and sort orders you can serve with indexes (Phase 7, indexes); an innocent ?sort=price on an unindexed column becomes a full table scan at scale. For complex search (free text, facets), use a search engine instead of the primary database (Phase 12, search engine).

Code & diagrams

keyset pagination in SQLsql

-- first page
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: cursor = last row's (created_at, id)
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ('2026-09-27 10:14:02', 90210)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- index: CREATE INDEX ON posts (created_at DESC, id DESC);

Explain without notes

Why does offset pagination get slower on deep pages?

Practice

An admin table needs 'jump to page N' and a user feed needs infinite scroll. Which pagination for each?

Trade-offs

↔
Offset: random access and totals, but slow and unstable at depth. Cursor: fast and stable, but sequential only and harder to show totals.

Run it in production

You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:

Stateful · Connections, pooling & query performance

EXPLAIN ANALYZE, indexes, PgBouncer, and lock contention on a live database.

Completion checklist

I can implement keyset pagination with a matching index
My list endpoints cap limits and return opaque cursors

Back to phase

Command Palette