In italiano || Site map - Software development

MySQL-PostgreSQL comparison

Table of contents

When choosing between the two foremost FLOSS (Free/Libre Open Source Software) database management systems, MySQL and PostgreSQL, it is easy to fall prey of hype and popularity. I show why it's best not doing so.

License, costs and portability

MySQL

MySQL is available in both binary and source code form; it may be modified and redistributed. Its usage terms are regulated by a double GPL/proprietary license.

Binaries and source code are available free of charge. Per the GPL terms, any redistribution of the binaries must be accompanied by the source code from which they are derived, whether original or modified. An exception to the GPL requirements is made for a few FLOSS programs that use MySQL.

Those not wanting to abide to those terms may buy a proprietary license from MySQL Inc. Support contracts are also available from the same supplier.

MySQL runs on many Unix-like operating systems, is included in most Linux distributions, and runs natively on Microsoft Windows.

Current stable version: 5.0.

PostgreSQL

PostgreSQL is available in both binary and source code form; it may be modified and redistributed. Its usage terms are regulated by a BSD-like license, that includes no source code redistribution requirements.

Binaries and source code are available free of charge. Support contracts are available from many independent firms.

PostgreSQL runs on many Unix-like operating systems, is included in most Linux distributions, and runs natively on Microsoft Windows since version 8.0.

Current stable version: 8.2.

Comparisons

Detailed examination of licenses and distribution models of MySQL, PostgreSQL and Oracle (in Italian):

DBMS a Confronto

Robustness

MySQL

MySQL implements transactions and foreign keys from version 4 only, and only on the InnoDB backend, not on MyISAM, the most used one. The developers' orientation toward such features, for years, has been one of contempt: they forwent compliance to ACID requirements and guarantee of data integrity.

MySQL manifests many problems and gotchas under usage. A large number of reports about development and deployment problems is available online.

Exhaustive list of problems and gotchas in MySQL 4.1:

MySQL Gotchas

A problematic use case:

MySQL Hate

PostgreSQL

PostgreSQL features a complete and compliant implementation of the SQL standard, with several optional extensions.

For quite some time now, it has based its architecture on the MVCC (Multi Version Concurrency Control) concurrency model, that guarantees compliance to ACID requirements and data integrity.

Reports of data loss due to a PostgreSQL malfunction are very rare.

Exhaustive list of problems and gotchas in PostgreSQL:

PostgreSQL Gotchas

Features and SQL standard

MySQL

MySQL implements transactions, foreign keys, subselects and unions from version 4 only, and not on all backends.

It implements views, stored procedures, triggers and Unicode support from version 5 only.

It does not yet implement full joins and constraints on data.

PostgreSQL

PostgreSQL has implemented for years transactions, foreign keys, subselects, unions, views, stored procedures, triggers, Unicode support, full joins and constraints on data.

It also features an extensible type system, sequences, inheritance and other extensions.

Comparisons

Detailed feature comparison between PostgreSQL 8.0, MySQL 4.1 and MySQL 5.0 (beta):

Mysql vs postgres

Speed

MySQL

MySQL is fast when concurrent access levels are low, and when there are many more reads than writes. On the other hand, it exhibits low scalability with increasing loads and write/read ratios.

PostgreSQL

PostgreSQL is relatively slow at low concurrency levels, but scales well with increasing load levels, while providing enough isolation between concurrent accesses to avoid slowdowns at high write/read ratios.

Comparisons

A comment about speed and scalability of MySQL e PostgreSQL, with references to a detailed benchmark:

Benchmark: PostgreSQL beats the stuffing out of MySQL

Another comment about the same benchmark:

Validating PostgreSQL Performance

Report of a migration from MySQL to PostgreSQL:

FeedLounge now running on PostgreSQL

Comparison test with a varying number of concurrent users

Miscellaneous

PostgreSQL

Rebuttal of a number of preconcepts about PostgreSQL:

Five reasons why you should never use PostgreSQL -- ever

Conclusion

MySQL was built for speed, to the detriment of everything else. SQL standard features like transactions, referential integrity and others have been grafted over only after considerable arm twisting.

However, it is only fast at low concurrency levels, and reports of instability, low robustness and scalability keep coming out.

PostgreSQL was built strictly following the ACID model, with an emphasis on data integrity and SQL standard compliance, speed notwithstanding. It then underwent optimizations and speedups, while at the same time adding new features to its already large set.

Today it is robust, standards compliant and well featured. While still being relatively slow at low concurrency levels, it scales well with increasing load levels.