title: mongodb caching with aggregation pipeline
body: <p>mongodb aggregate pipeline like</p>
  
  <pre><code>db.testing.aggregate(
      { 
      $match : {hosting : "aws.amazon.com"}
      },
      { 
      $group : { _id : "$hosting", total : { $sum : 1 } }
      },
      {
      $project : { title : 1 , author : 1, &lt;few other transformations&gt; }
      {$sort : {total : -1}}
  );
  </code></pre>
  
  <p>Now I want to enable paging. I have 2 options.</p>
  
  <ol>
  <li><p>Use skip and limit in the pipeline.</p>
  
  <pre><code>{ $skip : pageNumber * pageSize }
  { $limit : pageSize }
  </code></pre></li>
  </ol>
  
  <p>External API level caching for each page can be used which will reduce time for repeated loading of same pages, but the first loading of each page will be painful because of the linear scan due to sorting.  </p>
  
  <ol start="2">
  <li>Handle pagination in application.
  
  <ul>
  <li>Cache the findAll result i.e for List findAll();</li>
  <li>Now pagination will be handled at the service layer and result will be published</li>
  <li>From next request onward you will be referring to the cached result and send the desired set of records from the cache.</li>
  </ul></li>
  </ol>
  
  <p>Question: 2nd approach seems better if database is not doing some magical optimizations. In 1st, my view is that since the pipeline involves sorting, hence every page request will do a scan of the full table, which will be sub-optimal. What are your views? Which one should be done? What would you choose? What is the good practice(Is moving some db logic to service layer for optimizations advisable)?</p>