Антипаттерн: Почему OFFSET убивает твою базу (и как делать пагинацию правильно)

[GuDron]

Антипаттерн: Почему OFFSET убивает твою базу (и как делать пагинацию правильно)

Привет! Если вы когда-нибудь реализовывали каталог товаров или ленту новостей, то наверняка писали запрос с LIMIT и OFFSET. Для небольших таблиц это работает отлично, но как только проект взлетает и данных становится много, база начинает задыхаться. Давайте разберем, почему так происходит и как это лечить.

Как мы делаем обычно:

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 50 OFFSET 100000;

В чем подвох? База данных не умеет «магически» прыгать на 100 000-ю строку. Ей придется прочитать, отсортировать (если нет подходящего индекса) и отбросить первые 100 000 строк, чтобы вернуть вам всего 50. Чем глубже пользователь листает страницы, тем медленнее работает запрос. Нагрузка на CPU и диски растет экспоненциально.

Как делать правильно:
Вместо того чтобы говорить базе «пропусти N строк», мы говорим ей «дай мне 50 записей, которые идут сразу после последней записи, которую я уже видел».

SELECT id, title, created_at
FROM articles
WHERE created_at < '2023-10-25 14:00:00' -- дата из последней записи на предыдущей странице
ORDER BY created_at DESC
LIMIT 50;

Этот запрос мгновенно найдет нужное место по индексу (B-Tree) и прочитает ровно 50 строк. Никакой лишней работы!

Важный нюанс:
Если поле created_at не уникально (две статьи вышли в одну секунду), предыдущий запрос может пропустить данные. Используйте уникальный «тайбрейкер» - например, id. В PostgreSQL это можно сделать очень элегантно с помощью кортежей (Row Values):

SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2023-10-25 14:00:00', 10543)
ORDER BY created_at DESC, id DESC
LIMIT 50;

(Не забудьте создать составной индекс: `CREATE INDEX idx_articles_created_id ON articles (created_at DESC, id DESC);`)

Итог:
• OFFSET / LIMIT: Ок для админок с небольшим трафиком и малым объемом данных (до ~10-50к строк).
• Keyset Pagination: Must-have для бесконечных скроллов (infinite scroll), публичных API и таблиц на миллионы записей.
Скинь ссылку на этот пост фронтендеру, который просит «просто добавить номер страницы» в API. А какой метод пагинации чаще всего используете вы в своих текущих проектах? Делитесь в комментариях!