5 ms·

Big News in Databases

95 points by MarkusWinand 10y ago

netcraft 10y ago
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 10y ago
  Agree. A great code management feature like CTEs is limited to less data-intensive operations because of this :(
  - smilliken 10y ago
    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 10y ago
      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 10y ago
        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 10y ago
        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.
        cneumuel 10y ago
        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.
daenney 10y ago
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.
nn3 10y ago
>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 ...
- Jgrubb 10y ago
  I dunno, is it fair to say something is on the hype train if it's been around for 30 years?
- netcraft 10y ago
  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?
atemerev 10y ago
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.
- bdcravens 10y ago
  > 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.
  - jack9 10y ago
    > 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.
    - ak4g 10y ago
      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.
- 10y ago

95 points by MarkusWinand 10y ago

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 10y ago
Agree. A great code management feature like CTEs is limited to less data-intensive operations because of this :(
- smilliken 10y ago
  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 10y ago
    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 10y ago
      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 10y ago
        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.
      - cneumuel 10y ago
        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.

daenney 10y ago

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.

nn3 10y ago

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

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

atemerev 10y ago

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.

bdcravens 10y ago
> 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.
- jack9 10y ago
  > 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.
  - ak4g 10y ago
    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.
10y ago