A slow 500th page and repeated rows often have different causes. A large OFFSET can make a query slower because the database still has to compute rows it will skip. Duplicate or missing rows can result when page boundaries rely on a non-unique or changing order, though joins and application logic can also duplicate results. Use a deterministic order with a unique tie-breaker first; consider keyset pagination when people browse large result sets sequentially.
Why can page 500 be slow?
With offset pagination, the application asks the database to skip a number of rows and return the next batch. PostgreSQL cautions that skipped rows still have to be computed inside the server, so a large offset may be inefficient. Django likewise notes that requests for high page numbers can be slow on large QuerySets.
As an Amazon Associate I earn from qualifying purchases.
For example, a page size of 50 means page 500 starts after 24,950 rows. The database may need to process those preceding rows before returning the requested 50. The actual cost depends on the query, indexes, data, and execution plan; the page number alone does not establish a particular delay or a universal threshold. PostgreSQL 17: LIMIT and OFFSET · Django: Paginator
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsWhy do rows repeat or disappear between pages?
The sort order does not uniquely identify rows
LIMIT and OFFSET do not define which rows come first. PostgreSQL advises using an ORDER BY that constrains results to a unique order; without it, the selected subset can be unpredictable. Sorting only by a field such as created_at is not enough when multiple rows share that value: their relative order is not guaranteed.
#1 Best Overall
Add a unique tie-breaker, typically the primary key. For a descending chronological feed, ORDER BY created_at DESC, id DESC gives each row a distinct position if id is unique. Django also recommends ordering a QuerySet for consistent pagination. PostgreSQL 17: LIMIT and OFFSET · Django: QuerySet API reference · Django: Paginator
The data changes between requests
Offset pages are usually separate queries. If rows are inserted or updated so they move before a later page’s offset, the next query can have shifted boundaries: a row may appear again, or another may be missed. This is a possible consequence of paging through changing data, not proof that every duplicate is caused by OFFSET.
The query or application duplicates entities
Check whether joins produce multiple result rows for one entity, whether the query’s filters and grouping match the intended result, and whether application code appends or merges rows more than once. A stable sort fixes ordering ambiguity; it does not remove duplicates that the query or application already creates.
Make offset pagination deterministic
Before changing pagination strategy, specify a stable order that ends in a unique key, then inspect the actual query and results. For example:
Rank #3
SELECT id, created_at, payload
FROM items
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 24950;This pattern assumes the ordered columns and their null behavior make sense for the application. Confirm the generated SQL, joins, filters, and query plan rather than assuming a tie-breaker alone will make the query fast.
When keyset pagination helps
Keyset, or cursor, pagination continues from the last row’s ordered values instead of asking the database to skip every preceding row. It can avoid deep-offset work when the ordering and query support efficient continuation. It is a better fit for next/previous browsing through a large feed than for jumping directly to an arbitrary numbered page.
For the same descending order, the continuation query can look like this:
Recommended Free Tools
SELECT id, created_at, payload
FROM items
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;The cursor values are those of the last row returned on the previous page. This is an implementation sketch, not a drop-in query for every database: tuple comparisons, sort directions, and NULL handling vary. Build a predicate that matches the exact ordering and verify its plan. An index aligned with the query can matter; Django REST framework recommends an index on the cursor ordering field. Its guidance also favors a stable, non-null, non-float ordering field that is unique or nearly unique. If the field is not unique, account for a unique tie-breaker. Django REST framework: Pagination
Best Value
Offset or keyset: which fits?
| Decision | Offset pagination | Keyset/cursor pagination |
|---|---|---|
| Deep pages | Large offsets may be inefficient because skipped rows still have to be computed. | Can avoid deep-offset work when the continuation query and ordering are efficient. |
| Navigation | Maps naturally to numbered pages and direct jumps. | Typically supports forward or backward traversal, not arbitrary page-number jumps in the documented Django REST framework cursor paginator. |
| Ordering | Needs a deterministic order; a unique combined order avoids unpredictable subsets. | Needs a stable ordering represented by the cursor; ties require careful handling. |
| Changes during paging | Inserts or updates before a later offset can shift page boundaries across requests. | Django REST framework says properly used cursor pagination can avoid showing the same item twice when other clients insert rows during paging. |
| Implementation | Straightforward for numbered-page interfaces. | Requires cursor handling and care over supported sort fields and continuation predicates. |
Keep offset pagination when numbered jumps matter and measured performance is acceptable. For a large sequential feed, keyset pagination may be a better fit. Anchor or checkpoint designs can preserve some jump-like behavior, but add complexity and should be justified by a real product requirement. Neither strategy is universally faster: measure representative queries and inspect their plans before promising a speedup.
Quick Recap
How to diagnose the specific symptom
- Inspect the generated SQL. Confirm the actual
ORDER BY, filters, joins, grouping, limit, and offset or cursor predicate. - Make ordering unique. Add a unique tie-breaker after the user-visible sort field and verify that null values and sort directions are handled consistently.
- Check whether the result itself has duplicates. Compare entity IDs in the raw query result with the rows rendered by the application to distinguish query-level duplication from pagination-boundary changes.
- Consider concurrent writes. Repeat the page requests against a stable dataset if possible, then compare with requests while inserts or updates occur.
- Measure the query plan. Test the actual workload and indexes at representative page depths. Documentation describes general behavior, not a benchmark or guaranteed improvement for your application.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
