
Big News in Databases - MarkusWinand
http://use-the-index-luke.com/blog/2016-11/big-news-in-databases
======
atemerev
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.

~~~
thijsvandien
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.

~~~
ashmud
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...](http://web.archive.org/web/20071016095932/http://www.rightstuf.com/)

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

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

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

~~~
smilliken
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.

~~~
paulryanrogers
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.

~~~
smilliken
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.

~~~
netcraft
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.

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

~~~
netcraft
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?

