ContextQMD
Back to Pagy

Choose Right

docs/guides/choose-right.md

43.5.35.7 KB
Original Source

:icon-thumbsup:  Choose the right tool

Pagination just means retrieving a (potentially big) collection of items in small sequential chunks (pages).

Pagy offers four different pagination techniques, each implementing different types of paginators. Choose the right one to ensure the best performance and workflow.

Pagination Types

:icon-move-to-end:  OFFSET {#offset}

The most common pagination technique is counting the collection items (COUNT DB query), using the count to calculate where the specific pages start (i.e., OFFSET) and retrieving only the LIMIT items for that page (OFFSET+LIMIT DB query).

It is straightforward to understand and set up and very versatile for the UI, but it also has a few shortcomings.

==- Shortcomings

  • DB Performance
    • Counting records might be quite expensive in terms of execution time and DB load. The standard OFFSET pagination causes the DB to count twice per page: one for the total records and another for the records to skip.
  • Data Shift
    • If records are created or deleted during browsing, the calculated OFFSET may become inconsistent. The user may see previous records again or miss records entirely.
    • Notice that querying the precise count for each page DOES NOT fix the data-shift problem.

!!!tip For better DB performance with OFFSET...

==- Paginators  [!badge variant="contrast" size="xs" corners="pill" text="3"] {#offset-paginators}

{.list-icon}

  • :icon-move-to-end: :offset
    • :icon-check-circle: Best for: Standard App (Small Data)
    • :icon-thumbsup: Pros: Simple setup, full UI support
    • :icon-thumbsdown: Cons: Slow on big tables (two queries per page), data-shift

{.list-icon}

  • :icon-move-to-end: :countish
    • :icon-check-circle: Best for: Standard App (Large Data)
    • :icon-thumbsup: Pros: Same as :offset, but far better performance than :offset (memoizes the total count)
    • :icon-thumbsdown: Cons: Same as :offset, the count may become stale (likely a negligible side effect)

{.list-icon}

  • :icon-move-to-end: :countless
    • :icon-check-circle: Best for: API, Infinite Scroll
    • :icon-thumbsup: Pros: Fastest in the offset family (only one query per page)
    • :icon-thumbsdown: Cons: Same as :offset, the count is always nil, UI support with a few limitations

===

:icon-key:  KEYSET {#keyset}

The KEYSET pagination technique allows the fastest and lighter DB performance. It does not count the (ordered) collection (which makes it faster), nor calculates any numeric page pointers in advance (which avoids the data-shift during browsing). It just uses the values in the last record of the page to retrieve the next page.

==- Shortcomings

  • It knows only the current page and the pointer to the next page.
  • Page pointers are encoded strings and the count is not known.
  • It supports only APIs and infinite scrolling.

!!!tip For UI support with KEYSET pagination... Use the :keynav_js paginator. !!!

==- Paginators  [!badge variant="contrast" size="xs" corners="pill" text="2"] {#keyset-paginators}

{.list-icon}

  • :icon-key: :keyset
    • :icon-check-circle: Best for: API, Infinite Scroll
    • :icon-thumbsup: Pros: Fastest paginator, no data-shift, fastest single query per page
    • :icon-thumbsdown: Cons: Very limited UI support, appropriate DB indices required

{.list-icon}

  • :icon-key: :keynav_js
    • :icon-check-circle: Best for: Standard App (Large Data)
    • :icon-thumbsup: Pros: All the pros of :keyset+:countless, numeric pages
    • :icon-thumbsdown: Cons: Same as :countless, requires JavaScript Support (or it falls back to the :countless paginator)

===

:icon-calendar:  TIME-RANGE {#time-range}

This hybrid technique filters by a specific time period (Year, Month, Day, etc.) and applies the offset paginator within that period.

==- Paginator  [!badge variant="contrast" size="xs" corners="pill" text="1"]

{.list-icon}

  • :icon-calendar: :calendar
    • :icon-check-circle: Best for: Time-Series, Logs collections
    • :icon-thumbsup: Pros: Natural navigation for date-based data
    • :icon-thumbsdown: Cons: The setup for the UI is more involved

===

:icon-search:  SEARCH {#search}

Pagy supports ElasticsearchRails, Meilisearch, Searchkick, and TypesenseRails.

The search paginators get the count, limit and results provided by the search platform. Pagy acts as an interface to these underlying gems, using the OFFSET technique, without its shortcomings.

==- Paginators  [!badge variant="contrast" size="xs" corners="pill" text="4"]

{.list-icon}

===