The crash at scale
You built an endpoint that returns items. It works perfectly with fifty rows. Your client renders the list. You celebrate. Then the database grows. A month later, the table has fifty thousand rows. A user hits the endpoint. The server hangs. The database CPU spikes to 100 percent. The client times out. You just discovered pagination.
Pagination isn't a feature you add when you have time. It's a survival mechanism. Without it, your API returns the entire table on every request. That burns memory, blocks the database, and transfers megabytes of JSON over the wire. Pagination breaks a massive dataset into manageable chunks. The client asks for a slice. The server delivers that slice and nothing more.
The contract of pagination
Pagination is a negotiation between the client and the server. The client sends parameters describing which slice it wants. The server translates those parameters into a database query that fetches only the relevant rows.
The most common pattern is offset pagination. The client specifies a page number and a page size. The server calculates an offset, skips that many rows, and returns the next batch.
Think of a book. The database is the text. page is the chapter number. per_page is how many lines you read at once. If you want page 3 with 10 lines per page, you skip the first 20 lines and read the next 10. The offset is 20. The limit is 10.
Offset pagination is intuitive. Humans count pages starting at one. It supports jumping to arbitrary pages. It also has a hidden cost that bites you later. You'll see that cost when the dataset grows large.
Parsing parameters with Axum
Axum handles parameter parsing with extractors. The Query extractor reads URL parameters and deserializes them into a struct. You define the struct with serde::Deserialize.
use axum::{extract::Query, routing::get, Router};
use serde::Deserialize;
// Convention: suffix with Params to distinguish from domain models.
// This signals that the struct represents input, not business data.
#[derive(Deserialize)]
struct PaginationParams {
page: usize,
per_page: usize,
}
async fn list_items(pagination: Query<PaginationParams>) {
let page = pagination.0.page;
let per_page = pagination.0.per_page;
// Fetch data using page and per_page
}
let app = Router::new().route("/items", get(list_items));
The Query extractor wraps the struct in a tuple. You access the fields via pagination.0. If the client sends ?page=2&per_page=10, Axum populates the struct. If the client omits page, serde fails deserialization and Axum returns a 400 Bad Request.
That behavior is strict. Most APIs should be forgiving. Missing parameters should fall back to sensible defaults rather than breaking the request.
Defaults and safety
Clients often forget parameters. A browser might bookmark a URL without query strings. A mobile app might send a stale request. Your API should handle these cases gracefully.
Use serde defaults to provide fallback values. Define functions that return the defaults and reference them in the struct attributes.
#[derive(Deserialize)]
struct PaginationParams {
// Default to page 1 if missing.
#[serde(default = "default_page")]
page: usize,
// Default to 20 items per page if missing.
#[serde(default = "default_per_page")]
per_page: usize,
}
fn default_page() -> usize {
1
}
fn default_per_page() -> usize {
20
}
Now ?per_page=50 works. page defaults to 1. The request succeeds. The API is robust.
There's a second safety concern. Humans count pages starting at one. Databases count offsets starting at zero. If the client sends page=1, the offset should be 0. If the client sends page=0 by mistake, the math breaks.
Subtracting 1 from 0 panics in debug mode. Use saturating_sub to handle the edge case. saturating_sub returns 0 instead of panicking when the result would underflow.
let offset = pagination.0.page.saturating_sub(1) * pagination.0.per_page;
Trust saturating_sub. It saves you from the panic at page zero.
Realistic database query
Pagination lives in the database query. SQL uses LIMIT and OFFSET clauses to implement offset pagination. LIMIT caps the number of rows returned. OFFSET skips rows before returning results.
Here's a realistic handler using sqlx for PostgreSQL.
use axum::{
extract::{Query, State},
response::Json,
routing::get,
Router,
};
use serde::Deserialize;
use sqlx::PgPool;
#[derive(Deserialize)]
struct PaginationParams {
#[serde(default = "default_page")]
page: usize,
#[serde(default = "default_per_page")]
per_page: usize,
}
fn default_page() -> usize { 1 }
fn default_per_page() -> usize { 20 }
#[derive(sqlx::FromRow)]
struct Item {
id: i64,
name: String,
}
async fn list_items(
State(pool): State<PgPool>,
pagination: Query<PaginationParams>,
) -> Result<Json<Vec<Item>>, StatusCode> {
// Cap per_page to protect the database.
// Never trust the client's limit.
let limit = pagination.0.per_page.min(100);
let offset = pagination.0.page.saturating_sub(1) * limit;
let items = sqlx::query_as!(
Item,
r#"SELECT id, name FROM items ORDER BY id LIMIT $1 OFFSET $2"#,
limit,
offset
)
.fetch_all(&pool)
.await
.map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
Ok(Json(items))
}
The ORDER BY clause is mandatory. Without it, the database returns rows in arbitrary order. Pagination becomes unpredictable. Items might appear on multiple pages or vanish entirely. Always sort by a stable column like the primary key.
The min(100) cap prevents abuse. A client could request per_page=100000. That forces the database to fetch and serialize a massive result set. Capping the limit protects the server. Return 100 items even if the client asks for more. Or return an error. Never honor an unlimited request.
Cap the limit. Your database will thank you.
The deep pagination penalty
Offset pagination has a performance trap. The deeper you paginate, the slower it gets.
When you request page 1000 with 20 items per page, the offset is 19,980. The database scans the index, finds the first 19,980 rows, discards them, and returns the next 20. The scan cost grows linearly with the offset. Page 1 is fast. Page 10,000 is slow.
This is the deep pagination penalty. The database doesn't jump to the offset. It walks to the offset and throws away everything before it. On large tables, this kills performance.
There's a second problem with offset pagination. If rows are inserted or deleted between requests, the page contents shift. Items might appear twice or skip entirely. This is less of a performance issue and more of a consistency issue. It matters when the dataset changes frequently.
Cursor pagination
Cursor pagination avoids the deep pagination penalty. Instead of skipping rows, the client sends the ID of the last item it saw. The server queries for items after that ID.
SELECT id, name FROM items WHERE id > :last_id ORDER BY id LIMIT :limit
The database uses the index to jump straight to last_id. It fetches the next rows. No scanning. No discarding. The performance is constant regardless of how far back the client has scrolled.
Cursor pagination requires the client to store the cursor value. The cursor is usually the ID of the last item, or a base64-encoded string containing the ID and timestamp. The client sends the cursor back on the next request.
Cursor pagination doesn't support jumping to arbitrary pages. You can only go forward or backward one page at a time. It's ideal for infinite scroll interfaces where the user loads more items as they scroll.
Decision matrix
Pick the pagination strategy that matches your data shape and user interface.
Use offset pagination when the user interface requires page numbers, next/previous buttons, or jumping to specific pages. Offset pagination is simple and supports random access. It works well for small datasets or admin panels where users need to navigate directly.
Use cursor pagination when you have infinite scroll, massive datasets, or high write throughput. Cursor pagination delivers constant performance and avoids the deep pagination penalty. It's the standard for social media feeds and chat histories.
Use a capped offset approach when you need simplicity but want to protect the database. Allow offset pagination only up to a reasonable depth, such as page 100. Beyond that, return an error or switch to cursor pagination. This gives users the convenience of page numbers without exposing the server to deep pagination attacks.
Metadata without the count
Clients often need metadata to build the UI. They want to know the current page, the total number of items, and whether there's a next page.
Returning total_items requires a COUNT(*) query. Counting rows is expensive. On large tables, COUNT(*) can be slower than fetching the data itself.
Skip the count. Return has_next instead. Fetch one extra row. If you get the extra row, set has_next to true and drop the last item. This tells the client there's more data without running a count query.
let fetch_limit = limit + 1;
let items = sqlx::query_as!(
Item,
r#"SELECT id, name FROM items ORDER BY id LIMIT $1 OFFSET $2"#,
fetch_limit,
offset
)
.fetch_all(&pool)
.await?;
let has_next = items.len() > limit;
let data = if has_next {
items[..limit].to_vec()
} else {
items
};
let response = PaginatedResponse {
data,
current_page: pagination.0.page,
per_page: limit,
has_next,
};
The client can calculate the total pages if it knows the total count, or it can just rely on has_next to drive the UI. For most applications, has_next is sufficient.
Skip the count. Fetch one extra row. It's faster.