
Why PostgreSQL is better than MySQL - furkansahin
https://blog.2ndquadrant.com/postgresql-better-mysql-1/
======
craigkerstiens
I'm a huge Postgres fan and yes I'm probably biased towards it. I do think
it's probably the best open source database out there, but I also don't love
these types of posts.

What makes something better than another things is a whole host of things, not
just highlighting one really bad case from the other side. I'm sure if the
MySQL world wanted they could point out ways that MySQL is so much better than
Postgres. Just look back at how long it too for us to get upsert, we're
finally getting a better replication story in recent years. To take this one
case and highlight it doesn't put the Postgres community in a positive light
and it's a shame, because most I know within the community value good
engineering work.

MySQL has had a lot of people that contribute to it and has some good things
about it, it might be worthwhile for us to pay more attention to where they
are better and just focus on improving Postgres and leaving things at that.

~~~
danudey
I remember ages ago (a decade? or so?) I started working at a company using
Postgres for a project, and one of the things my boss asked me to do was to
set up replication for the database. We had it on all of our MySQL databases,
and everyone knew (myself included) that Postgres was a more serious,
enterprise-y, "real" database.

Well, I googled around and couldn't find anything conclusive, so I hopped into
the / channel for postgres and asked about it. "Is there any straightforward
replication solutions for Postgres?"

It was like the early days of open source all over again. The first thing they
did was challenge my needs. "Why do you need replication in the first place?"
"Replication isn't a panacea you know." "Postgres doesn't have the issues that
MySQL does that makes replication necessary." "Replication doesn't work well
in the first place."

A ton of people jumped into what had been a quiet channel to make sure I knew
that replication, which postgres didn't have, was something I didn't need in
the first place, most of which happened before they even knew why I was
asking.

After sorting through all that, a few people suggested "solutions". "Just have
a cronjob rsync your data directory over to another machine every minute, and
if your first server dies then you can just start postgres on the second one."
Other solutions were much more convoluted. Patch postgres, add this software,
manage it all yourself. And through it all, they kept telling me that I didn't
need replication anyway, "but _if you INSIST_ …".

In comparison, the MySQL channel on freenode was staffed with MySQL employees
and volunteers who were always happy to help you solve your problem while
teaching you what you were missing and giving you resources you needed. They'd
answer questions, help your configuration, and even fix your SQL queries for
you, all without being condescending about it or making you feel like an
idiot.

And that's why I gave up on Postgres: the community. Postgres's was
ridiculously hostile and self-important, verging on insecure and defensive.
MySQL's was helpful, friendly, and full of resources. To this day I still hear
great things about postgres, and I know it does a lot of things better than
MySQL, but whenever someone writes a self-superior blog post about it I always
think back to how much easier my life was by using a database where I could
ask questions of the community and get useful answers.

~~~
rosser
And it was the community that drew me in. Very early in my database career, I
was having massive performance problems with my postgres install. I sent a
very sharply toned message to the postgres performance mailing list. Within
_hours_ of that message, I was trading stack traces with _Tom Lane_. He was
under no obligation to help me; in fact, I was, in hindsight, kinda a dick
about it. But he did.

You probably can't buy support of that caliber, no matter how much money you
throw at it.

YM, as always, MV.

~~~
manigandham
> You probably can't buy support of that caliber, no matter how much money you
> throw at it.

You're underestimating how good support can be. Postgres doesn't actually have
a primary sponsoring company so good (and equivalent) talent is widely
available from multiple vendors.

------
erulabs
MySQL is bad because they've tracked a bug across 3 different companies &
working groups.

Postgres is good because we have no open bug tracker whatsoever and we're
always adding new features!

I can't figure out who this article is for - certainly not DBAs!

~~~
danudey
Not being sarcastic: this type of article always seems to be written with an
audience of "people who love to pick a side and hate the other side, but
haven't picked a side yet".

The article picks an extreme example and uses insinuation and condescencion to
denigrate MySQL based on one example, but it goes further than that. For
example:

> Maybe its wrong to start a flame war on this

So he knows that's what he's doing, and he's okay with that.

> but I need to say, at a personal level, that this is exactly the reason why
> I have spent years contributing to PostgreSQL and never once contributed to
> MySQL.

Maybe, but if I wanted to contribute to Postgres where would I start? There's
no bug tracker to look through. With the MySQL family, there is.

Also, I'm willing to bet that most people who've contributed to MySQL haven't
contributed to Postgres and vice-versa, so to act as though you're doing it
from some kind of principled stance rather than just practicality makes no
sense to me.

This entire blog post is a giant attack ad against MySQL for the purpose of
making it look bad. "MySQL left a bug unfixed for 14 years while its users'
data slowly corrupted. Vote Postgres for RDBMS, and I promise to keep your
auto_increment fields consistent, day in and day out."

~~~
pgaddict
> Not being sarcastic: this type of article always seems to be written with an
> audience of "people who love to pick a side and hate the other side, but
> haven't picked a side yet".

> The article picks an extreme example and uses insinuation and condescencion
> to denigrate MySQL based on one example, but it goes further than that. For
> example:

Yeah, I share this opinion. I may agree with some points Simon makes in the
blog post, but I certainly dislike how it's presented.

> Maybe, but if I wanted to contribute to Postgres where would I start?
> There's no bug tracker to look through. With the MySQL family, there is.

I really doubt bug tracker is where people start their contributions. I mean,
you don't go to a bug tracker, because (a) if it's broken someone else is
probably already working on fixing it, and (b) the unsolved issues are rather
complex and not quite suitable for new contributors.

There's actually a bunch of pages on the wiki that might help you:

* [https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_develo...](https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F)

* [https://wiki.postgresql.org/wiki/TODO](https://wiki.postgresql.org/wiki/TODO) (a nice list of development topics, including some quite simple ones)

I'd recommend picking a feature that matters to you and either add it (if it's
missing) or improve it in some way. That's how I started contributing - I
improved a bunch of stuff in the internal statistics tracking, because I
needed that. I improved a bunch of performance regressions, because they were
affecting the systems in our production systems. And so on.

Then start talking to people on pgsql-hackers. Send a patch, review patches
from other people in the commitfests.

------
arekkas
The reality, for once, met my expectations in this article. Making imperative
assumptions and backing them up with a poorly written 450 word count article
that some angry (literally: "OMG sorry LMAO there is a 14,5 year old bug that
was solved just now so the product must definitely be worse than the other
one") developer wrote down in two minutes. That's what the title implied
instantaneously.

Sometimes I don't understand the HN community. There is so much good stuff on
this page, but at other times things that certainly don't deserve any
attention make it to front page.

------
vijayagrawal18
I was hoping to see analysis of few features and their performance under
various constraints.

In the end, felt like click-baited

------
nobleach
Perhaps a better title would have been: "One way in which PostgreSQL is better
than MySQL". While I love Postgres, I have to admit that MySQL has its
usefulness as well. I just did a side project where I started in node and
postgres, realized I needed to hand it over to a guy who would want to end up
hosting it on some 5 dollar a month PHP-esque plan. So, I bit the bullet,
migrated the DB over to MySQL, and rewrote the backend in Laravel/PHP. It'll
do just fine for his usage. My alternative was to host it on DigitalOcean, and
then become the IT guy.... or perhaps spin up Heroku... but in the end, I just
wanted to pass off a package and be done with it. MySQL was dead simple for my
use.

~~~
syncsynchalt
In my experience writing software with generic SQL support the example shown
here highlights something that's endemic in MySQL.

MySQL has a bug (eg. #199) or some deficiency (no subselects, no transactions,
incorrect utf-8 support)[1]. The community's solution is a workaround (Here's
how to restructure your select so that you don't need a subselect. Maybe you
could use UUIDs instead of integer keys). Meanwhile the problem never existed
in Postgres, which seemed to have been correctly and completely implemented
from the start. After several years the issue is fixed in MySQL and the
boosters ask that the score be reset and the clock re-started.

Postgres has its deficiencies and bugs but other than replication they never
seemed core to the product in the way that they were for MySQL.

[1] These deficiencies span several decades and might seem unfair to bring up
but I want to show that what the article is highlighting is just another round
of the same problem.

------
wolph
This post actually makes me wonder... is there anyone that objectively still
thinks MySQL is the better database of the two?

The only small advantage of MySQL I can think of (which is a great downside as
well) is that it's really tolerant towards all sorts of erroneous usage and
data.

The following should be a problem but are accepted by MySQL

\- missing group by clauses

\- all of the `ignore` commands (insert, alter table, etc.) \- incorrect dates

\- not enforcing foreign key constraints

~~~
tyingq
Uber switched from Postgres to MySql. [https://eng.uber.com/mysql-
migration/](https://eng.uber.com/mysql-migration/)

If I remember right, though, mostly because their use pattern was more like a
key value store, and MySQL worked better for that pattern.

~~~
wolph
Thank you, very interesting read! They do raise a few good points where MySQL
outperforms PostgreSQL. I suppose that's one of the trade offs between them,
data safety vs. performance.

~~~
ris
> very interesting read!

Well, it's also quite a misleading read as the numerous rebuttals will point
out:

[http://rhaas.blogspot.co.uk/2016/08/ubers-move-away-from-
pos...](http://rhaas.blogspot.co.uk/2016/08/ubers-move-away-from-
postgresql.html)

[https://blog.2ndquadrant.com/thoughts-on-ubers-list-of-
postg...](https://blog.2ndquadrant.com/thoughts-on-ubers-list-of-postgres-
limitations/)

[http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choic...](http://use-
the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases)

------
derefr
Curious: has anyone tried linking a Postgres installation with a MySQL
installation using
[https://github.com/EnterpriseDB/mysql_fdw](https://github.com/EnterpriseDB/mysql_fdw)
?

My intuition is that, if there's anything MySQL does uniquely well on the
"being an ACID datastore" front, then this would let you get the best of both
worlds—essentially letting some of your Postgres tables use MySQL's storage
engines _et al_.

~~~
pgaddict
It's possible, but FWDs are not really a viable solution for "pluggable
storage". It also limits what the planner/optimizer can do, and (obviously) it
introduces latency because there's network communications.

FWD are a great thing when you need to occasionally access remote systems, but
it's not a drop-in replacement for storage.

For example, how exactly would you do consistent backups?

------
mannanali413
I had hoped that this article will illustrate the scientific/technical reasons
for PostgreSQL's superiority over MySQL. Alas, it just mentions that there was
a bug that got fixed after 14+ years in MySQL. The author doesn't even
mentions the severity of the bug and the negative effects it had. Overall, for
me this article is nothing but the author just "blowing his/her own horn".

------
tzs
What annoys me about MySQL is that it gets simple basic SQL wrong. For example

    
    
      update foo set a = b, b = a
    

is supposed to swap the values in columns a and b, according to the SQL
standard. Logically, all of the right hand sides are evaluated using the pre-
update values from the columns, and then the results are assigned.

In MySQL, it logically does the assignments left to right, not evaluating an
assignment's right hand sides until it gets to that assignment, and that
evaluation uses the updated values from the already evaluated assignments.

In Python terms, it is supposed to be like this:

    
    
      a, b = b, a
    

but it is instead like this:

    
    
      a = b
      b = a
    

If we had the time, I'd love to switch to something else just to get rid of
annoyances like that.

------
jayflux
My only take away from this article was PostgreSQL doesn't use a bug tracker.
I didn't even know there are large projects out there not using some tracking
system for bugs/issues

------
mlinksva
> If you use PostgreSQL in the cloud or from a services company, make sure you
> ask them what they have contributed to PostgreSQL and what their capability
> is to fix bugs and with what SLA. The answer to that question is what keeps
> open source well funded for these latest developments and future ones too.

Reasonable sounding theory. How true is it, how significant?

Does that story describe how 2ndquadrant gets business -- other vendors have
customers asking for an SLA on PostgreSQL fixes, which would require
PostgreSQL experts to meet?

------
mlinksva
I'm curious about the strategic thinking behind a post like this. I'd think
that the serious money that can be gained by taking marketshare from another
database for a company like 2ndquadrant would be in businesses using (or
contemplating) Oracle Database, not Oracle (or some version of) MySQL.

------
zilchers
Is it just me, or was that huge bug with serious implications if you don’t
know it exists? I don’t often use MySQL, maybe its well know in the community.

~~~
tatersolid
I agree. Basically makes MySQL useless for a ton of application (data
warehousing comes to mind).

I wonder how many private forks of MySQL had fixed that and never upstreamed a
patch.

------
rompompel
if you're not keeping score you're only practicing

So the main point why postgres is better is that they don't use a bug-tracking
system? Definitely that makes any qualified comparison impossible of such
claims as the better "time to fix" or bug responsiveness. IMHO this post is
trying to sell a weakness as a strength.

------
tkyjonathan
What about Postgres DBAs? I don't see too many of those around to allow
Postgres to be picked up more....

------
metafunctor
Isn't MariaDB where it's at, nowadays? And MySQL is tainted by Oracle?

------
lwh
This type of shitpost makes the "victim" look better every time.

------
w0m
Not being owned by Oracle?

------
marklit
Does anyone have a link to a collective list of all known bugs in PostgreSQL?

~~~
sgift
[https://wiki.postgresql.org/wiki/Open_Items](https://wiki.postgresql.org/wiki/Open_Items)

This seems to be the canonical list.

~~~
geofft
Wow, this surprises me a lot, especially combined with the consensus position
that Postgres is good, robust software. What is Postgres doing right and how
can other projects follow their lead?

~~~
pgaddict
On a more serious note, what makes Postgres so reliable is a number of things.
For example:

* careful patch review process - Sometimes it's a bit grueling, and it takes time to get stuff in, but it's extremely valuable. Not only for finding bugs but alternative approaches to implementing the feature.

* careful testing - It's a natural part of the review process (Does the patch have tests? Can I come up with something weird that makes it fail?). Of course, there's a bunch of machines with very different configurations (OS, hw resources, ...) that are generally quite good in finding race conditions, incorrect assumptions in the code, ...

* bugfixing - Someone reported a bug on pgsql-bugs? Challenge accepted. Customer reported a bug internally? Better fix it ASAP.

~~~
geofft
Was Postgres always this way? Was the code from Berkeley abnormally
stable/robust for commercial software (and _extremely_ abnormally
stable/robust for academic software), or was there an effort to clean it up to
get to high quality?

~~~
pgaddict
I wasn't around back then, but AFAIK it improved over time.

------
whipoodle
It is refreshing to see someone just come out and say this.

------
tuna
2017 and my dude still trying to sell consulting by applying the old emacs vs
vim strategy. I usually defer to facebook using thousands of mysql and other
big companies using way more instances of pgsql for day to day advice. Maybe
next time, with a better blog or a matrix with some effective info noting the
experience you guys have managing/developing against it. Also the patch for
MySQL would be appreciated, why it did took you so long ? xoxox

