Pagination represents one of those foundational engineering problems that appears trivially simple during initial system design, only to reveal layers of profound complexity as an application scales. At its core, pagination is about managing the transfer of large datasets from a server to a client in manageable chunks. However, beneath this simple premise lies a myriad of considerations regarding database performance, state mutability, user experience, and architectural cost.
The naive implementation—fetching items by specifying a skip count and a limit—works flawlessly for small datasets. But as data grows to millions of rows, and as concurrent users continuously mutate that data by inserting and deleting records, simplistic pagination strategies break down. They manifest as duplicate items rendered on a client interface, skipped records during data exports, and massive database performance degradation that can unexpectedly inflate cloud infrastructure bills by amounts ranging from $5K to upwards of $1.3M annually.
This deep dive explores the fundamental pagination strategies, analyzing their mathematical implications, database mechanics, and real-world trade-offs, enabling you to build APIs that are both robust and performant at scale.
Offset-based pagination is the most ubiquitous pattern in API design, largely because it maps directly to the SQL LIMIT and OFFSET clauses.
A standard offset paginated request looks like this:
GET /api/orders?offset=100&limit=50
Or, in its page-based variant (which is mathematically identical):
GET /api/orders?page=3&per_page=50
In this model, the client instructs the server to skip a certain number of records and then take the next slice. The database query typically looks like this:
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 100;
While this works well for the first few pages, the computational complexity degrades linearly as the offset increases. Relational databases do not maintain a magically ordered array of rows that they can instantly jump into. To execute an OFFSET 100000, the database engine must fetch, sort, and scan 100,000 rows, discard them, and then return the next 50 rows.
We can express the computational cost C of an offset query as a function of the offset o and the limit l:
Where k is the constant time required to evaluate and scan a single row. When o becomes sufficiently large, the term o dominates the equation, leading to an O(N) time complexity just to locate the starting point of the page. This linear degradation means that user requests for deep pages will eventually timeout, causing cascading failures as long-running queries tie up database connections and consume CPU cycles.
Beyond performance, offset pagination suffers from fundamental data consistency flaws in highly mutable environments. Consider a social media feed where new posts are constantly being inserted at the top.
page=1 (items 1-50).page=2 (items 51-100).Because the entire dataset shifted down by 5 positions, the items that were originally at positions 46-50 are now at positions 51-55. When the user receives page=2, they will see those 5 items duplicated. Conversely, if items are deleted, the dataset shifts up, and the user will permanently skip items that moved across the page boundary.
Because of these two critical flaws—linear performance degradation and vulnerability to shifting windows—offset pagination should be strictly limited to static datasets, small collections, or administrative interfaces where jumping to a specific page number is absolutely required and data mutability is low.
To resolve the performance and consistency issues of offset pagination, modern APIs employ cursor-based pagination (often referred to as keyset pagination in database contexts).
In cursor-based pagination, the client does not specify how many items to skip. Instead, it provides a pointer—a cursor—that indicates exactly where the last page left off.
GET /api/orders?after=eyJjcmVhdGVkX2F0IjoiMjAyNi0wNC0yNlQxMjowMDowMFoiLCJpZCI6ImFiYyJ9&limit=50
The cursor is typically an opaque, base64-encoded string representing the sort keys of the last seen item. When decoded, the payload might look like this:
{
"created_at": "2026-04-26T12:00:00Z",
"id": "abc"
}
By making the cursor opaque, the server retains full control over the underlying pagination mechanics. Clients treat the cursor as a meaningless token to be passed back in the subsequent request.
When the server receives the cursor, it translates it into a precise WHERE clause:
SELECT * FROM orders
WHERE (created_at, id) > ('2026-04-26T12:00:00Z', 'abc')
ORDER BY created_at ASC, id ASC
LIMIT 50;
If a composite index exists on (created_at, id), the database engine traverses its B-tree structure to jump directly to the exact node matching the cursor. The time complexity of navigating a B-tree to find the starting point is logarithmic, and reading the subsequent rows is linear with respect to the limit l.
Thus, the execution time T can be modeled as:
Because O(\log N) grows extremely slowly, the performance of the query remains practically constant regardless of how deep the user paginates into the dataset. Fetching the first page takes the same amount of time as fetching the millionth page. Furthermore, because the cursor points to a specific physical record rather than an abstract offset, insertions or deletions prior to the cursor have absolutely no impact on the results. The window never shifts.
Transitioning from offset-based to cursor-based pagination can have massive architectural implications. Consider an enterprise SaaS platform processing vast data exports via an API. Using offset pagination, a client exporting millions of rows might place such an extreme load on the database that it requires provisioning larger, more expensive read replicas.
By migrating to keyset pagination, a company can drastically reduce database CPU utilization and disk I/O. In high-throughput environments, this optimization can confidently reduce infrastructure overhead, saving operations budgets anywhere from $50K to over $1.3M over a multi-year horizon, purely by eliminating the wasteful row-scanning inherent in deep offsets.
A crucial, often-overlooked requirement for cursor-based pagination is that the sort order must be strictly deterministic. This means there can be no ambiguity in how the database orders the results.
Suppose you paginate users ordered by created_at.
SELECT * FROM users ORDER BY created_at DESC LIMIT 50;
It is highly probable that multiple users were created at the exact same millisecond. If the database encounters ten users with identical created_at timestamps, it will return them in whatever order it finds them on disk. This order is non-deterministic; it might change between queries.
If a cursor lands on one of these identical timestamps, the database does not know which of the ten rows to start after, leading to duplicated or skipped records.
To guarantee determinism, you must always append a unique, sequential tie-breaker to your sort criteria, usually the primary key.
SELECT * FROM users ORDER BY created_at DESC, id DESC LIMIT 50;
This guarantees that every single row has a strictly unique position in the sorted dataset, ensuring the cursor always points to a single, unambiguous location.
A common feature of paginated interfaces is displaying a total count to the user (e.g., "Showing 1-50 of 1,234,567 results"). While clients love this metadata, generating it requires the database to execute a full aggregate query:
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
In relational databases like PostgreSQL (which uses Multi-Version Concurrency Control), counting rows is an expensive operation because the database must verify the visibility of every single row for the active transaction. Running a COUNT(*) alongside every paginated API request introduces a massive performance bottleneck.
To mitigate the cost of counting, experienced API designers utilize several strategies:
has_more flag, which is easily determined by requesting limit + 1 rows from the database. If the extra row is returned, has_more is true.pg_class.reltuples provides an extremely fast, albeit approximate, row count.In the GraphQL ecosystem, the Relay specification has defined a standardized structure for cursor-based pagination that has seen widespread adoption across the industry (including at GitHub and Shopify).
The pattern wraps the dataset in a "Connection" object, providing explicit edges and page metadata:
type OrderConnection {
edges: [OrderEdge!]!
pageInfo: PageInfo!
}
type OrderEdge {
node: Order!
cursor: String!
}
type PageInfo {
hasNextPage: Boolean!
hasPreviousPage: Boolean!
startCursor: String
endCursor: String
}
This robust structure allows bidirectional pagination, enabling clients to navigate forwards using first and after, or backwards using last and before. The explicit separation of nodes and cursors ensures that clients always have access to the exact cursor needed for precise navigation, cementing it as a highly reliable pattern for complex, data-heavy applications.
Pagination strategies are rarely one-size-fits-all, but the trajectory of system scale pushes toward standardizing on cursors. For any new API, adopting cursor-based pagination as the default, accompanied by opaque cursor tokens and strict unique tie-breakers, guarantees stability and flat performance characteristics as the platform grows. While offset pagination retains utility in small, immutable datasets or environments requiring direct page jumps, it is a liability at enterprise scale. By understanding the underlying mechanics and mathematical complexities, developers can design APIs that remain resilient, performant, and cost-effective under extreme load.