Dlaczego paginacja z użyciem OFFSET to zły pomysł (i jak robić to lepiej).

Ostatnio wpadłem na bardzo ciekawy stary kod w języku Java (prawie 20-letni), w którym paginacja była wykonywana z użyciem Result Set i scrollowaniem wyników zapytania. Kod jest bardzo generyczny i służy do wyciągania danych z wielu tabel i prezentowaniu tych danych na GUI wraz ze stronicowaniem. Z tego powodu jest dość skomplikowany. Udało się zmienić ten kod na zapytania z użyciem SQL offset oraz limit gdzie definiujemy wprost ile wyników chcemy pobrać bezpośrednio z bazy danych i od którego momentu. Jak się okazuje to rozwiązanie też nie jest uznawane za idealne.

ResultSet i paginacja

Zacznijmy od wspomnianego ResultSet i jak on działa:

try (Statement statement = 
     connection.createStatement(
       ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
  ...

Zazwyczaj podczas ładowania danych do ResultSet to sterownik bazy danych sam decyduje, ile wierszy pobrać naraz. Np.

W Oracle:

• Sterownik nie pobiera całej tabeli naraz
• Oracle ma domyślny fetch size = 10 lub = 50 (w zależności od wersji sterownika — w nowszych zwykle 50).
• To oznacza, że zapytanie od razu pobiera tylko pierwszą paczkę, a kolejne są dociągane w miarę przeglądania wyników.
• Można ręcznie ustawić fetch size:
  • statement.setFetchSize(700); lub resultSet.setFetchSize(500);
  • Oracle JDBC traktuje to jako „hint” i przyjmuje wartość jako „ile rekordów pobrać w jednym round-tripie”.

Natomiast, w MySQL domyślne zachowanie jest takie, że ResultSet ściąga cały zestaw danych do pamięci od razu.

Scrollowanie w ResultSet

Typ ResultSet określa, w jaki sposób możemy poruszać się po pobranym zestawie danych:

• TYPE_FORWARD_ONLY – ustawienie domyślne; kursor przechodzi tylko w jedną stronę, od początku do końca.
• TYPE_SCROLL_INSENSITIVE – możemy przemieszczać się po danych w obie strony; jeśli dane w bazie zmienią się podczas przeglądania, ResultSet tego nie zauważy – widzimy stan danych z momentu wykonania zapytania.
• TYPE_SCROLL_SENSITIVE – również pozwala na przewijanie w obie strony, ale w tym trybie ResultSet od razu odzwierciedla zmiany dokonane w bazie w trakcie pracy.

Nie wszystkie bazy danych obsługują wszystkie typy ResultSet.

Kiedy nie stosować scrollowania z Result Set:

Scrollowanie z ResultSet nie nadaje się do typowych aplikacji GUI ze stronicowaniem i prezentowaniem wyników, ponieważ:

• jest wolniejsze niż typowy OFFSET i LIMIT,
• może prowadzić do Out Of Memory – jeśli niewłaściwie użyte,
• jest bardziej pamięciożerne (po stronie bazy oraz aplikacji),
• bardziej obciąża bazę danych,
• ryzyko niespójności, kursor nie widzi nowych rekordów (insensitive) albo odświeża je ręcznie (sensitive)
• ryzyko wystąpienia przerwań ponieważ cały zbiór danych przeglądamy na jednym połączeniu z bazą danych (timeout-y, zerwane połączenie)

Kiedy można stosować scrollowanie z Result Set:

Scrollowanie wyników przy użyciu Result Set może być wykorzystany jeśli:

• i tak mamy zamiar pobrać/użyć całej zawartości zwróconych danych z bazy – całego zbioru a nie tylko kilku stron z wyników,
• zbiór jest READ-ONLY i nie zmienia się w czasie pobierania danych,
• zależy nam na ograniczeniu zapytań do bazy danych (w przypadku Result Set zapytanie jest wykonywane tylko raz i połączenie z bazą jest cały czas trzymane, a rekordy są dociągane w razie potrzeby – w przypadku OFFSET i LIMIT każde przejście strony to kolejny SELECT wysłany do bazy)

OFFSET, LIMIT i paginacja

Rozważmy teraz alternatywę z użyciem OFFSET oraz LIMIT i pobieraniem danych partiami. Jak już wspomnieliśmy powyżej w przypadku Result Set utrzymujemy jedno połączenie z bazą danych i możemy dociągać wyniki na bierząco kiedy je potrzebujemy. W przypadku użycia keywordów OFFSET i LIMIT sytuacja wygląda całkowicie inaczej. Za każdym przesunięciem (przejściem na kolejną stronę wyników) wysyłamy nowy SQL ze zmodyfikowanymi parametrami:

SELECT *
FROM employees
ORDER BY employee_id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Kolejna strona to kolejny wysłany SQL:

SELECT *
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Zauważ, że zamiast słowa kluczowego LIMIT używam OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY – jest to składnia dla bazy danych ORACLE, w której właśnie tak jest wykonywane limitowanie wyników zapytania.

Warto zaznaczyć, że ORDER BY jest obowiązkowe przy stosowaniu OFFSET/LIMIT(FETCH)

Zalety stosowania OFFSET i LIMIT

• Brak długotrwałych połączeń – pełna odporność na timeouty:
  • OFFSET/FETCH: każda strona to nowe zapytanie SQL → nowe, świeże połączenie z puli.
• Lepsza skalowalność przy wielu użytkownikach
  • zapytanie wykonujesz tylko w momencie kliknięcia strony,
  • brak „zajętych” połączeń czekających bezczynnie.
• Stateless – idealne do aplikacji webowych i REST API
  • nie wymaga trzymania stanu po stronie serwera (cursora, ResultSet-u, Connection),
  • pasuje do HTTP, który jest stateless.
• Odporność na zmieniające się dane (nie do końca ale o tym póżniej):
  • zawsze odpytuje bazę o aktualne dane,
  • nie używa starych buforów ani cache.
• Bardzo niski koszt pamięci po stronie aplikacji
  • aplikacja pobiera tylko jedną stronę naraz (np. 10–100 wierszy),
  • brak konieczności trzymania dużej listy w pamięci.
• Prostsze API i proste debugowanie
  • prosty SQL,
  • proste monitorowanie,
  • łatwe optymalizacje (indeksy, explain plan).

Wady stosowania OFFSET i LIMIT

No, wyglada na to, że mamy same pozytywy? Czyli nic tylko wszędzie stosować OFFSET i LIMIT. No niestety nie jest tak kolorowo jak by się mogło wydawać. OFFSET i LIMIT maja również bardzo poważne wady:

Brak kontroli “stanu” zapytania

To największa wada OFFSET i LIMIT — każde kliknięcie kolejnej strony wysyła nowe, niezależne zapytanie do bazy. Skoro nie utrzymujemy żadnej sesji ani kursora, jedynym punktem odniesienia jest ORDER BY. Jeśli dane w tabeli zmienią się między zapytaniami, mogą pojawić się anomalie: duplikaty lub pominięte wiersze.

• duplikaty:
  • Wyobraźmy sobie, że pierwsze zapytanie zwraca 10 rekordów. Jeśli przejdziemy na kolejną stronę a w międzyczasie inna transakcja doda nowy rekord na początku listy, to wszystkie pozostałe przesuną się o jedno miejsce. W efekcie rekord, który wcześniej był na 10. pozycji, trafi na pozycję 11 — czyli zobaczymy go ponownie na drugiej stronie wyników.

• pominięte rekordy:
  • Możemy również zgubić część rekordów. Jeśli podczas przechodzenia na kolejną stronę inna transakcja usunie jeden z rekordów z pierwszej strony, pozostałe elementy przesuną się w górę. Rekord, który wcześniej znajdował się na pozycji 11, trafi na stronę pierwszą. Ponieważ pierwszą stronę wyświetliliśmy jeszcze przed usunięciem rekordu, nie zobaczymy go tam. Na drugiej stronie również się nie pojawi, bo został już przesunięty na stronę pierwszą.

Fatalna wydajność przy dużych OFFSET-ach

Im większy OFFSET, tym wolniej działa zapytanie – bo baza musi policzyć i odrzucić pierwsze N wierszy zanim zwróci kolejne.
Np. OFFSET 500000 LIMIT 20 – baza realnie skanuje ponad 500 000 rekordów.

Skutki:

• bardzo duże obciążenie CPU i I/O,
• skanowanie i sortowanie dużych zbiorów,
• wolne działanie paginacji na dalszych stronach.

Powtarzalne kosztowne sortowanie

Przy paginacji opartej na OFFSET i LIMIT baza musi za każdym razem ponownie wykonać zapytanie i posortować dane. Każde przejście na kolejną stronę oznacza więc pełne sortowanie od nowa.
W przypadku ResultSet scrollującego sortowanie odbywa się tylko raz — podczas pierwszego wykonania zapytania. Później po prostu przesuwamy kursor po już posortowanym zbiorze danych.

Większa wrażliwość na brak indeksów

Każde przejście strony (pełne sortowanie i skanowanie) przy OFFSET/LIMIT wykonuje się od nowa:

• baza musi posortować całą tabelę (albo duży jej fragment),
• musi policzyć i pominąć X rekordów,

Bez indeksu na kolumnie z ORDER BY, sortowanie będzie kosztowne, pomijanie rekordów (OFFSET) będzie wolne, a każde kliknięcie „next page” to powtórka tej pracy. Przy dużych tabelach robi to gigantyczną różnicę.

Seek/Keyset paginacja na ratunek

Dwie największe wady OFFSET — słaba wydajność przy głębokim przewijaniu oraz możliwe anomalie w wynikach — można wyeliminować, stosując paginację opartą na warunku w klauzuli WHERE. Tak jak w przypadku OFFSET, nadal potrzebujemy uporządkowanych wyników (ORDER BY), ale zmienia się sposób przechodzenia między stronami. Zamiast pomijać stałą liczbę wierszy, wykorzystujemy wartość ostatniego widzianego rekordu i na kolejnej stronie zwracamy tylko te wyniki, które znajdują się za nim w kolejności sortowania.

Co ciekawe, ta metoda zapewnia stabilną wydajność — w przeciwieństwie do OFFSET, gdzie im dalej przesuwamy się w wynikach, tym więcej wierszy baza musi odczytać i odrzucić. W paginacji opartej na wartości ostatniego rekordu ten problem całkowicie znika.

Przykład z keyset pagination:

W pierwszym query zwracamy po prostu listę wyników z limitem:

SELECT *
FROM employees
ORDER BY id
FETCH NEXT 10 ROWS ONLY;

Dla kolejnego query musimy pobrać ostatnie id ze zwróconych wyników i przekazać je jako parametr do WHERE:

SELECT *
FROM employees
WHERE id > :lastIdFromPreviousQuery
ORDER BY id
FETCH NEXT 10 ROWS ONLY;

Warto zauważyć, że w tym podejściu nie występują ani duplikaty, ani pominięte rekordy. Jeśli ktoś usunie wiersz między kolejnymi zapytaniami, nie ma to wpływu na naszą paginację, ponieważ opieramy się na stałej wartości ID ostatniego widzianego rekordu, a nie na jego pozycji. Podobnie, jeśli zostaną dodane nowe rekordy przed naszym ID, nie przesuną one wyników, dzięki czemu nie przegapimy żadnego rekordu, który powinien pojawić się w kolejnych stronach.

Niestety, są też koszty takiego podejścia. W przypadku gdy mamy jedno ID dla wyników, query jest proste, jednak sprawa komplikuje się gdy mamy sortowanie po wielu kolumnach:

SELECT *
FROM orders
WHERE (created_at > :lastCreatedAtFromPreviousQuery)
   OR (created_at = :lastCreatedAtFromPreviousQuery AND id > :lastIdFromPreviousQuery)
ORDER BY created_at, id
FETCH NEXT 10 ROWS ONLY;

• created_at > :lastCreatedAtFromPreviousQuery – pobieramy wszystkie rekordy nowsze niż ostatni widziany created_at.

• created_at = :lastCreatedAtFromPreviousQuery AND id > :lastIdFromPreviousQuery – w przypadku tej samej daty, wybieramy tylko te rekordy, które mają większe id.

Jak widać, SQL staje się już mniej czytelny. Przy większej liczbie kolumn warunki stają się jeszcze bardziej złożone. Dodatkowo komplikacje rosną, gdy wprowadzimy możliwość zmiany kolejności sortowania (ASC/DESC) lub gdy tworzymy generyczne rozwiązanie do prezentacji wyników z różnych tabel, które mają różne nazwy kolumn.

Kolejną barierą jest trudniejsza obsługa po stronie frontendu. W przypadku OFFSET/LIMIT wystarczyło przesyłać z UI dwa parametry: numer strony i liczbę wyników na stronę. W paginacji typu Seek/Keyset musimy natomiast przekazywać ostatnie widziane wartości klucza (np. ID lub kombinację kolumn), aby backend mógł poprawnie zwrócić kolejną stronę wyników.

Pamiętajmy, że w keyset pagination do przejścia na kolejną stronę opieramy się na ostatnim wierszu aktualnie wyświetlonej strony. Natomiast aby wrócić do poprzedniej strony, musimy wiedzieć, od którego wiersza zacząć — czyli potrzebujemy również wartości pierwszego wiersza na bieżącej stronie. W praktyce oznacza to, że ID (lub klucz) również pierwszego wiersza musi być przekazywany przez wszystkie warstwy aplikacji.

Jak widać, pozornie lepsza paginacja typu seek/keyset jest trudniejsza w implementacji. Dodatkowo nie możemy w niej bezpośrednio przejść do wybranej strony o numerze n, ponieważ opieramy się na bieżącym wskaźniku ostatniego rekordu, a nie na pojęciu stron. Paginacja ta opiera się na przechodzeniu w przód i tył, bo musimy mieć kontekst poprzednich wyników.

Podsumowanie

Jak widać, żadna z omawianych metod nie jest idealna. Trzeba jednak przyznać, że najwięcej zalet ma paginacja typu Seek/Keyset. Jeśli tylko jesteśmy w stanie poprawnie ją zaimplementować, warto iść właśnie w tę stronę.

W pozostałych przypadkach dopuszczalne jest stosowanie OFFSET/LIMIT, zwłaszcza gdy lista wyników nie jest bardzo duża — wówczas problem wydajności przy głębokim przewijaniu jest minimalny. OFFSET sprawdzi się też, gdy przeglądany zbiór danych nie jest modyfikowany przez inne transakcje, dzięki czemu nie napotkamy problemów z duplikatami ani pominiętymi rekordami.

Źródła:

https://use-the-index-luke.com/no-offset

https://medium.com/javarevisited/offset-vs-cursor-pagination-implement-production-ready-search-and-pagination-in-spring-boot-db6af32e32af

Medium

Read the English version on Medium: Why pagination using OFFSET is a bad idea (and how to do it better).