

MySQL vs. PostgreSQL comparison by Anchor (github's migration team) - adriand
http://www.anchor.com.au/hosting/dedicated/mysql_vs_postgres

======
pilif
The absolutely worst thing about MySQL is its lack of strictness. Maybe this
has changed, but I was furious when I, by accident, inserted latin1 data into
an UTF-8 table.

Instead of complaining about invalid input data, MySQL accepted the data and
_truncated_ it at the first invalid byte. Without error or warning.

Even if this was due to a misconfiguration on my part, behavior like this is
completely unacceptable.

<http://www.gnegg.ch/2008/02/failing-silently-is-bad/>

and don't get me started on other comparable issues like truncating overlong
texts added to varchar fields, or accepting invalid dates and silently
converting them to 0000-00-00 _shudder_

~~~
ilyak
[http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-
fa...](http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-faq.html)

Most of questions here are lack-of-strictness-related.

------
amix
It's a pretty crappy comparison. If you need transactions and ACID properties
then you pick InnoDB and not MyISAM. Now what are the cons of PostgreSQL?

1) It's hard to install and manage, which could explain why Postgre's adoption
is fairly low.

2) MySQL is used and proven to scale on very large installments (Yahoo,
Facebook, Google - just to name a few). Postgre's usage is much lower (Skype
and Reddit from those I know of)

3) Due to the adoption of MySQL it's very easy to get help and to buy help.
For example: <http://mysqlperformanceblog.com> offers great advice on
everything MySQL. Due to Postgre's smaller user base it's harder to get or buy
help.

4) The development of "MySQL and MySQL tools" is very rapid. Check out
Drizzle, Xtradb, xtrabackup, innodb plugin, rethinkdb.com (this is just a few,
there are tons of other engines and tools under development). It's unclear to
me how rapid Postgre's development is (for example, they still lack a decent
replication), but I doubt Postgre's development is as rapid as MySQL's.

5) The advances features of Postgres are not that needed in most web
applications. If they were that needed, more people would have used Postgres.
I.e. Postgres has a lot of non-essential features, while it lacks some
essential features (such as decent replication).

The bottom line is that MySQL is proven, easy to use and install, is under
heavy development, is backed up by billion dollar companies and has a very
large user base. MySQL is also built mostly for web-applications and offers
_great_ performance, great tools and great replication.

~~~
pilif
1) hard to install? What about `apt-get install postgresql` or the usual
./configure-stuff is hard? Also, EnterpriseDB provides pre-built one-click
installers for Windows and Mac OS X. Upgrading between major releases could be
painful as it requires a dump/restore, but lately, we got pg_migrator which
takes care of that (and pg 8.4's parallel restore helps in cases where
pg_migrator fails - not that I know of any)

2) the .org domain is hosted with PostgreSQL. Scalability-wise, that looks
good enough for me.

3) Kind of agreed, but then again, the documentation of PostgreSQL really
rocks. In my 8 years of (near-fanatic) use of PostgreSQL, I never came across
a problem the docs could not solve for me.

4) One major release per year might actually be quicker than what MySQL does.
I don't know about related tools, because I don't generally have need for any.
There's PGAdmin III which, AFAIK is updated as often as PostgeSQL itself.

5) You don't need the advanced features, until you use them and then you can't
live without them. It's like addiction to drugs :-) - I moved from MySQL to
Postgres back in the days (end of 2001) for a large project after spending
hours and hours of hair-pulling due to the lack of subqueries and views in
MySQL and since then, I never wanted to go back.

We're using PostgreSQL as the backend database for multiple large e-commerce
applications. Some tables are over 20G in size (1.5 billion rows in one of
them). PostgreSQL is easily handling ~60 queries per second on a quite dated
dual core box with 4 GB of RAM.

You might have had a lot of success with MySQL. I on the other hand had a lot
of it with PostgreSQL.

I don't know about the later versions of MySQL but when I moved to PostgreSQL,
it was years ahead of MySQL feature-wise and as I never had any performance or
scalability issues, I never felt the need to go back or even see if MySQL has
caught up already.

Just wanted to add my two cents here.

~~~
sandGorgon
Just wanted to point out a few differences to people hitting PGSQL after
getting their feet wet with MySQL.

1\. While installing PGSQL get your configuration variables correct, or it
wont work out-of-the-box (kernel.shmmax, autovacuum, shared_buffers)

2\. learn about authentication. PGSQL depends on authentication of the OS
("sameuser" on pg_hba.conf). MySQL behaves by default as the "password"
authentication method of PGSQL.

3\. Learn about template databases. Your first database is a template
database.

4\. PGSQL has "pg_" databases and slash commands (\dt, \connect, \q) for
system administration.

~~~
neilc
_get your configuration variables correct, or it wont work out-of-the-box
(kernel.shmmax, autovacuum, shared_buffers)_

The only thing you really need to worry about is making sure the kernel SysV
limits are appropriate (kernel.shmmax); autovacuum and shared_buffers are
configured reasonably out of the box.

 _GSQL depends on authentication of the OS ("sameuser" on pg_hba.conf)_

That depends on how PostgreSQL is packaged by your OS.

 _Learn about template databases. Your first database is a template database._

No; the default database is called "postgres", and it is not a template
database.

~~~
sandGorgon
Sorry - I should have clarified. These are specific to Ubuntu packaging.

I worked on MySQL before I had to work on PostGres and these were the major
turning points in _my_ understanding. Hope it helps someone else.

~~~
catch23
I use ubuntu all the time, I don't remember having to configure shmmax to get
it working with my blog. I think you only need to mess with that stuff if
you're trying to increase the number of connections or "scale" your blog. I
think 90% of the people who use mysql, use it without touching a configuration
variable, and those people could just as easily use postgresql without
configuring kernel.shmmax.

I don't know, but I'm guessing that 90% includes people messing around with
django/rails/lift etc, or they're running a blog/cms/wiki on some shared
virtual hosting somewhere.

------
JustAGeek
That is a very, very superficial comparison. Half of the contras for MySql
(the ones referring to MyIsam) don't really apply, since you simply use InnoDB
if you need those features. So it's no real help in deciding which DB to
pick...

~~~
jbellis
But then a lot of the pro- points go away, too. It's almost like it's two
different pieces of software.

~~~
vidarh
True to a point, but MySQL lets you pick and match on a per-table basis.

I've used MySQL extensively, and use Postgres for most things now, and I
prefer MySQL for many things. Not least replication, which is so much simpler
with MySQL (I'd love to be proved wrong on this - please someone point me to a
replication solution for Postgres that is as simple as for MySQL).

But the differences aren't great enough for most typical use that I'd worry
too much about which one to use. Currently I use Postgres because that is what
my team is familiar with.

------
tzury
Well, let us put it straight! GitHub uses MySQL. In fact, these guys maybe
helped install the new GitHub at Rackspace's data center, but their choices
are irrelevant to GitHub choice of platform (the title is bit misleading
though).

AS for the subject itself, I guess, if you are more comfort with either one of
them, this one would be the best choice for you.

If you never used anyone of them and wish to start a new project which
requires an RDBMS, you may search for other comparisons at Google (as well as
look at here: <http://news.ycombinator.com/item?id=184328>)

Most important, no matter what language you are using, make sure to use a
database abstraction library, so to make it easy to switch between them in
case you are not satisfied with your first choice.

------
rburhum
How about spatial support? Only PostgreSQL has support for spatial types (as
opposed to those GIS API skeletons that MySQL has).

------
technomancy
I didn't realize MVCC was only implemented in Postgres. That's a huge
advantage if you're doing anything other than a mostly-read DB.

~~~
CrLf
If I'm not mistaken (I'm more of a DB2 guy), Oracle implements MVCC too.

~~~
masklinn
It does, since version 7. MySQL also does under InnoDB or Falcon. Other DB
with MVCC are BDB, Firebird, InterBase, SQL Server, Sybase, ZODB, CouchDB or
ObjectStore.

------
dylanz
MySQL Con: Owned by Oracle

~~~
dylanz
If you need more background, Oracle "still" hasn't made any press releases as
to the fate of MySQL. This should be a very big "con", with a huge question
mark next to it. Many core MySQL developers aren't being too optimistic about
the future.

~~~
spudlyo
Oracle has owned the important parts of MySQL (InnoDB/InnoBASE) for a few
years now, and that hasn't stopped them from fixing bugs and adding features
in InnoDB.

~~~
ams6110
They've owned BerkeleyDB for a while also, and AFAIK they have been good
stewards.

------
sam1976
I was happy with MySQL except Master-slave configuration. Now that Oravle has
aquired MySQL I am looking for another open source DB and Postgres seems a
perfect fit.

