Меню Brew в приложении показывают постранично: «ещё 20 напитков», «следующая страница». Пока страниц мало — всё работает. Но как только каталог разрастается и пользователь долистывает до страницы 500, запрос с OFFSET 10000 внезапно тормозит — хотя отдаёт он всё те же 20 строк. Причина не в количестве отданного, а в количестве пройденного: OFFSET заставляет сервер вычислить и отбросить первые 10000 строк, прежде чем вернуть нужные.

Цель юнита — научиться доставать ровно нужные строки (WHERE), в нужном порядке (ORDER BY), нужной пачкой (LIMIT), и листать так, чтобы стоимость страницы не зависела от её глубины. Это и есть keyset-пагинация (она же «seek»): вместо «пропусти N строк» — «дай строки после вот этой».

WHERE / ORDER / LIMIT — три кита выборки

WHERE отбирает строки по условию (категория, диапазон цены), ORDER BY задаёт порядок, LIMIT ограничивает количество. Порядок логических шагов — сначала фильтр, потом сортировка, потом обрезка — важен для понимания: LIMIT без ORDER BY отдаёт «какие-то N строк», и при следующем запуске они могут быть другими, потому что у строк нет гарантированного порядка без явного ORDER BY.

Для пагинации это критично: порядок должен быть полным (total order). Если сортировать только по цене, а у нескольких напитков цена одинаковая, их взаимный порядок не определён — между страницами они могут «переставляться», и одна и та же строка попадёт на две страницы или не попадёт ни на одну. Лечится tie-break'ом: добавляем в ORDER BY уникальную колонку (id). ORDER BY base_price DESC, id DESC — порядок строгий и стабильный.

OFFSET: просто, но дорого на глубине

LIMIT n OFFSET k читается как «пропусти k, отдай следующие n». Это самый простой способ постраничной выдачи, и для первых страниц он отличен. Беда в том, что OFFSET не «прыгает» через k строк — сервер всё равно их вычисляет (применяет WHERE, сортирует) и только потом отбрасывает. Стоимость страницы растёт линейно с её номером: страница 1 дёшева, страница 1000 читает тысячу ненужных строк перед двадцатью нужными.

Keyset: «дай строки после вот этой»

Keyset-пагинация хранит курсор — значения сортировки последней строки текущей страницы. Следующую страницу запрашивают не по номеру, а по курсору: «дай строки, которые в порядке ORDER BY идут после (цена, id) последней». Сравнение кортежей делает это одним выражением:

WHERE (base_price, id) < (:after_price, :after_id)
ORDER BY base_price DESC, id DESC

(a, b) < (x, y) в Postgres — это лексикографическое сравнение: сначала по a, при равенстве — по b. Оно ровно повторяет ORDER BY base_price DESC, id DESC, поэтому условие «строки после курсора» совпадает с порядком сортировки. По индексу (base_price, id) сервер сразу переходит к нужному месту (index range scan) и читает только LIMIT строк — без отбрасывания. Цена страницы 1000 такая же, как страницы 1.

Плата: keyset не умеет «прыгнуть на страницу 500» (нет курсора без прохода) и требует полного ORDER BY. Но для «бесконечной ленты» / «показать ещё» это ровно то, что нужно.

OFFSET против keyset: что делает сервер

Возьмём меню, отсортированное base_price DESC, id DESC, и страницу 2 (по две строки). OFFSET 2 проходит и отбрасывает строки страницы 1; keyset по курсору последней строки страницы 1 сразу прыгает на нужное место по индексу:

меню по убыванию (base_price DESC, id DESC):
 
  #4 Колд брю    5.20   OFFSET 2 → прочитать и отбросить · keyset → пропустить по индексу
  #3 Латте       4.80   OFFSET 2 → прочитать и отбросить · keyset → пропустить по индексу
  ─────────────────────  курсор после страницы 1 = (4.80, #3)
  #2 Капучино    4.50   ┐
  #1 Эспрессо    3.00   ┘ страница 2 (page_size = 2) — её и возвращаем
  #5 Зелёный чай 2.50    (дальше — страница 3)
 
OFFSET 2 LIMIT 2                     → сервер вычислил 4 строки, 2 отбросил (дороже с глубиной)
WHERE (base_price, id) < (4.80, #3)  → индекс прыгнул к курсору и прочитал ровно 2

Что показывает наш код

Три запроса в query.sql. Базовая выборка:

-- name: FilterMenu :many
SELECT id, name, base_price FROM drinks
WHERE category = sqlc.arg(category) ORDER BY base_price LIMIT sqlc.arg(page_size);

И две пагинации — обе с одинаковым полным порядком base_price DESC, id DESC, но разным способом «сдвига»:

-- PageByOffset:  ... ORDER BY base_price DESC, id DESC LIMIT :page_size OFFSET :skip;
-- PageByKeyset:  ... WHERE (base_price, id) < (:after_price, :after_id)
--                    ORDER BY base_price DESC, id DESC LIMIT :page_size;

В main.go keyset-пагинация листает всё меню в цикле: после каждой страницы курсор (after_price, after_id) становится (цена, id) последней строки. Первую страницу берём со «сторожевым» курсором — заведомо больше любой реальной строки (по убыванию это «с самого начала»).

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew:

docker compose up -d
make lecture L=03-crud-fluency/03-02-select-where-order-limit T=db-reset
make lecture L=03-crud-fluency/03-02-select-where-order-limit

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

1) WHERE/ORDER/LIMIT — category='coffee', по возрастанию цены, LIMIT 2:
   #1 Эспрессо 3.00
   #2 Капучино 4.50
 
2) Keyset-пагинация по всему меню (по убыванию цены, page_size=2):
   страница 1 (∞ (с начала)): #4 Колд брю 5.20 | #3 Латте 4.80
   страница 2 (после 4.80 / #3): #2 Капучино 4.50 | #1 Эспрессо 3.00
   страница 3 (после 3.00 / #1): #5 Зелёный чай 2.50
 
3) OFFSET — та же страница 2 через LIMIT 2 OFFSET 2:
   #2 Капучино 4.50 | #1 Эспрессо 3.00
   → результат тот же, но сервер вычислил и отбросил первые 2 строки; keyset — нет.

Keyset прошёл всё меню тремя страницами, каждый раз сдвигаясь по курсору. Страница 2 keyset'а (#2, #1) и страница 2 через OFFSET 2 — одни и те же строки: способ листать разный, результат одинаковый. Разница не видна на пяти напитках, но на миллионе строк OFFSET к глубокой странице читает миллион строк впустую, а keyset — только свои две.

Заборчик

На пяти строках seed'а разницы в скорости нет — её видно на больших таблицах и под нужный индекс. Что мы упростили:

• Keyset быстр ровно тогда, когда ORDER BY лёг на индекс — здесь это был бы индекс (base_price, id). Без него сервер всё равно отсортирует всю таблицу, и преимущество испарится (про индексы и чтение планов — модуль 06).
• «Общее число страниц» мы не показали. count(*) по большому отфильтрованному набору сам по себе дорог; в проде его либо кэшируют, либо заменяют на «есть ли ещё» (запросить LIMIT n+1 и проверить, пришла ли n+1-я строка).
• OFFSET не «плохой». Для первых нескольких страниц небольшого набора он проще и совершенно нормален; keyset нужен там, где листают глубоко.
• Выбор зависит от навигации. Нужны прыжки на произвольную страницу — без OFFSET/нумерации не обойтись; достаточно «показать ещё» — бери keyset.

Что забрать с собой

• WHERE фильтрует, ORDER BY сортирует, LIMIT ограничивает — и LIMIT без ORDER BY отдаёт неопределённый набор строк.
• Для пагинации ORDER BY должен быть полным: добавь уникальный tie-break (id), иначе строки с равным ключом «плавают» между страницами.
• OFFSET k заставляет сервер вычислить и отбросить первые k строк — стоимость глубокой страницы растёт линейно.
• Keyset-пагинация (WHERE (cols) < (cursor)) листает по курсору из последней строки — стоимость страницы не зависит от глубины, если ORDER BY лёг на индекс.
• Keyset не умеет прыгать на произвольную страницу и требует полного порядка — это его цена за масштабируемость.

Дальше — юнит 03-03 «UPDATE/DELETE безопасно»: научимся менять и удалять строки, видеть масштаб изменений через RETURNING и RowsAffected, и поймём, почему рискованную запись стоит делать внутри транзакции — чтобы забытый WHERE можно было откатить.