Big News in Databases | Hacker News Reader

Big News in Databases

use-the-index-luke.com

95 pointsMarkusWinand23 comments

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.

Isn't the issue there a lack of (usable) ability to have the computer remember what you were looking at in some reasonable manner?

I'm perfectly fine with my own memory for that. However, yes, bookmarking the place in the timeline would be a nice feature to have.

(Now if only Twitter, Facebook or Hacker News would have implemented this...)

> it fills the page (and therefore memory) with lots and lots of entries

Yet observables are all the rage. Additionally, can't you just move prior entries out, so the "scroll and fetch" goes both ways?

> disallows me to quickly navigate to exact place in the view I need

I've seen infinite scrolling that implements paging as well

In general I'll agree infinite scrolling isn't implemented well, and is usually just the result of dropping in a module and forgetting about UX.

> I've seen infinite scrolling that implements paging as well

Then you're using offsets, which is the crux of the disagreement regarding the utility of offsets.

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.

Twitter?

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?

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).

[1] http://web.archive.org/web/20071016095932/http://www.rightst...

Static Ice does this nicely. When you hover over the pagination links it tells you what price range is covered by the products on that page:

http://staticice.com.au/cgi-bin/search.cgi?q=laptop&spos=3

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.

I'll be interested to hear if MySQL's CTEs are optimization fences like in postgres (one of the few gripes I have with pg).

Agree. A great code management feature like CTEs is limited to less data-intensive operations because of this :(

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.

But I agree, it would be a nice ability to have.

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.

>Parallel Queries >Commercial databases can do it for decades, but in the open >source database scene it is unique:

That's wrong. Open Source Ingres did it for many years too Of course unlike PG they are not on the Hype train ...

I dunno, is it fair to say something is on the hype train if it's been around for 30 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?

Reply on news.ycombinator.com