Evaluation: The pros and cons of MySQL and PostgreSQL

In this article, we use the MySQL 4.0.2-alpha to compare with PostgreSQL 7.2, because the MySQL 4.0.2-alpha begins to support the concept of the transaction, so this comparison is more advantageous for MySQL.

We don't want to be just a performance test report, because at least from my point of view, stability and speed do not represent everything for a database. For a mature database, stability is certainly increasingly available. With the rapid improvement of hardware performance, the speed is no longer a big problem.

　　

A common advantage of both

Both products are part of the open source, and performance and functionality are being increased and enhanced at a high speed. MySQL AB and PostgreSQL developers are trying to make their databases as good as possible, so it's not a bad choice for any commercial database to use any of them.

　　

Ii. background of the two different

Behind MySQL is a mature business company, and behind PostgreSQL is a huge volunteer development group. This makes MySQL's development process more prudent, and PostgreSQL's response more quickly.

These two backgrounds directly lead to their inherent advantages and disadvantages.

　　

Third, the main advantages of MySQL

1, first is the speed, MySQL is usually much faster than PostgreSQL. MySQL has also claimed speed as one of the main goals they pursue, and for that reason MySQL has said in previous documents that it is not prepared to support transactions and triggers. But in the latest documentation, we see that MySQL 4.0.2-alpha has started to support transactions, and in MySQL's TODO, triggers, constraints, and so on are doomed to slow down the speed of the function is also included in the agenda. However, we still have reason to believe that MySQL will have the advantage of being able to maintain speed.

2, MySQL more popular than PostgreSQL, popular for a commercial software, is also a very important indicator, popular means more users, means to undergo more tests, means better business support, means more, more perfect documentation.

3, compared with PostgreSQL, MySQL is more suitable to run in the Windows environment. MySQL runs as a local Windows application (under NT/WIN2000/WINXP, is a service), while the PostgreSQL is running in the Cygwin simulation environment. PostgreSQL running under windows without MySQL stability, it should be conceivable.

4, the MySQL uses the thread, but PostgreSQL uses is the process. Environment conversions between different threads and access to common storage areas are obviously much faster than between different processes.

5, MySQL can adapt to 24/7 operation. In most cases, you don't need to run any cleanup programs for MySQL. PostgreSQL is still not fully adapted to the 24/7 run because you have to run vacuum every once in a while.

6, MySQL in the permission system than PostgreSQL some aspects more perfect. PostgreSQL only supports inserts, select, and update/delete for each user on a database or on a datasheet, and MySQL allows you to define a set of different levels of data, table, and column permissions. For column-level permissions, PostgreSQL can be made up by establishing a view and determining the permissions of the view. MySQL also allows you to specify host-based permissions that are not achievable for current PostgreSQL, but in many cases this is useful.

7, since the MySQL 4.0.2-alpha began to support the concept of transactions, so the transaction for MySQL is no longer just a disadvantage. Conversely, because MySQL retains a table type with no transactions. This gives the user more choices.

8, the MySQL merge table provides a unique way to manage multiple tables.

9, the MySQL myisampack can compress the read-only table, you can still directly access the rows in the table.

　　

Four, the main advantages of PostgreSQL:

1, the support of the transaction compared with MySQL, experienced a more thorough testing. For a serious business application, the support of the transaction is indispensable.

2, MySQL for no transaction MyISAM table. With table locking, a long-running query is likely to block updates to the table for a long time. And PostgreSQL does not have such a problem.

3, PostgreSQL support stored procedures, and currently not supported by MySQL, for a serious business application, as the database itself, there are many business logic exists, at this time using stored procedures can be less to increase the burden of the database server, the premise of such a business logic encapsulation, The implementation of the stored procedure can be optimized by using the internal mechanism of the database server itself. Furthermore, the existence of stored procedures avoids the transmission of a large number of original SQL statements on the network, and the advantage is obvious.

4, view support, the existence of views can also maximize the use of the database server internal optimization mechanism. And for the reasonable use of view permissions, in fact, can provide row-level permissions, this is the MySQL permissions system can not be achieved.

5, the support of triggers, the existence of triggers will inevitably affect the efficiency of database operation, but at the same time, the existence of triggers is also conducive to the packaging of business logic, can reduce the application of the same business logic of repetitive control. The proper use of triggers also helps to ensure data integrity.

6, the support of constraints. The role of constraints more surface now on the assurance of data integrity, the rational use of constraints, can also reduce the workload of programming.

7, support for subqueries. Although it is inefficient to use subqueries in SQL statements in many cases, and in most cases you can substitute a subquery with a conditional, multiple-table join, the presence of subqueries is often unavoidable. Also, SQL statements that use subqueries have a higher program readability than those that use a conditional multiple-table connection.

8, support r-trees Such an extensible index type, you can more easily handle some special data.

9. PostgreSQL can be extended using UDF (user-defined functions) more easily.

　　

Five, then I should use MySQL or PostgreSQL

This question is hard to say, and in fact it is wise to use Oracle, Sybase, Informix, and so on, in addition to MySQL and PostgreSQL. How do you decide to choose only in MySQL and PostgreSQL, the following rules are always valid.

1, if your operating system is windows, you should use MySQL.

2, if you are not very understanding of the database, even do not know the transaction, stored procedures, and so on exactly what, you should use MySQL.

3, if your application to the integrity and seriousness of the data requirements is not high, but the pursuit of high speed processing. For example, it is a forum and community where you should use MySQL.

4, your application is a serious business application, the data integrity requirements are high. And you want to have a good encapsulation of some business data logic, such as an online bank, you should use PostgreSQL.

5, your application is dealing with geographical data, because r-trees exist, you should use PostgreSQL.

6, you are a database kernel enthusiasts, you even want to have your own version of the database, no doubt, you must use PostgreSQL, the next PostgreSQL version of the author of a module is you.

　　

Postscript

The above is only the author from his own understanding as far as possible objectively and fairly evaluation of MySQL and PostgreSQL pros and cons. One of the tendentious views of the representative's personal views, about these two databases, welcome friends to put forward their views.