> ## Content Index > Fetch the complete content index at: https://debugly.dev/llms.txt > Use this file to discover other available public pages before exploring further. # The Offset Pagination That Scanned Every Row It Skipped - URL: https://debugly.dev/offset-pagination-scanned-everything/ - Published: 2026-10-09T16:10:00.000Z - Updated: 2026-10-17T05:55:54.000Z - Description: The page query read and discarded ninety thousand rows to return the twenty on page four thousand, and the cost grew with the page number rather than the… - Author: Rohit Bhadani - Tags: Performance, PostgreSQL, Pagination Between the timed out and the offset sit 6 quiet assumptions, and the incident is the story of one of them failing. Here is the shape of the offset pagination's cost, and the one property that makes it scale badly: the database must produce every skipped row before it can discard it, so the cost is proportional to the offset rather than the page size. This was Postgres with an index on the sort column, and the mechanics are the same for any database implementing LIMIT with OFFSET. ## Why the skip is not free The offset is the skip, and the skip is the intuition's free, and the free is the wrong, because the database reads the index, and the index is the ordered, and the ordered is the walk, and the walk is the eighty thousand entries, and the entries are the rows, and the rows are the fetched, and the fetched is the discarded, and the discarded is the work, and the work is the cost, and the cost is the proportional to the offset. The page size is the twenty, and the twenty is the returned, and the returned is the constant, and the constant is the expectation, and the expectation is the flat cost, and the flat is the wrong, because the cost is the offset plus the size, and the offset is the growing, and the growing is the last page's timeout, and the timeout is the incident. The index helps the ordering and not the skipping, because the index gives the order, and the order is the walk's path, and the path is the eighty thousand steps, and the steps are the cost, and the cost is the index's limit, and the limit is the reason the keyset pagination exists. ## Why the count made it worse The total count was the other query, and the other query was the full scan, and the full scan was the every row, and the every row was the count's cost, and the cost was the independent of the page, and the independent was the every request, and the every request was the two queries, and the two was the scan plus the walk, and the walk was the offset, and the offset was the growth, and the growth was the timeout. The count is the pagination's UI, and the UI is the page numbers, and the numbers are the total, and the total is the scan, and the scan is the cost, and the cost is the reason to ask whether the total is needed, and the needed is the design, and the design is the infinite scroll's absence, and the absence is the saving, and the saving is the fix. This is the same reading as [the EXPLAIN ANALYZE plan that showed the estimate gap](https://debugly.dev/reading-explain-analyze-like-map/), and the shared property is the one worth fixing. ## The fix, in order **Switched to keyset pagination.** The query became the where the sort key is greater than the last seen, and the greater is the index's seek, and the seek is the jump, and the jump is the no walk, and the no walk is the constant cost, and the constant is the fix, and the fix is the cursor, and the cursor is the last row's key, and the key is the state, and the state is the client's. **Required a unique sort key.** The keyset needs the total order, and the order is the unique, and the unique is the tiebreaker, and the tiebreaker is the identifier, and the identifier is the composite key, and the composite is the fix, and the fix is the index, and the index is the sort column plus the identifier, and the two are the seek's requirement. **Capped the reachable depth.** The maximum offset is the bound, and the bound is the timeout's prevention, and the prevention is the fix, and the fix is the limit, and the limit is the product's decision, and the decision is the user's need, and the need is the filter rather than the page four thousand, and the filter is the better answer. **Dropped the total count where it was not needed.** The count is the scan, and the scan is the cost, and the cost is the removal, and the removal is the fix, and the fix is the infinite scroll or the has-next flag, and the flag is the one extra row, and the row is the cheap, and the cheap is the saving. **Indexed the sort column with the filter.** The index is the seek, and the seek is the filtered set, and the set is the smaller walk, and the smaller is the fix, and the fix is the composite index, and the composite is the filter plus the sort, and the two are the plan's improvement. ## What I now do Offset pagination makes the database produce every skipped row before discarding it, so the cost grows with the page number rather than the page size. Switch to keyset pagination on a unique sort key, cap the reachable depth, drop the total count where it is not needed, and index the filter with the sort column. Page four thousand timed out because the query read eighty thousand index entries to discard them and return twenty rows, and the page size was constant while the cost was not. I now check for that property first, because it is where the failure actually lives. --- Published on [debugly.dev](https://debugly.dev/) by [Rohit Bhadani](https://debugly.dev/about/) | Founder of [Krova Cloud](https://krova.cloud/?ref=debugly.dev)