21.06.2024 14:20
stoi 45 1700 Источник

Доброго дня! Хочу поделиться лайфхаком по реализации универсального способа пагинации в произвольных запроса к БД для получания списков.

Имеем изначально задачу по реализации HTTP REST CRUD сервера на Go. База данных - PostgreSQL. Используемый драйвер - pgx - PostgreSQL Driver and Toolkit.

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

Общее количество страниц в таблице БД получить несложно:

SELECT count(*) FROM client_accounts WHERE <условия>

Также, без проблем мы можем выбрать нужную страницу используя LIMIT и OFFSET:

SELECT * FROM client_accounts
WHERE <условия>		
LIMIT a_limit
OFFSET a_offset;

Вся соль в том, что крайне нежелательно делать это в лоб двумя запросами к БД. Помимо прочего, чисто теоретически, к моменту выполнения второго запроса данные могут стать неактуальными, если в это время была добавлена/удалена запись в таблицу. Можно, конечно, использовать транзакцию, тем не менее, такой подход показался мне громоздким и не самым удачным. Два запроса - почти всегда хуже, чем один. Кроме того, в запросах секции WHERE должны быть строго одинаковыми.

Не то чтобы задача объединить эти запросы в один сама по себе - мега сложная, тем не менее, сходу придумать или найти готовый пример лично у меня не получилось.

В итоге ,через какое-то время родилось решение, которым и хочу поделиться.

Создаем функцию:

CREATE OR REPLACE FUNCTION get_account_list(
	IN a_limit INT,
	IN a_offset INT
)
RETURNS TABLE (
  list client_accounts,
  full_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
		SELECT a, count(*) OVER() AS full_count FROM client_accounts AS a
		ORDER BY a.id  ASC
		LIMIT a_limit
		OFFSET a_offset;			
END;
$$;

Здесь мы возвращаем таблицу с двумя полями - в одном хранится строка таблицы со всеми полями, в другом -  количество записей. Это возможно благодаря тому, что всякий раз, когда в PosgreSQL создается таблица, вместе с ней автоматически создается составной тип, представляющий тип строки таблицы, именем которого будет имя таблицы.

Второй нюанс заключается в использовании конструкции OVER(), которая превращает агрегатную функцию COUNT() в оконную и позволяет помещать общее количество записей full_count  в каждую строку нашей выборки, в отдельную колонку. В результате выполнения данного запроса нам будет возвращаться таблица с двумя колонками. В первой колонке будут содержаться строки из таблицы  client_accounts, во второй - будет повторяться значение full_coun, соответствующее общему количеству строк в выборке.

Чтобы получить все колонки таблицы плюс колонку количества записей, нам нужно “раскрыть” первую колонку в выборке. Поэтому, при вызове функции используем такой запрос:

SELECT (list).*, full_count FROM get_account_list()

Остается демаршалить результат запроса в слайс структур. В данном случае я использовал драйвер pgx и пакет pgxscan:

err = pgxscan.Select(ctx, db.Pool, &data, "SELECT (list).*, full_count FROM get_account_list(10, 0)")
if err == nil {
    for i, v := range data {
        log.Printf("%d) Name: %s; Login: %s; Password: %s; EMail: %s; Role: %s",
            i, v.Name, v.Login, v.Password, v.EMail, v.Role)
    }

    if len(data) > 0 {
        log.Println("----------------------------------------")
        log.Printf("Record count: %d", int(data[0].FullCount))
    } else {
        log.Println("there are no rows in the table")
    }
}

В реальных проектах удобно будет создать некую универсальную процедуру для демаршалинга подобных запросов, принимающую на вход имя функции PostgreSQL, её параметры, limit, offset, и ссылку на слайс структур произвольного типа, в который будут помещены результирующие данные.

Полный код примера доступен на GItHub

Комментарии (45)


  1. jonic
    21.06.2024 14:25
    #26959574
    +2

    И как это решает проблему offset?


    1. stoi Автор
      21.06.2024 14:25
      #26959582

      о какой проблемы вы говорите? Я не обещал решения этой проблемы в этой статье )


      1. jonic
        21.06.2024 14:25
        #26959892

        Проблема дублей и пропадающих записей


  1. astec
    21.06.2024 14:25
    #26959612
    +10

    То есть если есть миллиард записей и надо выбрать первые 100 всегда будет делаться full table or index scan?

    Пользователи будут очень довольны.

    Много лет назад пришёл к мысли что пользователям не нужна "пагинация". Кого интересует конкретно 3я страница например?

    Достаточно load N first + load more = load N starting from last ID. Если позволяет можно позволить "load all".

    Никто ни разу не пришёл и не попросил страницы.


    1. stoi Автор
      21.06.2024 14:25
      #26959622

      Вопрос о нужности/ненужности пагинации - это уже совсем другой вопрос. Если заказчик требует - куда деваться?


      1. WLMike
        21.06.2024 14:25
        #26959994
        +1

        Донести ему, что она не нужна и предложить решение получше


      1. Andrey_Solomatin
        21.06.2024 14:25
        #26961702

        Если у вас есть хороший поиск с фасетами и фильтрами, то вам пагинация не нужна как таковая.

        Хотя для хорошего поиска надо всё это загнать в подходящую базу. Поиск сразу начнет отставать от реальности.


  1. Kerman
    21.06.2024 14:25
    #26959652
    +1

    к моменту выполнения второго запроса данные могут стать неактуальными

    Данные могут стать неактуальными в то время, когда результат запроса летит к пользователю. В то время, когда пользователь их читает. Собственно, для того, чтобы показывать данные, такой трюк не поменяет примерно ничего.

    Да, есть ситуации, когда нужно пагинировать данные для сервиса, разбивая большую таблицу на батчи, но здесь непременное условие - не пропустить ни одной строки. По вашему алгоритму между запросами страниц может быть удалена строка из текущей страницы, после запроса которой курсор сместится на количество удалённых записей вперёд. Это касается любых алгоритмов, основанных на row_id.

    некую универсальную процедуру для демаршалинга

    Под демаршаллингом подразумевается маппинг данных из таблицы в поля класса?


    1. stoi Автор
      21.06.2024 14:25
      #26959724

      он. Но демаршалинг звучит круче! )


  1. amaprograma
    21.06.2024 14:25
    #26959728
    +3

    а при чем здесь го? энивей

    какие вопросы нужно задать себе, прежде чем делать пагинацию

    1) правда ли нам нужно знать точное кол-во записей и страниц?

    2) можем ли мы обойтись отображением первых n страниц?

    3) готовы ли мы обрести беспонтовый ux порожденный limit/offset подходом?

    4) понимаем ли мы, что эта штука положит базу?

    5) разделяем ли передовую мысль человечества про пагинацию с помощью last_id?


    1. stoi Автор
      21.06.2024 14:25
      #26959730

      с пятым пунктом не знаком


    1. stoi Автор
      21.06.2024 14:25
      #26959736

      6) Можем ли мы убедить заказчика отказаться от пагинации в таком виде?


    1. stoi Автор
      21.06.2024 14:25
      #26959752

      Скиньте, плз, ссылку - что почитать про передовую мысль и last_id. Буду благодарен!


      1. Kerman
        21.06.2024 14:25
        #26959808
        +6

        Мысль интуитивно понятна, наврядли кто-то найдёт статью про это. Это тот кейс, про который я рассказывал: нельзя пропускать строки. Вся статья, которую бы я написал, сведётся к следующей строке:

        SELECT * FROM table WHERE ID > last_id LIMIT :limit:


        1. jonic
          21.06.2024 14:25
          #26959884

          Мир еще не потерян


        1. Akuma
          21.06.2024 14:25
          #26959898
          +2

          Это все очень горько ломается когда нужны произвольные сортировки, увы.

          ps. Вы забыли order by


          1. Kerman
            21.06.2024 14:25
            #26959924

            При order by не существует решения вменяемой пагинации вообще-то. Я про него не забыл, я его не учитываю.

            При требовании "не пропускать строки" пагинация бесполезна. Тут нужно сортировать ПОСЛЕ получения данных. Причём всех.

            При показе пользователю метод last_id не нужен. Всё равно пользователь видит устаревшие данные, что там пропущено - это его проблемы.


            1. Akuma
              21.06.2024 14:25
              #26959944

              В теории можно пользоваться курсорами, но это тот ещё геморрой.

              В целом если данных до пары миллионов, оффсеты работают нормально.


              1. jonic
                21.06.2024 14:25
                #26960114

                Можно вырезать куски id на where not in например, человеку не реально так далеко открутить что бы получить нагрузку.


                1. Akuma
                  21.06.2024 14:25
                  #26960612

                  Это будет работать медленнее offset, скорее всего. Потому что у вас не range, а офигевше большой список


                  1. jonic
                    21.06.2024 14:25
                    #26961872

                    Не, индексы решают)


                    1. jonic
                      21.06.2024 14:25
                      #26962650

                      Минусят явно те кто ни разу даже explain такого запроса не делал.


                      1. Andrey_Solomatin
                        21.06.2024 14:25
                        #26963260

                        Минусят явно те кто ни разу даже explain такого запроса не делал.

                        Эксплейн на пустой базе и на большой покажет разные планы.


                      1. jonic
                        21.06.2024 14:25
                        #26963666

                        Логично, только не отменяет того факта, что поможет сохранить любую сортировку (а это уже просадка по скорости) и вывести данные порционно. Это рабочий вариант и много кто им пользуется. Не подскажете лимит на количество сущностей в in? А, он настолько велик что в жизни туда не дойдет пользователь.


                      1. Andrey_Solomatin
                        21.06.2024 14:25
                        #26964110

                        Это рабочий вариант и много кто им пользуется


                        Это один из вариантов работы с информацией который удобен для человека при небольшом колличестве данных.

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

                        Пользовательские проблемы начнуться намого раньше и от пагинации попросит отказаться бизнес.

                        Если у вас есть 10 тысяч записей типа Пользователь, то вы можете сортировать и пагинировать по любым полям без дополнительныx индексов и база скушает это.

                        Вот только человеку работать с этим через погинацию будет неудобно.

                        Можно переделать на поиск с автокомлитом, который отсортирован с фиксированной сортировкой. Тогда и пользователю удобно и на базу нагрузка не большая.


        1. Andrey_Solomatin
          21.06.2024 14:25
          #26961694

          Почему нет, даже термин придумали: Курсорная пагинация (Cursor-Based Pagination)


        1. shushu
          21.06.2024 14:25
          #26962018

          Это называется курсорная пагинация.

          Но что бы её использовать с ид, сортировка по ид тоже нужна.


  1. stoi Автор
    21.06.2024 14:25
    #26959742

    Вопрос не в том - делать или не делать пагинацию. Его решили без меня. Вопрос в данном случае - как сделать проще и удобнее, раз уж требуют


  1. kogemrka
    21.06.2024 14:25
    #26959760
    +3

    Не уверен, что пагинацию через limit и offset в принципе делать правильно.

    Допустим, пользуясь пагинацией через limit и offset кто-нибудь начнёт табличку целиком "постранично забирать".

    Т.е. представьте, пусть у вас таблица на миллиард-десяток элементов.

    пришли к вам один раз с

    SELECT * FROM client_accounts WHERE <условия> LIMIT 100 OFFSET 0;

    Потом пришли с

    SELECT * FROM client_accounts WHERE <условия> LIMIT 100 OFFSET 100;

    Пришли к вам ещё раз с

    SELECT * FROM client_accounts WHERE <условия> LIMIT 100 OFFSET 200;

    И т.д.

    Мне кажется, или нам придётся из базы забрать n^2 строчек суммарно таким образом, чтобы читающий код мог исходные n строчек выгрести?


  1. miruzzy
    21.06.2024 14:25
    #26960682

    Давайте я вам предложу способ получше:

    возьмите приблизительное количество строк из статистики

    select 
  *,
  (
    select n_live_tup 
    from pg_stat_user_tables 
    where relname = 'clients'
    /*добавь схему ещё*/
  )
from clients
offset 10
limit 10

    UPD

    Если вы хотите очень приблизительный результат ( допустим, у вас условия по выборке идёт и вы не можете применить выше ) - запустите explain и вытяните кол-во записей из планировщика. ( конечно, если у вас данные нормально распределены )

    Ну ещё кэши запросов есть на всякий ( внешний кэш имею в виду )


  1. anayks
    21.06.2024 14:25
    #26961042

    https://habr.com/ru/articles/301044/

    Мне понравился способ из комментариев, где человек ищет по индексу (например, по идентификатору) через JOIN или временную таблицу, а далее из неё по идентификатору получает эти самые необходимые данные. Правда издержка очевидная - сам индекс.


  1. stvoid
    21.06.2024 14:25
    #26961524
    +3

    Ну это всё прекрасно, но как и писали выше - это всё ерунда, главная проблема лимит-оффсет в производительности. Если вы делаете это в лоб, то чем дальше страница тем медленнее будет выполняться запрос.

    Где-то когда-то нашел в комментариях решение, которое на удивление дает около константное время запроса на любую страницу. Концепция простая:

    SELECT * FROM mega_table AS m0
INNER JOIN (
    SELECT id FROM mega_table
    WHERE <conditions>
    ORDER BY <conditions>
    LIMIT <i>
    OFFSET <j>
) AS fast_filter ON m0.id = fast_filter.id
<conditions if need>

    Суть заключается в том, чтобы избежать некоторых тяжелых операции, если делаете и оффсет и условия и сортировку на одной таблице. Ну и главное, чтобы на поле id из примера был повешен индекс, а так же на те поля по которым вы подразумеваете фильтрацию и сортировку.
    Хак в том, чтобы использовать только индексные поля, тем самым не заставляя БД тащить тяжелые тосты и вот это всё, а уже в самом конце, когда нам известны id выборки, то их сильно проще забрать, чем перечитывать страницы для оффсета еще и с сортировкой и условиями.

    Извиняюсь если описал кривовато, но суть я думаю понятна. На боевом проекте с десятками миллионов записей, где основные данные хранятся в jsonb, а нужные для фильтров данные лежат в соседних полях с индексами - полет отличный, погрешность времени выполнения на уровне погрешности.


    1. stoi Автор
      21.06.2024 14:25
      #26961572

      Не очень понял, почему SELECT id FROM mega_table будет существенно быстрее, чем SELECT * FROM mega_tabl


      1. stvoid
        21.06.2024 14:25
        #26961602
        +2

        https://stackoverflow.com/a/27169302
        https://dba.stackexchange.com/a/205286

        Простите, но слишком лень гуглить более техническое описание того, почему чистый оффсет такой тяжелый.
        СОВСЕМ ПРИМИТИВНОЕ ОБЪЯСНЕНИЕ: если мы применяем оффсет на что-то, что в конечном итоге использует только индексы (в данном случае возвращается только таблица с полем id), то pg не нужно заглядывать целиком в реальную таблицу с данными, читать байтики там чтобы не ошибиться с оффсетом, попутно проверяю условия и т.д. Т.к. мы используем чистое индексное поле на выходе, то pg может на много быстрее посчитать оффсет. По сути мы упираемся только в то, сколько условных байт нам надо прочитать, чтобы корректно отсчитать строки оффсета. ЭТО ОЧЕНЬ ПРИМИТИВНОЕ ОБЪЯСНЕНИЕ! Возможно тут появится гуру pg и объяснит более корректно.


        1. Andrey_Solomatin
          21.06.2024 14:25
          #26961742
          +1

          Индекс это еще одна таблица с данными. В некоторых базах он хранится точно так-же как и основая таблица.

          SELECT id FROM mega_table
          В запросе база сама выбирает читать из таблицы или из индекса. Если в индексе есть все нужные поля, подходящая сортировака и он меньше по размерам, то база выберет его для чтения.


          1. stvoid
            21.06.2024 14:25
            #26961866

            Да, именно так, спасибо что сократили мою мысль )))

            Короче говоря, перепробовав разные варианты, если вам нужна фильтрация и ограничение выдачи, то более оптимального варианта я не нашел.


        1. stoi Автор
          21.06.2024 14:25
          #26962638

          А как же условия WHERE, в котором может быть много разных полей? Например, отображение товара в интернет магазине с фильтрацией по куче разных параметров (полей)? Как СУБД осуществит фильтрацию, читая только таблицу первичного индекса? Ничего не понимаю...


          1. stvoid
            21.06.2024 14:25
            #26962742
            +1

            Я же написал, просто используйте индексы на те поля которые вам нужны. Индексы хранятся в отдельной таблицы и со своей магией, все что вам нужно от этого джойна - это получить id, а все остальные данные вы получаете уже "наверху".
            Вы можете обернуть это ещё раз в под запрос и присоединить что то ещё - это все равно будет быстро, т.к. у вас уже будет ограниченный список выборки.
            И да, джойн тут играет магическую роль, не знаю почему, но планировщик мне показывал бОльшую эффективность, чем с where. Но тут не ручаюсь, стоит 15 версия если что.

            Конечно, есть крайний случай, когда у вас фильтр подразумевается по всем полям выводимых данных. Ну, тут уже сами прикидывайте что вам важнее, скорость или место на диске/памяти.


          1. Andrey_Solomatin
            21.06.2024 14:25
            #26963284

            Если данные хранятся отсортированные по id, то возможно просто вычитать их из таблицы будет даже проще. Но вот если порядок другой, то через индекс с объединением будет быстрей. В идеале построить индекс отсортированный по каждому возможному типу сортировки.

            Мне не приходилась делать пагинацию с сортировкой по большим данным, мы данные копировали в Solr (брат ElasticSearch и OpenSearch). Там уже из коробки одним запросом и фильтрация и полное количество записей и фасеты с количеством попаданий.

            https://www.elastic.co/guide/en/app-search/current/facets.html


  1. stoi Автор
    21.06.2024 14:25
    #26963694

    Копья ломаются по поводу того, что пагинация по limit/offset - плохо или вообще - пагинация зло. Но, по предложенному способу вроде замечаний особо нет )). Дело в том, что есть случаи, когда и объем и нагрузка небольшие и заказчик хочет вот простейшую пагинацию. Это мой случай как раз. ИМХО, с ума сходить не стоит и лучше сделать простейшим способом. Ну а когда/если понадобится оптимизация - тогда и будем морщить лоб )). Спасибо, коллеги!


    1. Andrey_Solomatin
      21.06.2024 14:25
      #26963992

      Дело в том, что есть случаи, когда и объем и нагрузка небольшие и заказчик хочет вот простейшую пагинацию


      Это противоречит тому, что написанно в статье:

      Хочу поделиться лайфхаком по реализации универсального способа пагинации в произвольных запроса к БД для получания списков


      1. stoi Автор
        21.06.2024 14:25
        #26964148

        Ни одного противоречия нет. Вы придираетесь )


    1. Andrey_Solomatin
      21.06.2024 14:25
      #26964006

      ИМХО, с ума сходить не стоит и лучше сделать простейшим способом. Ну а когда/если понадобится оптимизация - тогда и будем морщить лоб ))


      Под морщить лоб вы подрузумеваете хранимую процедуру с оконными функциями, вместо двух запросов?

      Вся соль в том, что крайне нежелательно делать это в лоб двумя запросами к БД.


      1. stoi Автор
        21.06.2024 14:25
        #26964138

        Под морщить лоб я понимаю либо отказ от пагинации либо оптимизация для снижения нагрузки )


  1. Andrey_Solomatin
    21.06.2024 14:25
    #26964144

    Немного о пагинации в реальности.

    https://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=key

    Вверху страницы:

    На странице 4:
    145-192 of over 200,000 results for "key"
    На странице 5:
    193-240 of over 100,000 results for "key"

    Где-то внизу плашка пагинации, там 1, 2, 3, ..., 7. Всего семь страниц на эти over дофига результатов.

    Сортировка такого объёма выдачи не имеет смысла логически, я так никогда стану делать, потому что перед тем как начать пользоваться пагинацией и сортировкой я добавлю больще фильтров и уточнений запросов.