源码搜藏网

MySQL PostgreSQL

VS

MySQL vs PostgreSQL is a decision many must make when approaching open-source relational database management systems. Both are time-proven solutions that compete strongly with proprietary database software. MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle. MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.

These assumptions, however, are mostly outdated and incorrect. MySQL has come a long way in adding advanced functionality while PostgreSQL dramatically improved its speed within the last few major releases. Many, however, are unaware of the convergence and still hold on to stereotypes based on MySQL 4.1 and PostgreSQL 7.4. The current versions are MySQL 5.6 and PostgreSQL 9.2.4.

Contents

PostgreSQL is a unified database server with a single storage engine. MySQL has two layers, an upper SQL layer and a set of storage engines. When comparing the two it's typically necessary to specify which storage engines are being used with MySQL because that greatly affects suitability, performance and (even basic) feature availability. The most commonly used storage engines in MySQL are InnoDB for almost full ACID support and high performance on large workloads with lots of concurrency and MyISAM for lower concurrency workloads or higher concurrency read-mostly workloads that don't need ACID properties. Applications can combine multiple storage engines as required to exploit the advantages of each.

Database systems can be optimized according to the environment they run in. Thus, it is very difficult to give an accurate comparison in performance without paying attention to configuration and environment. PostgreSQL and MySQL both employ various technologies to improve performance.

MySQL began development with a focus on speed while PostgreSQL began development with a focus on features and standards. Thus, MySQL was often regarded as the faster of the two. The default configuration for both is tuned to run on small systems, and it's common for people performing benchmark tests to either not change the defaults, or properly tune only the one they are most familiar with. Either action will usually give misleading results. Furthermore, both DBMS's will do better in benchmarks related to their original strengths (i.e. MySQL fast in simple operations, PostgreSQL more reliable and faster in complex operations)

PostgreSQL provides significant performance features

The 8.x releases have added more than 75 new discrete performance features. These have been added as a result of a multi-year project to improve performance by steadily identifying and removing key bottlenecks in scalability, as well as adding low-level tuning and architectural features. [1] shows feature set added for the 8.3 release, for example.

PostgreSQL can compress and decompress its data on the fly with a fast compression scheme to fit more data in an allotted disk space. The advantage of compressed data, besides saving disk space, is that reading data takes less IO, resulting in faster data reads.

PostgreSQL supports one storage engine, with tight integration between that storage engine and the rest of the database. Options like asynchronous commit can be set on a per-transaction, per-user or whole system basis, allowing different transaction types to co-exist efficiently without the need to select storage engine types once for each table ahead of time.

By default, PostgreSQL comes tuned to run on a shared server, so has low performance settings. When running on a dedicated server performance can be improved by changes to a few key parameters.

MySQL 5.1 natively supports 9 storage engines [2]:

However, the federated and blackhole engines are not actually "storage" engines (for example, "blackhole" does not store anything).