Re: Decide between Postgresql and Mysql (help of

On Tue, 2006-03-28 at 09:31, Marcos wrote:
> Hi,
>
> I'm a Postgresql's user and I think that it's very very good and
> robust.
>
> In my work we're confuse between where database is the best choose:
> Postgresql or Mysql. The Mysql have the reputation that is very fast
> working in the web but in our application we are estimating many access
> simultaneous, then I think that the Postgresql is the best choice.
>
> Am I right?
>
> Our server have 1 GB of RAM, how many users can it support at the same
> time with this memory?

This is as much about the code in front of the database as the database
itself. You'll want to use an architecture that supports pooled
connections (java, php under lighttpd, etc...) and you'll want to look
at your read to write ratio.

MySQL and PostgreSQL can handle fairly heavy parallel loads. PostgreSQL
is generally a much better performer when you need to make a lot of
parallel writes.

But the bigger question is which one is suited to your application in
general. If some major issue in MySQL or PostgreSQL makes it a poor
choice for your app, then it doesn't matter how much load it can handle,
it's still a poor choice.

Generally speaking, MySQL is a poor choice if you're doing things like
accounting, where the maths have to be correct. It's quite easy to ask
MySQL to do math and get the wrong answer. It also has some serious
problems with referential integrity, but most of those can be worked
around using innodb tables. But at that point, you're using the same
basic storage methods as PostgreSQL uses, i.e. an MVCC storage engine.
And now that Oracle has bought Innodb, the availability of that in the
future to MySQL is in doubt.

There's also the issue of licensing. If you'll be selling copies of
your app to customers, you'll be writing a check for each install to
MySQL AB. Not so with PostgreSQL.

So, what exactly are you planning on doing?

Lastly, take a look here:

http://sql-info.de/mysql/gotchas.html

and here:

http://sql-info.de/postgresql/postgres-gotchas.html

for a list of the common "gotchas" in both databases.

Generally you'll find the PostgreSQL gotchas are of the sort that make
you go "oh, that's interesting" and the MySQL gotchas are the kind that
make you go "Dear god, you must be kidding me!"

But that's just my opinion, I could be wrong.