API with NestJS #107. Offset and keyset pagination with Prisma

NestJS SQL

This entry is part 107 of 158 in the API with NestJS

The bigger our database, the more we need to care about the performance. Returning too much data at once through our API might not be the best approach when it comes to performance. A common solution is to divide our data into chunks and present it to the user as infinite scrolling or multiple pages. In this article, we implement it with PostgreSQL and Prisma. We also compare various ways of paginating and how it can affect performance.

You can find the code from this article in this repository.

Offset and Limit

First, let’s take a look at a fundamental query.

The most important thing is that it returns all of the records from our table. Also, we need to acknowledge that the order of the rows in the result is not guaranteed. When implementing pagination, we need the order to be predictable. Because of that, we have to sort the results.

To divide our data into chunks, we need to limit the number of rows in the result. To do that, we should use the keyword.

By doing the above, we limit the result to ten items and get the first page of the results. To get the next chunk of the data, we need to skip a certain number of rows. To change the starting point of our query, we need the keyword.

With the above approach, we omit the first ten posts and get rows with ids from 11 to 20.

Using offset and limit with Prisma

To implement pagination in our API, we need the users to be able to send the offset and limit through query parameters. To allow that, let’s create a class that transforms and validates the data.

paginationParams.dto.ts

We can now use the class with the decorator to handle the query parameters in our controller.

posts.controller.ts

The last step is to use the and parameters to apply the offset and limit to our query.

posts.service.ts

Counting the number of rows

It’s very common to display the number of available pages. For example, a hundred rows with ten elements per page gives us ten pages of data.

To achieve the above with SQL, we need the keyword.

Unfortunately, Prisma does not support counting the rows and fetching them in the same query. To deal with this problem, we can use a transaction.

posts.service.ts

If you want to know more about using transactions with Prisma, check out API with NestJS #104. Writing transactions with Prisma

Thanks to the above approach, we can display the number of available pages in our interface.

Disadvantages

The offset and limit approach is very common. But, unfortunately, it has some severe disadvantages.

The most crucial downside is that the database needs to compute all of the rows skipped with the operator:

  1. the database sorts all of the rows in the table as specified with the keyword,
  2. PostgreSQL removes the number of rows specified in the .

Unfortunately, the above can take a toll on the performance. Aside from that, we can run into a problem with the consistency of our data:

  1. the first user gets the first page of data,
  2. the second user creates a new entry that ends up on the first page,
  3. the first user fetches the second page of data.

Unfortunately, the above causes the first user to see the last element of the first page again on the second page. Also, the user won’t see the new element added to the first page.

Advantages

The offset and limit approach is very common and easy to implement. Also, it’s very straightforward to change the column we use for sorting. It’s also easy to skip a certain number of elements and go from the first page to the fifth page, for example.

All of the above makes the offset an acceptable solution in some cases if the expected data is not too big and the possible inconsistencies are acceptable.

Keyset pagination

Another approach to pagination involves using the keyword instead of the . Let’s take another look at a simple query first.

The thing we need to notice in the above results is that the last element has an id of . Let’s use this knowledge to request the next ten elements.

To get the third page of results, we should notice that the last element above has the id of . We need to use it to modify our filter.

The above approach is referred to as the keyset pagination or the cursor pagination.

Implementing keyset pagination with Prisma

To add the keyset pagination to our NestJS application, we need to accept an additional query parameter. To do that, let’s modify our class.

paginationParams.dto.ts

We can use the parameter to implement the keyset pagination with Prisma.

posts.service.ts

When using the parameter built into Prisma, we need to acknowledge that it uses the instead of . Therefore, if we provide a particular id, we will see it in the results.

Disadvantages

The most crucial drawback of the keyset pagination is that we need to know the id we want to start with. However, we can overcome it by mixing the cursor-based approach with the offset pagination.

Another important thing is that the column we use with the keyset pagination should have an index to have an additional performance boost. Fortunately, the official documentation states that PostgreSQL creates indexes for each primary key out of the box. Thanks to that, the cursor-based approach should be fast when used with ids.

If you want to know more about indexes with Prisma, check out API with NestJS #106. Improving performance through indexes with Prisma

Also, ordering the pagination results by text columns might not be straightforward if we aim for natural sorting. If you want to know more, check out this thread on StackOverflow.

Advantages

The keyset pagination offers a significant performance over the offset approach. It also fixes the data inconsistency issue that can occur with offset-based pagination. Thanks to that, the keyset pagination can be a good solution for many applications.

Summary

In this article, we’ve implemented two different approaches to pagination. We did that both through raw SQL and Prisma. Both approaches have pros and cons and can be helpful in various situations. The keyset approach is more restrictive but offers a serious performance boost. Since it’s difficult to skip a few pages of data at once when using the keyset pagination, we can resolve to the offset pagination when necessary. Thanks to that, we can cover different use cases while still providing a good user experience.

Series Navigation<< API with NestJS #106. Improving performance through indexes with PrismaAPI with NestJS #108. Date and time with Prisma and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments