A paginated query that slows down over time may be paying to skip more rows or search hits—but the symptom alone does not prove pagination is the cause. Compare shallow and deep requests, their execution plans, and their database-side costs before changing the endpoint. If the slowdown tracks page depth, seek-based pagination may help for sequential navigation; if it tracks elapsed time instead, investigate the query, data, indexes, workload, and surrounding application path.
Why later pages can cost more
With LIMIT/OFFSET, the database must find the requested position and return the following window. Rows skipped by a large offset still have to be computed; PostgreSQL warns that a large OFFSET might therefore be inefficient. MongoDB likewise documents that skip() gets slower as the offset increases, and that indexed range queries can typically outperform skip as offsets grow. The size of the penalty depends on the engine and execution plan, not on a universal slowdown formula. (PostgreSQL documentation; MongoDB cursor.skip documentation)
As an Amazon Associate I earn from qualifying purchases.
Search pagination has its own mechanics. In Elasticsearch, from/size pagination across shards makes shards load hits for earlier pages as well as the requested page, which can increase memory and CPU use on deep pages. Elastic advises against paging too deeply with from and size. This is not interchangeable with SQL behavior, so diagnose the system actually serving the endpoint. (Elastic documentation)
Deep offsets are one plausible explanation, not a complete post-mortem. A query can also regress because its plan, indexes, filters, sort, data distribution, cache conditions, or workload changed. The title alone supplies no database, query plan, or timing evidence to identify which cause applies.
#1 Best Overall
- Dell PowerEdge R730xd 24B SFF 2U Server
- 2x Intel Xeon E5-2690 v4 2.6Ghz 14-Core (28-cores Total)
- 128GB DDR4 RAM – 4x 1.2TB 10K SAS 2.5” 12Gb/s
- Dell H730P mini 2GB 12Gb/s RAID
- 2x 750W PSU - 2x 10Gb SFP+ 2x 1Gb (RJ45) NIC
First establish what is getting slower
- Capture the exact request. Record the SQL or search request, parameter values, sort order, page size, and representative shallow and deep positions. Include the request that users experience as slow rather than testing a simplified query that changes its behavior.
- Make the ordering deterministic. Check that results have an explicit order and that the final sort key uniquely breaks ties. PostgreSQL warns that
LIMIT/OFFSETsubsets are unpredictable without anORDER BYthat constrains rows to a unique order; plan changes can otherwise produce different subsets. (PostgreSQL documentation) - Compare shallow and deep execution. Use the database’s native execution-plan and timing or buffer metrics for the same filters, order, and page size at different positions. Look for work spent finding skipped rows or hits, and compare the plan as well as elapsed time.
- Separate database time from the rest of the request. Measure query execution independently from serialization, network transfer, application processing, and any separate count query. A slow response does not by itself show that the page-fetch query is slow.
- Inspect index support. Check whether the filters and ordering keys have useful index support for this query and data distribution. MongoDB’s query-optimization guidance recommends indexes for commonly issued queries and explains that indexes improve queries on indexed fields. An index must fit the actual predicate and sort; adding one without checking the plan is not a diagnosis. (MongoDB documentation)
- Repeat under representative conditions. Compare measurements with representative data and workload. A rewrite is not evidence of a fix until the endpoint’s relevant costs and behavior improve in those conditions.
Choose pagination to match how people navigate
The right approach depends on whether users need arbitrary numbered-page jumps, sequential traversal, or a search-specific continuation mechanism.
| Approach | Best fit | Deep-page cost and constraints | Navigation and consistency |
|---|---|---|---|
LIMIT/OFFSET or skip |
Shallow pages, numbered pages, and direct jumps | Large offsets may require processing skipped rows or hits; cost varies by engine and plan. (PostgreSQL; Elastic; MongoDB) | Use a deterministic order. Changes between separate requests can affect which records appear on a page. |
| Keyset or range pagination | Sequential feeds and APIs that move forward or backward through an ordered result set | Continue from the last seen sort key rather than counting past prior rows. Requires an appropriate index and a predicate that handles the full sort tuple. MongoDB documents indexed range queries as typically more efficient than skip as offsets increase. (MongoDB) | Natural for next/previous traversal; arbitrary page-number jumps and exact page counts are less natural. The anchor is the last result, so a product that requires “go to page 237” may still need a different interface or strategy. |
| Search-after or cursor token | Search engines and APIs with a documented continuation mechanism | Sort and token requirements are engine-specific. For deep Elasticsearch traversal where the index state must stay consistent, Elastic recommends search_after with a point in time. (Elastic documentation) |
Cursor semantics and snapshot guarantees vary by product and deployed version; verify the behavior of the actual system. |
| Server-side cursor or bulk traversal | Batch processing and exports | May hold server-side state or require initial result work; it is not automatically appropriate for interactive page requests. Elastic describes scroll as intended for large-volume processing rather than real-time user requests. (Elastic documentation) | Can suit traversal of a stable result context, with resource and lifetime considerations specific to the engine. |
How to prototype keyset pagination safely
Keyset pagination resumes after the last result seen rather than asking the database to skip a count of earlier rows. For a sequential endpoint, prototype it with the complete ordering tuple and a stable unique tie-breaker. For example, if the order is by creation time and then a unique record ID, the continuation predicate must account for both values; comparing only the timestamp can omit or duplicate records when timestamps tie. The exact predicate and index depend on the database, sort direction, filters, and schema, so they should be confirmed against that engine’s plan rather than copied from a generic example.
Rank #2
- Model: Dell OptiPlex 7050 Small Form Factor (SFF)
- Processor: Intel Core i7-7700 3.60 GHz
- Memory: 32GB DDR4 Ram
- Storage: 1TB Solid State Drive (SSD) Fast Boot + Storage
- Operating System: Windows 11 Pro (64-bit)
- Define whether each page sees a fresh view of the data or should remain within a consistent snapshot. Inserts and deletions between requests can change what a user sees; test that behavior explicitly.
- Provide a continuation token or equivalent anchor based on the last returned sort values when the client does not need page-number jumps.
- Test both directions if the interface supports previous as well as next, including ties and records added or removed between requests.
- Check the actual execution plan and measurements after the change. Seek-based traversal still depends on suitable indexing and query shape; it is not guaranteed to have constant cost in every implementation or workload.
Account for count queries and search consistency
A page request may issue a separate count to display a total or determine whether more results exist. That count can have its own cost and should be measured separately from fetching the page. MongoDB Search specifically warns that counting results can affect performance and advises using its count option only when needed. (MongoDB Search documentation)
For Elasticsearch deep traversal, use the documented search_after and point-in-time mechanism when a consistent index state is required. MongoDB Search also supports searchAfter and searchBefore tokens for moving from a reference point. These are product-specific features, not interchangeable cursor guarantees; confirm their requirements and consistency behavior for the deployed version. (Elastic; MongoDB Search)
Rank #3
- 2.80 GHz processor speed ensures efficient operation with consistent reliability
- Intel Xeon 2.80 GHz processor provides enterprise-grade performance with built-in security and remote management capabilities
- Quad-core (4 Core) processor core helps server process data quickly and reliably for maximum productivity
- 1 processors supported for faster processing and improved access to data, optimizing performance under heavy loads
- With 16 GB memory, you can multitask between applications seamlessly, keeping productivity high and response times quick
What a defensible post-mortem can conclude
If deeper positions consistently require more database work while other request costs remain comparable, the evidence supports deep pagination as a contributor. If the slowdown occurs regardless of page depth, or the database work does not rise with depth, the investigation should follow the evidence toward plans, indexes, workload, counts, or application overhead instead. Without measurements from the actual endpoint, no particular cause—or successful fix—can be claimed.
Quick Recap
Best Value
- HP Z4 G4 Workstation Tower
- Intel Xeon W-2133 6-Core 3.6GHz (3.9GHz Turbo)
- 64GB DDR4 Memory - Nvidia Quadro P400 2GB
- 512GB NVMe M.2 SSD (boot) + 2TB HDD (storage)
- Windows 11 Pro 64-bit
Rank #4
- MODEL P74439-005: Compact and affordable HPE ProLiant MicroServer Gen11 powered by Intel Pentium Gold G7400 3.7GHz processor, ideal for file sharing, NAS, and basic business workloads
- READY OUT OF THE BOX: Includes 16GB DDR5 UDIMM memory (expandable to 128GB), one 1TB SATA 6G Business Critical HDD, embedded Intel VROC SATA, dedicated iLO-M.2 port kit, 180w external power adapter and 1/1/1 warranty for dependable plug-and-play server operation
- WHISPER-QUIET & SPACE-SAVING: Ultra-compact mini tower design fits easily in small office spaces; supports wall, flat, or vertical placement for deployment flexibility
- INTEGRATED REMOTE MANAGEMENT: Comes with HPE iLO 6 and embedded TPM 2.0 for secure, license-free remote server administration through shared port access
- EXPANDABLE DESIGN: Two PCIe slots (including PCIe 5.0) and four LFF-NHP drive bays provide robust options for storage and component scalability. Features new MR408i-p controller support for enhanced storage performance
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.
