package com.example.feed;
import java.nio.charset.StandardCharsets;
import java.time.Instant;
import java.util.Base64;
public record Cursor(Instant createdAt, long id) {
public String encode() {
String raw = createdAt.toEpochMilli() + ":" + id;
return Base64.getUrlEncoder().withoutPadding()
.encodeToString(raw.getBytes(StandardCharsets.UTF_8));
}
public static Cursor decode(String token) {
try {
byte[] bytes = Base64.getUrlDecoder().decode(token);
String[] parts = new String(bytes, StandardCharsets.UTF_8).split(":", 2);
return new Cursor(Instant.ofEpochMilli(Long.parseLong(parts[0])),
Long.parseLong(parts[1]));
} catch (RuntimeException ex) {
throw new IllegalArgumentException("Malformed cursor token", ex);
}
}
public static Cursor of(Article article) {
return new Cursor(article.getCreatedAt(), article.getId());
}
}
package com.example.feed;
import java.time.Instant;
import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
public interface ArticleRepository extends JpaRepository<Article, Long> {
@Query("""
select a from Article a
where a.published = true
order by a.createdAt desc, a.id desc
""")
List<Article> findFirstPage(Pageable pageable);
@Query("""
select a from Article a
where a.published = true
and (a.createdAt < :createdAt
or (a.createdAt = :createdAt and a.id < :id))
order by a.createdAt desc, a.id desc
""")
List<Article> findAfter(@Param("createdAt") Instant createdAt,
@Param("id") long id,
Pageable pageable);
}
package com.example.feed;
import java.util.List;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class ArticleService {
private static final int MAX_LIMIT = 100;
private final ArticleRepository repository;
public ArticleService(ArticleRepository repository) {
this.repository = repository;
}
@Transactional(readOnly = true)
public CursorPage<Article> getPage(String cursorToken, int limit) {
int size = Math.min(Math.max(limit, 1), MAX_LIMIT);
Pageable probe = PageRequest.of(0, size + 1); // fetch one extra to detect next page
List<Article> rows;
if (cursorToken == null || cursorToken.isBlank()) {
rows = repository.findFirstPage(probe);
} else {
Cursor cursor = Cursor.decode(cursorToken);
rows = repository.findAfter(cursor.createdAt(), cursor.id(), probe);
}
boolean hasNext = rows.size() > size;
List<Article> page = hasNext ? rows.subList(0, size) : rows;
String nextCursor = null;
if (hasNext && !page.isEmpty()) {
nextCursor = Cursor.of(page.get(page.size() - 1)).encode();
}
return new CursorPage<>(page, nextCursor);
}
public record CursorPage<T>(List<T> items, String nextCursor) {
}
}
Offset pagination (LIMIT ? OFFSET ?) degrades badly on large tables because the database still scans and discards every skipped row, and it can silently skip or duplicate rows when data changes between page loads. Keyset pagination avoids both problems by remembering the last row seen and asking for rows after it, using a stable, indexed ordering. This set of files shows how to implement it end to end in a Spring Boot service.
The Cursor record in the first tab encapsulates the position between pages. Because the sort is on createdAt (which is not unique) the cursor also carries the row id as a tiebreaker, so the ordering is total and deterministic. Cursor.encode and Cursor.decode serialize this into an opaque URL-safe Base64 token; clients treat it as a blob and never construct it themselves, which keeps the ordering columns an implementation detail that can change later.
ArticleRepository defines two @Query methods. findFirstPage handles the initial request with no cursor, while findAfter implements the keyset predicate. The comparison (a.createdAt < :createdAt) OR (a.createdAt = :createdAt AND a.id < :id) is the row-value comparison expressed in JPQL — it selects everything strictly after the cursor under the compound (createdAt DESC, id DESC) ordering. A composite index on (created_at, id) lets Postgres jump straight to the start position, so each page costs the same regardless of how deep the client has paged.
Both queries request limit + 1 rows via Pageable. The service reads one extra row to detect whether a further page exists without a separate COUNT query, which is the other expensive part of classic pagination.
ArticleService.getPage in the third tab ties it together: it decodes the incoming cursor, picks the first-page or after-cursor query, trims the sentinel row, and builds the next cursor from the last returned entity. When fewer than limit + 1 rows come back, nextCursor is left null to signal the end. The returned CursorPage gives clients exactly what they need to fetch the following page.
The main trade-off is that keyset pagination only supports next/previous navigation, not jumping to an arbitrary page number, and the sort columns must be immutable and indexed. For infinite-scroll feeds and large datasets that fits perfectly.
Related snips
class PostsController < ApplicationController
def index
@posts = Post.includes(:author)
.order(created_at: :desc)
.page(params[:page])
.per(10)Turbo Frames: infinite scroll with lazy-loading frame
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
Rails.application.configure do
config.after_initialize do
Bullet.enable = true
Bullet.alert = false
Bullet.bullet_logger = true
Bullet.console = trueN+1 query detection with Bullet gem
json.array! @posts do |post|
json.cache! ['v1', post], expires_in: 1.hour do
json.id post.id
json.title post.title
json.excerpt post.excerpt
json.published_at post.published_atFragment caching for expensive JSON serialization
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_column :accounts, :settings, :jsonb, null: false, default: {}Postgres JSONB Partial Index for Feature Flags
Share this code
Here's the card — post it anywhere.
