43.4.4
Powered by

  Choose the right tool

At the most basic level, pagination just means retrieving a (potentially big) collection of items in small sequential chunks (pages)... but there's more than one way to crack an egg.

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

Pagination Types

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.

  • 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.
  •  :offset
    • Best for: Standard App (Small Data)
    • Pros: Simple setup, full UI support
    • Cons: Slow on big tables (two queries per page), data-shift
  •  :countish
    • Best for: Standard App (Large Data)
    • Pros: Same as :offset, but far better performance than :offset (memoizes the total count)
    • Cons: Same as :offset, the count may become stale (likely a negligible side effect)
  •  :countless
    • Best for: API, Infinite Scroll
    • Pros: Fastest in the offset family (only one query per page)
    • Cons: Same as :offset, the count is always nil, UI support with a few limitations

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.

  • 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.
  •  :keyset
    • Best for: API, Infinite Scroll
    • Pros: Fastest paginator, no data-shift, fastest single query per page
    • Cons: Very limited UI support, appropriate DB indices required
  •  :keynav_js
    • Best for: Standard App (Large Data)
    • Pros: All the pros of :keyset+:countless, numeric pages
    • Cons: Same as :countless, requires JavaScript support (or it falls back to the :countless paginator)

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

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

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.