Choosing Between Offset and Cursor Pagination in Rails

The Pagination Performance Wall

When a Rails application scales from hundreds to millions of records, the standard approach to pagination often becomes a primary source of database latency. The core problem is that as a user navigates deeper into a dataset, the database must perform more work to find the starting point of the requested page.

The goal is to select a pagination strategy that balances user experience (the ability to jump to a specific page) against system stability (consistent query execution time regardless of dataset depth).

Comparing Pagination Strategies

Feature	Offset-based (LIMIT/OFFSET)	Cursor-based (Keyset)
UI Capability	Page numbers, jump-to-page	Next/Previous, Infinite Scroll
DB Performance	Degrades as offset increases	Constant time (O(1) lookup)
Data Consistency	Prone to drift (skipped/duplicate items)	Stable during insertions/deletions
Implementation	Trivial (Gems like Kaminari)	Requires unique, ordered columns

Trade-offs and Decision Logic

Offset-based pagination uses the SQL OFFSET clause. The database must scan through all preceding rows and discard them before returning the requested subset. For example, OFFSET 10000 LIMIT 20 requires the database to read 10,020 rows just to show 20. This leads to linear performance degradation as the page number increases.

Cursor-based pagination (also known as Keyset pagination) avoids the scan by using a pointer—usually the ID or a timestamp of the last item on the previous page. Instead of telling the database how many rows to skip, you tell it exactly where to start: WHERE id > 10000 LIMIT 20. This leverages the primary key index for a direct lookup.

Choose Offset when:

• The dataset is small (under 50,000 records).
• The business requirement mandates specific page numbers (e.g., "Go to page 45").

Choose Cursor when:

• The dataset is large or growing rapidly.
• You are implementing an API for a mobile app or an infinite-scroll feed.
• Data is frequently inserted, which would cause items to shift between pages in an offset model.

Implementation Example: Cursor-based Pagination

To implement cursor-based pagination in ActiveRecord, you must ensure you are ordering by a unique, sequential column. Using a non-unique column (like created_at without an ID fallback) can cause records to be skipped if multiple entries share the same timestamp.

Run this logic within your controller or a dedicated query object. This example assumes a Post model with a primary key id.

# Example: Fetching the next page of posts
# Params: { page_size: 20, after_id: 1234 }

def index
  page_size = params[:page_size] || 20
  after_id = params[:after_id]

  # Start with a base scope
  @posts = Post.order(id: :asc).limit(page_size)

  # Apply the cursor if provided
  @posts = @posts.where('id > ?', after_id) if after_id.present?

  # Determine the cursor for the next request
  @next_cursor = @posts.last&.id
end

Required Permissions: The database user must have SELECT permissions on the table and the associated index.

Risk: If you change the ORDER BY clause to a non-indexed column, the performance benefit of the cursor is lost, as the database will perform a full table scan to sort the data before applying the WHERE filter.

Verifying Performance Gains

You can verify the performance difference using the EXPLAIN ANALYZE command in your database console (PostgreSQL example). Compare these two queries on a table with 100k+ rows:

-- Offset approach (Slow on deep pages)
EXPLAIN ANALYZE SELECT * FROM posts ORDER BY id ASC LIMIT 20 OFFSET 50000;

-- Cursor approach (Fast on deep pages)
EXPLAIN ANALYZE SELECT * FROM posts WHERE id > 50000 ORDER BY id ASC LIMIT 20;

In the EXPLAIN output, look for Seq Scan (Sequential Scan) versus Index Scan. The offset query will typically show a significantly higher number of rows scanned than the cursor query.

Limitations

Cursor pagination cannot natively support "Jump to Page X" because the application does not know the ID of the first record on page X without scanning all previous records. If your UI requires arbitrary page jumping, you must either accept the performance hit of offsets or implement a hybrid approach where deep pages are discouraged.