PostgreSQL or MySQL?

It's funny how people become impassioned about their database choices, but here's my 2 cents.

I go for postgresql. I am just biased against mysql, though it is very popular and works for many people. I hear rumors of cases where mysql lost transactions under certain situations, though I hear that was fixed long ago. It still makes me not quite trust it, though. Mysql is also not quite as free as postgres. I hear mysql is very easy to install and maintain compared to postgres, but I still find postgres to be very easy to install in the scheme of things. I hear mysql is faster for low numbers of users and low query rates, and postgres is faster for high numbers of simultaneous users, though the benchmarks I've seen are kinda old. Also postgres seems to have always been ahead in terms of the capabilities of its query language, and other various transaction management strategies and database standards, though I forget all the alphabet soup of acronyms that come up.

I've used postgres on a linode 64 and 128 and the speed is fine for my needs. At first I thought there was a problem consuming IO tokens during routine maintenance, but it turned out I had a huge database I'd forgotten about that was causing it. And postgres has made routine maintenance better lately and I haven't seen that problem any more.

Another factor is that already existing open source software is most likely to work with mysql over anything else. Postgres might be #2, and other databases are much less supported.

To be honest, that is the only thing about mysql that appeals to me. It's always a pain to have to reconfigure software that comes with mysql support as the deault and is most tested with mysql. Otherwise, the speed differences are unnoticable, and I am familiar enough with postgresql that I could install it in my sleep. I'll take the rock solid stability and power of postgres over the slightly faster, easier to use mysql any day.

I'm a fan of postgres. I just don't see any reason to use a weaker system, although mysql is improving quickly, Postgres has always been way ahead of the game.

Small site, speed a must, use SQLite, then MySQL, otherwise use PostgreSQL

@OverlordQ:

Small site, speed a must, use SQLite, then MySQL, otherwise use PostgreSQL

Does SQLite do transactions? How about subqueries?

@ptomblin:

@OverlordQ:

Small site, speed a must, use SQLite, then MySQL, otherwise use PostgreSQL

Does SQLite do transactions? How about subqueries?

I could easily answer, then explain my answers (I had the same questions when I first heard of it). Why not take a look at what it does not support under SQL92:

http://www.sqlite.org/omitted.html

:D

HTH – Mark

@ptomblin:

@OverlordQ:

Small site, speed a must, use SQLite, then MySQL, otherwise use PostgreSQL

Does SQLite do transactions? How about subqueries?

MySQL is the least ANSI-SQL compliant Database in the marketplace. MySQL isn't even ACID, so you can't say MySQL 'supports' transactions.

On mysql do this for example:

create table T (a int, b int);
insert into T values(1, 2);
update T set a=b, b=a;

What is the result? Well, on most REAL database the values whould be swapped: A=2, B=1.

On MySQL unfortunately the result is: A=2, B=2.

Man, with all the LAMP people around, I'm really surprised nobody's come to the defense of mysql.

I'd really suggest testing postgresql with your config. It's really not bad on a low end linode, and will give you plenty of room to grow.

@OverlordQ:

Small site, speed a must, use SQLite, then MySQL, otherwise use PostgreSQL

FWIW, the primary development site for SQLite, http://www.sqlite.org/ is hosted by linode.com. It is a linode160 on host43.