Hacker News Reader

Comment by smcleod | Hacker News Reader

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.

View on HN

One 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.

I'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.

That 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.

Where do you draw the line on synthetic vs. non-synthetic tests, and do you know where I should look to find them?

No, 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.

They are working to fix that. It didn't make it into PG10 but the developers are aiming for PG11.

I know, it just was so unexpected. Very few people realised that this is how it worked until some big blog post a few months ago.

If you have a table that undergoes frequent updates and this is a concern of yours, look into setting a fillfactor on it. This will help Postgres keep writes in the same page, which means it doesn't need to touch indexes for non-indexed columns.

Edit: non-updated columns, I mean.

> when MySQL didn't have transactions, it was the fastest "database" for writes!

`/dev/null` is the fastest "database" for writes ;)

You can always find (pretty basic) examples where either one is faster, and sometimes by a lot.

http://dba.stackexchange.com/questions/149729/mysql-vs-postg...

Performance a priority for MySQL? Just moments ago I spoke with some friends about their database performance problems with big joins on big datasets in MySQL. Switching to PostgreSQL solved the problem. And for a customer, simply upgrading from MySQL to MariaDB improved the performance 5-fold after just a 15 minute install.

> '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?

Uber made a pretty good case for mysql and against postgres when it comes to indexing: https://eng.uber.com/mysql-migration/

do you have specific example? (sincerely interested)

I'd start with reading this one: https://grimoire.ca/mysql/choose-something-else

Thanks 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.

Was 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.

- 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.

I 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.

The good news is they are changing the defaults in 5.8: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...

Thanks 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

Postgres is like the Apple of RDBMS - just kidding.

so 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.

> 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.

I 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".

In other words, MySQL is the PHP of RDBMSes.

They practically grew up together.

Reply on news.ycombinator.com