4 ms·
Postgres adhered to a philosophy of "make it correct, then make it fast". MySQL adhered to a philosophy of "make it fast, then if someone complains enough, con
by ubernostrum 10y ago
Postgres adhered to a philosophy of "make it correct, then make it fast".
MySQL adhered to a philosophy of "make it fast, then if someone complains enough, consider making it correct".
The result of this is predictable: Postgres is now fast and correct in its implementations, while MySQL is fast and burdened with a large number of "gotchas" and legacy incorrectness.
For example:
MySQL's traditional "fast" table type doesn't support transactions. So you have to select a different table type if you want transactions, but then when you do, you discover that you need to usually go "up" one level from what you'd expect, because the REPEATABLE READ level only affects SELECT, not INSERT, UPDATE or DELETE queries. Which in turn means you can have an inconsistency between the set of rows you'd see in a SELECT with a set of conditions, and the set of rows that would actually be affected if you issued an UPDATE with that same set of conditions. You have to go up one level to READ COMMITTED in order to start solving this.
MySQL's Unicode collations have some bugs; when I was at Mozilla, one we ran into was that we had to set up our own custom collation to get, for example, "e" and "é" to be considered distinct characters (if you ever used MDN, and once saw a strange bug where French versions of topic tags showed on an English article? That's why -- MySQL thought "CSS Reference" and "CSS Référence" were the same tag).
MySQL infamously accepted almost any kind of garbage values regardless of declared column type, accepted values larger than the declared column width (i.e., you could insert a 300-character string into a 256-character VARCHAR column), and would silently or near-silently mangle, truncate or just drop the data.
etc., etc.
I cannot at this point imagine using MySQL for any kind of serious project.
- nailk 10y ago> MySQL's traditional "fast" table type doesn't support transactions InnoDB is the traditional table type now. Myisam was forgotten long time ago. > we had to set up our own custom collation to get, for example, "e" and "é" to be considered distinct characters You could just use "varchar CHARACTER SET utf8 COLLATE utf8_bin" > MySQL infamously accepted almost any kind of garbage values regardless of declared column type There is a setting that allows strict checking. > I cannot at this point imagine using MySQL for any kind of serious project. It is used by many for serious projects.
- philliphaydon 10y agoIn regards to charset and strict checking, these are settings which should be defaulted. I think the problem is that people run into these issues then have to seek solutions, which shouldn't be issues to begin with. I don't know why they aren't now defaults.
- ubernostrum 10y agoPretty much. "By default we mangle and throw away your data, but there's a configuration setting to change that and make it actually store what you wanted to store" is not a useful way to do things.
- morgo 10y agoStrict has been the default since MySQL 5.7. The default character set switches to utf8mb4 in MySQL 8.0.
- philliphaydon 10y agoThat's great to hear.
- i336_ 10y agoI'm reminded of https://developers.slashdot.org/comments.pl?sid=204433&cid=16703529 https://developers.slashdot.org/comments.pl?sid=204433&cid=1... as I read this exchange. I found the above link in another HN thread some time ago. When I showed it to some PHP devs recently (I unfortunately don't remember exactly who I talked about it to) I was promptly told PHP does not do what is described in the above link (from 2006) and is generally smarter nowadays. It's sad that languages and runtimes take so long to fix their core insanities (if ever), and it's also sad when systems are built without front-and-center regard to design correctness from the start. But on the flip side, the only people capable of envisioning good designs of this level of scale are often worn out shells of their former selves at the "you'd have to pay me 7 figures" stage... and of course you have to make sure you pick people who aren't completely out of touch with the latest developments in $category science...