MySQL vs. PostgreSQL two database

In this article, we use the MySQL 4.0.2-alpha and PostgreSQL 7.2 to compare, because MySQL 4.0.2-alpha started to support the concept of transactions, so this comparison for MySQL should be more advantageous. 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. First, the preface

Some time ago, I have translated your site from MySQL to PostgreSQL, in fact, I was more interested in the background of an application database from MySQL to postgresql the specific operation, and did not care about the advantages and disadvantages of MySQL and PostgreSQL, I did not expect to respond unexpectedly big, so I also feel that there is a need to write this article.

In this article, we use the MySQL 4.0.2-alpha and PostgreSQL 7.2 to compare, because MySQL 4.0.2-alpha started to support the concept of transactions, so this comparison for MySQL should be more advantageous.

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.

Ii. Common advantages 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.

Iii. 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.

Four, 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.