The list that breaks your server
You ship an endpoint that returns all items. It works perfectly for ten rows. Then a user adds ten thousand. The response payload balloons to five megabytes. The browser freezes trying to render the DOM. Your database connection pool exhausts because a single query holds a lock for seconds while streaming results. Pagination isn't a UI polish. It's a database survival mechanism. You slice the data so the client gets what it needs and the server keeps breathing.
Offset pagination: the standard approach
Offset pagination uses two numbers: the page number and the page size. The client asks for page 2 with 10 items per page. You translate that to "skip 10, take 10". The database scans the first 10 rows, discards them, and returns the next 10.
Think of it like a deck of cards. You ask the dealer for the fifth hand of five cards. The dealer flips through 20 cards, tosses them aside, and deals the next five. The mechanic is simple. The cost is hidden. If you ask for the thousandth hand, the dealer still flips through 5,000 cards just to get to the ones you want. The database does the exact same thing. It reads rows it's going to throw away.
Minimal example
Axum provides a Query extractor that pairs with Serde to parse URL parameters into a struct. You define the shape of the request, and the framework handles the deserialization.
use axum::{extract::Query, routing::get, Router};
use serde::Deserialize;
/// Extracts pagination parameters from the query string.
#[derive(Deserialize)]
struct Pagination {
/// Current page number. Humans start counting at 1.
page: usize,
/// Number of items per page.
per_page: usize,
}
/// Handles the list endpoint with basic offset pagination.
async fn list_items(pagination: Query<Pagination>) {
// Convert 1-based page index to 0-based offset for SQL.
// Page 1 becomes offset 0. Page 2 becomes offset per_page.
let offset = (pagination.page - 1) * pagination.per_page;
let limit = pagination.per_page;
// Pass limit and offset to your database query.
// Example: db.fetch("SELECT * FROM items LIMIT $1 OFFSET $2", &[limit, offset]);
}
fn main() {
// Wire up the route.
let app = Router::new().route("/items", get(list_items));
// app.run(...);
}
The Query<Pagination> extractor reads ?page=2&per_page=10 from the URL. Serde maps page to 2 and per_page to 10. If the client sends ?page=abc, Serde fails to parse the integer. Axum catches the error and returns a 400 Bad Request automatically. You don't write manual parsing logic.
If you forget #[derive(Deserialize)], the compiler rejects the handler with E0277 (the trait bound Deserialize<'_> is not satisfied). Axum requires Serde to transform the query string into your struct. Add the derive macro and the error vanishes.
Convention aside: page usually starts at 1 in APIs because humans count from one. Databases count from zero. The subtraction (page - 1) bridges that gap. Keep the API user-friendly and do the math inside the handler.
Realistic validation
Real code needs guardrails. A client might send ?per_page=999999. You need to clamp that value. A client might send ?page=0. You need to correct that. Never trust the query string. It's hostile territory.
use axum::{extract::Query, Json, routing::get, Router};
use serde::{Deserialize, Serialize};
/// Extracts pagination parameters from the query string.
#[derive(Deserialize)]
struct Pagination {
page: usize,
per_page: usize,
}
/// Response wrapper that includes metadata for the client.
#[derive(Serialize)]
struct PaginatedResponse<T> {
items: Vec<T>,
total_pages: usize,
current_page: usize,
}
/// Validates and normalizes pagination inputs.
impl Pagination {
fn validate(self) -> Self {
// Clamp per_page to a reasonable range.
// Prevents DoS via massive result sets.
// 1 is the floor to avoid division by zero or empty pages.
// 100 is the ceiling to protect database performance.
let per_page = self.per_page.clamp(1, 100);
// Ensure page starts at 1.
// Clients sometimes send 0 by mistake.
let page = if self.page == 0 { 1 } else { self.page };
Self { page, per_page }
}
}
/// Handles the list endpoint with validation.
async fn list_items(pagination: Query<Pagination>) {
// Validate inputs before touching the database.
let pagination = pagination.0.validate();
let offset = (pagination.page - 1) * pagination.per_page;
let limit = pagination.per_page;
// Fetch items and total count.
// In practice, fetch total count separately or use a window function.
// let items = db.fetch_items(limit, offset).await;
// let total = db.count_items().await;
// let total_pages = (total + limit - 1) / limit;
// Json(PaginatedResponse { ... })
}
fn main() {
let app = Router::new().route("/items", get(list_items));
// app.run(...);
}
The validate method returns a new Pagination struct with safe values. clamp ensures per_page stays between 1 and 100. The page check handles the zero edge case. This pattern keeps the handler clean. You validate once, then use the safe values downstream.
Clamp the limit. Your database will thank you.
The offset trap
Offset pagination has a flaw that appears at scale. The cost grows linearly with the offset. OFFSET 1000000 forces the database to read one million rows, discard them, and return the next batch. The query takes seconds. The connection pool dries up.
This happens because most databases implement offset by scanning. They can't jump to row 1,000,000 instantly. They walk the index or table from the beginning. The deeper you go, the slower it gets.
There's also a consistency problem. If data changes between requests, items can duplicate or disappear. You request page 1. An item gets deleted. You request page 2. The item that was at position 11 shifts to position 10. You see it again on page 2. The user notices the jump.
Offset pagination is fine for small datasets or static data. It breaks when you have millions of rows and active writes.
Keyset pagination: the scalable alternative
Keyset pagination, also called seek method or cursor pagination, avoids the scan. Instead of skipping rows, you use the last value from the previous page to fetch the next batch.
The client sends the ID of the last item it saw. You query WHERE id > last_id ORDER BY id LIMIT 10. The database uses the index to jump straight to last_id. It reads only the rows it returns. The cost is constant, regardless of how deep you go.
use axum::{extract::Query, routing::get, Router};
use serde::Deserialize;
/// Extracts keyset pagination parameters.
#[derive(Deserialize)]
struct KeysetPagination {
/// The ID of the last item from the previous page.
after_id: Option<i64>,
/// Number of items to fetch.
limit: usize,
}
/// Handles the list endpoint with keyset pagination.
async fn list_items(pagination: Query<KeysetPagination>) {
// Clamp limit to prevent abuse.
let limit = pagination.limit.clamp(1, 100);
// Build query based on whether we have a cursor.
// If after_id is present, use WHERE id > $1.
// Otherwise, fetch the first page.
let query = if let Some(after_id) = pagination.after_id {
// Keyset query: seek to the last known position.
// This uses an index seek, not a scan.
"SELECT * FROM items WHERE id > $1 ORDER BY id ASC LIMIT $2"
} else {
// First page: no offset needed.
"SELECT * FROM items ORDER BY id ASC LIMIT $1"
};
// Execute query with appropriate parameters.
// db.fetch(query, &[limit, after_id.unwrap_or(0)]);
}
fn main() {
let app = Router::new().route("/items", get(list_items));
// app.run(...);
}
Keyset pagination requires a sortable, unique column. Usually the primary key. You must order by that column. The client stores the last ID and sends it back for the next request. This supports "Next" and "Previous" navigation. It does not support jumping to page 50. The client has to walk through pages to get there.
Keyset pagination is faster and consistent. New inserts don't shift existing items. The user never sees duplicates or gaps. The trade-off is random access. You lose the ability to jump to arbitrary pages.
Pitfalls and compiler errors
Pagination code runs into a few common traps.
If you use i32 for page numbers but your database driver expects u64, you'll hit E0308 (mismatched types) when passing arguments to the query. Rust catches this at compile time. Use the correct integer type for your database bindings.
If you forget to order your results, pagination becomes non-deterministic. The database might return rows in any order. Page 2 could contain items from page 1. Always include ORDER BY in your query. The order must match the pagination logic.
If you calculate total_pages using integer division, you might get zero for small datasets. Use (total + limit - 1) / limit to round up. Or use a library function that handles ceiling division.
Convention aside: Return total_pages and current_page in the response. Clients need this to build page controls. Don't make the client guess how many pages exist.
Decision matrix
Pick the pagination strategy that matches your data and your UI.
Use offset pagination when the dataset is small or static and users need to jump to arbitrary pages. It supports "Go to page 50" naturally. The performance cost is acceptable for thousands of rows.
Use keyset pagination when you have millions of rows and performance matters. Keyset uses the last ID from the previous page to fetch the next batch. It avoids scanning discarded rows. It only supports "Next" and "Previous", no random access.
Use cursor pagination when building an infinite scroll feed. Cursors are opaque tokens that encapsulate the keyset logic. They hide implementation details from the client. The server decodes the token to get the position. This allows you to change the pagination strategy without breaking the API.
Offset is cheap until it isn't. Switch to keyset before the profiler screams.