I. preface some time ago, I have 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 PostgreSQL
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.
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.
V. Major advantages of PostgreSQL:
1. transaction support has gone through a more thorough test than MySQL. For a serious commercial application, transaction support is indispensable.
2. MySQL for MyISAM tables without transactions. When table locking is used, a long-running query may impede table updates for a long time. PostgreSQL does not have such a problem.
3. PostgreSQL supports stored procedures, but MySQL does not currently support them. for a serious commercial application, as a database, there are many commercial logics, in this case, the stored procedure can encapsulate such commercial logic without increasing the burden on the database server, the database server's internal mechanism can be used to optimize the execution of stored procedures. In addition, the existence of stored procedures also avoids the transmission of a large number of original SQL statements on the network. this advantage is obvious.
4. support for views. the existence of views can also maximize the use of the internal optimization mechanism of the database server. In addition, the rational use of View permissions can actually provide row-level permissions, which cannot be implemented by the MySQL permission system.
5. with support for triggers, the existence of triggers inevitably affects the efficiency of database operations. However, the existence of triggers also facilitates the encapsulation of business logic, this reduces repeated control over the same business logic in applications. Reasonable use of triggers can also ensure data integrity.
6. constraints. The role of constraints is more manifested in the guarantee of data integrity. reasonable use of constraints can also reduce programming workload.
7. subquery support. In many cases, the efficiency of using subqueries in SQL statements is low. In most cases, you can use multi-table join with conditions to replace subqueries, however, the existence of subqueries is still inevitable in many cases. Moreover, SQL statements using subqueries have higher program readability than those using conditional multi-table connections.
8. supports scalable index types such as R-trees, which makes it easier to process some special data.
9. PostgreSQL can more easily use UDFs (user-defined functions) for extension.
6. Should I use MySQL or PostgreSQL?
This problem is hard to tell clearly, and in fact, in addition to MySQL and PostgreSQL, using Oracle, Sybase, Informix and so on is also a wise choice. The following rules are always valid if you are sure to select only MySQL and PostgreSQL.
1. if your operating system is Windows, you should use MySQL.
2. if you do not know much about the database, or even know what transactions, stored procedures, and so on are, you should use MySQL.
3. if your applications do not have high requirements on data integrity and seriousness, but they are pursuing a high processing speed. For example, for a forum or community, you should use MySQL.
4. your application is a serious commercial application with high requirements on data integrity. In addition, you want to encapsulate some commercial data logic. for example, if you are an online bank, you should use PostgreSQL.
5. your application is processing geographic data, because of the existence of R-TREES, you should use PostgreSQL.
6. you are an avid fan of the database Kernel. you even want to have your own database version. without a doubt, you must use PostgreSQL, maybe the author of a module in the next PostgreSQL version is you.
VII. Conclusion
The above is just a hope to objectively and fairly evaluate the advantages and disadvantages of MySQL and PostgreSQL from my understanding. The biased opinions only represent personal opinions. Please criticize and correct the mistakes due to your own level constraints.
