MySQL – PostgreSQL

As I just told, my new job requires me to do a lot of work with PostgreSQL, which is relatively new to me, since I have done almost all of my database work with MySQL so far.

I also told that this will give me a wider perspective on RDBMS in general and I would like to use this opportunity to discuss about how features are implemented in MySQL and how in PostgreSQL.

Don’t worry, this will not end up in a MySQL vs. PostgreSQL fight. As I know MySQL much better than PostgreSQL, I do understand that this position doesn’t allow me to make representative judgements on which database system is better (most people would naturally prefer the one they know better). I also believe that both of them are great RDBMS and none of them deserves to be involved in a “A rulez, B suckz” struggle (I love MySQL and I try to fall in love with PostgreSQL, too). I would rather like to show, how certain features work with MySQL and how with PostgreSQL and how the differences affect me personally – which could mean that I miss a feature in one or the other database system, but that does never mean that this product sucks.

I would like to start with listing up some differences that I have discovered so far. If you find something that’s not true as a fact, please don’t hesitate to contact me (m.popp@db4free.net) or comment this article.

  • MySQL uses auto_increment to create numeric values that iterate for every inserted record, PostgreSQL uses sequences. It’s possible to use the serial data type to automate this in PostgreSQL so I think there are no big advantages or disadvantages for each particular implementation.
  • When a transaction is started and you write a SQL statement that has a syntax error in it, PostgreSQL rolls back the transaction immediately, while MySQL shows an error message, but still continues the transaction (it’s up to the user to do error handling and roll back the transaction, if desired). I think, both ways have their own advantages and disadvantages. It would be great if both database systems could create a setting to let the user decide how to handle syntax errors in transactions.
  • PostgreSQL allows check constraints. I have heard that check constraints will be implemented in MySQL 5.1 (can somebody confirm this?).
  • MySQL has an ‘enum’ data type. I haven’t found a similar data type in PostgreSQL, however, ‘enum’s can be emulated with check constraints.
  • Other than PostgreSQL, MySQL allows to insert more than one row in one INSERT statement. MySQL’s implementation is a very useful extension to SQL standard.
  • There are several storage engines in MySQL with several features. Starting with MySQL 5.1, storage engines will be pluggable. PostgreSQL doesn’t allow to choose between storage engines, there’s only one way to store the data, however, all the features of PostgreSQL are available within this data storage method. This is an advantage and disadvantage as well. You can tailor the requirements of a particular table better with MySQL, on the other hand, if you need a feature from two storage engines in one table (for example foreign keys and full text indexing), you have a problem. As MySQL implements more and more features into the available storage engines, this will be less of a problem in the future.
  • MySQL allows to set @@foreign_key_checks to 0 to disable foreign key checking. It’s sometimes necessary to change the structures of related tables and temporarily disable these checks. One thing that I miss in MySQL is that enabling @@foreign_key_checks again doesn’t verify referential integrity (please correct me, if I’m wrong or if that has changed). In PostgreSQL, you can add “INITIALLY DEFERRED” to the foreign key definition, so this allows that referential integrity may be violated inside a transaction, but the data must be correct when the transaction is being committed (I will possibly write more about this later).
  • PostgreSQL is (or has been) more restrictive checking the data that’s inserted. MySQL has introduced SQL mode settings to enable restrictive error checking. However, in older versions of MySQL (or if SQL mode is not set), MySQL allows obviously invalid data to be inserted.
  • PostgreSQL allows to write stored procedures, functions and triggers in different languages, however, these languages are not installed by default. MySQL offers only one language, but more languages will probably be available in later versions.
  • MySQL stores user permission data inside a MySQL database (called mysql). This makes it very simple for a system administrator to edit permissions or look up how they are set (besides the SQL commands GRANT, REVOKE, SHOW GRANTS etc.). I haven’t yet figured out where PostgreSQL stores this data and I think it’s only possible to use the SQL commands to set the permissions accordingly.
  • MySQL and PostgreSQL do of course have different client programs and it’s up to somebody’s preferences which ones to like more. But I found out that if you are logged in as a “normal” user (without administrator privileges and access to all databases) in PostgreSQL, you can always see all databases, including those to where no access is granted. In MySQL, a particular user can only see the databases that he has access to. Maybe there’s something in PostgreSQL that I don’t know yet to also make only those databases visible that I user has permissions to access (please tell me, if you know a solution).

This list can and will be continued and if you know some more important differences that I haven’t listed, or if you find errors or things that should be added, I would appreciate your comments.

Several Open Source database system producers (including MySQL and PostgreSQL, beside some others) have agreed to found the Open Source Database Consortium (a website is planned at http://www.osdbconsortium.org/). Find detailed information on what it’s all about in Kaj Arnö’s article about the foundation of the Consortium. I hope that this project makes it easier for people who work with more than one Open Source database product. It’s a great thing to keep track of as soon as the site becomes available and if there is a chance, I would be happy to contribute to this fascinating project in one way or another.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.