We are building a high-traffic dashboard. What is the most optimized pattern for pagination in SQL Queries when dealing with millions of records? OFFSET seems to slow down drastically as page numbers increase.
3 answers
The performance degradation you are experiencing is a known limitation of using LIMIT with a high OFFSET. When you run those SQL Queries, the engine must still scan and discard all preceding records before returning the requested page. To achieve true scalability, you should implement 'Keyset Pagination' or the 'Seek Method'. This approach uses a WHERE clause to filter for records that have an identifier greater than the last item of the previous page, ensuring constant execution time regardless of the page depth.
Does your frontend dashboard allow users to jump to specific random page numbers, or do they primarily navigate sequentially using 'Next' and 'Previous' buttons?
If you absolutely must use random page jumping, creating a dense covering index that includes your sorting keys can help mitigate the offset slowdown.
I agree with Wayne. A covering index allows the system to satisfy the pagination search directly from index trees without touching raw data pages.
If your users only need sequential navigation, Martha's seek method is flawless. It completely bypasses table scanning, keeping your database load minimal even when users browse through millions of rows deep into the log archives.