A table that feels instant with 500 rows can become a production problem at 500,000. Shipping every record to the browser turns a useful grid into a slow initial load, a memory drain, and an expensive API response. This server side pagination guide explains how to keep data-heavy tables responsive by making the database and API do the work they are designed to do.
The goal is not merely to display page 1, page 2, and page 3. A production-ready approach must keep results correct when users sort, filter, edit records, and navigate while other users are changing the same dataset. That means defining a query contract, choosing the right pagination strategy, and treating total counts as a deliberate product decision rather than a default checkbox.
When server-side pagination is the right call
Client-side pagination is perfectly reasonable for a small, fixed result set. If an admin screen loads 200 reference records once and users need instant local searching, downloading the data and paginating in the browser is simple and effective.
Server-side pagination earns its complexity when the full dataset is too large to transfer or hold in the client comfortably. Common examples include CRM contacts, financial transactions, audit logs, inventory movements, support tickets, and analytics events. These views often need filtering and sorting across millions of rows, with permissions applied before the data ever reaches the browser.
The key principle is straightforward: the UI sends the current grid state to the server, and the server returns only the requested slice. Grid state usually includes the page position, page size, sort model, filter model, and any tenant or permission context derived from the authenticated user.
Do not let the client send arbitrary SQL-like strings. Accept structured, allowlisted fields instead. A table should be able to ask for `createdAt` descending, but not inject an unvalidated column name or operator into your query builder.
Define a pagination API contract first
A stable API contract prevents every framework component from inventing its own parameter format. Whether the grid is built in React, Vue, Angular, Svelte, Solid, or vanilla TypeScript, the request should describe intent rather than UI implementation details.
For offset pagination, a request might look like this:
```json { "page": 3, "pageSize": 50, "sort": [ { "field": "createdAt", "direction": "desc" }, { "field": "id", "direction": "desc" } ], "filters": [ { "field": "status", "operator": "in", "value": ["open", "pending"] } ] } ```
And the response should give the grid enough information to render rows and navigation controls:
```json { "rows": [], "page": 3, "pageSize": 50, "totalRows": 12483, "totalPages": 250 } ```
Use zero-based or one-based page numbers consistently. Either works. Inconsistency between the API, URL state, and grid component is where off-by-one bugs start appearing.
Validate page size on the server, even if the UI only offers approved choices. A practical maximum is often 100 or 250 rows, depending on row complexity. A 10,000-row page request can bypass the exact performance controls pagination was meant to provide.
Reset the page when query state changes
A user on page 18 who applies a narrow filter should not see an empty table and assume there are no matches. Whenever filtering, global search, or sorting changes, reset the requested page to the first page.
Keep that behavior in one state transition, not scattered across filter controls. It also helps to reflect page, sort, and filter state in the URL for shareable reports and browser navigation. Debounce free-text search by roughly 250 to 400 milliseconds, then cancel stale requests when a newer query begins.
Offset pagination versus cursor pagination
Offset pagination maps naturally to numbered pages. The database query is familiar:
```sql SELECT id, customer_name, status, created_at FROM orders WHERE account_id = $1 AND status = ANY($2) ORDER BY created_at DESC, id DESC LIMIT $3 OFFSET $4; ```
It is easy to explain, supports jumping directly to page 42, and works well when indexed filters keep the query selective. For back-office grids where users expect page numbers and data changes at a moderate rate, offset pagination is often the pragmatic choice.
Its weakness appears on deep pages and frequently changing datasets. A large offset can force the database to scan past many rows before returning the requested slice. Meanwhile, records inserted or deleted between requests can cause duplicates or skipped rows as a user moves through pages.
Cursor pagination, also called keyset pagination, uses the final row from the previous response as a boundary. Instead of requesting page 42, the client asks for records after a specific sort position:
```sql SELECT id, customer_name, status, created_at FROM orders WHERE account_id = $1 AND (created_at, id) < ($2, $3) ORDER BY created_at DESC, id DESC LIMIT $4; ```
The response returns a `nextCursor`, typically an encoded representation of `createdAt` and `id`. This approach is excellent for event streams, activity feeds, logs, and large datasets where users move forward sequentially. It remains fast at depth because the database seeks from an indexed position instead of discarding an ever-growing offset.
The trade-off is product behavior. Cursor pagination does not naturally support "go to page 42," and exact page counts may be unavailable or too expensive to calculate. If a grid needs a familiar numbered pager, offset pagination may still be the better experience. If it behaves more like a feed with Next and Previous controls, cursors are usually the stronger technical fit.
Stable sorting is not optional
Sorting by a non-unique field alone creates unstable pagination. Consider 800 orders with the same `createdAt` timestamp. If the query sorts only by that column, the database is free to return tied rows in varying orders. Users may see duplicates across pages or miss records entirely.
Always add a unique tie-breaker to the server query and send it as part of the grid's effective sort model. For example, sort by `createdAt DESC, id DESC`. The same rule applies to names, status values, prices, and any other non-unique column.
The database index should match the access pattern as closely as possible. An index on tenant identifier, active filter fields, and sort fields can change a slow grid endpoint into a predictable one. There is no universal index recipe, though. Measure actual queries with realistic tenant sizes and common filters before adding broad indexes that slow writes.
Treat filtering, permissions, and counts separately
A good server-side endpoint applies authorization before pagination. A user should never receive a total count that reveals records outside their account or role, and row-level access checks must be part of the query itself.
Filters should use an explicit allowlist of fields and operators. Text search needs its own constraints, such as minimum query length, normalized matching rules, and database-appropriate indexes. A contains filter across an unindexed column may be acceptable for a small internal dataset and unacceptable for a multi-tenant production table.
Exact `totalRows` is useful because it enables page counts, "showing 101-150 of 12,483," and reliable jump-to-page behavior. It can also be one of the most expensive parts of a request. Counting millions of filtered records repeatedly is not free.
You have options: return an exact count for selective queries, cache counts for stable reports, return an estimated count, or omit it for cursor-based feeds. Do not calculate an expensive total just because a grid component can display one. Match the count strategy to the user decision it supports.
Build the client for latency and change
The UI should retain the previous rows while the next request is loading when that makes navigation easier to follow. Show a subtle loading state in the grid rather than replacing the entire table with a blank spinner. Disable duplicate navigation actions while the same request is in flight, but do not make the interface feel frozen.
Each request needs an identifier or cancellation mechanism. If a user types "acme" quickly, the response for "a" must not overwrite the later response for "acme." Modern fetch cancellation with `AbortController` is often enough, paired with a check that the response still matches current grid state.
Inline editing adds another decision. After an edit, you can patch the changed row locally, refetch the current page, or invalidate the full query. Patching is fast but needs care when the edited field affects the active filter or sort order. Refetching is simpler and more trustworthy for high-stakes records, even if it costs one extra request.
A grid library should make the UI side predictable, not hide the data contract. Simple Table can provide the table mechanics - paging controls, sorting, filtering, loading states, and TypeScript-friendly column definitions - while your API remains the source of truth for large datasets.
Test the cases users actually create
Test more than a static happy path. Verify a user can sort a tied column, filter while on a later page, rapidly change search text, and navigate when the final page has fewer rows. Test what happens when a record is edited out of the current filter, deleted between page requests, or inserted ahead of the user’s cursor.
Also monitor query duration by page depth, filter combination, tenant size, and count behavior. A median response time can look healthy while a deep offset query quietly punishes a small group of power users. Observability turns pagination from a UI feature into an operating part of your application.
The best pagination design is the one that reflects how people move through your data. Give audit logs fast cursors, give report tables meaningful page numbers when they need them, and make every sort order stable enough that users can trust the rows in front of them.