The computer programming field is plagued by a tendency that needs a name. What do you call it when the blogosphere pressures you to reject all these time-honored, perfectly good techniques, because of some edge case in a niche field that you will never have to deal with? Like the programmer ensconced in some corporation writing a business app that will only ever be used by 100 people. He rejects using just one server, because that couldn't possibly be enough, you must at least separate your web server from your database. He uses NoSQL instead of SQL because SQL doesn't scale. He uses React and a single-page application, because old-fashioned HTML and jQuery will get way out of hand --- even though he's just printing some tables and maybe some graphs, based on choices made in a form. And so his solution is worse than all of the problems he's trying to avoid combined. And none of them were going to come to pass anyway. Okay, this was an extreme example.
However, I see the same tendency at work in the demonization of poor old LIMIT and OFFSET. Let me talk about OFFSET first. This article talks about its inefficiency. Well, it may be inefficient, but every page I've generated with OFFSET still comes back in a split second. So coding around it seems to me like a premature optimization. Now let's talk about LIMIT. It's limited in reliability, because what if someone inserts while you're paging along? The item at the end of page 3 is now at the top of page 4! More insidiously, what if someone deletes? Then the top result on page 4 becomes the last result on page 3, and you never see it.
That scenario troubles my perfectionistic mindset, but I have to say in all my apps it is not the end of the world. I run into this all the time anyway on other people's websites, major and minor: whether I'm paging through search results on Google and Amazon or the latest posts on some web forum and even Hacker News. It's no big deal. At least, for my purposes it doesn't seem worth doing one of the other methods I read about here.
Now if you are programming a self-driving car or automating the mixture of medicines, maybe be careful where you use LIMIT and OFFSET.
The article isn't "demonizing" LIMIT/OFFSET. It's very clear about the use cases:
> When to Use: Limit-offset
> Applications with restricted pagination depth and tolerant of result inconsistencies.
I don't agree with your conclusion that result inconsistencies are "not a big deal" for most cases. It's a deliberate trade off. For example, I think it is annoying on Hacker News, but I understand why they chose to show inconsistent results (If I recall correctly, HN used to have consistent pagination using something similar to cursors, but they threw it out because it caused too much server load and it was annoying when sessions expired)
On the other hand, when my accountant goes through my expenses one by one to check if they have been booked correctly, I don't want him to miss lines due to inconsistent pagination.
Just because a technique is "time-honored", it doesn't mean it's "perfectly good" in every situation, or even in most situations. You always need to evaluate your techniques, no matter how common they are, to see if they work for your particular use case.
This is the syndrome I was getting at. An article attacks a shortcoming of an established way of doing things and then glosses over the deficiencies of its own alternative. The deficiencies of the original solution aren't a problem for most people, but the deficiencies of the new one are.
This post doesn't reject limit, offset -- it's just that you shouldn't use it if your data is very deep or if you need hard consistency. It's nice that you live in a world where this stuff doesn't matter though. Congrats on not having these requirements.
> That scenario troubles my perfectionistic mindset, but I have to say in all my apps it is not the end of the world. I run into this all the time anyway on other people's websites, major and minor: whether I'm paging through search results on Google and Amazon or the latest posts on some web forum and even Hacker News. It's no big deal. At least, for my purposes it doesn't seem worth doing one of the other methods I read about here.
While I agree with nearly 100% of what you're saying, I have run into situations where an application requires an infinite-scroll style of pagination, where the typical LIMIT/OFFSET approach can be problematic: users end up seeing duplicates while scrolling due to the ever-shifting boundaries, which seems to bother them more than if it was a completely separate page that they reloaded and saw the same duplicates.
As such, in any situation where we encounter an infinite-scroll pagination setup (which is quite common on mobile applications) we've implemented keyset-based pagination. It requires a modicum of additional thought to ensure it is correct, especially if you have very… interesting sort conditions, but ends up being quite bulletproof once implemented.
> Now if you are programming a self-driving car or automating the mixture of medicines, maybe be careful where you use LIMIT and OFFSET.
There are plenty of cases where the LIMIT/OFFSET behavior is not great. More boring example: you’re displaying a paginated list of financial transactions and you don’t want duplicates to appear if the list gets updated. Or any kind of audit log. Or an event feed, or infinite scroll.
I think the term you're looking for is cargo cult programming.
Sure, applying keyset pagination prematurely can be a form of cargo cult programming, but OTOH I'd rather work with somebody who is aware of the different pagination options available. Hopefully that same person can also choses the right implementation for the right problem, but that's sometimes easier said than done.
> What do you call it when the blogosphere pressures you to reject all these time-honored, perfectly good techniques, because of some edge case in a niche field that you will never have to deal with?
At first the term "over-engineering" came to my mind, but that term doesn't quite catch it.
The tendency I was thinking of isn't chiefly about chasing the new shiny thing (Magpie programming) or mindlessly including needless libraries (Cargo Cult programming).
The thing I'm thinking of is when someone points out a shortcoming of a particular way of doing things. Like, "If you use a relational database, it might go down if you get 50 million writes at once."
All techniques have trade-offs. So it's no surprise that some established way of doing things has one. The criticism is valid. The thing will fail in that situation. The problem is that the alternative presented by the blogger has more problems than the first. It would be useful in a rare kind of job. But that's not made clear, or the reader can't see past the stain that was shown on the old way of doing things, or the reader can't get past how cool it is that there's a database that can handle 50 million simultaneous writes, or the mere novelty is intoxicating (so there is some overlap with Magpie programming).
This new way doesn't have the first one's shortcoming, but it is literally 10 or 100 times as much work to set up, is missing certain important features that the original solution had, and solves a problem that the reader will never have.
I think this might be a communication problem, where more mature technology needs to be framed differently in order to reach a larger audience with a different background. Maybe this is a direct result of the technical complexities involved, so each engineering audience has a specific culture/lingo that needs to be taken into account when "selling" them a piece of technology or a certain approach.
As other points out, the article doesn't demonize LIMIT/OFFSET at all. In fact, it's offered as one way to paginate. That said, there's a good reason why it's worth demonizing.
First, LIMIT/OFFSET will not give you a transactionally consistent view of the data. If anyone inserts or deletes rows while you're paginating, you'll get dupes or holes. So already you're on thin ice. Good, as the article points out, for stateless pagination in Web 2.0-style web views, but problematic for any application that needs a consistent view (e.g. infinite scroll, or if you're, say, indexing everything into a search engine). Developers might easily miss this.
More importantly, LIMIT/OFFSET doesn't scale to particularly large datasets, and it's one of those things that will bite you at the worst possible time — i.e. when the size of your application plummets over a certain performance threshold that suddenly causes lots of queries to pile up because they're all at OFFSET 10000000. (Watch out for Googlebot paginating everything to infinity!) Since LIMIT/OFFSET requires the result set to be sorted on every query, this paves the way for some truly terrible query plans. If you're lucky, you'll get a fairly efficient index scan, but if you have joins and subqueries, things can get impossibly slow.
I'm currently working on an application where even the "SELECT ... WHERE id > :last_id ORDER BY id LIMIT :page_size" trick is failing me, with queries taking 30-60 seconds because there are many going on concurrently. The entire table is maybe 10 million records, and the WHERE is very selective (i.e. it's looking at a fairly small portion of the full table), but it's still a problem. LIMIT/OFFSET worked back when we had just a few hundred thousand rows in that table.
Whether you're talking about LIMIT/OFFSET or ORDER BY it's still an important thing to be aware of. The crux of the matter is being aware of how much data you're forcing the database to sort to get your end result.
If you're expecting 100 results back from a few million records, just being aware that if you don't trim the results in your WHERE clause it will force processing on that ORDER on a lot of records you're never going to see is a big step.
Totally agree in regards to premature optimization that LIMIT/OFFSET is tremendously more convenient. When it becomes a problem, it's good to know where to look.
Skill set in the team, supportability, experience etc are all just as important as which technology to choose.
And so if someone knows React better and are able to deliver business value quicker then how is that not a good thing. These days it's far easier to find people with React experience than JQuery experience. Likewise for NoSQL or whatever other technology.
Pretty insulting to assume that everyone who uses React, Redis, Cassandra etc are all only driven by blogs and not by any rationale thought.
However, I see the same tendency at work in the demonization of poor old LIMIT and OFFSET. Let me talk about OFFSET first. This article talks about its inefficiency. Well, it may be inefficient, but every page I've generated with OFFSET still comes back in a split second. So coding around it seems to me like a premature optimization. Now let's talk about LIMIT. It's limited in reliability, because what if someone inserts while you're paging along? The item at the end of page 3 is now at the top of page 4! More insidiously, what if someone deletes? Then the top result on page 4 becomes the last result on page 3, and you never see it.
That scenario troubles my perfectionistic mindset, but I have to say in all my apps it is not the end of the world. I run into this all the time anyway on other people's websites, major and minor: whether I'm paging through search results on Google and Amazon or the latest posts on some web forum and even Hacker News. It's no big deal. At least, for my purposes it doesn't seem worth doing one of the other methods I read about here.
Now if you are programming a self-driving car or automating the mixture of medicines, maybe be careful where you use LIMIT and OFFSET.