How to properly set up keyset pagination?

[aaronm137]

Hello, I have a table with 1,5M records and the classic OFFSET pagination performs extremely slow, so I am looking for a way to fix it. After some research, I found that the keyset pagination might be the way to go.

I tried to implement Pagy - added include Pagy::Backend to the controller. Added the following to the controller action:

@pagy, @cars = pagy_keyset(Car.by_id(params[:id])
                              .by_color(params[:color])
                              ...
                              .by_manufacturer(params[:manufacturer]
                              .order(created_at: :desc), limit: 50)

Created the PaginationHelper:

module PaginationHelper
  include Pagy::Frontend
end

and finally, added the following to the view:
<%= pagy_nav(@pagy) %>

And this results into an error
undefined method `pages' for #<Pagy::Keyset::ActiveRecord:0x000000011545b7d8 @vars={:limit=>50, :page_param=>:page, :page=>nil}, @limit=50, @set=#<ActiveRecord::Relation [#<Car id: ...

I also noticed the following helpers in the documentation, but I am not sure how to properly use them.

# URL Helpers
pagy_keyset_first_url_page(@pagy, absolute: true)
#=> "http://example.com/foo?page" 

pagy_keyset_next_url_page(@pagy)
#=> "/foo?page=eyJpZCI6MzB9"

How do I properly set up the keyset pagination?

[ddnexus]

The Keyset pagination technique is not usable with frontend helpers like the pagy_nav.

The reasons are explained in details in the Pagy::Keyset documentation, which also shows you the differences between offset and keyset pagination.

If you need frontend features and you have big tables, you can use the countless extra and/or the :max_pages variable. More importantly you need also proper DB indexing for the columns you order/search/filter.

[ddnexus]

BTW, the new v43 implements an exclusive technique that might interest you: see the keynav pagination.

[aaronm137]

Thank you for pointing me to the right direction. So far, I have only been using OFFSET, so this is all new to me.

Using pagy_countless certainly improves the performance of data, however the pagination below, along with the absence of the total number of records it's a bit of trouble for me.

I found that that I can get a row estimate by running a query like this: SELECT reltuples::integer FROM pg_class WHERE relname = 'my_table'; As I am not deleting data and adding them in batches in certain parts of the day, I believe this estimate is good enough for me.

Is there a way to integrate this number/estimate of total rows and make it work with Page?

[ddnexus]

You can pass your estimated count to the normal pagy method and pagy will skip the count query, so you can use regular frontend helpers.

I recommend to use also :max_pages and proper indexing.

[ddnexus]

You can try something like:

@pagy, @records = pagy(my_big_collection, count: my_estimated_count, max_pages: 300)

<%== pagy_nav(@pagy) %>