Implementing Offset Pagination with Quarkus Panache: Practical Guide

Useful answer

Use PanacheQuery<T> from an entity or a PanacheRepository, call .page(Page.of(index, size)) (zero‑based index), then .list() to fetch the page and .count() (or .pageCount()) for pagination metadata. Return a small DTO from your REST endpoint instead of the raw entity to avoid lazy‑loading surprises.

Worked example

The following snippets assume a Quarkus 3.x project with the quarkus-hibernate-orm-panache extension and a PostgreSQL dialect (the generated SQL uses LIMIT/OFFSET).

Entity

@Entity
public class Person extends PanacheEntityBase {
    @Id @GeneratedValue
    public Long id;
    public String name;
    public Status status;
    public LocalDate createdAt;
}

Repository (optional)

@ApplicationScoped
public class PersonRepository implements PanacheRepository<Person> {}

Service method

@ApplicationScoped
public class PersonService {
    @Inject
    PersonRepository repo;

    public PageResult<PersonDto> findAlive(int pageIndex, int pageSize) {
        // deterministic ordering is mandatory
        PanacheQuery<Person> query = repo.find("status", Status.Alive)
                .orderBy("createdAt, id");
        query.page(Page.of(pageIndex, pageSize));
        List<Person> page = query.list();
        long total = query.count(); // reuses the same WHERE clause
        return new PageResult<>(
                page.stream().map(this::toDto).toList(),
                pageIndex,
                pageSize,
                total,
                query.pageCount()
        );
    }

    private PersonDto toDto(Person p) {
        return new PersonDto(p.id, p.name, p.status, p.createdAt);
    }
}

REST endpoint

@Path("/people")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public class PersonResource {
    @Inject
    PersonService service;

    @GET
    public PageResult<PersonDto> list(
            @QueryParam("page") @DefaultValue("0") int page,
            @QueryParam("size") @DefaultValue("20") int size) {
        return service.findAlive(page, size);
    }
}

DTO and pagination wrapper

public record PersonDto(Long id, String name, Status status, LocalDate createdAt) {}

public record PageResult<T>(
        List<T> items,
        int pageIndex,
        int pageSize,
        long totalElements,
        int totalPages
) {}

How it works

1. repo.find(...) builds a PanacheQuery that translates the HQL/JPQL fragment into SQL.
2. .page(Page.of(index, size)) adds a LIMIT size OFFSET (index * size) clause (dialect‑specific).
3. .list() executes the paged query.
4. .count() runs a separate SELECT COUNT(*) … with the same WHERE and ORDER BY so the total stays consistent with the page contents.

Dev Services integration

When no quarkus.datasource.jdbc.url is set, Quarkus Dev Services automatically starts a PostgreSQL (or other) container in dev mode and in @QuarkusTest. The pagination code above runs against that container without any extra configuration.

Verification steps

• Enable SQL logging – add to src/main/resources/application.properties:

  quarkus.hibernate-orm.log.sql=true
  quarkus.hibernate-orm.log.format_sql=true

  Run the app in dev mode (./mvnw quarkus:dev) and inspect the console for the generated LIMIT/OFFSET statements.
• Unit test – create a @QuarkusTest that inserts 50 Person rows, requests page 1 with size 10, and asserts:
  • returned list size == 10
  • totalElements == 50
  • pages do not overlap (collect IDs from consecutive pages and ensure uniqueness).
• Deep‑page benchmark – on a production‑like dataset, request page 10 000 (size 20) and measure latency. If the query scans the full offset, consider keyset pagination (see limits).

Limits and common mistakes

Deterministic ordering

Without a unique ORDER BY (e.g., createdAt, id) the database may return rows in any order, causing duplicate or missing items across pages.

Zero‑based page index

Panache uses zero‑based indexes. UI components that are one‑based must subtract one before calling the service.

Count query cost

count() executes a separate query. On very large tables this can dominate response time. Options:

• Cache the total for a short TTL if the data changes infrequently.
• Use an approximate count (e.g., pg_class.reltuples on PostgreSQL) when exactness is not required.

Offset pagination degradation

Deep offsets require the database to scan and discard preceding rows. On tables with millions of rows and frequent writes, this leads to high latency and possible row skipping/duplication under concurrent modifications. The usual remedy is keyset (seek) pagination on a stable, indexed column, but Panache does not provide a built‑in API for it; you must write the query manually (e.g., WHERE createdAt > ? AND id > ? ORDER BY createdAt, id LIMIT ?).

Version‑specific API names

Method names such as Page.of vs Page.ofSize have changed between Quarkus 2.x and 3.x. Verify the exact signatures against the version on your classpath (e.g., io.quarkus.panache.common.Page).

Practical checklist

• Add a unique ORDER BY to every paginated query.
• Map UI page numbers (1‑based) to Panache’s zero‑based index.
• Return a DTO wrapper (PageResult) instead of entities.
• Enable SQL logging during development to confirm generated LIMIT/OFFSET.
• Write a @QuarkusTest that validates page boundaries and total count.
• Benchmark deep pages; if latency is unacceptable, implement keyset pagination manually.