Comparison between MySQL and PostgreSQL _ MySQL

In this article, we use MySQL4.0.2-alpha and PostgreSQL7.2 for comparison, because MySQL4.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, the stability and speed of a database do not represent PostgreSQL.

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 and PostgreSQL developers are trying to make better changes to their respective databases. Therefore, any use of any commercial database is not a wrong choice.

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.

7. since MySQL 4.0.2-alpha starts to support the concept of transactions, transactions are no longer a disadvantage for MySQL. On the contrary, MySQL retains the non-transactional table type. This gives you more options.

8. MySQL's MERGE table provides a unique method for managing multiple tables.

9. MySQL myisampack can compress read-only tables and then directly access the rows in the tables.