Используется ли пагинация в SQL запросах?

Question

[Вадим]

есть сложный sql запрос, со многими joints, типа

SELECT
    customer.id AS customer_id,
    customer.name,
    order.id AS order_id,
    order.product_id
FROM
    customer
LEFT JOIN
    order
ON
    customer.id = order.customer_id AND order.product_id = 12345;

который возвращает около 200 тыс записей, что много для фронтэнда. Хочу добавить дальнеюшую обработку через пагинацию в sql

SELECT
    customer.id AS customer_id,
    customer.name,
    order.id AS order_id,
    order.product_id
FROM
    customer
LEFT JOIN
    order
ON
    customer.id = order.customer_id AND order.product_id = 12345
LIMIT 10000 OFFSET m;

Таким образом m будет постепенно меняться от 0 до 190000, а пользователи будут постепенно перемещаться по базе кнопками страниц 1, 2 ... 200 на фронте.

1) Является ли это нормальной практикой?
2) Все запросы будут содержать неповторяющиеся rows? Или нет гарантии, что они не будут в случайном порядке?

Answer 1

[Melkij]

Хотите сделать пагинацию правильно - не используйте offset, а передавайте сведения сортировки последнего показанного поста.
https://use-the-index-luke.com/sql/partial-results...
https://use-the-index-luke.com/no-offset

Наверняка видели, когда переходите на следующую страницу (например, лента постов отсортированная по времени публикации), вы вдруг видите те же самые записи что только что видели - просто потому что пока вы читали предыдущую страницу кто-то опубликовал новые посты и offset вас честно сдвинул.

Comments

[Вадим]

1) Это все хорошо, хотя я не полностью описал свое query, но скажу вам что там используется 3 JOIN для сбора данных с разных таблиц. Насколько там можно это использовать.
2) Можете изменить мой запрос, чтобы показать использование keyset pagination? Скорее всего промежуточные данные придется хранить во фронтенде... т.к. у нас нет сессии или чего-то такого, чтобы идентифицировать пользователя - все делат отдельный авторизатор и к сервису request приходит уже авторизованным

Answer 2

[mayton2019]

Да какой пользователь 10000 тыщ записей способен прочитать??
Сделай поисковые атрибуты по Name там... еще где то.
И показывай первые 100 штук.

Comments

[Вадим]

там будет фильтр на фронте, но доставать хочу по 10000, да

Answer 3

[Nik Faraday]

Как я делал на бекенде: передаётся номер страницы n. Так же у меня уже есть константа на количество записей на странице (допустим 10). Далее я делаю выборку от n * 10 элемента до (n + 1) * 10

Comments

[mayton2019]

Кажется курсор в Postgres поддерживает опцию SCROLL. Тоесть можно делать перемотку вперед и назад.
В этом случае вам не нужно на каждый page down делать еще один запрос. Просто продолжаете
перемотку следующих 10 записей.

[mayton2019]

Dr. Bacon, это очень странный trafe-off. Вы где-то сэкономили на user-sessions но проиграли в execution plan.

Вобщем я согласен с вами что веб-разработка имеет свою специфику но я считаю что этот вопрос глубого не исследован. Мне как базовику и как человеку который очнень любит и ценит SQL/DBMS очень не хотелось бы использовать подход основанный на многократном execution сортированного курсора особенно тогда, когда этого можно было бы избежать.

[mayton2019]

Если основывать разработку на User Experience - то 99% пользователей дальше первой страницы листать не будут. Допустим landing page банковского кабинета показывает на 1 странице движение средств на счетах клиента сортированные по дате. Вот первые 10 записей можно материализовать для каждого клиента и положить их в отдельную табличку. Или если модернизировать подходы - то положить JSON в Redis. И в этом случае база данных даже не будет задействована. А уж если клиент оказался нудный и дотошный и полезет листать вторую страницу - то тогда можно и запустить курсор с сортировкой.

[Вадим]

Dr. Bacon, отлично, а могли бы полный пример указать, например с моим кодом?

[Вадим]

Dr. Bacon, я не понимаю как вы разделяете на страницы и получаете last_id_from_prev_page - для использования в след запросе WHERE id>last_id_from_prev_page

И да, у меня таблица получается через JOINs из других таблиц, не уверен, что это сработает.