Why Offset and Limit in SQL Can Be Problematic

And What to Do Instead

When retrieving data from large SQL tables, it’s common to use OFFSET and LIMIT for pagination, like this:

This query skips the first 20 rows and retrieves the next 10. While this approach is easy to use, it comes with problems, especially when dealing with large datasets.

Problems with OFFSET and LIMIT

1. Slow Performance:

The database has to process and skip the rows specified by OFFSET , even though they aren't returned. As the offset increases, the query takes longer, slowing down your application.

2. Inconsistent Results:

If data changes between queries (e.g., new rows are added), the results might overlap or skip rows, making pagination unreliable.

3. Not Scalable:

As your data grows, the performance of OFFSET queries degrades, making them impractical for large datasets.

A Better Solution: Keyset Pagination

Keyset pagination is a more efficient way to paginate data. Instead of using OFFSET, you use an indexed column like the primary key (id) to get the next set of results.

Here’s how it works:

1. Initial Query: Fetch the first 10 rows ordered by id.
2. Next Page: Use the id of the last row to fetch the next 10 rows, continuing from where the previous query left off.
3. Efficient: This method is much faster because the database doesn’t need to skip over rows like with OFFSET.

Benefits of Keyset Pagination

• Faster: Avoids skipping rows, making it faster, especially with large datasets.
• Consistent: Always fetches the next set of rows, even if data changes.
• Scalable: Handles large datasets better without performance degradation.

Conclusion

While OFFSET and LIMIT are easy to use, they can lead to slow and inconsistent queries as your data grows. Keyset pagination offers a more efficient and reliable way to paginate through large datasets, ensuring your queries remain fast and consistent.

By the way, it's one of the reason I think UUIDv7 it's the best for keyset pagination!

It offers globally unique, time-based IDs perfect for efficient pagination without relying on OFFSET.

UUIDv7 ensures consistent, scalable, and collision-free data retrieval.