4 ms·

Whenever I see functions like these and PostgreSQL's JSON functions, I get the feeling that MySQL/MariaDB is not progressing, or not nearly as fast. For those

by rebelde 12y ago

Whenever I see functions like these and PostgreSQL's JSON functions, I get the feeling that MySQL/MariaDB is not progressing, or not nearly as fast. For those who have worked with both, do you find the PostgreSQL-only functions and features to be extremely useful or does it not really matter much?

pilif 12y ago
I moved away from MySQL a long time ago (to finally get sub selects which weren't supported by MySQL back then), so my opinion might not really count, but usually there's no day passing in which I'm not writing some query using features in Postgres that are not in MySQL. Could I avoid these features? Probably. At the cost of query readability and sometimes possibly performance and/or server roundtrips. Furthermore, for years now, PostgreSQL is adding features in places that are very interesting to my use case, so I'm usually jumping ahead to newer versions very quickly. Case in point is the new jsonb support which I'll be using to displace an old, bulky and join-heavy (performance!) system for handling custom attributes of products and of course, once you've gotten a taste of the awesome, you will never want to go back. From an operations standpoint, I wouldn't say Postgres is any harder to deal with than MySQL either, especially for smaller setups. Bigger ones are probably similar too, though I personally only have experience with bigger Postgres setups. There's nothing stopping you from trying it out: At this point, both Postgres and MySQL have proven their reliability even in big setups, so when you start your next thing, you won't ever have to be afraid that your pick of database will cause you trouble later down the road. As such, I would really stop asking questions and start using Postgres.
cwyers 12y ago
It really depends on your needs. I still am kind of in shock that Postgres is willing to add these baroque sorts of features, but still doesn't have an UPSERT.
- sergiosgc 12y ago
  It's bound to appear on the next release. If I recall correctly, it almost made the last one, except for a last minute bug. Anyhow, if you really need it, you can easily write upserts with triggers.
- neilc 12y ago
  UPSERT is not a simple feature to implement correctly: http://www.pgcon.org/2014/schedule/events/661.en.html http://www.pgcon.org/2014/schedule/events/661.en.html
jamabam 12y ago
They're incredibly useful. My company uses MySQL, and RedShift. It's hilarious and sad that many of my fellow engineers regularly extol the virtues of RedShift simply because it has CTEs, Window Functions, and other basic modern database amenities. They don't understand how bad MySQL is, and how much is weakens our product.
Amezarak 12y ago
There is not a day that goes by that I don't write at least one query with a windowing function. Features like that are extremely useful. For me, it's usually ROW_NUMBER(), but LAG() and LEAD() have been enormously helpful too. IME, it's easy to express the direct utility of features like this - you get row numbers, okay, so what? - and hard to express the huge indirect utility - using them as building blocks in complex (but efficient) queries.

by rebelde 12y ago

pilif 12y ago

I moved away from MySQL a long time ago (to finally get sub selects which weren't supported by MySQL back then), so my opinion might not really count, but usually there's no day passing in which I'm not writing some query using features in Postgres that are not in MySQL. Could I avoid these features? Probably. At the cost of query readability and sometimes possibly performance and/or server roundtrips. Furthermore, for years now, PostgreSQL is adding features in places that are very interesting to my use case, so I'm usually jumping ahead to newer versions very quickly. Case in point is the new jsonb support which I'll be using to displace an old, bulky and join-heavy (performance!) system for handling custom attributes of products and of course, once you've gotten a taste of the awesome, you will never want to go back. From an operations standpoint, I wouldn't say Postgres is any harder to deal with than MySQL either, especially for smaller setups. Bigger ones are probably similar too, though I personally only have experience with bigger Postgres setups. There's nothing stopping you from trying it out: At this point, both Postgres and MySQL have proven their reliability even in big setups, so when you start your next thing, you won't ever have to be afraid that your pick of database will cause you trouble later down the road. As such, I would really stop asking questions and start using Postgres.

cwyers 12y ago

It really depends on your needs. I still am kind of in shock that Postgres is willing to add these baroque sorts of features, but still doesn't have an UPSERT.

sergiosgc 12y ago
It's bound to appear on the next release. If I recall correctly, it almost made the last one, except for a last minute bug. Anyhow, if you really need it, you can easily write upserts with triggers.
neilc 12y ago
UPSERT is not a simple feature to implement correctly: http://www.pgcon.org/2014/schedule/events/661.en.html http://www.pgcon.org/2014/schedule/events/661.en.html

jamabam 12y ago

They're incredibly useful. My company uses MySQL, and RedShift. It's hilarious and sad that many of my fellow engineers regularly extol the virtues of RedShift simply because it has CTEs, Window Functions, and other basic modern database amenities. They don't understand how bad MySQL is, and how much is weakens our product.

Amezarak 12y ago

There is not a day that goes by that I don't write at least one query with a windowing function. Features like that are extremely useful. For me, it's usually ROW_NUMBER(), but LAG() and LEAD() have been enormously helpful too. IME, it's easy to express the direct utility of features like this - you get row numbers, okay, so what? - and hard to express the huge indirect utility - using them as building blocks in complex (but efficient) queries.