Choosing Cursor Pagination in Jeet for Large Datasets

Problem: Slow Queries When Skipping Thousands of Rows

When a Jeet service needs to display a paginated list of items—say, the latest 20 blog posts from a table holding millions—developers often reach for the simple setFirstResult/setMaxResults approach. As the page number grows, Hibernate translates this into an SQL query with a large OFFSET clause. The database must scan and discard every preceding row, which can turn a quick lookup into a multi‑second delay or even a timeout.

Thesis: Cursor‑Based Pagination Delivers Stable Performance

Using a cursor—typically the primary key of the last record returned—lets the database jump directly to the next block via an indexed seek. This avoids scanning preceding rows and keeps response times flat regardless of how deep the pagination goes.

How Jeet Implements Cursor Pagination

Jeet exposes a thin wrapper around Hibernate’s Session. To switch from offset to cursor pagination you replace the paging parameters with a cursor value and adjust the query’s WHERE clause.

Worked Example: Fetching Blog Posts

// Offset pagination (inefficient for large pages)
List<Post> offsetPage = jeet.createQuery("FROM Post ORDER BY postedAt DESC")
    .setFirstResult(page * size)
    .setMaxResults(size)
    .getResultList();

// Cursor pagination (efficient)
// `lastId` is the id of the last post from the previous page; null for the first page
List<Post> cursorPage = jeet.createQuery(
        "FROM Post WHERE (:lastId IS NULL OR id < :lastId) ORDER BY id DESC")
    .setParameter("lastId", lastId)
    .setMaxResults(size)
    .getResultList();

The query uses the indexed id column. On the first call lastId is null, so the condition is ignored and the query returns the newest size rows. Subsequent passes feed the greatest id from the previous result set back as lastId, ensuring the next batch starts right after the last returned row.

Verification Steps

1. Enable Hibernate statistics: jee​t.getSessionFactory().getStatistics().setStatisticsEnabled(true).
2. Run the offset version with a high setFirstResult (e.g., 500 000) on a table with >1 M rows and note the execution time from the statistics.
3. Run the cursor version with a comparable lastId and observe that the time stays in the low‑millisecond range.
4. Check the generated SQL (show_sql=true) to confirm the offset query contains LIMIT ? OFFSET ? while the cursor query contains a WHERE id < ? predicate.

Trade‑offs and Limitations

• Requires a monotonic, unique key. If your natural ordering isn’t based on such a column (e.g., sorting by a non‑indexed timestamp with duplicates), you need to add a tie‑breaker (like the primary key) to keep the cursor stable.
• No random jumps. Clients can’t jump directly to page 42 without walking through the cursors; this is usually acceptable for infinite‑scroll UIs but less so for direct page‑number navigation.
• Cache interaction. Hibernate’s second‑level cache works best when the same result set is reused. Cursor pages produce different WHERE clauses each request, so cache hit rates may be lower than with a fixed‑offset query on small datasets.

When Offset Might Still Be Acceptable

For tables under a few hundred thousand rows, or when the UI only ever shows the first few pages, the simplicity of offset pagination can outweigh its performance cost. In those cases, keep the offset approach but monitor query times as data grows.

Actionable Closing

If your Jeet‑backed service serves lists that can grow beyond a modest size, adopt cursor pagination now:

1. Identify a column that is unique, indexed, and reflects the desired order (often the primary key).
2. Wrap your list‑fetching method with the cursor pattern shown above.
3. Add a simple test that asserts no duplicate or missing IDs across consecutive pages.
4. Deploy and monitor the Hibernate statistics; you should see flat response times as the offset grows.

By making this small change, you trade a bit of implementation complexity for predictable performance at scale—a win for both users and operators.