Table of contentsClick link to navigate to the desired location
This content has been automatically translated from Ukrainian.
In web applications with large data sets, sooner or later the question of pagination arises. Displaying thousands of entries in a table or list at once is a bad idea for both performance and the user. The most common approaches in Rails are offset pagination and cursor pagination. Next, we will analyze what it is, what are the advantages and disadvantages, as well as see examples of implementation.
Offset pagination
What is it
The Offset approach uses SQL statements OFFSET and LIMIT. He tells the base, "Skip N records and take the next M's".
An example in Rails
# Get a second page with 20 entries users = User.order(:id).offset(20).limit(20)
SQL that will execute:
SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT 20 OFFSET 20;
Advantages
- Easy to implement.
- Integrates well with ready-made gems (for example, kaminari, will_paginate).
- User-friendly: You can quickly go to any page (eg №50 page).
Disadvantages
- Performance problem: the bigger the offset, the slower the query runs. The DB still has to pass N records to skip them.
- Instability of results: If entries are added or deleted in the table when browsing, the pages "move down". The user can 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 entries after ID = X".
An example in Rails
Suppose we have users sorted by id.
# Get the first 20 entries
users = User.order(:id).limit(20)
# Get the next ones after the last id
last_id = users.last.id
next_users = User.where("id > ?", last_id).order(:id).limit(20)
SQL will look like this:
SELECT "users".* FROM "users" WHERE (id > 20) ORDER BY "users"."id" ASC LIMIT 20;
Advantages
- High performance: the base does not go through thousands of records to search, but immediately jumps to the right place.
- Stable results: When adding or removing entries, the cursor ensures that you do not see duplicates or omissions.
Disadvantages
- No "arbitrary pages". You can only move forward or backward.
- More difficult to implement, especially if the cursor should not be based on one field (id), but on complex sorting conditions.
When to use which approach?
Use offset pagination, if:
- your tables are relatively small;
- it is important to be able to go to a specific page (for example, "Page 50");
- users rarely work with very deep lists.
Use cursor pagination, if:
- you work with large amounts of data;
- performance is critical;
- you have enough Back/Next buttons instead of specific page numbers;
- display stability is required with frequent data updates.
Offset-pagination easier to implement, but it is slow and less reliable for large amounts of data. Cursor-pagination faster and more stable, but limits navigation.
The choice depends on the task: if you are building a blog or catalog with thousands of entries - offset is suitable. If you are creating an API for a mobile application or working with constantly changing data (chats, news feeds) —, choose cursor.
This post doesn't have any additions from the author yet.
