We are refactoring our legacy application's data layer. We need to implement server-side pagination for tables with millions of records. What are the best practices for writing efficient SQL queries when handling offsets? The traditional OFFSET keyword is causing massive slowdowns on deeper pages.
3 answers
The standard OFFSET and LIMIT approach is a massive anti-pattern for large datasets because the database engine still has to read and discard all the preceding rows before reaching the target offset. To implement true best practices for writing efficient SQL queries here, you must switch to keyset pagination, also known as the seek method. Instead of skipping rows, you filter based on the last seen value of an indexed column, like a timestamp or sequential ID. This allows the database to instantly jump to the correct row via an index seek, maintaining constant execution time.
Keyset pagination works beautifully for forward scrolling, but how do your engineering teams handle UI requirements that strictly demand jumping to a specific, random page number further down the dataset?
Switching from OFFSET to a WHERE clause filter on your sequential primary keys will instantly drop query times from seconds to mere milliseconds on giant tables.
Raymond is right on target. We implemented this seek methodology last month and saw our API response latency drop by over eighty percent on our heavily populated historical logging tables.
Laura, that is the ultimate trade-off. If random page jumping is mandatory, we use a deferred join. You query only the primary keys using standard OFFSET, which is fast since it uses a covering index, and then join back to the main table to fetch the full columns for just those few rows.