Pagination Offset vs Cursor: How to Display Large Data Without Slowing Down Your Website

When a website starts to have thousands or millions of data entries, displaying everything on a single page is no longer a sensible option. Besides increasing load times, this approach also burdens the database and the user's browser.

This is where pagination becomes necessary. Pagination is a technique for dividing data into several parts, such as 20 or 50 items per request. However, choosing the wrong pagination method can create new problems: the next page feels slow, data shifts while the user is reading, or API results become inconsistent.

The two most commonly used approaches are offset pagination and cursor pagination. Both can be correct, but they are suitable for different situations.

Offset Pagination: Simple and Easy to Understand

Offset pagination works on the concept of “skip a number of data entries, then take the next few items.” For example, the first page retrieves data starting from position 0, the second page skips 20 entries, and so on.

An example of a MySQL query would look like this:

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

This query retrieves 20 articles after skipping the first 40 articles. In applications, the OFFSET value is usually calculated from the page number:

$offset = ($page - 1) * $perPage;

The main advantage is that it is easy to implement. A URL like /articles?page=3 is user-friendly, easy to test, and suitable for pages that require direct navigation to a specific page.

Offset is also convenient for admin tables. Users typically want to jump to page 10, see the total number of pages, or sort data by a specific column. For such needs, the offset approach is often sufficient.

Offset Issues as Data Grows

Offset may seem simple, but the database still has to find and skip rows before retrieving the requested results. At small offsets, the difference may not be noticeable. However, on very distant pages, this process can become heavier.

For example, a request to retrieve data with OFFSET 500000 means the database needs to scan many rows before providing the last 20 results. Indexing can help, but it does not always eliminate that cost.

Another issue arises when data changes between two requests. Imagine a user is viewing the first page of a comment list. Before they open the second page, a new comment comes in at the top. Because the data position shifts, some comments may appear twice or be missed entirely.

This is not a bug that is always easy to spot. With infrequently changing data, everything may seem fine. However, in news feeds, user activity, logs, or continuously increasing transactions, the effect can be disruptive.

Cursor Pagination: Continuing from the Last Item

Cursor pagination does not rely on page numbers. The system stores a marker from the last item received and then uses that marker to find the next data.

A simple example is an application retrieving the latest articles based on created_at and id. After receiving 20 articles, the application records the last article as the cursor. The next request asks for older data from that point.

SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-09-19 10:30:00', 8421)
ORDER BY created_at DESC, id DESC
LIMIT 20;

In this example, the pair of created_at and id is used as the position determinant. Using two columns is important because the creation time can be the same for several rows. The id serves as a differentiator to maintain clarity in order.

Cursors are usually sent in the URL or API response, for example:

{
  "data": [...],
  "next_cursor": "eyJpZCI6ODQyMX0="
}

The cursor is often created in the form of an encoded token. The purpose is not to completely hide data, but to avoid displaying internal query details directly to the user.

When is Cursor More Suitable?

Cursor pagination is suitable for large and continuously changing data. Examples include timelines, notification lists, activity histories, messages, large product catalogs, and API endpoints consumed by mobile applications.

Since queries start from a specific position and utilize indexing, cursors are usually more stable for sequential data retrieval. The system does not need to skip hundreds of thousands of rows just to get to a distant page.

However, cursors have limitations. Users cannot easily jump directly to page 50. The concept is closer to a “Load more” button or infinite scroll than classic page navigation.

Ensure Data Order is Truly Stable

Both offset and cursor require a consistent data order. Avoid performing pagination without a clear ORDER BY. Without order, the database is not obligated to return rows in the same sequence on each request.

If using a single column with potentially duplicate values, add a differentiating column. For example:

ORDER BY created_at DESC, id DESC

This combined order makes the position of each item more deterministic. Also, ensure that the columns used for filtering and ordering have appropriate indexes. For the cursor query above, a composite index on created_at and id can help the database find data more efficiently.

Don't Forget Total Data and Count Calculation

In offset pagination, pages usually require information such as total data and the number of pages. This information is often obtained through a COUNT(*) query. For large tables, continuously counting totals can become an additional burden.

Cursor pagination typically does not need to know the total number of pages. The response simply needs to indicate whether there is more data available through next_cursor or a value like has_more.

This has implications for interface design. If the product requires a statement like “Displaying page 3 of 120,” offset is more natural. If the primary goal is to read the latest data sequentially, cursor is often simpler and more efficient.

What Does This Mean for Us?

Pagination should be chosen based on how users interact with data, not just based on developer habits.

• Use offset for admin tables, reports, or lists that require page numbers.
• Use cursor for feeds, logs, notifications, and large data that continuously changes.
• Use a stable order with a combination of primary columns and differentiating columns.
• Ensure that filter and sorting columns are supported by appropriate indexes.
• Limit the limit value from the server side to prevent clients from requesting thousands of data at once.
• Test pagination with truly large data, not just 50 rows in a local environment.

What You Can Do Now

Start by checking the endpoint or page that displays a list of data. Note the executed queries, table sizes, response times, and behavior when new data comes in during the reading process.

If the list requires page navigation and total data count, offset is likely a practical choice. If users only need to load the next data sequentially, consider using cursor. Whatever the choice, measure with realistic data and pay attention to user experience, not just query time in one condition.

Good pagination is invisible to users. The list feels smooth, results do not suddenly jump, and the database does not work harder than necessary.

– Rio Yotto @rioyotto