How does standard set controller work?
I want to know how StandardSetController actually works. My expectation on the insight is to understand how salesforce is handling the result set, in contrary with normal re-querying of records with offset and limits. How does the controller behave differently/better compare to a custom class implementation?
The StandardSetController uses a database cursor to paginate through the results. Unlike using LIMIT+OFFSET, this method supports 10,000 rows of data, the results will not change each time a new page is pulled back from the database (we call this a "consistent view" of the data), no custom code is required to implement paginating, page sizing, and saving modifications, and you can also mass edit records without custom code. However, being a cursor also means that it only remains viable for 15 minutes, after which a new query must be issued.
StandardSetController is typically better for naive paging implementations, since it requires the least amount of code and is trivial to use. The most powerful implementation of pagination I've ever written supported ~50,000 rows, an unlimited timeout, and a semi-consistent view (the rows were consistent but the field values were not), but did require ~100 lines of code, so it was fairly non-trivial in scope. Client-side pagination using remote actions or an API can support millions of rows with a stable view, if desired, but requires more memory and a rather significant amount of code. The StandardSetController offers the most functionality with the least amount of code needed to get up and running.