Pagination at Scale in Supabase: Offset vs Cursor-Based Pagination

As a Supabase application grows, fetching thousands or millions of database rows in a single request becomes inefficient. Supabase pagination allows large datasets to be divided into smaller result sets, but the pagination strategy you choose can have a significant impact on database performance and consistency.

Offset-based pagination is simple and works well when users need traditional numbered pages or need to jump directly to a specific page. However, large offsets can become increasingly expensive because PostgreSQL still has to process the rows that are skipped. Cursor-based pagination takes a different approach by telling PostgreSQL where to continue from the previous result, making it better suited for deep pagination, infinite scrolling, and large or frequently changing datasets.

This guide explains how both approaches work in Supabase, their trade-offs, implementation patterns, indexing requirements, and how to choose the right strategy for your application.

Quick Answer: Which Pagination Strategy Should You Use?

Use offset pagination when your dataset is relatively small, users need numbered pages, or users need to jump directly to a specific page.

Use cursor-based pagination, also called keyset pagination, when working with large datasets, deep pagination, infinite scrolling, transaction histories, logs, or frequently changing data. With an appropriate index, cursor pagination avoids processing an increasingly large number of skipped rows.

What Is Pagination?

Pagination is the process of dividing a large database result set into smaller chunks instead of returning every row at once.

For example, an application with 1,000,000 orders should not return all 1,000,000 records in one API response. Instead, it might return 50 records at a time:

Page 1 → 50 orders
Page 2 → 50 orders
Page 3 → 50 orders
...

This reduces:

• Database work per request
• Network traffic
• API response size
• Application memory usage
• Frontend rendering work

Pagination is therefore not just a frontend feature. It directly affects database query performance and API scalability. In Supabase, pagination can be implemented using the client libraries' range-based queries, while PostgreSQL provides the underlying query mechanisms such as LIMIT and OFFSET. Supabase's range() uses zero-based, inclusive start and end positions.

Why Does Pagination Become a Problem at Scale?

A query such as the following is usually straightforward when the dataset is relatively small:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50
OFFSET 0;

But the problem becomes more noticeable when the offset becomes very large:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50
OFFSET 900000;

OFFSET does not mean that PostgreSQL can simply jump to row 900,001. PostgreSQL still has to compute the rows that come before the requested portion and then discard the skipped rows. The PostgreSQL documentation explicitly notes that rows skipped by OFFSET still have to be computed, which means large offsets can become inefficient.

There is also a second problem: the underlying data can change between pagination requests.

If a new record is inserted between two requests, the position of existing records can change. This can cause users to see duplicate records, miss records, or see records move between pages. This behavior is commonly referred to as pagination drift.

Offset-Based Pagination in Supabase

Offset pagination uses two primary concepts:

• LIMIT — defines how many records the database should return.
• OFFSET — defines how many records should be skipped before returning results.

In practice, the application calculates the offset based on the requested page number and page size. PostgreSQL then processes the query in the specified order, skips the requested number of rows, and returns the next set of records. This makes offset pagination simple to understand and implement, especially for traditional page-based interfaces.

For example, the following query skips the first 100 records and returns the next 50:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50
OFFSET 100;

This means:

• Skip 100 rows
• Return the next 50 rows

If each page contains 50 records:

Page 1 → OFFSET 0
Page 2 → OFFSET 50
Page 3 → OFFSET 100

PostgreSQL supports this pagination approach directly through LIMIT and OFFSET. It is straightforward for smaller datasets, but as the offset grows, the database may need to process more rows before reaching the requested page.

Implement Offset Pagination in Supabase

Supabase provides the range() method for retrieving a specific range of rows from a query result. It works well for offset-based pagination because you can calculate the starting and ending positions based on the current page and page size.

For example, if each page contains 50 orders, the first page can be retrieved using:

const { data, error } = await supabase
  .from('orders')
  .select('*')
  .order('created_at', { ascending: false })
  .range(0, 49);

Here, .range(0, 49) retrieves the first 50 rows. The range starts at 0 because Supabase uses zero-based row positions, and the end value 49 is included in the result.

To retrieve the second page, the range moves to the next 50 rows:

const { data, error } = await supabase
  .from('orders')
  .select('*')
  .order('created_at', { ascending: false })
  .range(50, 99);

The ranges are zero-based and inclusive, so:

range(0, 49)      → 50 rows
range(50, 99)     → 50 rows
range(100, 149)   → 50 rows

For a dynamic implementation, the range can be calculated from the requested page number and page size:

const pageSize = 50;
const pageNumber = 3;
const start = (pageNumber - 1) * pageSize;
const end = start + pageSize - 1;
const { data, error } = await supabase
  .from('orders')
  .select('*')
  .order('created_at', { ascending: false })
  .range(start, end);

This approach is simple to implement and maps naturally to traditional page-number interfaces. However, as the requested page number increases, the underlying offset also increases, which can make deep pagination less efficient on large datasets.

Pagination Drift With Offset Pagination

Performance is not the only concern with offset pagination. Another important issue is pagination drift, which can occur when the underlying data changes between requests.

Imagine the first page contains:

Order 100
Order 99
Order 98
Order 97
Order 96

The client then requests the second page. Before that request is made, a new order is inserted:

Order 101
Order 100
Order 99
Order 98
Order 97
Order 96

Because the results are ordered by the newest records first, the newly inserted Order 101 moves the existing records down by one position. The original position of every existing record has therefore changed. If the client requests page 2 using the same offset calculated from the original dataset, PostgreSQL applies that offset to the new ordering, not the ordering the client saw on page 1.

As a result, the client may encounter:

• Duplicate records - a record from the previous page may appear again.
• Missing records - a record may be skipped entirely.
• Records moving between pages - the same record may appear on a different page than expected.

This problem becomes more noticeable in feeds, transaction histories, logs, or other datasets where new records are continuously being inserted or existing records are changing. For frequently changing datasets, cursor-based pagination is often a better option because the next request is based on the position of the last record returned rather than on a numeric offset.

Cursor-Based Pagination in Supabase

Cursor-based pagination takes a different approach from offset pagination. Instead of telling the database how many rows to skip, the client tells the database where to continue from the previous result.

For example, suppose the last record returned to the client has an ID of 12345. The client can send this value as the cursor for the next request. Instead of skipping the first 100,000 records, the client tells the database to continue after the record with ID 12345. The next query can be:

SELECT *
FROM orders
WHERE id < 12345
ORDER BY id DESC
LIMIT 50;

Here, the WHERE condition identifies the position from which PostgreSQL should continue. Because the query uses an appropriate index on the cursor column, PostgreSQL can locate the relevant portion of the index and retrieve the next set of rows without relying on a large OFFSET.

This makes cursor pagination particularly useful for deep pagination, where offset-based queries can become increasingly expensive as the offset grows. Cursor-based pagination is also commonly referred to as keyset pagination because the next set of records is selected using the values of the ordered key rather than a row number or offset.

How Cursor Pagination Works

Suppose the first request returns:

100
99
98
97
96

The client stores the last record as the cursor:

cursor = 96

For the next request, the cursor is used as a filter:

SELECT *
FROM orders
WHERE id < 96
ORDER BY id DESC
LIMIT 5;

This tells PostgreSQL to return the next 5 records that come after the last record from the previous result.

The result is:

95
94
93
92
91

The client then updates the cursor:

cursor = 91

The following request uses:

WHERE id < 91

The same process continues until the query returns no more records. The key difference is that each request uses the last record's position as the starting point instead of asking the database to skip an increasing number of rows.

Implement Cursor Pagination in Supabase

Supabase does not require a separate API specifically for cursor pagination. Instead, you can implement keyset pagination using the existing filtering, ordering, and limit() methods provided by the Supabase client.

Suppose lastId contains the ID of the last record returned by the previous request. The next request can use that value to retrieve the following 50 records:

const { data, error } = await supabase
  .from('orders')
  .select('id, created_at, total')
  .lt('id', lastId)
  .order('id', { ascending: false })
  .limit(50);

Here, each part of the query has a specific purpose:

• .lt('id', lastId) — returns records with an ID lower than the last record from the previous page.
• .order('id', { ascending: false }) — keeps the records in descending order.
• .limit(50) — restricts the response to the next 50 records.

The important difference from offset pagination is that the cursor is used as a database filter. PostgreSQL can use this value to determine where to continue retrieving records instead of counting and skipping all preceding rows. This makes the approach more suitable for deep pagination over large datasets

Why Ordering Matters for Pagination

Pagination should always use a deterministic ordering so that the database returns records in a predictable sequence.

Consider the following query:

SELECT *
FROM orders
LIMIT 50;

Because there is no ORDER BY clause, the query does not define which 50 rows should be returned or in what order. This can make pagination unreliable, especially when multiple requests are made for different pages.

A better approach is to explicitly define the ordering:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50;

This ensures that the newest orders are returned first. However, there is another subtle issue: what happens when multiple records have the same created_at value?

For example:

id    created_at
--------------------------
100   2026-08-29 10:00:00
99    2026-08-29 10:00:00
98    2026-08-29 09:59:59

If the query only uses:

ORDER BY created_at DESC

the two records created at 10:00:00 have the same sorting value. Their relative order is therefore not uniquely defined.

To make the ordering deterministic, add a unique column such as id as a tie-breaker:

ORDER BY created_at DESC, id DESC

Now the records are ordered first by created_at and, when timestamps are identical, by id. This gives every record a deterministic position, which is especially important for reliable cursor-based pagination.

Composite Cursor Pagination

Composite cursor pagination becomes important when the application sorts records using more than one column. If the ordering is based on both created_at and id, the cursor must contain both values so that the database can identify the exact position of the last returned record.

Suppose the ordering is:

ORDER BY created_at DESC, id DESC

The cursor therefore needs to store both created_at and id values.

For example, suppose the last record returned is:

created_at = '2026-08-29 10:00:00'
id = 100

The next query needs to retrieve records that come after this exact position in the ordering:

SELECT *
FROM orders
WHERE
    created_at < '2026-08-29 10:00:00'
    OR (
        created_at = '2026-08-29 10:00:00'
        AND id < 100
    )
ORDER BY created_at DESC, id DESC
LIMIT 50;

The condition handles both cases: records with an earlier created_at come after the cursor, while records with the same created_at use id to determine their order. This prevents records with identical timestamps from being accidentally skipped and gives the cursor a precise position in the result set.

The same logic can be implemented in Supabase using the client:

const { data, error } = await supabase
  .from('posts')
  .select('*')
  .or(
    `created_at.lt.${lastSeenTimestamp},` +
    `and(created_at.eq.${lastSeenTimestamp},id.lt.${lastSeenId})`
  )
  .order('created_at', { ascending: false })
  .order('id', { ascending: false })
  .limit(20);

The key principle is that the cursor should contain the values required to uniquely identify the last position in the sort order. When multiple columns determine the ordering, those columns should be represented in the cursor as well.

Indexing for Cursor Pagination

Cursor pagination is not automatically fast just because a cursor is being used. The database still needs a suitable index that matches the way the query filters and sorts the data.

For example, consider this query:

SELECT id, created_at, total
FROM orders
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 50;

Since the query filters and sorts using created_at, an index on that column can help PostgreSQL efficiently locate and return the required rows:

CREATE INDEX idx_orders_created_at
ON orders (created_at DESC);

When the query uses composite ordering:

ORDER BY created_at DESC, id DESC

the index can include both columns:

CREATE INDEX idx_orders_created_at_id
ON orders (created_at DESC, id DESC);

PostgreSQL can use B-tree indexes to provide ordered output. An index that matches the ORDER BY can be particularly useful with LIMIT, because PostgreSQL can retrieve the required rows directly from the index instead of sorting a larger result set.

Index the Filter and Sort Pattern

A common mistake is assuming that having an index on the primary key automatically makes every cursor query efficient. The index should instead match the actual filtering and sorting pattern of the query.

For example:

SELECT *
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;

The query pattern contains:

Filter → user_id
Sort   → created_at, id
Limit  → 50

A suitable composite index may therefore be:

CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at DESC, id DESC);

This allows the index to support both the user_id filter and the ordering used to retrieve the next set of records.

The exact index should be based on the application's real query patterns and verified using EXPLAIN. PostgreSQL's EXPLAIN command can be used to inspect how the database plans to execute a query, helping you determine whether the expected indexes are being used effectively.

Offset vs Cursor-Based Pagination

When Should You Use Offset vs Cursor Pagination?

The right pagination strategy depends on the size of your dataset, how users navigate through the results, and how frequently the underlying data changes.

Use Offset Pagination When

• The dataset is relatively small: Offset pagination works well when users rarely navigate beyond the first few pages.
• You use numbered pages: It is a natural fit for traditional interfaces such as 1 2 3 4 ... 20.
• Users need to jump to a specific page: Offset pagination makes it straightforward to navigate directly to a page such as page 37.
• The data changes infrequently: It is less likely to cause pagination drift when the underlying dataset remains relatively stable.
• Simplicity is the priority: range() makes offset pagination straightforward to implement in Supabase.

Use Cursor Pagination When

• The dataset is large: Cursor pagination is better suited to deep traversal through large datasets.
• You use infinite scrolling or Load More: It works naturally for feeds, notifications, chat history, and similar interfaces.
• You are displaying transaction histories or logs: These datasets often require users to move through many records without jumping to a specific page.
• The data changes frequently: Using the last record as the reference point can reduce pagination drift when new records are inserted.
• You need efficient deep pagination: Cursor pagination avoids relying on increasingly large numeric offsets.

The choice is not simply about which method is faster. Consider how users navigate the data, how frequently records change, and whether the query has appropriate deterministic ordering and indexes.

Common Problems With Supabase Pagination

1. Large OFFSET Values Make Queries Slow

Problem: Large offsets can require PostgreSQL to process many skipped rows.

Solution: For deep pagination, consider cursor-based pagination with an appropriate index.

2. Pagination Returns Duplicate or Missing Records

Problem: Changes to the underlying data between pagination requests can shift records between pages.

Solution: Use deterministic ordering and consider cursor pagination for frequently changing datasets.

3. Cursor Pagination Skips Records With Duplicate Timestamps

Problem: Using only created_at as the cursor can be problematic when multiple records have the same timestamp.

WHERE created_at < $cursor

Solution: Use a compound cursor containing created_at and a unique tie-breaker such as id.

ORDER BY created_at DESC, id DESC

4. Cursor Pagination Is Still Slow

Problem: Cursor filtering without an appropriate index may still result in inefficient queries.

Solution: Create an index that matches the query's filtering and sorting pattern, and verify the query plan using EXPLAIN.

5. Pagination Has No Deterministic Ordering

Problem: Using LIMIT without a meaningful ORDER BY does not guarantee a predictable result order.

Solution: Always define a stable ordering and use a unique tie-breaker when necessary.

Best Practices for Supabase Pagination

• Always use deterministic ordering. Do not rely on the natural order of database rows.
• Keep page sizes reasonable. Returning 50 or 100 records per request is often more practical than returning thousands.
• Avoid unnecessarily large offsets. Offset pagination is convenient, but deep offsets can become expensive.
• Use cursor pagination for deep traversal. It is generally better suited to large datasets and infinite-scroll interfaces.
• Use a unique tie-breaker for cursor ordering. For example:

ORDER BY created_at DESC, id DESC

• Create indexes that match the query pattern. Consider both filtering and sorting columns rather than indexing only the primary key.
• Verify performance with EXPLAIN. Do not assume an index is being used just because it exists.
• Keep cursors opaque in public APIs when appropriate. Instead of exposing raw database values, the API can encode cursor information into an opaque token.

Offset vs Cursor: A Real Example

Consider an application with 50 million orders, where the UI displays 50 orders per request. As users navigate deeper into the dataset, the difference between offset and cursor pagination becomes more noticeable.

Offset Approach

Suppose a user requests a page that requires skipping 10 million rows:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50
OFFSET 10000000;

PostgreSQL still needs to process the rows before the requested position and then discard them before returning the next 50 records. As the offset becomes larger, the amount of work required can increase significantly.

Cursor Approach

With cursor pagination, the client uses the last record from the previous page as the cursor.

Suppose the previous page ended at: created_at = '2026-01-01 12:00:00' and id = 123456

The next query can use those values to continue from that exact position:

SELECT *
FROM orders
WHERE
    created_at < '2026-01-01 12:00:00'
    OR (
        created_at = '2026-01-01 12:00:00'
        AND id < 123456
    )
ORDER BY created_at DESC, id DESC
LIMIT 50;

With an appropriate index on the filtering and sorting columns, PostgreSQL can locate the relevant portion of the ordered data and retrieve the next 50 records without processing a number of skipped rows proportional to a ten-million-row offset. This is one reason matching indexes are particularly useful for ORDER BY ... LIMIT queries.

Frequently Asked Questions

Is Supabase pagination the same as PostgreSQL pagination?

Supabase uses PostgreSQL as its database, so the underlying pagination behavior is based on PostgreSQL queries. Supabase client libraries provide convenient methods such as range(), while PostgreSQL provides mechanisms such as LIMIT and OFFSET.

Is cursor pagination faster than offset pagination?

Yes, cursor pagination is generally better for deep pagination because it avoids processing an increasingly large number of skipped rows. With a suitable index, PostgreSQL can use the cursor condition to locate the relevant portion of the ordered data.

Is offset pagination bad for large datasets?

Not necessarily, but large offsets can become inefficient. PostgreSQL documents that rows skipped by OFFSET still have to be computed, so deep pagination can become expensive.

Can Supabase support cursor pagination?

Yes. Supabase does not require a special cursor-pagination API. Cursor pagination can be implemented using filters such as .lt() or .gt(), together with order() and limit().

Do cursor-based queries require indexes?

They should generally use indexes that match the query's filtering and ordering pattern. For example:

CREATE INDEX idx_orders_created_id
ON orders (created_at DESC, id DESC);

The appropriate index should be verified against the actual query and execution plan.

Can cursor pagination use timestamps?

Yes, but timestamps alone may not uniquely identify a row. If multiple records can have the same timestamp, combine the timestamp with a unique column such as id to create a deterministic composite cursor.

Conclusion

Choosing between offset and cursor-based pagination in Supabase depends primarily on how users navigate your data and how large or dynamic the dataset is.

Offset pagination remains a good choice for smaller datasets, traditional numbered pages, and applications where users need to jump directly to a specific page. It is simple to implement through Supabase's range() method.

Cursor-based pagination is generally a better fit for large datasets, deep traversal, infinite scrolling, transaction histories, logs, and continuously changing data. By using a stable cursor and an index that matches the filtering and sorting pattern, applications can avoid the growing cost associated with large offsets.

The key takeaway is not to choose cursor pagination simply because it sounds faster. Choose the pagination strategy based on the application's access pattern, use deterministic ordering, create appropriate indexes, and verify real production queries with PostgreSQL's query plans.