Сортировка и ограничение: ORDER BY, LIMIT и keyset-пагинация

ORDER BY: направление, NULL, collation, выражения

ORDER BY сортирует спроецированный результат (шаг 7 из урока 1, так что видит алиасы SELECT). Детали, которые кусают:

• Направление и размещение NULL. ASC — по умолчанию; DESC разворачивает. Независимо NULLS FIRST / NULLS LAST управляет, куда падают NULL. По умолчанию NULLS LAST для ASC и NULLS FIRST для DESC — так что переход на DESC двигает NULL наверх, если не сказать иначе. Список «свежайшие первыми» с nullable timestamp может вывести все NULL перед любой реальной строкой, что редко имеется в виду; закрепи через ORDER BY created_at DESC NULLS LAST.
• Collation (правило сравнения символов, задаётся при создании базы или колонки) решает порядок текста. Под collation C верхний регистр сортируется перед нижним по значению байта (Z перед a); под локалью вроде en_US сортировка более-менее без учёта регистра. Collation также влияет, какие LIKE-предикаты может обслужить индекс — тонкость, которую разбирает глава indexes трека databases.
• Сортировка по выражению. Можно сортировать по вычисленному значению: ORDER BY price * qty DESC или ORDER BY lower(name).

Все три детали взаимодействуют: при смене направления меняется размещение NULL; при сортировке по выражению индекс может перестать обслуживать сортировку; при нестандартном collation LIKE-закреплённые индексы ведут себя иначе. Ошибка в любой из них даёт либо неверный порядок, либо лишнюю сортировку всей таблицы.

-- свежайшие первыми, но держим неизвестные timestamp внизу, а не вверху
SELECT id, title, created_at
FROM events
ORDER BY created_at DESC NULLS LAST, id DESC
LIMIT 20;

Top-N без сортировки: когда работу делает индекс

ORDER BY … LIMIT n просит верхние n строк в некотором порядке. Наивное выполнение сортирует всю таблицу, затем берёт первые n — O(n log n) по всему. Но если индекс уже хранит строки в запрошенном порядке, Postgres может обойти индекс и остановиться после n строк — вообще без узла сортировки, O(n) в limit, а не в размере таблицы.

Условие: порядок колонок индекса и направление должны совпадать с ORDER BY. Индекс на events(created_at) позволяет ORDER BY created_at DESC LIMIT 20 прочитать последние 20 листовых записей и остановиться. EXPLAIN показывает это как Index Scan без узла Sort над ним — сеньорский признак того, что твой top-N дёшев. (Механика index-ordered сканов живёт в главах indexes и execution-plans трека databases; здесь вывод в том, что ORDER BY + LIMIT на совпадающем индексе — самый дешёвый top-N, который можно написать.)

Обрыв OFFSET

LIMIT n OFFSET m возвращает n строк после пропуска m. Ловушка в «пропуске»: у базы нет короткого пути к строке номер m. Она должна сгенерировать первые m строк по порядку и выбросить их, затем вернуть следующие n. OFFSET 100000 LIMIT 20 делает полную работу по производству 100 020 упорядоченных строк и выбрасывает 100 000. Цена растёт линейно с номером страницы, так что последние страницы глубокого списка катастрофически медленны, хотя каждая показывает 20 строк.

Keyset (seek) пагинация: постоянная цена на любой глубине

Фикс — перестать считать строки для пропуска и вместо этого запомнить, где кончилась последняя страница — курсор из ключа сортировки. Каждый запрос просит «строки после этого курсора», что является WHERE, по которому индекс может искать, так что база прыгает прямо к месту и читает только страницу.

Курсор должен включать уникальный тайбрейкер (обычно первичный ключ), чтобы порядок был полным и ни одна строка не пропускалась и не дублировалась при равенстве значений сортировки. Postgres поддерживает чистую форму сравнения строк (row-comparison):

-- первая страница
SELECT id, title, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- следующая страница: передай (created_at, id) ПОСЛЕДНЕЙ строки как курсор
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;

При композитном индексе на (created_at DESC, id DESC) WHERE (created_at, id) < (…) — это один index seek, затем 20 последовательных чтений листьев — O(limit), одинаковая цена для страницы 2 и страницы 50 000. Глава indexes трека databases и каноничный гайд пагинации use-the-index-luke.com копают глубже, но правило простое: для глубокой или бесконечной пагинации используй keyset, никогда OFFSET. OFFSET нормален только для мелкого, ограниченного числа страниц (UI, не уходящий дальше страницы 10).