Speeding up tens of thousands of pages with PostgreSQL, LIMIT and OFFSET

fast_pagination.rb

require 'will_paginate/active_record'
require 'fast_paginator'

module FastPagination
  def paginate(per_page: 96, page: 1, total_entries: nil)
    WillPaginate::Collection.create(page, per_page, total_entries) do |pager|
      pivot = (pager.total_pages / 2.0).ceil
      paginator = FastPaginator.new(self, pager)

      if pager.current_page <= pivot
        pager.replace(paginator.page_from_front)
      else
        pager.replace(paginator.page_from_back)
      end
    end
  end
end

fast_paginator.rb

class FastPaginator
  attr_reader :relation, :pager
  private :relation, :pager

  def initialize(relation, pager)
    @relation = relation
    @pager = pager
  end

  def page_from_front
    relation.limit(limit).offset(front_offset)
  end

  def page_from_back
    return relation.none if invalid_page?
    
    reverse_relation.limit(limit).offset(back_offset).reverse
  end

  private

  def invalid_page?
    front_offset > pager.total_entries  
  end
  
  def back_offset
    pager.total_entries - front_offset - limit
  end

  def limit
    page_end - front_offset
  end

  def page_end
    [front_offset + pager.per_page, pager.total_entries].min
  end

  def front_offset
    pager.offset
  end

  def reverse_relation
    new_order = relation.order_values.map { |order|
      order.gsub!(/DESC NULLS LAST/i, 'ASC NULLS FIRST')
      order.gsub!(/DESC/i, 'ASC')

      order
    }

    relation.reorder(new_order)
  end
end

initializer.rb

require 'fast_pagination'

ActiveSupport.on_load :active_record do
  ::ActiveRecord::Base.extend(FastPagination)
  ::ActiveRecord::Relation.send(:include, FastPagination)
  ::ActiveRecord::Associations::CollectionProxy.send(:include, FastPagination)
end