Hacker News Reader

Comment by awj | Hacker News Reader

That's great if you need to do some kind of crawl through the DB for background work, but fails to support key elements (filtering, non-primary-key sorting) that are probably hard requirements at the API level.

View on HN

You can still support arbitrary filters and ordering, as long as you always sort by the ID last. The nice thing is the queries actually get more and more efficient as you approach the last page. (The normal approaches have the same cost across all pages, or even get more expensive toward the latter pages.)

The only thing this approach does not support is jumping to an arbitrary page number.

Not really:

  SELECT *
   FROM sales
  WHERE (sale_date, sale_id) < (?, ?)
  ORDER BY sale_date DESC, sale_id DESC
  FETCH FIRST 10 ROWS ONLY

This is a postgresql example. See use-the-index-luke[0] for a generic approach.

[0] http://use-the-index-luke.com/sql/partial-results/fetch-next...

Reply on news.ycombinator.com