Pagination With Keyset Queries in Rust and SQLx Step by Step
OFFSET pagination slows down and skips rows as data changes. Keyset pagination remembers the last key and stays fast. Build it with SQLx, including the tie-breaker and the cursor.
Page 1 of a feed returns in 4 ms. Page 5,000 takes seconds, and rows appear twice because new posts shifted everything while the user was scrolling. Both problems come from OFFSET. Keyset pagination replaces "skip n rows" with "start after this row", which is faster and stable. This post builds it with SQLx. It sits with the query posts in SQLx explained.
sqlx 0.8, PostgreSQL 17, Rust 1.90.
The query
CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC);SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3;The first page omits the WHERE. The index lets PostgreSQL walk straight to the cursor position and read LIMIT rows. Without the index on both columns, the planner falls back to sorting. Verify with EXPLAIN (ANALYZE) that it reads about LIMIT rows.
In Rust
use chrono::{DateTime, Utc};
use sqlx::PgPool;
#[derive(Debug, sqlx::FromRow)]
pub struct Post { pub id: i64, pub title: String, pub created_at: DateTime<Utc> }
pub struct Page { pub items: Vec<Post>, pub next: Option<(DateTime<Utc>, i64)> }
pub async fn feed(
pool: &PgPool,
after: Option<(DateTime<Utc>, i64)>,
limit: i64,
) -> Result<Page, sqlx::Error> {
let mut rows: Vec<Post> = match after {
Some((ts, id)) => {
sqlx::query_as(
"SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT $3",
)
.bind(ts).bind(id).bind(limit + 1)
.fetch_all(pool).await?
}
None => {
sqlx::query_as(
"SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC LIMIT $1",
)
.bind(limit + 1)
.fetch_all(pool).await?
}
};
let has_more = rows.len() as i64 > limit;
if has_more {
rows.truncate(limit as usize);
}
let next = if has_more { rows.last().map(|p| (p.created_at, p.id)) } else { None };
Ok(Page { items: rows, next })
}Asking for limit + 1 rows answers "is there another page" without a count query. The cursor is the last returned row's sort key.
The cursor
Return it as one string, so clients treat it as opaque:
fn encode(ts: DateTime<Utc>, id: i64) -> String {
use base64::{engine::general_purpose::URL_SAFE_NO_PAD, Engine};
URL_SAFE_NO_PAD.encode(format!("{}|{}", ts.timestamp_micros(), id))
}Decoding must validate: malformed cursors return a 400, not a panic. Microsecond precision matters, because a cursor rounded to milliseconds can skip rows that share a millisecond.
Why the tie-breaker
If two posts share created_at, ordering by created_at alone is ambiguous, and a page boundary between them loses one. id makes the order total. Any unique column works.
Limits
No random page jumps, no total count, and every sort option needs its own index. Sorting by a nullable column needs NULLS LAST handling in both the ORDER BY and the comparison. Background on the cost of OFFSET is in the PostgreSQL docs on LIMIT and OFFSET.
