
Who wants to discuss about MySQL vs PostgreSQL? - volida

======
nickb
Our Ruby on Rails app uses PostgreSQL (for now but that will change). We
started off with MySQL but after growing quickly, we were forced to look into
partitioning of the data. PostgreSQL was made to be easy for advanced stuff
like that. We tried to do it with MySQL and failed because we're not MySQL
experts and we managed to get it working with Postgres. Postgres has better
documentation than MySQL so that might be the factor if you don't have a DBA
you can readily ask for help. Here's a write-up by someone who went through
similar process:

<http://enfranchisedmind.com/blog/archive/2006/11/04/166>

Also, if you use Postgres on multi-CPU machines, it's faster than MySQL. But,
make sure you read this:

<http://www.suite101.com/article.cfm/oracle/115560> (comparison of
Oracle/MySQL/PostgreSQL)

One last word of advice if you end up using PostgreSQL, learn and use this:

<http://pgreplicator.sourceforge.net/>

Finally, we are moving back to MySQL because we found a really fast
replacement for the MyISAM storage engine (a lot faster than Postgres) and our
part-time DBA knows MySQL and this new storage engine a lot better than
Postgres. We are also moving to VPS architecture and Postgres' performance
advantage over MySQL on a VPS doesn't really exist.

In the end, go with what your staff/friends know and what your hosting place
recommends. If they have an optimized DB stack and provide some kind of a
support for it, stick with that DB because you can always go to them for help!

~~~
neilc
Actually, pgreplicator saw its last release in 2001, and I'm not aware of
anyone who uses it in production. The fact that it requires PostgreSQL 7.1
(which is ancient, buggy, and unsupported by the Postgres developers) would be
a pretty big clue not to use it. The most popular choice for replication
(async master-slave) with Postgres is Slony I. Commandprompt's replicator is
another choice, although it is a commercial product ($1000 USD per machine).

When you say that "Postgres' performance advantage over MySQL on a VPS doesn't
really exist.", I'm not sure what you mean: DBMS performance is a complicated
subject, and significant performance differences exist between MySQL and
PostgreSQL on essentially ANY hardware platform. For example, if your app uses
complex queries with a lot of joins and subselects, Postgres optimizer will
likely give you significantly better performance than MySQL's, regardless of
whether it happens to be running on a VPS. Similarly, MyISAM's performance
with an update-intensive concurrent workload is going to be pretty bad,
regardless of the hardware platform.

------
davidw
I've used both and prefer Postgresql. Mysql with MyISAM is "faster" in the
same way that bicycling down a hill with no brakes is faster. Mysql 5 with
InnoDB is a little bit more serious and is worth considering.

------
volida
I am wondering which choice is popular among this startup community? Tests
showcase PostgreSQL perfomance and scalability on a multicore to be better
than MySQL.

What are your thoughts and the database of your choice?

<http://tweakers.net/reviews/657/5> <http://tweakers.net/reviews/657/6>

~~~
jasonyan
I had been reading numerous articles comparing MySQL and PostgreSQL and ran
across this one. I wish they had mentioned some information about the
configuration of the DBMS itself, such as the size of database buffers and
query optimization settings.

Also, for their MySQL test procedures, I could not find any information
regarding which storage engine they were using. InnoDB is fully ACID-
compliant, whereas MyISAM has reduced overhead since it does support
transactions and foreign key constraints, for example. I found a good article,
<http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-
benchmarks-part-1/> , comparing the two storage engines, and the results were
pretty surprising to me.

It ultimately comes down to your individual usage patterns. Using a database
abstraction layer will allow you to test your application against different
databases so you can see how your application performs first hand.

~~~
volida
prior to multicore cpus asuming the scaling was achieved horizontally, MySQL
has been a good choice. But today, if your systems comprising the clusters are
at least dual core x2 cpu, I am lead to the conclusion, as nickb points out,
that is better to use PostgreSQL.

------
mattculbreth
We're building on PostgreSQL now. I'm not an expert on either platform but the
research I did led me to PostgreSQL due to its better performance on larger
systems and in more traditional enterprise software categories. Our software
will operate with Oracle sometimes and having a similar database will make a
few things easier.

------
eli
PostgreSQL versus MySQL: an unbiased comparison: <http://www-
css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html>

We could spend all day on this, though.

~~~
neilc
However, it is quite out of date: for example, PostgreSQL has had a native
Win32 port for several years, and the "--enable-locale" / "--enable-multibyte"
configure options have not been necessary (or supported) for even longer.
"Large row" (TOAST) support has been around since 7.1. etc.

------
ced
PG writes in the Viaweb FAQ that they (and Yahoo) did not use any database,
just files. It makes sense... Is there something fundamentally different about
your app, so that a DB is obviously a better choice?

~~~
zach
I think something is different about databases since then. You can, for free,
install a database in part of an afternoon that has excellent
library/framework support and tools. That makes a big difference. If you're
going to use Ruby on Rails, for example, you will have to justify why you
don't want to use database software.

Basically, databases have been well-commoditized, like Unix, HTTP servers, and
hosting. If your needs fit their common usage scenarios, you should just go
ahead and use them.

