Почему сортировка — это отдельная история

Реляция — это множество, и порядок в ней не определён. SQL добавляет ORDER BY как финальный шаг: после селекции, проекции, объединений и группировок результат расставляется в нужном порядке. Без ORDER BY любые гарантии порядка — иллюзия, даже если на маленькой таблице ты видишь привычную последовательность.

ORDER BY — это не часть реляционной алгебры. Это операция над списком, не над множеством. Поэтому она может встретиться только в конце запроса (или в подзапросе с явной обёрткой).

Базовый синтаксис ORDER BY

ORDER BY col1 [ASC|DESC] [NULLS FIRST|NULLS LAST], col2 ... — сортировка по нескольким ключам. Если первый ключ даёт равенство, сравнение продолжается по следующему.

• ASC — по возрастанию (по умолчанию)
• DESC — по убыванию
• NULLS FIRST — NULL идёт в начале
• NULLS LAST — NULL идёт в конце

В PostgreSQL по умолчанию: ASC NULLS LAST, DESC NULLS FIRST. Это странно — NULL ведёт себя как «больше всех» при ASC и «меньше всех» при DESC. Если хочется единообразия — пиши явно.

Сортировать можно не только по голым именам колонок, но и по выражениям, псевдонимам (видны), позиции колонки в SELECT (ORDER BY 1 — по первой колонке; считается дурным тоном) или функциям.

LIMIT — отрезать N первых

LIMIT n оставляет только первые n строк после ORDER BY. Без ORDER BY это «первые n в неопределённом порядке» — то есть мусор.

LIMIT 0 возвращает пустой результат, но всё равно полезен — может использоваться для проверки схемы без чтения данных.

OFFSET — пропустить N первых

OFFSET n пропускает первые n строк и возвращает остальные. Вместе с LIMIT это классическая пагинация:

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;
-- страница 3, по 10 элементов на страницу

Звучит безобидно. Но скрывает большую проблему.

Почему OFFSET — это ловушка

Чтобы выполнить OFFSET 1000000, PostgreSQL должен:

1. Выполнить весь запрос (включая сортировку).
2. Прочитать первые 1000000 строк.
3. Выбросить их.
4. Вернуть следующие N.

То есть стоимость растёт линейно с offset’ом. Страница 1 — мс. Страница 10000 — секунды. Страница 1M — минуты. Это называется

Keyset/seek-method — правильная пагинация

Альтернатива OFFSET — keyset pagination (также seek-method). Идея: вместо «пропусти первые N», скажи «дай мне строки, идущие после вот этого ключа».

Что произошло:

• Вместо «пропусти 5», мы говорим «id < 16».
• WHERE-условие отсекает уже виденные строки до чтения, не после.
• Если на id есть индекс (а это PRIMARY KEY, значит есть), запрос отрабатывает за O(log N) независимо от глубины.

Цена: на клиенте нужно хранить «курсор» (последний виденный id), а не номер страницы. И нельзя прыгнуть на «страницу 1000» — только последовательно.

Keyset с несколькими ключами

Если сортировка по одному столбцу неуникальна (например, по placed_at — могут быть одинаковые timestamp’ы), нужен составной ключ: основной + tiebreaker.

Конструкция (a, b) < (x, y) — это row-wise comparison, эквивалент a < x OR (a = x AND b < y). PostgreSQL умеет использовать составной индекс на (placed_at, id) для такого предиката напрямую.

NULL в ORDER BY

NULL в сортируемой колонке — отдельная тема. По умолчанию:

• ORDER BY col ASC — NULL в конце (NULLS LAST по умолчанию)
• ORDER BY col DESC — NULL в начале (NULLS FIRST по умолчанию)

Это часто противоречит ожиданиям. Если хочешь NULL в конце независимо от направления:

ORDER BY col DESC NULLS LAST

Углубление: стабильность сортировки

PostgreSQL не гарантирует стабильность сортировки. Это значит: если две строки имеют одинаковое значение по ORDER BY, их относительный порядок не определён и может меняться между запусками.

Практическое следствие: для воспроизводимости (например, в тестах или в пагинации) всегда добавляй уникальный tiebreaker:

ORDER BY placed_at DESC, id ASC

Даже если по placed_at всё уникально — добавление id страхует от граничных случаев и делает сортировку детерминированной.

Чек-лист

• ORDER BY — единственный способ гарантировать порядок результата.
• Сортировка иерархическая: следующий ключ работает только при равенстве по предыдущим.
• По умолчанию: ASC NULLS LAST, DESC NULLS FIRST. Пиши явно, если важно.
• LIMIT n — первые n строк. Без ORDER BY смысла нет.
• OFFSET n — пропустить n. Растёт линейно с глубиной — антипаттерн для deep pagination.
• Keyset/seek-method: WHERE (key1, key2) > (last_key1, last_key2) — O(log N) на индексе.
• Для стабильности сортировки всегда добавляй уникальный tiebreaker (обычно id).