Comparing MySQL and PostgreSQL 2

Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today. Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?

Comments

Re:Another question

[ron_ivi]

This page is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.

Heavy

[HadenT]

I'm using PostgreSQL and MySQL, from my experience:
1. I've never encountered corrupted data with mysql (It seems to be urban legend), and I have worked on tables with billions rows for two years.
2. PostgreSQL has more features and/or is more complete (simple example can be auto_increment vs. sequences)
3. PostgreSQL is heavier, and I hate statistics collector subprocess via udp (which seems to be eating 1-2% cpu all the time)*
4. mysql isn't much (if any) faster.

* - it's unlikely but possible my configs are to blame.

Re:MySQL vs. Oracle

[ttfkam]

1. MySQL supports all of the Oracle features you need to build and operate an enterprise software system.

Ummm... no. MySQL does not have user-defined data types, object-relational extensions, full support for the CHECK constraint (a big one IMHO), views in a stable release, updatable views, rules, stored procedures in a stable release, synonyms, support for more than one autoincrement column per table, automatic conversion of code pages between client and server, nested transactions, complete trigger support, access privilege grouping, access to multiple databases in one session, multi-master replication, gateways to other DBMSs, XML data and transformation tools, and better tools for recovery from failures.

You can use MySQL for your enterprise apps, but it is not Oracle. MySQL, while boasting impressive database sizes, is not even close to competing with Oracle (or DB2 or Sybase) on the largest deployed database sizes.

2. MySQL's new administration tools are significantly better than Oracle's out of the box tools (This is why a year ago I refused to use MySQL for production, and now I've switched everything).

The enterprise is not as price-sensitive as the SOHO market. Very few that buy an enterprise Oracle license use the out-of-the-box tools.

3. MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.

See my answer to number 2.

4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).

Only in environments that MySQL can handle. Oracle can handle scenarios where MySQL cannot run at all let alone run fast.

5. MySQL's supposed gotchas pale in to comparison to Oracle's. When I first used MySQL BLOBs it simply worked. I opened up the administration programs and I could actually see the images in the database. It was so beautiful I wanted to cry. I can't count the number of times I went through Oracle BLOB/CLOB hell with different platforms. (Not just getting them in there, but actually getting them to work with third party applications which is the real pain.)

Agreed. Oracle definitely has its warts.

That said, migration to and from Oracle is easier with PostgreSQL or Firebird -- especially if you start on the lower end. MySQL has been so far from SQL standard compliance, you may not know when you're doing something really weird. MySQL 5.0's strict mode has helped tremendously with this. Too bad it's not ready for production yet.

SCO issued a press release.

[falconwolf]

There isn't one from MySQL AB.

Actually there is a press release on MySQL's website:

SCO Partners With MySQL AB to Lower Costs and Increase the Power & Scalability of Modern Database Solutions

Falcon

Re:It really doesn't matter.

[tzanger]

I disagree.

Putting everything (by everything I mean business logic) in the DB is the only sane way to keep your data consistent across multiple access methods. You simply can't thow data at a DB and then try to code and maintain consistent business logic in a half dozen client apps. You might be able to get away with a shared client access lib but even that can get messy.

Let's face it: Your data's in the DB. Why pull it all into the application to work on some small subset? Do all the queries and joins and clauses and increments in the database. The DB knows best where the data is and how you're going to be tinkering with it (so long as you give it sufficent hints), so it's the only sane method to access your data in a logical fashion. That's precisely why all these scripting languages and language interfaces exist.

I too use XML-RPC and SOAP (moreso the latter it seems, as XML-RPC is a little too light IMO) to access my data, but you can bet your sweet bippy I'm having the DB do as much as possible in order to transfer as little data as possible across my app-db link.