5 ms·
From many years of hosting both - MySQL seems easier at first but there are so, so many problems with its internal design that hide problems until its too late.
by mrmondo 10y ago
From many years of hosting both - MySQL seems easier at first but there are so, so many problems with its internal design that hide problems until its too late. PostgreSQL in my eyes is engineered in a much smarter way and has sensible defaults. PostgreSQL won't implement something unless it's _going to work_ and work properly. MySQL is analogous to software like Drupal and 'hobbiest' projects that have grown organically without proper engineering - and when it's too late you realise you're missing data, MySQL makes it easy to do things the wrong way, PostgreSQL enforces you do things the right way. We've also found that PostgreSQL performance far outshines MySQL as long as you've setup basic things like shared buffer sizes etc correctly.
- jypepin 10y agodo you have specific example? (sincerely interested)
- eddd 10y agoI'd start with reading this one: https://grimoire.ca/mysql/choose-something-else https://grimoire.ca/mysql/choose-something-else
- mrmondo 10y agoThanks eddd, I would have probably linked to this also, it's easier to point to people who have done proper write-ups than write my own experiences as I think there's enough out there.
- eknkc 10y agoWas just dealing with this today: If some row is deleted due to a foreign key cascade constraint, delete triggers are not executed. When you delete directly, they run obviously. It's worse than having no trigger support.
- rmetzler 10y ago- MySQL example: for MySQL there is one thing called Collation, which decides about the correct ordering of chars in table values. The default was `latin1_swedish_ci` for years, not `utf8_unicode_ci` which might be more sensible. - Postgres Example: to set up Postgres correctly, you need a Postgres user in the OS, who runs the database. That should enforce to not run the database process as root.
- CoachRufus87 10y agoI spent this past week learning/solving this very issue. MySQL's utf8 character set doesn't actually include _all_ utf8 characters. Use utf8mb4 instead. Same goes for collation; use utf8mb4_unicode_ci.
- lathiat 10y agoThe good news is they are changing the defaults in 5.8: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...
- rmetzler 10y agoThanks for sharing this. I looked it up on SO [1] and since I'm from Germany, I'll stay with utf8_unicode_ci. > For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively). [1]: http://stackoverflow.com/a/766996/104959 http://stackoverflow.com/a/766996/104959
- JimDabell 10y agoOne thing I've heard said before that always seemed true to me: PostgreSQL was built with their priorities being: correctness first, then features, then performance. MySQL was built with their priorities being: performance, then features, then correctness.
- smcleod 10y agoI'd agree except that PostgreSQL performance seems to have always won over MySQL in non-synthetic tests regardless, perhaps just through good quality engineering they haven't needed to 'worry' about performance until later.
- JimDabell 10y agoThat wasn't always the case, but it's easier to start with something correct and make it fast than it is to start with something fast and make it correct.
- hyperpape 10y agoWhere do you draw the line on synthetic vs. non-synthetic tests, and do you know where I should look to find them?
- dorfsmay 10y agoNo, when MySQL didn't have transactions, it was the fastest "database" for writes! Also, as somebody who's being very careful with which fields I update, keeping in mind indexes, I'm still in shock about learning that PostgreSQL updates all indexes on a table, even for columns not included in that write - that ha to have a cost.
- gaius 10y agoso many problems with its internal design that hide problems until its too late I always got the impression (since I started using it in Prod in the late 90s) that the MySQL team had never worked on an RDBMS before, even as users, and didn't really understand it. Back then they would say, you don't need foreign keys, you can just enforce consistency in your application, you don't need transactions, you can just handle it in your application, and so on and so on. Monty Widenius was very arrogant and thought he knew everything. Eventually they matured a bit and realized that actually, yes, the entire rest of the database community weren't idiots, maybe there is something to these features, but now they needed to find a way to retro-fit them onto what they had and now - 20 years later - they still haven't figured out how to smush it into their architecture. Whereas the Postgres crew were fresh out of the Ingres project led by the genius Michael Stonebraker, they had a solid foundation and very clear vision and a culture of doing the right thing, not the easy thing. There's a steeper learning curve to Postgres but once you have learned a few things you can easily guess the rest because it's so consistent. For MySQL you really need a "guru" because there are so many dark corners and weird edge cases that make no sense, you just have to "know" them.
- dom0 10y ago> I always got the impression (since I started using it in Prod in the late 90s) that the MySQL team had never worked on an RDBMS before, even as users, and didn't really understand it. IIRC that's actually correct.
- gaius 10y agoI think some of them might have been involved in miniSQL but it's a long time ago and my memory is hazy now :-) I was working for an ISP at the time and responsible for a gTLD, my project was called DNSQL, it was as you can imagine a DNS server backed by MySQL so we could edit it with SQL statements rather than manipulating the zonefiles in Perl or sed... Relational constraints and transactions would have made my job a lot easier... A few years later I inherited a 2Tb MyISAM database, which was "fun".
- 67726e 10y agoIn other words, MySQL is the PHP of RDBMSes.
- jagger27 10y agoThey practically grew up together.
- falcolas 10y ago> 've also found that PostgreSQL performance far outshines MySQL as long as you've setup basic things like shared buffer sizes etc correctly. Interesting, I've found the exact opposite to be true the last time I looked. Most comparisons which I've looked at which have shown PG to be faster are usually making really basic mistakes in scaling up the MySQL config (which is, admittedly, not a trivial task, but there are a lot of resources out there). Can you point to any benchmarks or usage patterns which show PG to be the better performer?
- cygned 10y agoPostgres is like the Apple of RDBMS - just kidding.
- pmarreck 10y agoUber made a pretty good case for mysql and against postgres when it comes to indexing: https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/