Совет дня №15

Пагинация, продолжение.

Вернемся к случаю, когда нужна пагинация по убыванию времени (created_at, updated_at) и так далее. Этот критерий встречается так часто, что для него есть лазейка.

В прошлый раз мы использовали кортеж (created_at, id). Идея в том, что поскольку created_at не уникален, он не дает точного положения в таблице. Но так как id уникален, их комбинация – тоже уникальна.

От кортежа можно избавиться, если в качестве ID используется UUID v7. Дело в том, что v7 совмещает в себе эти два свойства: время и уникальность. Существуют разные подвиды UUID v7, но не зависимо от них пагинация будет:

• точно попадать в границы;
• использовать btree индекс.

Итак, если у вас API и нужна пагинация, ваши варианты:

• limit и offset; убедитесь, что offset не превышает какого-то разумного числа, например тысячи. Иначе вас будут парсить.

• keyset: комбинация полей (some_field + id) для уникальности. Требует отдельного индекса.

• UUIDv7 – если требуется пагинация по created_at. Это частый случай, поэтому рассмотрите его.

Перейдем к пагинации, которая используется не в API, а для служебных нужд. Например, в миграциях, переносе данных и так далее. Нужно обойти огромную таблицу, при этом выгрузить ее в память нельзя – слишком большая (например, 100 миллионов записей).

Один из способов – использовать курсор и FETCH API. Апишка у него довольно простая:

BEGIN;

DECLARE cur_foo CURSOR FOR SELECT * FROM items;

FETCH FORWARD 100 FROM cur_foo;
FETCH FORWARD 100 FROM cur_foo;
FETCH FORWARD 100 FROM cur_foo;
...
CLOSE cur_foo;
COMMIT;

Объявляем курсор, затем в цикле вытягиваем по 100 записей, пока результат не пустой. В конце закрываем курсор.

Казалось бы, вот он – святой Грааль. Но беда в том, что курсор требует транзакции. В момент открытия он запоминает текущий снимок (номер транзакции) и просматривает записи, версия которых не превышает его. Чтобы пользоваться курсором, нужно держать транзакцию в течение всего цикла. Для больших таблиц это дорого, поэтому подходит только для всяких ночных скриптов.

Кроме того, курсор привязан к конкретному соединению с БД. Использовать их в HTTP API невозможно.

Другой способ обойти большую таблицу – сдампить ее в файл при помощи COPY (см. прошлый совет). Чтобы таблица не заняла весь диск, ее сжимают в gzip. В результате у вас оказывается файл my_table.gzip, вы отрываете его и спокойно парсите. Идея в том, чтобы забрать данные из базы как можно скорее и потом не мучить ее пагинацией. Если скрипт упадет, не придется насиловать базу снова – у вас уже есть файл.

Третий способ – использовать драйвер, который позволяет обрабатывать записи в полете. Например, мой pg2. Функция execute принимает запрос и всякие опции. Среди прочих можно передать редьюсер – функцию трех тел:

(fn
  ([]
   (make-acc ...))
  ([acc row]
   (conj acc row))
  ([acc]
   (finalize acc)))

Тело без аргументов – инициатор аккумулятора (может быть мутабельным), тело с одним аргументом – финализатор аккумулятора, с двумя – приращение записи к аккумулятору. Из коробки доступны разные редьюсеры (см документацию). Легко написать свой, который будет отправлять каждую строку в сеть, в асинхронный канал и так далее. И все это – в полете, то есть по мере получения сообщений от Postgres, без исчерпания всей памяти.

Полагаю, это все, что можно сказать про пагинацию.