Ways to paginate in Postgres (2016)
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.