{"id":1993,"date":"2025-02-01T00:29:30","date_gmt":"2025-01-31T20:59:30","guid":{"rendered":"https:\/\/mshaeri.com\/blog\/?p=1993"},"modified":"2026-05-19T02:20:28","modified_gmt":"2026-05-19T12:20:28","slug":"improve-pagination-performance-in-sql-queries-using-keyset-pagination","status":"publish","type":"post","link":"https:\/\/mshaeri.com\/blog\/improve-pagination-performance-in-sql-queries-using-keyset-pagination\/","title":{"rendered":"Improve Pagination Performance in SQL Queries Using Keyset Pagination"},"content":{"rendered":"\n

Pagination is a common requirement in web applications dealing with datasets. Developers often use SQL queries with OFFSET<\/code> and LIMIT<\/code> to fetch paginated records. While this approach is straightforward and works well for many cases, it can become inefficient when dealing with very large datasets, leading to performance bottlenecks.<\/p>\n\n\n\n

Consider the following query:<\/p>\n\n\n\n

SELECT * FROM my_table\nORDER BY created_at DESC\nLIMIT 10 OFFSET 100000;\n<\/code><\/pre>\n\n\n\n
The database engine must scan and discard<\/strong> 100,000 rows before returning the next 10 results. As the offset increases, the query slows down because the database has to traverse more records before fetching the relevant data.<\/p>\n\n\n\n
Keyset Pagination<\/strong><\/h2>\n\n\n\n
To improve pagination performance, we can use keyset pagination<\/strong> (also called the seek method). Instead of using OFFSET<\/code>, we get the last retrieved record\u2019s information from client and fetch records after it in a query. This method is significantly more efficient, as it avoids scanning unnecessary rows. The performance difference in large tables can be as significant as 100 times faster<\/strong> with keyset pagination compared to offset-based pagination.<\/p>\n\n\n\n
Let\u2019s assume our table has an indexed id<\/code> column. Instead of using OFFSET<\/code>, we fetch records after the last retrieved id<\/code> that we got from client:<\/p>\n\n\n\n
SELECT * FROM product\nWHERE id > ?\nORDER BY id ASC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
But, limitation of the above query is that it only works when sorting by a unique column<\/strong> like id<\/code>. However, if we need to order by a non-unique<\/strong> column like expire_date<\/code>, keyset pagination alone may not work correctly. Multiple rows could have the same expire_date<\/code>, leading to incorrect pagination. To fix this, we use a composite key<\/strong> (multiple columns) for pagination, ensuring a unique ordering sequence:<\/p>\n\n\n\n
SELECT * FROM my_table\nWHERE (expire_date < ? OR (expire_date = ? AND id < ?))\nORDER BY expire_date DESC, id DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
This approach prevents duplicate records from appearing in multiple pages, as each query fetches results based on a specific starting point. <\/p>\n\n\n\n
Indexing for Best Performance<\/strong><\/h3>\n\n\n\n
To maximize query speed, create a composite index<\/strong>:<\/p>\n\n\n\n
CREATE INDEX idx_product_expire_date_and_id\nON product (expire_date DESC, id DESC);<\/code><\/pre>\n\n\n\n
Keyset Pagination in Spring Boot<\/strong><\/h2>\n\n\n\n
In a Spring Boot application using JPA and Hibernate, you can implement keyset pagination using JPQL as follows:<\/p>\n\n\n\n
@Query(\"SELECT m FROM Product m WHERE m.expireDate < :expireDate OR (m.expireDate = :expireDate AND m.id < :id) ORDER BY m.expireDate DESC, m.id DESC\") \nList<Product> findNextPage(@Param(\"expireDate\") LocalDateTime expireDate, @Param(\"id\") Long id, Pageable pageable); \/\/ use pageable only for page size<\/code><\/pre>\n\n\n\n
Spring Data JPA will automatically apply LIMIT based on Pageable.getPageSize(), even though it’s not explicitly in the JPQL. Since you’re not using Pageable for offset-based pagination, just pass PageRequest.of(0, pageSize) to only control the limit.<\/p>\n\n\n\n
Usage in service layer would be like this:<\/p>\n\n\n\n
Pageable pageable = PageRequest.of(0, 10);\nList products = productRepository.findNextPage(expireDate, id, pageable);<\/code><\/pre>\n\n\n\n
Keyset Pagination in Django<\/strong><\/h2>\n\n\n\n
In Django with Django ORM, you can implement keyset pagination using  Django\u2019s QuerySet API:<\/p>\n\n\n\n
from django.db.models import Q\n\ndef get_next_page(expire_date, last_id, expire_date, limit=10):\n    return Product.objects.filter(\n        Q(expire_date__lt=expire_date) | (Q(expire_date=expire_date) & Q(id__lt=last_id))\n    ).order_by('-expire_date', '-id')[:limit]\n\n<\/code><\/pre>\n\n\n\n
Conclusion<\/strong><\/h2>\n\n\n\n
Keyset pagination<\/strong> is a powerful technique for improving SQL query performance when paginating large datasets. While it works best with unique indexed columns, it can be adapted for non-unique sorting by using composite keys<\/strong>. By applying this technique, you can have faster and more efficient pagination in databases with huge amount of records in tables. \ud83d\ude80<\/p>\n","protected":false},"excerpt":{"rendered":"
Pagination is a common requirement in web applications dealing with datasets. Developers often use SQL queries with OFFSET and LIMIT to fetch paginated records. While … <\/p>\n","protected":false},"author":1,"featured_media":1998,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,215,217,69,41,70,214],"tags":[218,241,64,279,278,211,277,44,71,45],"_links":{"self":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/1993"}],"collection":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/comments?post=1993"}],"version-history":[{"count":5,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/1993\/revisions"}],"predecessor-version":[{"id":2294,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/1993\/revisions\/2294"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/media\/1998"}],"wp:attachment":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/media?parent=1993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/categories?post=1993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/tags?post=1993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}