title: Smart pagination algorithm that works with local data cache
body: <p>This is a problem I have been thinking about for a long time but I haven't written any code yet because I first want to solve some general problems I am struggling with. This is the main one.</p>
  
  <h2>Background</h2>
  
  <p>A single page web application makes requests for data to some remote API (which is under our control). It then stores this data in a local cache and serves pages from there. Ideally, the app remains fully functional when offline, including the ability to create new objects.</p>
  
  <h2>Constraints</h2>
  
  <ul>
  <li>Assume a server side database of products containing +- 50000 products (50Mb)</li>
  <li>Assume no db type, we interact with it via REST/GraphQL interface </li>
  <li>Assume a single product record is &lt; 1kB</li>
  <li>Assume a max payload for a resultset of 256kB</li>
  <li>Assume max 5MB storage on the client </li>
  <li>Assume search result sets ranging between 0 ... 5000 items per search</li>
  </ul>
  
  <h2>Challenge</h2>
  
  <p>The challenge is to define a stateless but (network) efficient way fetch pages from a result set so that it is deterministic which results we will get.</p>
  
  <h2>Example</h2>
  
  <p>In traditional paging, when getting the next 100 results for some query using this url:</p>
  
  <pre><code>https://example.com/products?category=shoes&amp;firstResult=100&amp;pageSize=100
  </code></pre>
  
  <p>the search result may look like this:</p>
  
  <pre><code>{
    "totalResults": 2458,
    "firstResult": 100,
    "pageSize": 100,
    "results": [
      {"some": "item"},
      {"some": "other item"},
      // 98 more ...
    ]
  }
  </code></pre>
  
  <p>The problem with this is that there is no way, based on this information, to get <em>exactly</em> the objects that are on a certain page. Because by the time we request the next page, the result set may have changed (due to changes in the DB), influencing which items are part of the result set. Even a small change can have a big impact: one item removed from the DB, that happened to be on page 0 of the result set, will change what results we will get when requesting all subsequent pages.</p>
  
  <h2>Goal</h2>
  
  <p>I am looking for a mechanism to make the definition of the result set independent of future database changes, so if someone was looking for shoes and got a result set of 2458 items, he could actually fetch all pages of that result set reliably even if it got influenced by later changes in the DB (I plan to not really delete items, but set a removed flag on them, for this purpose)</p>
  
  <h2>Ideas so far</h2>
  
  <p>I have seen a solution where the result set included a <code>"pages"</code> property, which was an array with the first and last id of the items in that page. Assuming your IDs keep going up in number and you don't really delete items from the DB ever, the number of items between two IDs is constant. Meaning the app could get all items between those two IDs and always get the exact same items back. The problem with this solution is that it only works if the list is sorted in ID order... I need custom sorting options.</p>
  
  <p>The only way I have come up with for now is to just send a list of all IDs in the result set... That way pages can be fetched by doing a <code>SELECT * FROM products WHERE id IN (3,4,6,9,...)</code>... but this feels rather inelegant...</p>
  
  <p>Any way I am hoping it is not too broad or theoretical. I have a web-based DB, just no good idea on how to do paging with it. I am looking for answers that help me in a direction to learn, not full solutions.</p>