Optimizing Large Dataset Retrieval in Rails with Kaminari Pagination

The Problem: Memory Bloat from Large ActiveRecord Collections

Loading thousands of database records into a single Ruby array creates a significant memory bottleneck. When a Rails application executes User.all on a table with 100,000 rows, ActiveRecord instantiates 100,000 Ruby objects. This consumes massive amounts of RAM, increases Garbage Collection (GC) pressure, and slows down response times, often leading to request timeouts or server crashes.

The solution is database-level pagination. By using LIMIT and OFFSET clauses in SQL, the application only fetches a small subset of records (e.g., 25 per request), keeping the memory footprint constant regardless of the total table size.

Prerequisites

• A Ruby on Rails application (version 6.0 or higher recommended).
• An ActiveRecord model connected to a relational database (PostgreSQL, MySQL, or SQLite).
• Administrative access to the Gemfile and the ability to restart the Rails server.

Implementing Database-Level Pagination

1. Installation

Add the Kaminari gem to your Gemfile. Kaminari provides the DSL (Domain Specific Language) required to modify ActiveRecord relations with pagination logic.

gem 'kaminari'

Run the following command in your terminal to install the gem:

bundle install

2. Controller Integration

Modify your controller action to use the .page and .per methods. The .page method tells the database which slice of data to return, while .per defines the page size.

# app/controllers/users_controller.rb
class UsersController < ApplicationController
  def index
    # Sanitize the 'per_page' input to prevent DoS attacks via massive page requests
    per_page = [params[:per_page].to_i, 100].min
    per_page = 25 if per_page <= 0

    @users = User.order(created_at: :desc).page(params[:page]).per(per_page)
  end
end

3. View Implementation

To allow users to navigate between pages, use the paginate helper in your view. This generates the HTML links necessary to update the page parameter in the URL.

<!-- app/views/users/index.html.erb -->
<% = render @users %>

<% = paginate @users %>

Comparison: Offset vs. Keyset Pagination

Kaminari uses Offset Pagination by default. It is important to understand when this is appropriate versus when a different strategy is needed.

Feature	Offset Pagination (Kaminari)	Keyset Pagination (Cursor)
SQL Logic	LIMIT 25 OFFSET 1000	WHERE id > 1000 LIMIT 25
Performance	Degrades as OFFSET increases	Constant performance
Data Stability	Items may shift pages if rows are deleted	Stable; items do not shift
Navigation	Jump to specific page (e.g., Page 50)	Next/Previous only

Verification and Diagnostics

To ensure pagination is happening at the database level and not in Ruby memory, check your Rails server logs during a request.

Log Inspection

Look for the LIMIT and OFFSET keywords in the SQL output. A successful implementation will look like this:

SELECT "users".* FROM "users" ORDER BY "users"."created_at" DESC LIMIT 25 OFFSET 0

If you see SELECT "users".* FROM "users" without a LIMIT clause, the application is loading the entire table into memory, and the pagination is failing.

Boundary Testing

• Empty State: Request a page number that exceeds the total count (e.g., ?page=999999). The application should return an empty collection rather than a 500 error.
• Parameter Injection: Attempt to pass a negative number or a string to ?page=. Ensure your controller handles these as 0 or 1 to avoid SQL errors.

Limitations and Risks

• Deep Paging Performance: In tables with millions of rows, OFFSET 100000 forces the database to scan and discard 100,000 rows before returning the result, which can cause slow queries.
• Drifting Results: If a record is deleted from page 1 while a user is navigating to page 2, the first record of page 2 will shift to page 1, causing the user to see a duplicate record.

Rollback Procedure

If pagination causes unexpected UI issues or query performance regressions, revert the changes as follows:

1. Remove .page(params[:page]).per(per_page) from the controller and return to .all or a limited .limit(n) query.
2. Remove the paginate @users helper from the view.
3. Remove gem 'kaminari' from the Gemfile and run bundle install.