Phoenix Pagination: Why OFFSET-LIMIT Slows Down and How Keyset Saves the Day

The Pagination Problem in Phoenix

When building applications that display large datasets—think user directories, transaction logs, or product catalogs—you need pagination. Apache Phoenix supports SQL OFFSET-LIMIT syntax, but using it naively can cripple performance as you move deeper into result sets. The issue isn't just theoretical; it's a real bottleneck in production systems.

How Phoenix Executes OFFSET-LIMIT

Phoenix translates your SQL OFFSET-LIMIT query into HBase filter chains that scan row keys sequentially. When you request SELECT * FROM table LIMIT 10 OFFSET 10000, Phoenix must scan through the first 10,000 rows to skip them, then return the next 10. This linear scan overhead grows proportionally with the offset size.

Keyset Pagination: The Efficient Alternative

Instead of counting rows, keyset pagination uses the last seen value from a previous page as a cursor. In Phoenix, this translates to a WHERE clause on an indexed column: SELECT * FROM users WHERE id > 12345 ORDER BY id LIMIT 10. Because Phoenix leverages the row key ordering and any secondary indexes, this query jumps directly to the starting point without scanning skipped rows.

Worked Example: User Directory Pagination

Consider a user table with an auto-incrementing primary key. Here's how to implement both approaches:

-- OFFSET-LIMIT approach (inefficient for deep pages)
SELECT * FROM users 
ORDER BY user_id 
LIMIT 20 OFFSET 5000;

-- Keyset approach (efficient for any page)
SELECT * FROM users 
WHERE user_id > 15432 
ORDER BY user_id 
LIMIT 20;

The keyset version requires tracking the last user_id from the previous page. This value becomes your cursor for the next query. In Phoenix 5.x, if you have a secondary index on a more selective column (like email), you could use that as your cursor instead.

Verifying Performance Differences

Run EXPLAIN on both queries to see the execution plan. The OFFSET-LIMIT version will show a full scan starting from the beginning, while keyset should show a direct seek to the cursor position. You can measure actual execution time with:

!time sqlline.py -u jdbc:phoenix:localhost:2181 -e "SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 50000"

Trade-offs and Limitations

Keyset pagination isn't a silver bullet. It requires:

• A stable, ordered column (typically the primary key or a secondary index)
• Client-side state management to track the cursor value
• Consistent data insertion patterns to avoid missing or duplicating rows

If your application needs to jump to arbitrary pages (like page 157 in a UI), OFFSET-LIMIT might still be necessary, but consider caching results or using a different pagination strategy for those edge cases.

Actionable Steps

1. Identify high-offset queries in your application logs
2. Add a secondary index on the column you'll use for keyset pagination if needed
3. Modify your API to accept cursor parameters instead of page numbers
4. Test with EXPLAIN to verify Phoenix uses the index for seeks

For most Phoenix applications handling large datasets, switching from OFFSET-LIMIT to keyset pagination will provide immediate performance gains without requiring schema changes.