How to paginate > 10000 records. SOQL OFFSET and StandardSetController too limiting

There is a good article about pagination in Apex here: https://developer.salesforce.com/page/Paginating_Data_for_Force.com_Applications that lists various options.

You have two options for pagination inside of Visualforce pages, OFFSET pagination, and StandardSetController pagination. With OFFSET pagination you are limited to 2,000 records; however, with a StandardSetController you can paginate up to 10,000. So, one thing you could do would be to refactor to use the StandardSetController pagination.

Another thing that you could do, would be to add some sort of filtering capability, so that the user can get to the records they need. For example, if you would return more than 2,000 records, display a message saying something like "Showing 2,000 records but there could be more. Please use a filter to narrow down the results."

I would look into using StandardSetController, see the docs here. This does not make it unlimited however, but it ups the limit from 2,000 to 10,000, which might help. In general, I try to make filters on the page to allow the end user to filter down the size of returned results.

Here are a few blog posts about using StandardSetController for pagination. Hope that helps.

http://forceguru.blogspot.com/2011/04/pagination-in-salesforce.html

http://hisrinu.wordpress.com/2012/01/09/pagination-using-standardsetcontroller/