sobota 13. srpna 2016

Pragmatic comparison of MySQL and PostgreSQL

The biggest advantage of MySQL is that it is feature lightweight and that it does not closely adhere to SQL standard. Consequently, MySQL has following nice properties:
  1. MySQL implements a rather subtle but sufficient subset of functions from SQL. Hence, it is easier to learn MySQL thoroughly than PostgreSQL.
  2. When you get stuck with MySQL, you know it must be doable. And after a while you write a query that does what do you want. In PostgreSQL, you just Google for the function. That is not sporty.
  3. It is easier to migrate from MySQL to another database than reversely, because almost each relational database implements the bare minimum implemented in MySQL. As old wisdom says: it is always easier to move from worse to better than reversely.
  4. Tables in MySQL are treated as a matrices, not as a relations (sets), as dictated by SQL standard. Consequently, rows in a table have fixed order and the order of the columns in a table can be altered. That makes the usage of the table more user friendly.
  5. Many of MySQL commands are shorter than PostgreSQL alternatives, although it is frequently at the expense of ability to configure the command. An example is autoincrement column.
  6. MySQL is more forgiving to errors in queries.
On the other end, PostgreSQL, which more closely adhere to SQL standard, has following advantages (some of them are likely more related to the habit of thinking thru the impact of a change than adherence to standards):
  1. PostgreSQL has much more usable default setting than MySQL. I had to reconfigure MySQL 10 times during the first 6 months of deployment, until MySQL was processing all the beasty queries it had. On the other end, PostgreSQL with the same content, on the same hardware and corresponding load was working to our satisfaction for 2 years. Then it become necessary to increase the memory limit from 64MB RAM to something bigger to work reasonably with 100GBs of data... MySQL is still better in this respect than Oracle or SAS. But PostgreSQL leads in this respect.
  2. Error messages in PostgreSQL not only tell you what is wrong, but they also tell you the line where is the error and propose a solution to the problem. PostgreSQL is the nicest database in this respect I have ever worked with. It may look like a minor advantage. And if you just need to run a legacy database, it is. But if you need to develop something new on a database, it makes a heaven from PostgreSQL. Not a hell like SAS.

Žádné komentáře: