4 ms·
This is cargo-cult programming based on an overreaction to OFFSET, which while inefficient, is not the only way to implement pagination. If you have data with a
by yowlingcat 7y ago
This is cargo-cult programming based on an overreaction to OFFSET, which while inefficient, is not the only way to implement pagination. If you have data with an auto-incrementing ID or timestamp and index on it, it's very easy to page arbitrarily. It's also very useful -- take any chat or news feed application. I don't want to lose my place in the feed just because there's some chattiness that occurred later on in time than my cursor is at.
The real answer to this question is to better understand how your database indexes your data and will try to use it to go and plan your queries, which it will very helpfully tell you. You want to avoid OFFSET to avoid a full-table scan? Great! You want to completely destroy a useful feature because you don't understand the way your database stores your data's intrinsic structure so as to more efficiently use it's very fast tree lookup? Better hold your horses there and engage your curiosity a bit more, because this is a common and efficiently solved problem.
- leeoniya 7y agofor many cases, paginating by a timestamp range is meaningful, performant and deterministic. though you lose the ability to view a fixed number of rows which can result in some awkward ui...but i guess this is when overflow-y: scroll can work around that.
- yowlingcat 7y agoCertainly true, although that pain is something that can be at least partially mitigated in the backend. You can use approaches that are similar to exponential backoff, just with LIMIT, so that you do end up filling up your page. While there's some annoying bookkeeping involved, it does work and it does scale.
- pmontra 7y agoDoing without OFFSET and by using an index field (an autoincrement id, a timestamp, etc) is feasible if there are no order by clauses. If you want to sort the columns of an on screen table and paginate it you need an index for each sort combination. It's quite a pain to keep it up to date. Edit: I run into this today because of a table with UUIDs and NULL values in a sort field. SELECT OFFSET 0 LIMIT 10 and SELECT OFFSET 10 LIMIT 10 returned overlapping results. I eventually added the record creation timestamp to the ORDER BY and all went well.
- yowlingcat 7y agoDefinitely a very real caveat, but in this case, you're breaking the monotonicity condition that makes paging this way very simple. As you describe, adding a little bit more context to restore the monotonicity condition makes all go well!
- balfirevic 7y ago> Better hold your horses there and engage your curiosity a bit more, because this is a common and efficiently solved problem. What's the solution?
- yowlingcat 7y agoUse the index. Understand the index.
- balfirevic 7y agoI use and understand them. What I don't understand is what kind of solution you're talking about, how it's different from the one recommended in the article, what exactly you mean by "page arbitrarily" and does that imply more capabilities than the solution offered in the article (where you remember the auto-increment-value/timestamp/sort-field of the last record and then request additional records based on that).
- yowlingcat 7y agoThe author references two methods in the post: one caches page boundaries and the other references using the seek method for keyset pagination. The former is brittle, and I am advocating for the latter.
- lukaseder 7y agoIt is solved with keyset pagination to be precise. "I don't want to lose my place in the feed" - well, you just lost it because of OFFSET.
- deleted 7y ago[deleted]
- yowlingcat 7y agoSure. I'm not advocating for OFFSET. I guess to be candid, I really didn't like the examples you picked. Arbitrary pagination is useful, and I don't understand why you spent so much time trying to attack it unconvincingly. Beyond that, in your post, you link to two approaches for keyset pagination: the seek method and the page boundary caching approach. I'd much rather use the seek method you link to and leave it at that, because it doesn't rely on the brittleness of row_number(), which is liable to change. Of course, this all depends on your use case -- as you point out, if your data is truly static, page boundary caching is unlikely to become dangerous.
- lukaseder 7y agoI'm not sure I get it. A row_number() based approach is the same thing as using OFFSET...