5 ms·

Citation please. (I prefer Postgres, but your statement seems harsh.)

ddorian43 12y ago
I'm talking solely based on my experience, which has been much harsher than I wrote in my comment. I know in-person only 1 guy that likes postgresql and he had good reasons for it and experience with many rdbms. While everyone else is like: lol php+mysql for life (talking only people that I know in-person). Nearly the same experience, but much better is online in my opinion. Exercise: Go to reddit.com/r/postgresql and reddit.com/r/mysql and see if there is any difference in topics.
Amezarak 12y ago
It makes some sense to me. I always find it pretty painful writing anything but the most simple queries in MySQL - for starters, there's no CTEs, no windowing functions, and no table-valued functions, all of which I find invaluable - and the query optimizer (and performance in general) seems... not so hot. As far as I can tell, the main reasons people use MySQL are 1) it's well-known, well-documented, free, and widely available; shared hosting, for example, is almost always MySQL and 2) replication. I often wonder if the inadequacies of MySQL are part of the reason for the success of NoSQL databases and general un-hipness of relational databases.
- cwyers 12y ago
  I used MySQL a lot at my last job (MS SQL Server now, which has its own ups and downs) and I examined using Postgres for some personal projects, and there's a few features that I used rather heavily in MySQL (partitioned tables and upsert are the really big ones) that Postgres doesn't have an out-of-the-box equivalent to. There's a lot of tradeoffs between the two, I think it's wrong to say one is better than the other without context.
  - Amezarak 12y ago
    Postgres does have partitioned tables, although the implementation is different from MySQL's. Upsert stands, though. I don't disagree there's tradeoffs - I mentioned replication, for example, but it seems to me that MySQL is lacking a lot of features that other DBMS's provide and except for a few use cases, the fewer features unique (or better implemented) in MySQL doesn't make up for it. Honestly I prefer SQL Server by leaps-and-bounds over the four I've used enough to be very familiar with - MySQL, MS SQL, Oracle, and Postgres - but I think Postgres has much more to offer to most users for most use cases than MySQL.
  - xtrumanx 12y ago
    Here's a great article[0] regarding different ways to accomplish to upsert in Postgres. I bring it up because the author adds his thought on why Postgres hasn't implemented it unlike the other open source RDBMS: > As a final word – yeah, but why PostgreSQL doesn't handle it internally, as the other database? > Answer lies somewhere between "developers have other things to do first" and "it's complicated, because there are many ways to do it, and the most correct one is slow – so there is a trade off decision to be made". I'm not sure how upsert is implemented in MySql but given its reputation and the fact that it is open source; I would imagine the Postgres team has considered their solution and have decided it wasn't good enough for their standards. Postgres may not have an out-of-the-box solution but there are solutions out there which are mostly pretty trivial functions. Although they may add a bit of bloat since it seems you'd need a function for each table you'd want to use upsert on. [0] http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
  - nieve 12y ago
    I've used PostgreSQL for 15 years and missed upsert/MERGE for all of them, but my understanding from previous research is that MySQL's is actually unreliable in the sense that it can violate ACID in some circumstances. This is believable to me, MySQL's performance lead on whole table count(*) is because it doesn't actually count the currently visible rows for large tables. Complete violation of sql's count semantics, but most people don't know or care. I'm digging up some references now.
    - nieve 12y ago
      http://lucumr.pocoo.org/2014/2/16/a-case-for-upserts/ http://lucumr.pocoo.org/2014/2/16/a-case-for-upserts/ "There is an obvious question what happens if there are two unique constraints on a table, and the answer is quite simply that it breaks down in that case. If there are two uniques MySQL will not give an error but just execute the operation as if it was happening on the first row that matches. Essentially the where is extended to be be an or for any of the unique constraints compared to the values from the insert statement. This is obviously dangerous and definitely not optional."

I'm talking solely based on my experience, which has been much harsher than I wrote in my comment. I know in-person only 1 guy that likes postgresql and he had good reasons for it and experience with many rdbms. While everyone else is like: lol php+mysql for life (talking only people that I know in-person). Nearly the same experience, but much better is online in my opinion. Exercise: Go to reddit.com/r/postgresql and reddit.com/r/mysql and see if there is any difference in topics.

Amezarak 12y ago

It makes some sense to me. I always find it pretty painful writing anything but the most simple queries in MySQL - for starters, there's no CTEs, no windowing functions, and no table-valued functions, all of which I find invaluable - and the query optimizer (and performance in general) seems... not so hot. As far as I can tell, the main reasons people use MySQL are 1) it's well-known, well-documented, free, and widely available; shared hosting, for example, is almost always MySQL and 2) replication. I often wonder if the inadequacies of MySQL are part of the reason for the success of NoSQL databases and general un-hipness of relational databases.

cwyers 12y ago
I used MySQL a lot at my last job (MS SQL Server now, which has its own ups and downs) and I examined using Postgres for some personal projects, and there's a few features that I used rather heavily in MySQL (partitioned tables and upsert are the really big ones) that Postgres doesn't have an out-of-the-box equivalent to. There's a lot of tradeoffs between the two, I think it's wrong to say one is better than the other without context.
- Amezarak 12y ago
  Postgres does have partitioned tables, although the implementation is different from MySQL's. Upsert stands, though. I don't disagree there's tradeoffs - I mentioned replication, for example, but it seems to me that MySQL is lacking a lot of features that other DBMS's provide and except for a few use cases, the fewer features unique (or better implemented) in MySQL doesn't make up for it. Honestly I prefer SQL Server by leaps-and-bounds over the four I've used enough to be very familiar with - MySQL, MS SQL, Oracle, and Postgres - but I think Postgres has much more to offer to most users for most use cases than MySQL.
- xtrumanx 12y ago
  Here's a great article[0] regarding different ways to accomplish to upsert in Postgres. I bring it up because the author adds his thought on why Postgres hasn't implemented it unlike the other open source RDBMS: > As a final word – yeah, but why PostgreSQL doesn't handle it internally, as the other database? > Answer lies somewhere between "developers have other things to do first" and "it's complicated, because there are many ways to do it, and the most correct one is slow – so there is a trade off decision to be made". I'm not sure how upsert is implemented in MySql but given its reputation and the fact that it is open source; I would imagine the Postgres team has considered their solution and have decided it wasn't good enough for their standards. Postgres may not have an out-of-the-box solution but there are solutions out there which are mostly pretty trivial functions. Although they may add a bit of bloat since it seems you'd need a function for each table you'd want to use upsert on. [0] http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
- nieve 12y ago
  I've used PostgreSQL for 15 years and missed upsert/MERGE for all of them, but my understanding from previous research is that MySQL's is actually unreliable in the sense that it can violate ACID in some circumstances. This is believable to me, MySQL's performance lead on whole table count(*) is because it doesn't actually count the currently visible rows for large tables. Complete violation of sql's count semantics, but most people don't know or care. I'm digging up some references now.
  - nieve 12y ago
    http://lucumr.pocoo.org/2014/2/16/a-case-for-upserts/ http://lucumr.pocoo.org/2014/2/16/a-case-for-upserts/ "There is an obvious question what happens if there are two unique constraints on a table, and the answer is quite simply that it breaks down in that case. If there are two uniques MySQL will not give an error but just execute the operation as if it was happening on the first row that matches. Essentially the where is extended to be be an or for any of the unique constraints compared to the values from the insert statement. This is obviously dangerous and definitely not optional."