Could you elaborate on that? What's "properly"? Do you mean that they don't do it at all, or they do it in memory instead of in an indexed query? Or is there a technique here that I'm missing?
You almost never need N rows. N rows are bad because Python has to parse them. Odds are, there's a way to get a constant number of rows back for every query in every view of your webapp.
By "N rows", do you mean having a view that only renders a constant number of rows, but your database query returns an larger collection that's then filtered in Python code? That seems like an obvious bug that can be usually fixed with a LIMIT clause.
SELECT *
FROM t
OFFSET 1000 LIMIT 10; -- does not use index, scans table
SELECT *
FROM t
WHERE id between 1000 and 1010; -- uses index
It's explained in your linked slides that LIMIT is ok, it's the offset that you need to worry about so your query looks like it would be better written:
SELECT *
FROM t
WHERE id > 1000 LIMIT 10;From there, given the number of items per page, you can trivially determine which items to display given a page number or which page to display given an item.idx. And the query uses the efficient WHERE.
Of course, the last_seen pagination is only usable for pagination where users aren't deep linking into pages (like HN).
Plus, as stated in the slideshow, you can't provide jumps to arbitrary pages. In some cases this doesn't matter, but worth taking note of.
(pagination is frustrating!)