Как правильно делать пагинацию на бэкенде?

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

Spring Data JPA для реализации пагинации предлагает использовать интерфейс PagingAndSortingRepository:

public interface ProductRepository extends PagingAndSortingRepository<Product, Integer> {
    List<Product> findAll(Pageable pageable);
}

В Pageable мы передаем нужный нам номер страницы, ее размер и поле для сортировки, без которого пагинация не имеет смысла.
Но, стандартное решение от Spring подходит не всегда. Вызов метода findAll для получения страницы 30 приведет приблизительно к следующему SQL запросу:

SELECT * FROM product ORDER BY id LIMIT 10 OFFSET 10*30

Значения LIMIT и OFFSET будут выбраны в зависимости от того, какую по счету страницу запрашиваем. Этот подход называется offset pagination.
Проблема в том, что при использовании конструкции OFFSET N БД все равно выбирает все начальные строки запроса, и только после выборки пропускает N первых строк (обратите внимание на rows=3010 в плане запроса). Из за этой особенности время на получение каждой следующей страницы растет линейно. Чтобы этого избежать стоит использовать подход keyset pagination (также известную как seek или cursor pagination). Суть подхода в том, что мы по некоторому полю (например по id) убираем из результата уже показанные пользователю записи:

SELECT * FROM product WHERE id > 478 ORDER BY id LIMIT 10

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

Стриминг данных из БД без пагинации¶

Keyset pagination, пожалуй, лучший вариант, если нужно обработать большое количество данных из БД. Но, представим, что по какой-то причине пагинация нам не подходит и мы хотим сделать выборку одним SQL запросом.
По умолчанию многие JDBC драйверы (PostgreSQL, MySQL) загружают в память приложения весь результат запроса из БД, что в нашем случае может привести к падению приложения из за нехватки памяти. Чтобы этого избежать мы можем настроить JDBC драйвер таким образом, чтобы он загружал данные небольшими порциям, пока мы последовательно проходим по ним курсором.
Как это сделать - лучше прочитать в документации на ваш JDBC драйвер, 100% универсального решения нет.

В большинстве случаев достаточно установить параметр запроса FetchSize, например, используя JdbcTemplate из Spring:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setFetchSize(200);
jdbcTemplate.query("select * from product", rs -> addRowToExcel(rs));

При таких настройках JDBC драйвер будет запрашивать результаты запроса пакетами по 200 строк, причем следующий пакет будет загружен только когда мы обработаем все строки из текущего.

Использование стриминга проигрывает в удобстве keyset pagination, так как вынуждает держать открытой сессию с БД все время пока делаем выборку и обработку всех записей.

Стриминг данных из БД в Spring Data JPA¶

JPA для стриминга больших ResultSet из БД подходит плохо, слишком много способов отстрелить себе обе ноги, плюс плохая производительность. Но, если вы не ищите легких путей, репозиторий будет выглядеть так:

public interface ProductRepository extends Repository<Product, Long> {

    @QueryHints(value = {
            @QueryHint(name = HINT_FETCH_SIZE, value = "50"),
            @QueryHint(name = HINT_CACHEABLE, value = "false"),
            @QueryHint(name = READ_ONLY, value = "true")
    })
    @Query("select p from Product p")
    Stream<Product> getAll();
}

Аннотациями @QueryHint меняем стандартное поведение Hibernate:

• HINT_FETCH_SIZE - загружаем из БД результаты запроса пачками по 50 записей
• HINT_CACHEABLE - на всякий случай отключаем кеширование Product в кеше второго уровня Hibernate.
• READ_ONLY - полученные Product будем использовать только для чтения. Это избавляет Hibernate от необходимости следить за изменениями состояния Product, что положительно скажется на производительности.

Но пачкой аннотаций история не заканчивается, важно еще правильно вызывать этот метод:

  @Transactional(readOnly = true)
  public void processProducts() {
    try(Stream<Book> stream = productRepository.getAll()) {
      stream.forEach(product -> {
        addRowToExcel(product);
        entityManager.detach(product);
      });
    }
  }

Обратите внимание на несколько моментов.

1. Все действия со Stream нужно делать в транзакции, иначе сессия с БД закроется и никаких данных мы не получим.
2. Stream необходимо закрывать самостоятельно, об этом нас просит документация Spring Data JPA. Хотя на практике можно этого и не делать, Hibernate закроет его за нас в конце сессии.
3. Hibernate держит в кэше первого уровня все полученные в транзакции объекты. Поэтому вручную выкидываем каждую entity из контекста, как только закончили ее обработку. Для этого вызываем entityManager.detach(product). Иначе на больших выборках приложение будет падать с OutOfMemoryException. Альтернативный и более производительный вариант - каждые N итераций вызывать полную очистку контекста функцией EntityManager.clean().

Keyset pagination в Spring Data JPA¶

В Spring Data JPA keyset pagination добавили с версии 3.1 и выглядит она следующим образом.
Сначала описываем метод в репозитории ограничивая количество записей в результате используя конструкции Top или First. При этом кастомные запросы в аннотации @Query, к сожалению, не поддерживаются.

public interface ProductRepository extends JpaRepository<Product, Long> {
    Window<Product> findTop10By(ScrollPosition position);
}

использование:

public void processAllProducts() {
    WindowIterator<Product> products = WindowIterator.of(repository::findTop10By)
            .startingAt(ScrollPosition.keyset());

    while (products.hasNext()) {
        Product product = products.next();
        processOne(product);
    }
}

В цикле while происходит последовательная обработка результатов выборки из таблицы. После обработки пачки из 10 элементов будет запущен следующий запрос.
Обратите внимание, что функция processAllProducts вызывается вне транзакции, каждую пачку записей запрашиваем в отдельной сессии. Если бы мы получали все страницы в рамках одной транзакции, то нам пришлось бы вызывать entityManager.detach(product) в цикле while, иначе все выбранные объекты будут оставаться в кэше Hibernate и приложение может упасть из за нехватки памяти на больших таблицах.

Ссылки¶

• OFFSET is bad for skipping previous rows