
MySQL v PostgreSQL: Which have you used and why? - babul
MySQL v PostgreSQL: Which have you used and why?
======
st3fan
We started with MySQL and switched to PostgreSQL when some unit tests started
to fail after doing a minor MySQL upgrade. Something like going from 5.0.1 to
5.0.2. We tracked this down to MySQL giving completely different result sets
for the same (not very complex) query after the small upgrade.

We never looked back. PostgreSQL is really powerful and we use it in a
clustered and soon sharded setup. Works well for us.

~~~
immad
How is PostgreSQL clustering going? I heard that was a pain, whats your setup?

~~~
st3fan
Slony. It is far from perfect though. But at least it is a good and stable way
to have a backup database ready.

------
jawngee
We use postgresql at <http://massify.com>

I don't have enough experience with MySQL to be objective. I chose PostgreSQL
because it had a feature set relatively on parity with Oracle and the other
big boys. So far I haven't been disappointed.

Well, except for tool support. I'm still looking for a good schema diff tool
for it (something as brilliant as SQL Compare by red gate) and a good ER tool
(something as simple but functional as Enterprise Manager for SQL Server).

Otherwise, I really haven't considered looking at anything else.

~~~
jm4
Have you tried DbVisualizer? I don't think it does schema diff, but it covers
quite a few other features in Enterprise Manager and can extract ERDs from
your schema. It's relatively cheap too (or free with limitations) and works
with any database with a JDBC driver.

There's also Aqua Data Studio. I've never used it, but it looks pretty
awesome. A little pricey, though.

<http://dbvis.com/products/dbvis/> <http://www.aquafold.com/>

~~~
jawngee
Haven't tried dbvisualizer.

Huge fan of Aqua Data Studio though, great tool.

------
jhancock
I've recently been through this decision process. I had v1.0 of
<http://shellshadow.com> using mysql. I ran into one bug in my assumption
about how mysql was handling unique constraints and from there started to look
at postgres again. It turned out my decision to move to postgres, which I put
in production last month, was more about timing of pfg 8.3 coming to maturity
than absolutes against mysql.

Here are a few points that tipped things to postgres. 1 - constraints and
complex queries. MySql handles simple selects fast but has somewhat
unpredictable behavior on complex queries. Including counts. Postgres
generally scales in proportion to complexity of the query well. 2 - Postgres
8.3 came out. This was a big deal as it cleans up lots of things that want
into 8.x. 3 - BSD-style engineering. Everything from docs to source code to
make files is more rigorous with old school BSD projects. FreeBSD and Postgres
are clean!!! I don't want to hammer on linux, GNU, GPL stuff too much, but to
be honest, this camp is stable because of massive user base and companies put
in effort to make it stable. Its a sledgehammer approach. In general I find
projects like FreeBSD and Postgres to follow more rigid engineering processes.
I like this in my DB ;) 4 - Full Text Search built into postgres 8.3. MySQL
has FTS, sort of. Postgres prior to 8.3 had it as an add-on. You can run
external Full Text Search engines like lucene or ferret or maybe even better,
sphinx. But this adds one more "engine" to manage. Most real world FTS use
cases needs to have its results filtered in context of user prefs and have
results filtered and sorted by other DB criteria. This means that for many FTS
use cases, it is very desirable to just give the DB one big complex query and
have it return things all sorted out. Using an external FTS system, you
usually end up taking your FTS results and going back to the DB for further
queries to filter and sort the FTS results. This adds to app code complexity.
With postgres 8.3, you can do this in ways that MySQL cannot begin to compete
with.

Hope these rational help. I am very happy with my decision to switch from
MySQL to postgres and the process was not painful.

good luck, Jon www.shellshadow.com

~~~
babul
I thought about that too and the timing of the new relaease is a big factor in
the decision making.

A year ago is would have been MySQL, just because everyone else who is big is
them (e.g. paypal, google, amazon etc.) but now I really think PostgreSQL is
the better choice and will be going ahead with it until I can think of a
reason not to.

It is now just as fast, but much more robust

Thanks

------
mdasen
While Postgres seems nice, mySQL is still the choice of the majority of high-
traffic sites that absolutely need to scale. I'm not saying Postgres is bad,
but their only huge-volume user is Skype. They simply don't have as many of
the huge, high-traffic users such as craigslist, eBay, Ticketmaster, Facebook,
Digg, del.icio.us, Flickr, LiveJournal, Wikipedia, YouTube, etc.

Those sites need to scale an order of magnitude (or several) greater than
sites like CD Baby or Macworld.

Just look at: <http://www.postgresql.org/about/users>

Other than Skype, there aren't as high-use sites as the ones I mentioned that
go for mySQL. Postgres isn't bad, but it just hasn't been tested at the same
level. Could it do better at that level? It IS possible. I prefer to go for a
sure thing.

~~~
nostrademons
Reddit is also Postgres, if you consider that high-volume.

~~~
apathy
Did not know this -- very cool.

One of the only reasons I still use MySQL is that PostgreSQL would break some
Drupal sites I deployed. I miss the ease of obtaining relational integrity
under Postgres (although pgsql's EXPLAIN can be horrifying compared to
MySQL's).

Have scaled up to saturating an OC12 with Postgres (17 web servers) and doing
30 million hits/month with MySQL, so it's not like I care that much, but MySQL
has a nasty tendency to piss me off in corner cases, and I'm getting tired of
it.

Definite pros and cons to both. I've seen people stick with (and patch) a
particular point release ofMySQL (eg. 3.23 for AdWords at one point) just
because the errors were consistent and they were no longer doing much to the
schema. Never seen that happen with PostgreSQL.

Jeremy Zawodny's tools (mytop, etc.) are invaluable if you have to get a MySQL
shop to scale up (eg. without migrating a metric fuckload of existing SQL),
btw.

~~~
neilc

      pgsql's EXPLAIN can be horrifying compared to MySQL's
    

Really? I'm curious if you could explain what you found unintuitive about PG's
EXPLAIN.

BTW, there's a "ptop" (pg_top) project for PostgreSQL as well.

~~~
apathy
psql's EXPLAIN basically shows you the execution plan, IIRC. MySQL's EXPLAIN
provides the information about what keys will be hit for which joins in a more
compact fashion.

------
eb
Didn't you just basically ask the same question 6 hours ago?

<http://news.ycombinator.com/item?id=184099>

Also:

<http://news.ycombinator.com/item?id=41133>

<http://news.ycombinator.com/item?id=4861>

~~~
babul
I had some problem intially getting the question posted here. The second post
asks a similar question but slight variation. I am really intersted in this at
the moment so sorry if this seems to be a double posting. Thanks.

------
1gor
I initially wanted to settle on MySQL for the sake of simplicity. But got
bitten twice by it and have since changed my mind.

a) Once I had to store a large chunk of time series data in MySQL 'TEXT'
field. Got errors in the application, could not figure out where they are
coming from.

Turned out, MySQL has 1 to 65535 characters limit for TEXT field, and I should
have used LONGTEXT type. MySQL has simply bitten the tail off my data and
happily stored the rest without any errors or warnings. OK, I should have RTFM
but still, the idea of a database corrupting my data without any warning makes
me very uncomfortable...

b) Second issue that I've discovered is simply the ability to make 'hot
backups' to SQL text files. pg_dump will produce consistent backup from
working db server, mysqldump requires you to stop the server or to lock its
tables etc. 'Hot backup' of mysql databases can be achieved through other
methods/tools but at the cost of greater complexity.

~~~
rbanffy
"MySQL has simply bitten the tail off my data and happily stored the rest
without any errors or warnings. OK, I should have RTFM but still, the idea of
a database corrupting my data without any warning makes me very
uncomfortable..."

That is just insane. I heard about it a couple months ago and wondered what
were those folks smoking when they wrote it like that.

The hot backups thing never crossed my mind, but is very interesting. Of
course, it relies on transaction consistency and MySQL has the option
(shivers) of not having it, thus the locking demands.

From what I know (I am a PostgreSQL guy too), when you use a transaction-aware
data store in MySQL, these behaviours are gone (along with any performance
edge it may have over PostgreSQL, but that's another story), so, you may want
to give it a try.

------
nostrademons
Started with MySQL, switched to PostGres for latest project, but latest
project doesn't have any significant traffic yet, so I can't give you a field
report yet.

MySQL can have fairly unpredictable performance characteristics if you're not
an expert in it. It certainly can scale, but it's very easy to kill your
performance if you're indexed wrong or have the wrong table type or wrong
cache settings or run into table locking issues. (And these aren't theoretical
issues; we've run into some of them at past MySQL-based websites.)

I don't have the time to spend debugging cache settings and EXPLAIN SELECTs
and locking issues, so I went with Postgres, which supposedly has many fewer
pitfalls "out of the box". Been happy with it so far, for development, but
haven't needed to scale it yet.

------
andrewf
Postgresql here, for all the reasons states by over commenters, with an
additional proviso: we were a Postgres shop when I arrived and nobody has ever
seen a reason to change.

Sometimes we'll think wistfully of more OTS solutions for Oracle and MySQL, in
a grass-is-greener sorta way, then we see stuff like this:
<http://bugs.mysql.com/bug.php?id=11472>

------
brlewis
I've been using PostgreSQL since 2004 on <http://ourdoings.com/>

I've been watching free-software databases since 1997, when people at MIT were
using msql and I thought mysql would be a better choice. PostgreSQL looked
like it had the potential to be better than mysql, but it wasn't fast or
stable yet.

A few years later I tried PostgreSQL 7 and it was great.

------
mrbits
Our main product uses MySQL 3. We are unable to upgrade it to version 5
because a key violation problem. Only next versions will use MySQL 5, so we
are not able to use UDF nor stored procedures. When developing another
strategical product, Ricardo Banffy used PostgreSQL with a very good
performance and nice UDF and stored procedures.

------
crad
PostgreSQL for many reasons. If you use MySQL check out <http://sql-
info.de/mysql/gotchas.html> to stay on top of potential problems.

------
fschmidt
We switched from MySQL to Postgres because MySQL does not scale. MySQL
rebuilds all indexes for InnoDB tables (and all other storage engines) on any
trivial "alter table" even though InnoDB indexes refer to records using the
primary key. So just adding a column to a big table means taking down your
database for hours, even though the rebuilt indexes will be exactly the same
as before the rebuild. So dumb... We are happy with Postgres.

------
systems
Postgresql

1\. license less confusing 2\. great mailing list support

~~~
st3fan
There is also enough commercial support for when you have to bring the marines
in to fix stuff.

------
patrickg-zill
I have used both, MySQL has the edge on friendliness but Postgres is overall
the better database. A dual or quad Opteron system with a decent amount of RAM
and well-tuned Postgres should be able to handle queries sufficient to drive a
web site at 50 to 100Mbps continuous bandwidth, assuming no ridiculous
queries.

------
Hexstream
I tried MySQL like 2 years ago and out of the box it preferred to insert a
known-invalid "000000" date than aborting loudly.

The last thing I want my DB to do is corrupt my data silently. There were
other braindead "philosophy" problems but I don't remember what they are.

Been a happy user of Postgres ever since.

------
rkeene
Both. We use Ruby on Rails both on Windows and Linux. MySQL and PostgreSQL
sometimes work on one, sometimes on the other. If one does not work we just
switch to he other. Often small revision changes in the DB or in Rails will
break one or the other.

------
wallflower
MySQL. Simple to setup for non-complex websites. And I wonder if MySQL's rise
to billion-dollar acquisition was fueled by the growth of PHP (read: non-CGI)
websites.

------
pyroman
I just ran into a case where I needed a FULL OUTER JOIN and MySQL let me down.
It looks like PostgreSQL would have saved me from UNIONing two queries.

------
xenoterracide
we are building our product on postgres, because we like the sql features that
mysql doesn't have, such as constraints and checks, or really much of anything
relational.

------
wvenable
MySQL. Easy to setup replication and good tool support.

