Technically, I agree that limit/offset paradigm is flawed, however, the author just dismisses a valid criticism of keyset pagination with the following:
"keyset pagination has some limitations: most notably that you cannot directly navigate to arbitrary pages. However, this is not a problem when using infinite scrolling. Showing page number to click on is a poor navigation interface anyway—IMHO."
Sorry, but NO. Infinite scrolling sucks, because it fills the page (and therefore memory) with lots and lots of entries, and disallows me to quickly navigate to exact place in the view I need. If I know that what I seek was approximately on page 12 of last search result, I will go straight there (1 click) and navigate from here. I hate downloading the entire history and scrolling from the beginning.
Infinite scrolling is fine the first time, but on repeated visits is just not an option and destroys everybody's valuable time.
I agree that infinite scrolling is terrible, but pagination based on nothing more than the rank isn't great either. Why do I need to guess that names starting with a 'G' could be on page 12 (no 14, no 13)? Just paginate by the first one or two letters then. When doing so, you can benefit from database indices again while improving usability.
The old Right Stuf website catalog [1] (old catalog not accessible) would list their pages with the first two letters of the first item's title on the page. I found it immensely helpful, and even "borrowed" the idea for a directory on a local govt website (also no longer accessible).
I'm sure he means infscroll using keyset pagination.
The UX, as noted, is ugly - have to somehow support scrolling in both directions with some sort of lazy-loading - but pagination in this context doesn't === offset-based pagination.
> If I know that what I seek was approximately on page 12 of last search result, I will go straight there (1 click) and navigate from here.
How do you know it's it's still on 12 and not 13 or 11 if the results changed? Wouldn't a better filter or other navigation be more useful?
> Infinite scrolling is fine the first time, but on repeated visits is just not an option and destroys everybody's valuable time.
To me this doesn't make sense. I can't think of a time where I've repeatedly gone to a specific page on results and where I wasn't able to filter for what I was looking for instead.
I'm not sure I understand this use case. I'm on twitter, I see a tweet I'm interested in. I leave, come back, and then try to find that same tweet by browsing for it.
Wouldn't it be simpler to save the tweet the first time around or if not, since I know the tweet I'm looking for, search for it where I'd get the exact result?
It would be nice if the article could also link to the original announcements of stuff like the parallel execution, with clause support etc (it does link it on one or two occasions). It's a bit sparse on details and this would give people the opportunity to dive deeper.
To each their own. The fact that CTEs are optimization fences in PostgreSQL is one of its dearest features for me: it allows me to insist on a particular execution plan by lifting an index scan into its own CTE. This is important specifically when there's a lot of data and I can't take a chance with the planner getting it wrong.
This is why I wish PostgreSQL just had explicit hints. Their argument against is sensible as it does increase the maintenance cost, but when features become defacto planner hints then readability suffers more.
I'm surely not the first to think of this idea: but wouldn't it be nice if we could just select from an index directly? Postgresql already represents indices as relations. Then we don't need to make comments executable, we preserve the spirit of SQL semantics, and give the user control to insist on a particular index.
It was different in a few ways, but I used to do that with db2 on the as/400, you had physical files (tables) and logical files (indexes) and you could select directly from the logical files - but they were more like materialized views that were sorted in a particular way than exactly an index.
That's not how it's supposed to work. You want DBAs to be able to tune the existing data model with indexes and materialized views. The optimizer should be smart enough to rewrite queries and, in your case, look up the necessary data from the index only. I don't know Postgresql, Oracle's optimizer has been doing that for years.
Not being on the hype train is a strange characterization - their last release was over two years ago, and its not like its just not popular in certain circles - there are less than 500 questions on SO for ingress verses almost 50k for postgres. Are you saying that it deserves more attention? As much as mysql/postgres/mssql and others in this article?
"keyset pagination has some limitations: most notably that you cannot directly navigate to arbitrary pages. However, this is not a problem when using infinite scrolling. Showing page number to click on is a poor navigation interface anyway—IMHO."
Sorry, but NO. Infinite scrolling sucks, because it fills the page (and therefore memory) with lots and lots of entries, and disallows me to quickly navigate to exact place in the view I need. If I know that what I seek was approximately on page 12 of last search result, I will go straight there (1 click) and navigate from here. I hate downloading the entire history and scrolling from the beginning.
Infinite scrolling is fine the first time, but on repeated visits is just not an option and destroys everybody's valuable time.