Offset-Based Pagination in Hibernate: Performance Pitfalls and Keyset Alternatives

You’re navigating through a long list of issues in Jiraalign, and the first page loads instantly. Jump to page 100, and the spinner drags. This isn’t a network issue or a JVM memory problem—it’s how offset-based pagination works under the hood. The takeaway is simple: offset pagination is O(n). To give you the 101st page, the database must fetch, count, and discard the first 100 pages of data before returning the slice you actually requested. As the offset increases, the workload on the database grows linearly.

Hibernate acts as an abstraction layer. When you call setFirstResult(offset) and setMaxResults(limit), Hibernate relies on the configured Dialect—a set of rules that tells Hibernate how to write SQL for a specific database such as PostgreSQL, MySQL, or Oracle. Depending on the dialect, Hibernate generates different SQL: MySQL and PostgreSQL use LIMIT and OFFSET. Older Oracle versions use nested queries with ROWNUM. SQL Server uses OFFSET...FETCH NEXT. Regardless of the syntax, the underlying engine still scans the index or table from the beginning to reach the offset. If you are skipping 50,000 rows, the database engine is still processing those 50,000 rows internally.

A common mistake is implementing pagination without an explicit ORDER BY clause in the HQL or Criteria query. In a relational database, rows have no inherent order. Without a sort key, the database returns rows in whatever order is most convenient at that moment. If the data changes between a user clicking Page 1 and Page 2, the database may shift rows. This leads to the duplicate row or missing row phenomenon, where a record that appeared at the bottom of Page 1 suddenly appears at the top of Page 2.

Worked Example: Keyset Pagination with Hibernate

// OFFSET APPROACH (Slows down as page increases) query.setFirstResult(10000); query.setMaxResults(20); // SQL: SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000; // KEYSET APPROACH (Constant performance) long lastSeenId = 10000; query.setEntity(Order.class); query.where(cb.gt(root.get('id'), lastSeenId)); query.setMaxResults(20); // SQL: SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;

In the keyset example, the database uses the primary key index to jump directly to the first record greater than 10,000, bypassing the need to scan the preceding rows entirely.

Trade-Offs and Limitations

• No random access: users cannot jump directly to Page 50. They can only navigate Next and Previous.
• Strict ordering: you must sort by a column, or combination of columns, that is unique and indexed. Sorting by a non-unique column such as Last Name requires adding the primary key as a secondary sort to ensure stability.
• Complexity: implementing Previous navigation requires reversing the sort order and the comparison operator (less than instead of greater than).
• Mixed query styles: combining Hibernate Criteria with native SQL pagination can produce mismatched row counts because the two paths may generate different LIMIT and OFFSET behavior. Keep pagination logic in one layer.

Verifying the Fix

1. Enable SQL logging: set hibernate.format_sql=true and hibernate.show_sql=true in your properties file.
2. Check the generated SQL: ensure that your keyset implementation is producing a WHERE clause targeting the index rather than an OFFSET clause.
3. Analyze the execution plan: run the generated SQL directly in your database console using EXPLAIN ANALYZE on PostgreSQL or EXPLAIN on MySQL. Look for an index scan versus a full table scan or parallel sequential scan.
4. Write a regression test: insert a known number of entities, iterate pages using setFirstResult and setMaxResults, and assert that the union of all pages equals the total count across the Hibernate versions you support.

By shifting to keyset pagination, you trade random-page access for consistent performance, which is often the right call for dashboards and export features.