MySql or Postgres?

[rsal]

Has anyone used these db's with JBoss? I'm not too sure which one to use. Is there any benefit of using Postgres instead of MySql with JBoss. We want to run this in a production environment so we will be using 2.x version.

Any info or tips would be great!!

Thanks,
/rick

[bpowell]

you cannot run stored procedures in postresql. you have to replace them with functions and this can be a real headache, especially since resultsets returning more than one column are impossible, and some strange things happen when you use temporary tables. there is not much in jdbc drivers, but the postgresql driver is ok within its limits. the gui 'pgadmin2' for windows is not bad. the gossip is that mysql is fast for simple reads ( like perhaps for web apps) but otherwise slow, but don't take that as gospel.

[pazu]

Please, don't start a flamewar here. PostgreSQL x MySQL is a non-issue. MySQL is a simple SQL interface to the file system while PostgreSQL is a complete ACID ORDBMS.

MySQL _is_ advancing and people are starting to think that foreign keys are a good thing (yeah, they believe FK's aren't needed) among other things. Go with MySQL if you don't need transactions, foreign keys, complex types, real scalability. In other words, if you need a simple data storage.

However, if you're talking about an enterprise application that must abide to the ACID rules, PostgreSQL is your database.

Take a look here for some extra info: http://openacs.org/philosophy/why-not-mysql.html

[rsal]

pazu, I completely agree with on using Postgres for developing robust, scaleable applications. I did hear that Postgres did have some problems and that is sort of the thing I was looking for. If anyone experienced big problems with it and how they handled it.

In no way was I trying to start another flame war on which one was better. Been there, done that, and Postgres always wins!!

Was just trying to see which one worked better with JBoss. I wasn't sure MySql would be able to work with JBoss.

bpowell, what's your experience with the postgres driver? I know that the MySql driver by Mark Matthews is pretty good. How well does the postgres one work? What sort of limitations are we looking at (if there are any)?

thanks,
/rick

[hubs2121]

You might find this article interesting:
http://www.eweek.com/article/0,3658,s=708&a=23115,00.asp

MySQL DOES support foreign keys and transactions with the InnoDB table type. The Mark Mathews driver is one of the best JDBC drivers, by the way.

We chose MySQL over Postgres because it can run natively on Windows (a requirement of our customers). Postgres must be run with an emulator on windows.

You might also want to try SAPDB...it is also open source, and has all of the goodies, like transactions, triggers, stored procedures, sub-queries, etc.

STeve

[davidjencks]

ok, think of firebird too. It has stored procedures, triggers, transactions, low overhead near-serializable transaction isolation... everything you could want. Firebird 1.0 was just released today...

If you want a more or less proven driver you can use interclient, if you want xa transactions you can use the still somewhat unreleased pure java jca-jdbc driver available from Firebird.