10 ms·

The guy has deliberately changed a default setting (sql_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresp

by kaamos 9y ago

The guy has deliberately changed a default setting (sql_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. Which is already telling something about PostgreSQL evangelism. Speaking of comparing MySQL and PostgreSQL functionality, I've been spending some time recently on this very topic. Both are great databases with interesting and sometimes unique features. Here are some of the features PostgreSQL is missing as compared to MySQL: - synchronous multi-master cluster (Galera, Group Replication) - distributed in-memory grid with automatic sharding (NDB) - semi-synchronous replication (will be available in PostgreSQL 10) - built-in logical replication (will be available in PostgreSQL 10) - built-in event scheduler - clustered indexes - declarative partitioning (limited functionality will be available in PostgreSQL 10) - optimizer hints - optimizer tracing - efficient MVCC implementation that is unaffected by XID wraparound problems and VACUUM issues (see also Uber's report on moving from PostgreSQL to MySQL) - page-level compression - page-level encryption - page-level incremental backups - user variables - NoSQL client APIs (HandlerSocket, memcached protocol, X protocol) - virtual columns - ability to choose data page size on database creation - ability to specify column order when adding a new column with ALTER TABLE - write-optimized storage engines similar to MyRocks and TokuDB All of that may seem small from a developer's standpoint. Those things, however, are huge for people who design and operate the busiest websites on the internet. Which is probably why MySQL still shines in that field.

fulafel 9y ago
> The guy has deliberately changed a default setting (strict_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. I don't think the comments read like that. Someone pointed out that some MySQL variants have changed the default in a recent version and the author criticised the defaults, so the implication is that he wasn't using a bleeding-edge MySQL installation and got the previous defaults.
- kaamos 9y ago
  Agree, I might have misread the discussion. However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. Even when multiple people mentioned sql_mode.
  - mmarx 9y ago
    > However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. From the comments of the original article, the actual version he was using is 10.0: > this is the default version coming with Fedora core 23 (mariadb.x86_64, 1:10.0.28-1.fc23).
    - kaamos 9y ago
      OK, I see it now, thanks. I would even edit my original comment if I could. Still, the original article is biased without mentioning strict mode, the fact that it's been recommended to turn it on in any serious deployment for years, and that it is enabled by default in the latest MySQL/MariaDB releases. There are reasons to choose PostgreSQL over MySQL (and vice versa), but the article is certainly an example of unfair evangelism.
      - mseebach 9y ago
        It's a pretty damning point against MySQL that there's a default setting that you have to know to change, or else the DB will happily throw away your data. A lot of the evangelism against MySQL is rooted in a deep skepticism against letting a DB that would even have such a feature, much less leave it on by default for years after sort-of admitting that it's probably a bad idea, anywhere near your data. I loved MySQL back in the day when it would work straight out of the box on both Windows and Linux (and god knows not a lot of web software did back then), and Postgres was always, frankly is still, a bit opaque to actually get from zero to one. But I also remember discovering the varchar overflow and the users being pissed at me for losing their data. Luckily it wasn't a really important field and we discovered it reasonably fast, so fallout was contained. But the same app actually dealt with payments, it could have been much worse. It's basically a country song: MySQL is the kind of guy that's tons of fun, always the centre of the party, but used to disappear for multi-day benders without a word, but have now cleaned up and is disappointed that his high school sweetheart prefers the boring guy who went to college, got a job and was never late for a date.
porker 9y ago
I didn't realise a few of these existed, so thanks. > - page-level encryption This got me excited, but for anyone else looking: to keep the key securely (not in a file on the DB server, where an attacker can get it + the DB files) you need to but MySQL Enterprise Edition. Quoting from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-en...: > The InnoDB tablespace encryption feature in non-enterprise editions of MySQL uses the keyring_file plugin for encryption key management, which is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).
gmac 9y ago
Which is already telling something about PostgreSQL evangelism. No. Speaking as an outsider, the Postgres community is about the nicest (and smartest) there is.
- kaamos 9y ago
  I know what I'm talking about. Over the last couple of years I had to speak to many people in the PostgreSQL community. While there are indeed many smart and nice people with balanced views and opinions there, the majority has this ideological bias. With ideology being that PostgreSQL is superior to MySQL in every single way. All counter-examples and arguments are usually ignored. I think it's something unique to PostgreSQL community, I've never met anything like that in other technical communities.
  - fusiongyro 9y ago
    As someone who might fairly get scolded for this, we feel like praising MySQL's features is a bit like enjoying the recessed lighting and granite counters in a house whose roof is actively leaking. There are certainly features MySQL has that Postgres doesn't, but data integrity is problem 1, and most of us got burned by MySQL on that one at some point before we got here. Or, we deployed MySQL and then found out that the features we were depending on were InnoDB-only, but switching to InnoDB cratered our performance. Or we tripped on one too many of MySQL's numerous special cases or silent limitations. You can use MySQL productively. You can even benefit from its special features. But the thing taken together is a bit of a mine field. Postgres has some false advertising issues too ("object-relational"?) but the day-to-day living with it is a lot nicer, because the roof doesn't leak. I can live without the granite counters.
    - kaamos 9y ago
      Data integrity is of course important, and MySQL indeed has a weak story here, especially in older versions. Is PostgreSQL flawless when it comes to data integrity? No, that would also be false advertising. Some examples: - data checksums are disabled by default (silent data corruptions!), and the user manual specifically warns they are not cheap. And the reasons they are expensive is that PostgreSQL does not support O_DIRECT IO; - removing a file on the filesystem may lead to a silent loss of data in PostgreSQL: https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3f-45bc94c11759%40postgrespro.ru https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3... - some important control structures (which also impact data visibility and thus, integrity) are not even protected by checksums: https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYKOZ6KGu2PbzGiX%2B1NF-dFkC35FJMCtWQ%40mail.gmail.com https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYK... - there are no tools to verify replication integrity, similar to pt-table-checksum for MySQL; that would require statement-based replication, which is not supported in PostgreSQL, even in the next major release
fusiongyro 9y ago
Advanced replication strategies in MySQL and Oracle mostly cause hair loss and divorce. Everywhere I have worked that uses these databases eventually convinces itself that it needs futuristic replication technology and then goes on to suffer from outages and weird corruption. It turns out, distributed consistency is a hard problem. Oracle and MySQL both benefit monetarily from people assuming it will work and then activating it, only to find out a month or two later that it's a complicated problem that requires actual planning and oversight. I think Postgres will be a better product when it incorporates similar functionality, but the implementations in MySQL and Oracle lie somewhere on the border between false advertising and reckless endangerment. I appreciate that Postgres not having it as two-lines of configuration sends a message that you are making a commitment. Nearly everything else in Postgres is set-it-and-forget-it. You really want to have a DBA if you're going to try to make replication work. When you read the docs on having a hot standby and how much of a commitment that is and sense yourself getting tired, you're starting to appreciate what you might be getting yourself into with single-master replication, let alone multi-master.
- kaamos 9y ago
  > Nearly everything else in Postgres is set-it-and-forget-it. There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.
  - fusiongyro 9y ago
    I read their article, and a dozen of the rebuttals. If you want to use MySQL badly enough, you can.
  - glogla 9y ago
    Uber switched from MySQL used as relational database, to Postgres used as relational database, to MySQL used as key-value store. Nobody is saying MySQL is a bad key-value store!
    - pmarreck 9y ago
Nican 9y ago
I have been curious to ask someone who is knowledgeable about databases/MySQL: Spanner has a feature for creating interleaved tables, which helps data locality when performing joins on a distributed database. [1] Is this an important feature for a large database, and does MySQL provide anything similar? [1] https://cloud.google.com/spanner/docs/schema-and-data-model#creating_interleaved_tables https://cloud.google.com/spanner/docs/schema-and-data-model#...
teddyh 9y ago
EDIT: Never mind, I read too much into what another commenter wrote.
- kaamos 9y ago
  No, this is all from MySQL Community Edition.
  - teddyh 9y ago
    Then what about (https://news.ycombinator.com/item?id=15172965 https://news.ycombinator.com/item?id=15172965)?
    - kaamos 9y ago
      Does that comment say I'm listing features only available in MySQL Enterprise Edition?
      - teddyh 9y ago
        It does say that one feature you’re listing is not really sensible to use without the Enterprise Edition, but you’re right, it does not say that. Original comment redacted.
        kaamos 9y ago
        It is, in fact sensible without Enterprise Edition. The only thing it says is that page-level encryption in Community Edition is not intended as a regulatory compliance solution. Nothing more, nothing less. Even in case you care about regulatory compliance, there are other options besides Enterprise Edition.
        porker 9y ago
        What are the other options, and when would page-level encryption in Community Edition be useful? If I'm understanding it correctly, page-level encryption is to protect against someone gaining access to the DB server and copying the files off it. If the key is stored in a file on this DB server, I might as well not bother encrypting the pages.
glogla 9y ago
> - synchronous multi-master cluster (Galera, Group Replication) Which doesn't work, see [1]. What other features of those you listed the authors claim that work, and just don't when you look deeper? Lot of the big companies are using MySQL as a NoSQL database, and that's not a bad way to use MySQL, but it's a pretty bad relational database, see [2]. [1]: https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-cluster https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-clu... [2]: https://grimoire.ca/mysql/choose-something-else https://grimoire.ca/mysql/choose-something-else
- kaamos 9y ago
  Let me just tell you that I've read both links before.
  - zimpenfish 9y ago
    If you've read the aphyr link with this (and the rest of his conclusion): > Unfortunately, even in totally healthy clusters, with no node failures or network failures, Galera Cluster does not satisfy its claims of Snapshot Isolation ...how on earth can you straight-facedly claim that it's a plus point for MySQL over PostgreSQL?
antoaravinth 9y ago
There is one point I need to add it here. Note that I'm not DB expert, but I need to say why I prefer Postgres over Mysql. Create a table like the following: create table user (name varchar(4)); MySql: insert into user (name) values ('antoaravinth'); The above statement works even though I have given the size of my varchar as 4! And retrieving gives me `anto`!! Postgres: insert into user (name) values ('antoaravinth'); Stops me there with the relevant exception! That shows how Postgresql is doing small things right. After seeing this, I never used MySql!
- sigi45 9y ago
  As said in many other comments: This is because you used MySQL in non strict mode. You don't run MySQL on Production in non strict mode.
  - koffiezet 9y ago
    Tell that to developers who develop with the default config and then expect that to work in "Production".
  - thehardsphere 9y ago
    As said in many other comments, the idea that there is even a "strict mode" which was not on by default for many years is itself a problem and a red flag for other problems.
sigi45 9y ago
master-master is evil. Not sure of your other points, i have no need for them. What i really don't like on MySQL (and i don't care for a lot of other stuff): MySQL 5.6 (yeah don't forget your version numbers) really sucks for queries with or. It sucks to write 2 selects with an union which is 100x faster than the same query with one select and ONE or.
grogers 9y ago
MySQL has similar issues to postgres vacuum. If 'checkpoint age' gets too high throughput drops by ~5x to prevent WAL wrapping. We hit this in production when using 5.5. They've made a lot of improvements to flushing in recent versions, but it isn't a strict advantage. With both DBs as long as you monitor you would be fine.
marcosdumay 9y ago
Well, personally what I see is people comparing a group of developers that are adamant on never corrupting any data, where everything is secondary and will refuse to push any feature that may cause problems with a group that is adamant on increasing market share, if it's by doing a low quality MVP so be it, that I have seen corrupting data on production several times, and whose software still carries the scars from those MVP's errors. Yes, MySQL is all featureful¹. The only thing missing there is guarantees that your data will stay good. 1 - Some of those are "no, we won't ever implement it" features that will do nothing but eat your data by design. Why would you ever want them in a persistence layer? Cache on memory somewhere better.
- bonesss 9y ago
  > Some of those are "no, we won't ever implement it" features that will do nothing but eat your data by design. Why would you ever want them in a persistence layer? Cache on memory somewhere better. I feel a significant motivation for MySql development are lightweight webapps on PHP like Wordpress where the developers are beholden to a third-party hosting environment and have a significant front-end focus... From that perspective I can see why bells and whistles that would be moronic in a normal application would start sticking to the database itself. MongoDB suffers from some of the same market pressure: being easy to demo and get from 0 to SAP in 10 min is great, but having to choose crazy insecure defaults and be open for data loss in order to get there is a showstopper in Enterprise circles. In both cases the ironic demands for competent administration are much higher because of all the potential for foot-shooting. "It's always worth it to take the pain up front."
feld 9y ago
> - optimizer hints > - optimizer tracing Hints are bad. They always have been. If you need hints your query planner is broken. https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion Even Oracle Tom says DO NOT USE HINTS IN PRODUCTION! Report the bug to Oracle and let them fix it properly.
- will_be_no_ai 9y ago
  As a general recommendation maybe this is ok. But, unrealistic relative to the world we live in. Sometimes you need to use hints. Oracle / PostgreSQL / what have you, are not perfect nor are developers writing the SQL. Is your customer going to wait for the next version of Oracle? Is your dev team going to fix some sev 3 performance bug? Hints allow you to use your brain to help the optimizer. Why would you make a hard rule not to use them? Why else are they there?
  - feld 9y ago
    I won't speak for Oracle because it's always been a "if you have the money you can get a custom patch for the behavior you want" game. But for Postgres, you contact the developers. If your problems are costing you so much money why wouldn't you just hire a core developer on the Postgres team or hire EnterpriseDB on contract to work on your issues? They'll fix the bug, upstream the fix, and roll you updated packages you can push out to your servers. Not entirely different than working with Oracle, except you are working in the open source world. Hints are bad. Next time you upgrade your database you may end up with worse performance due to other changes in the query planner. Once your data crosses a certain size threshold it may also start performing worse. They are unpredictable. You will never know when they could break. Using them is the wrong solution.
    - will_be_no_ai 9y ago
      I agree with paragraph three. A hint is a directive. Now we are stuck with it. Bad stuff can ensue...maybe. But, maybe my customer is pissed now. Total cost to fix: a couple hours. If I had the budget and developers to make PostgreSQL as performant as Oracle, I would be living on an island next to Larry.

by kaamos 9y ago

fulafel 9y ago

> The guy has deliberately changed a default setting (strict_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. I don't think the comments read like that. Someone pointed out that some MySQL variants have changed the default in a recent version and the author criticised the defaults, so the implication is that he wasn't using a bleeding-edge MySQL installation and got the previous defaults.

kaamos 9y ago
Agree, I might have misread the discussion. However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. Even when multiple people mentioned sql_mode.
- mmarx 9y ago
  > However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. From the comments of the original article, the actual version he was using is 10.0: > this is the default version coming with Fedora core 23 (mariadb.x86_64, 1:10.0.28-1.fc23).
  - kaamos 9y ago
    OK, I see it now, thanks. I would even edit my original comment if I could. Still, the original article is biased without mentioning strict mode, the fact that it's been recommended to turn it on in any serious deployment for years, and that it is enabled by default in the latest MySQL/MariaDB releases. There are reasons to choose PostgreSQL over MySQL (and vice versa), but the article is certainly an example of unfair evangelism.
    - mseebach 9y ago
      It's a pretty damning point against MySQL that there's a default setting that you have to know to change, or else the DB will happily throw away your data. A lot of the evangelism against MySQL is rooted in a deep skepticism against letting a DB that would even have such a feature, much less leave it on by default for years after sort-of admitting that it's probably a bad idea, anywhere near your data. I loved MySQL back in the day when it would work straight out of the box on both Windows and Linux (and god knows not a lot of web software did back then), and Postgres was always, frankly is still, a bit opaque to actually get from zero to one. But I also remember discovering the varchar overflow and the users being pissed at me for losing their data. Luckily it wasn't a really important field and we discovered it reasonably fast, so fallout was contained. But the same app actually dealt with payments, it could have been much worse. It's basically a country song: MySQL is the kind of guy that's tons of fun, always the centre of the party, but used to disappear for multi-day benders without a word, but have now cleaned up and is disappointed that his high school sweetheart prefers the boring guy who went to college, got a job and was never late for a date.

porker 9y ago

I didn't realise a few of these existed, so thanks. > - page-level encryption This got me excited, but for anyone else looking: to keep the key securely (not in a file on the DB server, where an attacker can get it + the DB files) you need to but MySQL Enterprise Edition. Quoting from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-en...: > The InnoDB tablespace encryption feature in non-enterprise editions of MySQL uses the keyring_file plugin for encryption key management, which is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

gmac 9y ago

Which is already telling something about PostgreSQL evangelism. No. Speaking as an outsider, the Postgres community is about the nicest (and smartest) there is.

kaamos 9y ago
I know what I'm talking about. Over the last couple of years I had to speak to many people in the PostgreSQL community. While there are indeed many smart and nice people with balanced views and opinions there, the majority has this ideological bias. With ideology being that PostgreSQL is superior to MySQL in every single way. All counter-examples and arguments are usually ignored. I think it's something unique to PostgreSQL community, I've never met anything like that in other technical communities.
- fusiongyro 9y ago
  As someone who might fairly get scolded for this, we feel like praising MySQL's features is a bit like enjoying the recessed lighting and granite counters in a house whose roof is actively leaking. There are certainly features MySQL has that Postgres doesn't, but data integrity is problem 1, and most of us got burned by MySQL on that one at some point before we got here. Or, we deployed MySQL and then found out that the features we were depending on were InnoDB-only, but switching to InnoDB cratered our performance. Or we tripped on one too many of MySQL's numerous special cases or silent limitations. You can use MySQL productively. You can even benefit from its special features. But the thing taken together is a bit of a mine field. Postgres has some false advertising issues too ("object-relational"?) but the day-to-day living with it is a lot nicer, because the roof doesn't leak. I can live without the granite counters.
  - kaamos 9y ago
    Data integrity is of course important, and MySQL indeed has a weak story here, especially in older versions. Is PostgreSQL flawless when it comes to data integrity? No, that would also be false advertising. Some examples: - data checksums are disabled by default (silent data corruptions!), and the user manual specifically warns they are not cheap. And the reasons they are expensive is that PostgreSQL does not support O_DIRECT IO; - removing a file on the filesystem may lead to a silent loss of data in PostgreSQL: https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3f-45bc94c11759%40postgrespro.ru https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3... - some important control structures (which also impact data visibility and thus, integrity) are not even protected by checksums: https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYKOZ6KGu2PbzGiX%2B1NF-dFkC35FJMCtWQ%40mail.gmail.com https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYK... - there are no tools to verify replication integrity, similar to pt-table-checksum for MySQL; that would require statement-based replication, which is not supported in PostgreSQL, even in the next major release

fusiongyro 9y ago

Advanced replication strategies in MySQL and Oracle mostly cause hair loss and divorce. Everywhere I have worked that uses these databases eventually convinces itself that it needs futuristic replication technology and then goes on to suffer from outages and weird corruption. It turns out, distributed consistency is a hard problem. Oracle and MySQL both benefit monetarily from people assuming it will work and then activating it, only to find out a month or two later that it's a complicated problem that requires actual planning and oversight. I think Postgres will be a better product when it incorporates similar functionality, but the implementations in MySQL and Oracle lie somewhere on the border between false advertising and reckless endangerment. I appreciate that Postgres not having it as two-lines of configuration sends a message that you are making a commitment. Nearly everything else in Postgres is set-it-and-forget-it. You really want to have a DBA if you're going to try to make replication work. When you read the docs on having a hot standby and how much of a commitment that is and sense yourself getting tired, you're starting to appreciate what you might be getting yourself into with single-master replication, let alone multi-master.

kaamos 9y ago
> Nearly everything else in Postgres is set-it-and-forget-it. There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.
- fusiongyro 9y ago
  I read their article, and a dozen of the rebuttals. If you want to use MySQL badly enough, you can.
- glogla 9y ago
  Uber switched from MySQL used as relational database, to Postgres used as relational database, to MySQL used as key-value store. Nobody is saying MySQL is a bad key-value store!
  - pmarreck 9y ago

Nican 9y ago

I have been curious to ask someone who is knowledgeable about databases/MySQL: Spanner has a feature for creating interleaved tables, which helps data locality when performing joins on a distributed database. [1] Is this an important feature for a large database, and does MySQL provide anything similar? [1] https://cloud.google.com/spanner/docs/schema-and-data-model#creating_interleaved_tables https://cloud.google.com/spanner/docs/schema-and-data-model#...

teddyh 9y ago

EDIT: Never mind, I read too much into what another commenter wrote.

kaamos 9y ago
No, this is all from MySQL Community Edition.
- teddyh 9y ago
  Then what about (https://news.ycombinator.com/item?id=15172965 https://news.ycombinator.com/item?id=15172965)?
  - kaamos 9y ago
    Does that comment say I'm listing features only available in MySQL Enterprise Edition?
    - teddyh 9y ago
      It does say that one feature you’re listing is not really sensible to use without the Enterprise Edition, but you’re right, it does not say that. Original comment redacted.
      - kaamos 9y ago
        It is, in fact sensible without Enterprise Edition. The only thing it says is that page-level encryption in Community Edition is not intended as a regulatory compliance solution. Nothing more, nothing less. Even in case you care about regulatory compliance, there are other options besides Enterprise Edition.
        porker 9y ago
        What are the other options, and when would page-level encryption in Community Edition be useful? If I'm understanding it correctly, page-level encryption is to protect against someone gaining access to the DB server and copying the files off it. If the key is stored in a file on this DB server, I might as well not bother encrypting the pages.

glogla 9y ago

> - synchronous multi-master cluster (Galera, Group Replication) Which doesn't work, see [1]. What other features of those you listed the authors claim that work, and just don't when you look deeper? Lot of the big companies are using MySQL as a NoSQL database, and that's not a bad way to use MySQL, but it's a pretty bad relational database, see [2]. [1]: https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-cluster https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-clu... [2]: https://grimoire.ca/mysql/choose-something-else https://grimoire.ca/mysql/choose-something-else

kaamos 9y ago
Let me just tell you that I've read both links before.
- zimpenfish 9y ago
  If you've read the aphyr link with this (and the rest of his conclusion): > Unfortunately, even in totally healthy clusters, with no node failures or network failures, Galera Cluster does not satisfy its claims of Snapshot Isolation ...how on earth can you straight-facedly claim that it's a plus point for MySQL over PostgreSQL?

antoaravinth 9y ago

There is one point I need to add it here. Note that I'm not DB expert, but I need to say why I prefer Postgres over Mysql. Create a table like the following: create table user (name varchar(4)); MySql: insert into user (name) values ('antoaravinth'); The above statement works even though I have given the size of my varchar as 4! And retrieving gives me `anto`!! Postgres: insert into user (name) values ('antoaravinth'); Stops me there with the relevant exception! That shows how Postgresql is doing small things right. After seeing this, I never used MySql!

sigi45 9y ago
As said in many other comments: This is because you used MySQL in non strict mode. You don't run MySQL on Production in non strict mode.
- koffiezet 9y ago
  Tell that to developers who develop with the default config and then expect that to work in "Production".
- thehardsphere 9y ago
  As said in many other comments, the idea that there is even a "strict mode" which was not on by default for many years is itself a problem and a red flag for other problems.

sigi45 9y ago

master-master is evil. Not sure of your other points, i have no need for them. What i really don't like on MySQL (and i don't care for a lot of other stuff): MySQL 5.6 (yeah don't forget your version numbers) really sucks for queries with or. It sucks to write 2 selects with an union which is 100x faster than the same query with one select and ONE or.

grogers 9y ago

MySQL has similar issues to postgres vacuum. If 'checkpoint age' gets too high throughput drops by ~5x to prevent WAL wrapping. We hit this in production when using 5.5. They've made a lot of improvements to flushing in recent versions, but it isn't a strict advantage. With both DBs as long as you monitor you would be fine.

marcosdumay 9y ago

Well, personally what I see is people comparing a group of developers that are adamant on never corrupting any data, where everything is secondary and will refuse to push any feature that may cause problems with a group that is adamant on increasing market share, if it's by doing a low quality MVP so be it, that I have seen corrupting data on production several times, and whose software still carries the scars from those MVP's errors. Yes, MySQL is all featureful¹. The only thing missing there is guarantees that your data will stay good. 1 - Some of those are "no, we won't ever implement it" features that will do nothing but eat your data by design. Why would you ever want them in a persistence layer? Cache on memory somewhere better.

bonesss 9y ago
> Some of those are "no, we won't ever implement it" features that will do nothing but eat your data by design. Why would you ever want them in a persistence layer? Cache on memory somewhere better. I feel a significant motivation for MySql development are lightweight webapps on PHP like Wordpress where the developers are beholden to a third-party hosting environment and have a significant front-end focus... From that perspective I can see why bells and whistles that would be moronic in a normal application would start sticking to the database itself. MongoDB suffers from some of the same market pressure: being easy to demo and get from 0 to SAP in 10 min is great, but having to choose crazy insecure defaults and be open for data loss in order to get there is a showstopper in Enterprise circles. In both cases the ironic demands for competent administration are much higher because of all the potential for foot-shooting. "It's always worth it to take the pain up front."

feld 9y ago

> - optimizer hints > - optimizer tracing Hints are bad. They always have been. If you need hints your query planner is broken. https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion Even Oracle Tom says DO NOT USE HINTS IN PRODUCTION! Report the bug to Oracle and let them fix it properly.

will_be_no_ai 9y ago
As a general recommendation maybe this is ok. But, unrealistic relative to the world we live in. Sometimes you need to use hints. Oracle / PostgreSQL / what have you, are not perfect nor are developers writing the SQL. Is your customer going to wait for the next version of Oracle? Is your dev team going to fix some sev 3 performance bug? Hints allow you to use your brain to help the optimizer. Why would you make a hard rule not to use them? Why else are they there?
- feld 9y ago
  I won't speak for Oracle because it's always been a "if you have the money you can get a custom patch for the behavior you want" game. But for Postgres, you contact the developers. If your problems are costing you so much money why wouldn't you just hire a core developer on the Postgres team or hire EnterpriseDB on contract to work on your issues? They'll fix the bug, upstream the fix, and roll you updated packages you can push out to your servers. Not entirely different than working with Oracle, except you are working in the open source world. Hints are bad. Next time you upgrade your database you may end up with worse performance due to other changes in the query planner. Once your data crosses a certain size threshold it may also start performing worse. They are unpredictable. You will never know when they could break. Using them is the wrong solution.
  - will_be_no_ai 9y ago
    I agree with paragraph three. A hint is a directive. Now we are stuck with it. Bad stuff can ensue...maybe. But, maybe my customer is pissed now. Total cost to fix: a couple hours. If I had the budget and developers to make PostgreSQL as performant as Oracle, I would be living on an island next to Larry.