Sorting and limiting: ORDER BY, LIMIT, and keyset pagination

ORDER BY: direction, NULLs, collation, expressions

ORDER BY sorts the projected result (step 7 from lesson 1, so it can see SELECT aliases). The details that bite:

• Direction and NULL placement. ASC is the default; DESC reverses. Independently, NULLS FIRST / NULLS LAST controls where NULLs land. The default is NULLS LAST for ASC and NULLS FIRST for DESC — so flipping to DESC moves NULLs to the top unless you say otherwise. A “most recent first” list with nullable timestamps can surface all the NULLs before any real row, which is rarely intended; pin it with ORDER BY created_at DESC NULLS LAST.
• Collation decides text ordering. Under C collation, uppercase sorts before lowercase by byte value (Z before a); under a locale like en_US, sorting is case-insensitive-ish and locale-aware. Collation also affects which LIKE predicates an index can serve — a subtlety the databases indexes chapter covers.
• Expression sort. You can sort by a computed value: ORDER BY price * qty DESC, or ORDER BY lower(name).

Together these three details interact: when you flip direction, NULL placement changes; when you sort by an expression, the index may no longer serve the sort; when you use a non-C collation, LIKE-anchored indexes behave differently. Getting any one of them wrong produces either incorrect ordering or an unintended full sort.

-- newest first, but keep unknown timestamps at the bottom, not the top
SELECT id, title, created_at
FROM events
ORDER BY created_at DESC NULLS LAST, id DESC
LIMIT 20;

Top-N without a sort: when the index does the work

ORDER BY … LIMIT n asks for the top n rows in some order. The naive execution sorts the entire table then takes the first n — O(n log n) over everything. But if an index already stores rows in the requested order, Postgres can walk the index and stop after n rows — no sort node at all, O(n) in the limit, not the table size.

The condition: the index’s column order and direction must match the ORDER BY. An index on events(created_at) lets ORDER BY created_at DESC LIMIT 20 read the last 20 leaf entries and stop. EXPLAIN shows this as an Index Scan with no Sort node above it — the senior tell that your top-N is cheap. (The mechanics of index-ordered scans live in the databases indexes and execution-plans chapters; here the takeaway is that ORDER BY + LIMIT backed by a matching index is the cheapest top-N you can write.)

The OFFSET cliff

LIMIT n OFFSET m returns n rows after skipping m. The trap is in “skipping”: the database has no shortcut to row number m. It must generate the first m rows in order and discard them, then return the next n. OFFSET 100000 LIMIT 20 does the full work of producing 100,020 ordered rows and throws 100,000 away. Cost grows linearly with the page number, so the last pages of a deep list are catastrophically slow even though every page shows 20 rows.

Keyset (seek) pagination: constant cost at any depth

The fix is to stop counting rows to skip and instead remember where the last page ended — a cursor made of the sort key. Each request asks for “rows after this cursor”, which is a WHERE the index can seek on, so the database jumps straight to the spot and reads only the page.

The cursor must include a unique tiebreaker (typically the primary key) so the order is total and no row is skipped or duplicated when sort values tie. Postgres supports the clean row-comparison form:

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

-- next page: pass the LAST row's (created_at, id) as the cursor
SELECT id, title, created_at
FROM events
WHERE (created_at, id) < ('2026-03-01 10:00:00+00', 84213)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Backed by a composite index on (created_at DESC, id DESC), the WHERE (created_at, id) < (…) is a single index seek, then 20 sequential leaf reads — O(limit), identical cost for page 2 and page 50,000. The databases indexes chapter and the canonical use-the-index-luke.com pagination guide go deeper, but the rule is simple: for deep or infinite pagination, use keyset, never OFFSET. OFFSET is fine only for shallow, bounded page counts (a UI that never goes past page 10).