19 ms·

I'm a developer who drastically prefers PostgreSQL due to things like window functions, a more predictable query planner, a variety of index types and the abili

I'm a developer who drastically prefers PostgreSQL due to things like window functions, a more predictable query planner, a variety of index types and the ability to index over calculated fields, improved strictness out of the box (e.g., UTF-8 being UTF-8, no implicit truncation of long strings, no silent and lossy automatic typecasting), and so on. I'm forced to use MySQL at work, because it's much easier to work with for our operations teams. That said, my perception is that PostgreSQL is catching up to MySQL in terms of operational overhead and replication strategies faster than MySQL is catching up to PostgreSQL on the end-user side of things. To an end-user like me, what would you point out are some current advantages that MySQL has over PostgreSQL, and what do you see the MySQL project doing to help it catch up to the growing gulf in feature parity?

morgo 9y ago
I would maybe start off by saying feature parity was/is never the goal. The original goal of MySQL was to be the "Ikea of databases" (both come from Sweden). Having said that, I think expectations on what is the minimal functionality have evolved, and we have responded by adding functionality like JSON in MySQL 5.7, and CTEs and Window Functions in 8.0. In terms of the specific issues you raise: * utf8 vs utf8mb4 is "problem #1" http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf... - we have switched the default in 8.0, and will deprecate utf8mb3 to reduce confusion: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ove... * Implicit truncation and automatic type casting is no longer the default (strict was enabled for new installs in 5.6 (2013) and all installs in 5.7 (2015)). That is, unless the standard specifies it should (there are some weird cases). * 5.7 has virtual columns + indexes. This allows for a functional index. Edit: Missed a word, added computed columns
- stouset 9y ago
  Thanks for your reply! I'm thrilled to hear that MySQL is taking strictness much more seriously with the above-mentioned strategies. It looks the top few of my biggest gripes are getting addressed, and that's exciting news. Could you elaborate more on MySQL being the Ikea of databases? What exactly does that mean, and how do those things differentiate it from PostgreSQL currently (and how will they in the future)? If it's mostly just around new-developer friendliness, I worry that MySQL has more of an (admittedly somewhat exaggerated) reputation as the "PHP of databases" right now: it's easy to use out of the box, but in a way that doesn't discourage poor practice and results in traps and land-mines for future development. How do you guys plan on achieving such a (laudable) goal without bringing about the kind of baggage that stereotypically has come with it? FWIW, I suspect the strictness changes you mentioned will go a long way towards addressing that, but I'm curious if there's more you have in mind. PS. Apologies if I've set up a bit of a straw man in my last paragraph. I'm trying to predict what your answer to the prior paragraph might be and pose additional questions based on that answer to avoid an extra round-trip.
  - morgo 9y ago
    I want to start by saying that gripes/land mines don't help anyone. We try to balance fixing these issues, while keeping a good upgrade story. XKCD does a good job of explaining the problem in https://xkcd.com/1172/ https://xkcd.com/1172/ In every major release we have a pseudo quota for incompatibility (5.5 changed default storage engine, 5.6 and 5.7 refactored a lot of the optimizer and transitioned defaults like strict mode on, 8.0 makes large changes to data dictionary). w.r.t Ikea of databases - I have a large Ikea desk that I sit some very heavy computers on. It is simple in design, but that does not make it limited.
    - stouset 9y ago
      I appreciate the responses. Thanks so much for your time!
- StevePerkins 9y ago
  > adding functionality like JSON in MySQL 5.7 In the Java world, the Spring framework is about to drop a new major release revolving around asynchronous or "reactive" programming. Many other programming languages and frameworks are already moving in this direction. This model pulls people toward alternative databases such as Cassandra, MongoDB, etc. Because you lose many of the advantages of asynchronous processing in the application tier, if your data access tier still relies on synchronous blocking API's (such as Java's JDBC and JPA). Recently I was researching whether any relational database vendors were adding support for asynchronous access, and stumbled across MySQL's new "X Protocol" basically by accident. The documentation is still rather new and thin, and I was puzzled that it seems to revolve 100% around the use case of MySQL as a document store to compete with MongoDB. Are there any plans to push the X Protocol further toward the spotlight, beef up the documentation, and emphasize use cases around asynchronous access as a relational database rather than document store?
  - morgo 9y ago
    The "X" comes from it being a crossover/hybrid protocol between SQL and a CRUD API. It has a lot of modern protocol features, and uses protobufs, which makes it easy to add new driver support. The CRUD API has overlap with Document Stores like MongoDB, but the X Protocol really goes beyond that. There are plans to continue developing the X Protocol. We have also chatted about doing translation from the classic protocol to X Protocol in the MySQL Router. I agree with you that async is important. Stay tuned :-)
  - eeZi 9y ago
    Red Hat is trying to address this with their Debezium project: http://debezium.io/ http://debezium.io/
takeda 9y ago
My company was one of those set on MySQL and was resisting PostgreSQL, and we succeeded that we now have Postgres and MySQL side by side, and operationally Postgres is actually less overhead (especially with tools like repmgr and barman). It also doesn't allow idiotic setups, which later turns out to be a very bad decision. We had one cluster that was two machines: master & slave, over time, decision was made to write certain data just to slave, while different kinds of data was only written to master. Then, since slave contained more data, we started backing up the slave. Now replication between slave and master broke and that was discovered week later once all old logs were purged. An interesting acrobatics needed to be performed to restore that to working condition. Postgres, it would not let you make any changes to the slave, the replication slots make sure no logs are purged if replication is down. It's still a bit unfair to say, because we have more MySQL than Postgres nodes, but Postgres just seems to work so currently it requires no attention. Barman doesn't make daily backups, but continuously every transaction. It can be integrated with nagios/icinga and makes much more through tests. I think your operations team resisting is just being scared of the unknown, but it is also very beneficial to them too.
- taf2 9y ago
  Sorry but you could have limited access to the slave to prevent writes... there are legit replication strategies that allow you to write to multiple nodes, master-master, etc...
  - morgo 9y ago
    There is an option called [super_]read_only designed for this use-case: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...
  - deleted 9y ago
    [deleted]

morgo 9y ago

I would maybe start off by saying feature parity was/is never the goal. The original goal of MySQL was to be the "Ikea of databases" (both come from Sweden). Having said that, I think expectations on what is the minimal functionality have evolved, and we have responded by adding functionality like JSON in MySQL 5.7, and CTEs and Window Functions in 8.0. In terms of the specific issues you raise: * utf8 vs utf8mb4 is "problem #1" http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf... - we have switched the default in 8.0, and will deprecate utf8mb3 to reduce confusion: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ove... * Implicit truncation and automatic type casting is no longer the default (strict was enabled for new installs in 5.6 (2013) and all installs in 5.7 (2015)). That is, unless the standard specifies it should (there are some weird cases). * 5.7 has virtual columns + indexes. This allows for a functional index. Edit: Missed a word, added computed columns

stouset 9y ago
Thanks for your reply! I'm thrilled to hear that MySQL is taking strictness much more seriously with the above-mentioned strategies. It looks the top few of my biggest gripes are getting addressed, and that's exciting news. Could you elaborate more on MySQL being the Ikea of databases? What exactly does that mean, and how do those things differentiate it from PostgreSQL currently (and how will they in the future)? If it's mostly just around new-developer friendliness, I worry that MySQL has more of an (admittedly somewhat exaggerated) reputation as the "PHP of databases" right now: it's easy to use out of the box, but in a way that doesn't discourage poor practice and results in traps and land-mines for future development. How do you guys plan on achieving such a (laudable) goal without bringing about the kind of baggage that stereotypically has come with it? FWIW, I suspect the strictness changes you mentioned will go a long way towards addressing that, but I'm curious if there's more you have in mind. PS. Apologies if I've set up a bit of a straw man in my last paragraph. I'm trying to predict what your answer to the prior paragraph might be and pose additional questions based on that answer to avoid an extra round-trip.
- morgo 9y ago
  I want to start by saying that gripes/land mines don't help anyone. We try to balance fixing these issues, while keeping a good upgrade story. XKCD does a good job of explaining the problem in https://xkcd.com/1172/ https://xkcd.com/1172/ In every major release we have a pseudo quota for incompatibility (5.5 changed default storage engine, 5.6 and 5.7 refactored a lot of the optimizer and transitioned defaults like strict mode on, 8.0 makes large changes to data dictionary). w.r.t Ikea of databases - I have a large Ikea desk that I sit some very heavy computers on. It is simple in design, but that does not make it limited.
  - stouset 9y ago
    I appreciate the responses. Thanks so much for your time!
StevePerkins 9y ago
> adding functionality like JSON in MySQL 5.7 In the Java world, the Spring framework is about to drop a new major release revolving around asynchronous or "reactive" programming. Many other programming languages and frameworks are already moving in this direction. This model pulls people toward alternative databases such as Cassandra, MongoDB, etc. Because you lose many of the advantages of asynchronous processing in the application tier, if your data access tier still relies on synchronous blocking API's (such as Java's JDBC and JPA). Recently I was researching whether any relational database vendors were adding support for asynchronous access, and stumbled across MySQL's new "X Protocol" basically by accident. The documentation is still rather new and thin, and I was puzzled that it seems to revolve 100% around the use case of MySQL as a document store to compete with MongoDB. Are there any plans to push the X Protocol further toward the spotlight, beef up the documentation, and emphasize use cases around asynchronous access as a relational database rather than document store?
- morgo 9y ago
  The "X" comes from it being a crossover/hybrid protocol between SQL and a CRUD API. It has a lot of modern protocol features, and uses protobufs, which makes it easy to add new driver support. The CRUD API has overlap with Document Stores like MongoDB, but the X Protocol really goes beyond that. There are plans to continue developing the X Protocol. We have also chatted about doing translation from the classic protocol to X Protocol in the MySQL Router. I agree with you that async is important. Stay tuned :-)
- eeZi 9y ago
  Red Hat is trying to address this with their Debezium project: http://debezium.io/ http://debezium.io/

takeda 9y ago

My company was one of those set on MySQL and was resisting PostgreSQL, and we succeeded that we now have Postgres and MySQL side by side, and operationally Postgres is actually less overhead (especially with tools like repmgr and barman). It also doesn't allow idiotic setups, which later turns out to be a very bad decision. We had one cluster that was two machines: master & slave, over time, decision was made to write certain data just to slave, while different kinds of data was only written to master. Then, since slave contained more data, we started backing up the slave. Now replication between slave and master broke and that was discovered week later once all old logs were purged. An interesting acrobatics needed to be performed to restore that to working condition. Postgres, it would not let you make any changes to the slave, the replication slots make sure no logs are purged if replication is down. It's still a bit unfair to say, because we have more MySQL than Postgres nodes, but Postgres just seems to work so currently it requires no attention. Barman doesn't make daily backups, but continuously every transaction. It can be integrated with nagios/icinga and makes much more through tests. I think your operations team resisting is just being scared of the unknown, but it is also very beneficial to them too.

taf2 9y ago
Sorry but you could have limited access to the slave to prevent writes... there are legit replication strategies that allow you to write to multiple nodes, master-master, etc...
- morgo 9y ago
  There is an option called [super_]read_only designed for this use-case: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...
- deleted 9y ago
  [deleted]