GGistDev

Sorting and Pagination

Order results deterministically and page through large datasets efficiently.

ORDER BY basics

SELECT id, name, created_at
FROM users
ORDER BY created_at DESC, id ASC;  -- tie-breaker for stable order
  • Multiple expressions define precedence left-to-right
  • Use explicit ASC/DESC; default is ASC

NULLS FIRST/LAST (engine-specific)

-- Postgres
ORDER BY last_login DESC NULLS LAST;

When not available, emulate with expressions:

ORDER BY (last_login IS NULL), last_login DESC;

LIMIT/OFFSET

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40; -- page 3 (0-based)

Simple but can get slow for large offsets because the engine must scan/skip many rows.

Keyset (seek) pagination

Use the last seen sort key instead of OFFSET.

-- After fetching a page with last_created_at = '2024-01-01 12:34:56'
SELECT *
FROM orders
WHERE created_at < '2024-01-01 12:34:56'
ORDER BY created_at DESC
LIMIT 20;

For tie-breaks, include a second key:

WHERE (created_at, id) < ('2024-01-01 12:34:56', 12345)
ORDER BY created_at DESC, id DESC

Indexing considerations

  • Create indexes matching your sort keys for fast ORDER BY
  • For keyset pagination, index the composite (created_at, id)
  • Beware of expressions in ORDER BY that prevent index use unless you use expression indexes

Collation-aware sorting

Sorting text depends on collation; specify or normalize for consistent behavior, especially across locales.

Summary

  • Always ORDER BY for deterministic results; add tie-breakers
  • Prefer keyset pagination at scale; OFFSET is fine for small pages
←PreviousFiltering and Predicates
NextJoins→