I find the Postgre envronment much more pleasant to work with, and query results seem quicker. One thing that is strange though--Postgre's storage on disk occupies much more space than Mysql.
]]>For me though, the biggest issue is with how mysql handles failures and edge cases.
If you attempt to add a 200 char data element to a column set as 100 chars, in mysql...only the first 100 chars are added. the rest are discarded. This 'silent data munging' is something i am no longer comfortable with.
I choose to go with a *perhaps* slower database engine (postgres) that places a higher importance on maintaining data consistency, than a *perhaps* faster database engine that places more empasis on raw query speed.
Lisence issues are concerns as well. With the recent fuss over the, arguably blown out of proportion, deal that mysql has entered into with SCO, I felt it was a good time to migrate my own development efforts on a platform that I felt more comfortable supporting.
Hopefully, for the rest of the community's sake, mysql continues to get better. It is the predominantly supported hosting db out there.
Redhat did choose postgres to base their 'redhat database' product on though. They must know something about something. lol
]]>Support for views, safe transactions, subqueries, foreign keys, etc will no longer be an advantage of postgres, as MySQL 5.x (and 4.1 also, to some extent) will also support these features.
The greatest advantage that postgres has over MySQL, is its license and its independance, I think. For example, the most popular and advanced storage engine in MySQL has just been bought out by Oralce, which might become somewhat problematic for mysql in the future.
For mission critical stuff, I would choose for postgres, because they are probably the most mature when it comes to the aforementioned features.
For other stuff, I'm not sure. If you want to run it on a shared host, mysql is probably the only option. Also, mysql might just prove to be faster for you and provide all the features you need. But then again, mysql's dependence on innoDB, for example, might prove to be a huge problem.
]]>From a user's point of view I have no idea, never used them.
]]>mysql's online nmanual is very comprehensive so any user should be quite good with it in a short time.
]]>If you want to run a mission-critical DB-Server, I'd stick with Postgres, because of the Transaction-Support, which may be quite important in such cases.
A nice feature in any case are the subselects in Postgres. MySQL doesn't support this yet, so this may be kind of a "killer-criteria"...
Actually, I believe mysql 4.1.x has support for both now.
Transactions
Using the InnoDB or Berkeley DB (BDB) storage engines, the MySQL database server supports transactions. The InnoDB storage engine also supports foreign key constraints.
Expanded support for subqueries
Subqueries allow you to use the result of one query as a component of a larger query. The MySQL server already supports some forms of this technique, such as INSERT INTO ... SELECT ..., and this support will be expanded in version 4.1 to include nested SELECT queries, which is one of the most-requested features from our users.
If you want to run a mission-critical DB-Server, I'd stick with Postgres, because of the Transaction-Support, which may be quite important in such cases.
A nice feature in any case are the subselects in Postgres. MySQL doesn't support this yet, so this may be kind of a "killer-criteria"...
But don't forget: never change a running system. If you run mySQL right now and you are happy with it, then why change?
]]>