Table of contentsClick link to navigate to the desired location
This content has been automatically translated from Ukrainian.
In web applications with large datasets, the issue of pagination arises sooner or later. Displaying thousands of records in a table or list at once is a bad idea for both performance and user experience. The most common approaches in Rails are offset pagination and cursor pagination. Let's explore what they are, their advantages and disadvantages, and look at implementation examples.
Offset pagination
What is it
The offset approach uses SQL operators OFFSET and LIMIT. It tells the database: "Skip N records and take the next M."
Example in Rails
# Get the second page with 20 records users = User.order(:id).offset(20).limit(20)
The SQL that will be executed:
SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT 20 OFFSET 20;
Advantages
- Simple to implement.
- Well integrated with existing gems (e.g., kaminari, will_paginate).
- User-friendly: you can quickly jump to any page (e.g., page #50).
Disadvantages
- Performance issue: the larger the offset, the slower the query works. The database still has to go through N records to skip them.
- Result instability: if records are added or removed from the table during browsing, pages "shift." Users may skip or see the same data twice.
Cursor pagination
What is it
The cursor approach uses a "pointer" (cursor) to the last element of the previous page. Instead of counting from the beginning, we say: "Show the next 20 records after ID = X."
Example in Rails
Let's assume we have users sorted by id.
# Get the first 20 records
users = User.order(:id).limit(20)
# Get the next after the last id
last_id = users.last.id
next_users = User.where("id > ?", last_id).order(:id).limit(20)
The SQL will look like this:
SELECT "users".* FROM "users" WHERE (id > 20) ORDER BY "users"."id" ASC LIMIT 20;
Advantages
- High performance: the database does not go through thousands of records to find the right place; it jumps directly to it.
- Stable results: when adding or removing records, the cursor ensures that you won't see duplicates or skips.
Disadvantages
- No "arbitrary pages." You can only move forward or backward.
- More complex to implement, especially if the cursor must be based on multiple fields (id) rather than simple sorting conditions.
When to use which approach?
Use offset pagination if:
- your tables are relatively small;
- it's important to have the ability to jump to a specific page (e.g., "Page 50");
- users rarely work with very deep lists.
Use cursor pagination if:
- you are working with large volumes of data;
- performance is critically important;
- you are fine with "Back/Next" buttons instead of specific page numbers;
- you need display stability with frequent data updates.
Offset pagination is easier to implement, but it is slow and less reliable for large datasets. Cursor pagination is faster and more stable, but it limits navigation.
The choice depends on the task: if you are building a blog or a catalog with thousands of records - offset is suitable. If you are creating an API for a mobile application or working with constantly changing data (chats, news feeds) — choose cursor.
