Solutions to paginate data in your databse and which one is the best

We have several ways of paginating your data, but which is best for your case?

Standard LIMIT and OFFSET

Limit/offset pagination is a simple and straightforward technique used in many web applications. It is easy to implement and works well for small to medium sized datasets. However, as the size of the dataset increases, the performance of limit/offset pagination can start to degrade. This is because the database needs to scan through all the rows in the result set before returning the limited set of rows specified by the offset and limit. This can become prohibitively slow as the offset and limit increase and the number of rows to be scanned increases.

SELECT *
FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

Function ROW_NUMBER()

This function assigns a unique sequential number to each row in a found set. By using this function and filtering on the row number, you can achieve pagination.

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users
) AS subquery
WHERE row_num BETWEEN 1 AND 10;

FETCH and OFFSET clause

This is a newer feature in SQL that allows you to specify both the number of rows to be returned and the starting row. To use FETCH and OFFSET for pagination, you typically combine them with an ORDER BY clause to define the desired ordering of the rows. Here's an example:

SELECT *
FROM users
ORDER BY id
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;

The OFFSET clause is set to  0 ROWS, indicating that we want to start retrieving rows from the beginning of the result set. The FETCH FIRST clause is used to specify that we want to retrieve the first 10 rows of the result set. You can adjust the value according to the number of rows you want for each page of your pagination.
By using these clauses together, you can control the pagination of your query results. For subsequent pages, you would increase the value in the OFFSET clause to skip the appropriate number of rows. For example, to retrieve the next page of results, you would set OFFSET 10 ROWS to skip the first 10 rows and retrieve the next set of rows.

Keyset Pagination

This approach relies on using a unique key or combination of keys to paginate data. It involves keeping track of the last key in the current result set and using it to retrieve the next record.

SELECT *
FROM users
WHERE id > :last_key_in_previous_result_set
ORDER BY id ASC
LIMIT 10;

This is therefore a more complex technique, requiring additional logic to implement, but is generally more efficient for large datasets. Keyset pagination uses the values of one or more columns in the table to determine the start and end points for each page of results. This allows the database to efficiently retrieve only the rows needed for each page, without having to scan the entire table. This can result in significant performance improvements, especially for large datasets.

Summary

The most efficient pagination method depends on the specific database and table structure and the types of queries used. However, in general, keyset pagination tends to be the most efficient for large datasets (performance depends on the database index configuration and the number of columns we use in the ORDER BY clause). In most cases, the primary key id is covered by a database index, so it should be efficient. Please remember to test not only the first page, but also the second page to make sure it's faster in your project.

However, the other pagination methods can still be effective for certain use cases and smaller datasets. Ultimately, it's important to test and benchmark different pagination methods in your specific environment to determine which provides the best performance.