Confrontation between MySQL and PostgreSQL _ MySQL

In this article, we use MySQL 4.0.2-alpha to compare with PostgreSQL 7.2, because MySQL 4.0.2-alpha starts to support the concept of transactions, therefore, this comparison is more advantageous for MySQL. We do not want to be just a performance test report, because at least from my perspective, stability and speed for a database do not represent everything. For a mature database, stability will certainly be increasingly available. With the rapid improvement of hardware performance, the speed is no longer a big problem.

I. Preface

Some time ago, I translated an article about changing your website from MySQL to PostgreSQL, in fact, I was more interested in the specific operation of converting the background database of an application from MySQL to PostgreSQL, and did not care about the advantages and disadvantages of MySQL and PostgreSQL. I did not expect an unexpected response, therefore, I think it is necessary to write this article.

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

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

II. common advantages

Both products are part of the open source code, and their performance and functions are rapidly improved and enhanced. MySQL AB and PostgreSQL developers are trying to make better changes to their respective databases. Therefore, it is not a wrong choice for any commercial database.

3. different backgrounds

MySQL is a mature commercial company, while PostgreSQL is a huge volunteer development group. This makes the MySQL development process more cautious, while PostgreSQL responds more quickly.

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

IV. main advantages of MySQL

1. first, speed. MySQL is usually much faster than PostgreSQL. MySQL also claims that speed is one of their main goals. For this reason, MySQL has also said in previous documents that it is not prepared to support transactions and triggers. However, in the latest document, we can see that MySQL 4.0.2-alpha has started to support transactions, and in MySQL TODO, functions that are bound to speed down triggers and constraints are also on the agenda. However, we still have reason to believe that MySQL will always be able to maintain the speed advantage.

2. MySQL is more popular than PostgreSQL. popularity is also a very important indicator for a commercial software. popularity means that more users have stood more tests, this means better commercial support and more comprehensive documentation.

3. MySQL is more suitable for running in Windows than PostgreSQL. MySQL runs as a local Windows application (in NT/Win2000/WinXP, it is a service), while PostgreSQL runs in the Cygwin simulation environment. PostgreSQL runs in Windows without the stability of MySQL.

4. MySQL uses threads, while PostgreSQL uses processes. Environment conversion between different threads and access to public storage areas are obviously much faster than between different processes.

5. MySQL can adapt to 24/7 running. In most cases, you do not need to run any purge programs for MySQL. PostgreSQL is still not fully compatible with running 24/7, because you have to run VACUUM at intervals.

6. MySQL has better permission systems than PostgreSQL in some aspects. PostgreSQL supports only INSERT, SELECT, and UPDATE/DELETE authorization for each user on a database or a data table, mySQL allows you to define a set of permissions at different data, table, and column levels. For column-level permissions, PostgreSQL can compensate by creating a view and determining the view permissions. MySQL also allows you to specify host-based permissions, which cannot be implemented by PostgreSQL currently, but is often useful.