MySQL vs PostgreSQL

From WikiVS, the open comparison website

Jump to: navigation, search
MySQL PostgreSQL
MySQL
VS
 PostgreSQL
http://www.mysql.com http://www.postgresql.org

MySQL vs PostgreSQL is a decision many must make when approaching open-source relational databases management systems. Both are time-proven solutions that compete strongly with propriety database software. MySQL has long been assumed to be the faster but featureless 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, 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 major releases. Many, however, are unaware of the convergence and still hold on to stereotypes based on MySQL 4.1 and PostgreSQL 7.4.

Also, the speed comparisons are mainly between MyISAM and PostgreSQL engines. If the comparison is between the latest versions of InnoDB and PostgreSQL, PostgreSQL is often faster.

Contents

[edit] Performance

Database systems can be optimized according to the environment in which they run. 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 at the basic level:

[edit] Beginnings

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. PostgreSQL's default configuration was designed to run on low-memory systems.

[edit] Raw Speed

MySQL's MyISAM engine performs faster than PostgreSQL, but at the cost of not supporting transactions, foreign keys, and not offering guaranteed data durability.

[edit] Data Compression

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

MySQL's high performance storage engines do not support on-the-fly compression as of 5.1. MySQL 6.0 will support on-the-fly compression with its Falcon storage engine:

Data stored in the Falcon tablespace is compressed on disk, but is stored in an uncompressed format in memory. Compression occurs automatically when data is committed to disk.

— MySQL AB ,  12.6.6.6. Data Compression

MySQL's MyISAM engine supports index compression which it uses by default to some extent. Better index compression can be gained by using the PACK_KEYS option. Other stable storage engines in MySQL at the time of writing do not have this feature, so their indexes use more space.

Tables in the MyISAM engine can be packed with myisampack which makes them readonly, but saves a lot of space.

MySQL also supports network protocol-level compression which is an option which can be turned on by the client if the server allows it. This compresses everything to and from the server.

MySQL supports on-the-fly compression since version 5.0 with the ARCHIVE storage engine. Archive is a write-once, read-many storage engine, designed for historical data. It compresses data up to 90%. It does not support indexes. However, in version 5.1 it can be used with partitioning.

[edit] Multi-Processor

PostgreSQL's speed advantage over MySQL can be seen drastically in a large multi-core/processor environment. [1] Even MySQL developers admit that current MySQL's use of multi-core/processor technology is not up to par.

[edit] Configured Speed

PostgreSQL provides features that can lead to faster performance on certain queries:

  • partial indexing
  • TOAST data compression
  • greater memory allocation by default in recent versions on capable systems
  • improved cache management in versions 8.1 and 8.2

MySQL also supports partial indexing using the InnoDB engine, but not with the MyISAM engine. Even when using the InnoDB engine, however, system tables use the MyISAM engine.

While it is true that with a default install MySQL used to soundly beat PostgreSQL on many performance metrics, past benchmarks showing greater performance from MySQL than PostgreSQL have tended to suffer from a number of problems:

  • It was not uncommon to see a performance-tuned MySQL server pitted against an untuned, default-configuration PostgreSQL server.
  • Comparison benchmarks often grouped transactions in unrealistic manners that did not reflect real-world application behavior, leading to a mismatch in how many discrete operations were being performed at a time and the relationships between them from one database to the next.
  • MyISAM ACID-noncompliant "transactions" were often compared against PostgreSQL ACID-compliant transactions. While this often resulted in greater performance in benchmarks, it also involved testing systems doing significantly different things. If transactional integrity is of importance to you, greater performance with no transactional integrity guarantees may not be an option at all.
  • MyISAM has largely been optimized for single-user performance. This meant a win for MySQL using MyISAM in many benchmarks. Under heavy use, however, with many concurrent users, MyISAM use of table locks to control access had a dramatic negative effect on performance as the number of users grew -- which was not often addressed by comparison benchmarks.

[edit] Concurrency

PostgreSQL scales much better both in terms of using up-scale hardware and dealing with concurrency. MySQL, on the other hand, focuses on scale-out technologies and the use of off-the-shelf commodity hardware.

[edit] COUNT(*)

PostgreSQL COUNT(*) is very slow because instead of counting rows using an index scan, it goes through the entire table sequentially. PostgreSQL implements COUNT(*) this way because of the way MVCC (PostgreSQL's concurrency system) stores transaction visibility in the data row and not in the index.

MySQL uses an index for COUNT(*) and also caches the count and is thus much faster:

The reason MySQL has fast counts, is because, they’re cached. This works in MySQL because MySQL uses serialized INSERTs, but higher grade DBs are transactional, and PostgreSQL uses MVCC, so caching row counts produces inaccurate results.

— Bad CTK ,  PostgreSQL Slow Count() Workaround

[edit] ACID Compliance

ACID stands for Atomicity, Consistency, Isolation and Durability. This model is used to judge data integrity across database management systems. Most database systems achieve ACID compliance by using transactions.

PostgreSQL is fully ACID-compliant while MySQL's InnoDB storage engine provides table-level ACID-compliance:

MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines. InnoDB provides full ACID compliance. MySQL Cluster is also a transaction-safe storage engine.

—MySQL AB , MySQL 5.0 Reference Manual :: 1.8.5.2 Transactions and Atomic Operations

To use MySQL's ACID-compliant engine by default simply set default-storage-engine=innodb in your configuration file.

Innobase Oy, the company that developed InnoDB, was bought by Oracle in October 2005. Oracle and MySQL AB signed a contract to extend licensing for the InnoDB engine in 2006, but some worry that when that license period ends the commercial competition between Oracle (owner of the InnoDB storage engine) and Sun (which bought MySQL AB in February 2008) may lead to future licensing and cost issues for MySQL users.

[edit] Features

PostgreSQL and MySQL both have an impressive array of features that increase data integrity, functionality, and performance. The features included in a database may help improve performance, ease of use, functionality, or stability.

[edit] Ease of use

A "gotcha" is a feature or function which works as advertised - but not as expected.

— http://sql-info.de/mysql/gotchas.html

MySQL has sensibly more "gotcha"s[2] than PostgreSQL[3].

[edit] Insert Ignore / Replace

MySQL supports INSERT IGNORE and REPLACE statements which inserts if a row exists and does nothing otherwise or replaces the current row, respectively.

PostgreSQL supports neither of these statements and suggests using stored procedures to get around the lack of these statements. However, there are major shortcomings:

it can only insert a single value at a time. This is a major performance limitation, and also suffers concurrency issues. INSERT IGNORE and REPLACE handle multi-valued inserted much more gracefully.

— Robin Johnson ,  PostgreSQL Vs. MySQL for INSERT IGNORE + REPLACE - stored procedures, savepoints and beyond

[edit] Stored Procedures

Both PostgreSQL and MySQL support stored procedures.

The first query language for PostgreSQL, PL/PgSQL, is similar to Oracle's PL/SQL. PostgreSQL also supports stored procedures in many other languages, including Python, Perl, TCL, Java &c — notably ISO SQL:2003 PSM.

MySQL follows the SQL:2003 syntax for stored routines, which is also used by IBM's DB2.

—MySQL AB , MySQL 5.1 Reference Manual :: 18 Stored Procedures and Functions

[edit] Triggers

Both PostgreSQL and MySQL support triggers. A PostgreSQL trigger can execute any user-defined function from any of its procedural languages, not just PL/pgsql.

MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server; in particular, they are not activated by updates made using the NDB API.

—MySQL AB , MySQL 5.1 Reference Manual :: 19 Triggers

PostgreSQL also supports "rules," which allow operating on the query syntax tree and can do some operations more simply that are traditionally done by triggers.

[edit] Replication and High Availability

Replication is a database management system's ability to duplicate its stored data for the purposes of backup safety and is one way to prevent database downtime. PostgreSQL and MySQL both support replication:

[edit] PostgreSQL

PostgreSQL is modular by design, and replication is not in the core. There are, several packages that allow replication in PostgreSQL:

It is a common misconception that these "third-party packages" are somehow less well integrated. Slony for example was designed and built by Jan Weick, a PostgreSQL core team member and has a number of other members of the PostgreSQL community involved in its ongoing design and maintenance.

[edit] MySQL

MySQL ships with replication support. [4]

Starting with version 5.1 MySQL supports two forms of replication; statement based replication (SBR) and row based replication (RBR). SBR collects SQL queries which affect changes to the database in a binary log which the slave servers subscribe to for their changes. RBR instead records the incremental row changes them selves in this binary log. Some storage engines such as NDB and Falcon only support replication using this new row based format. [5]

[edit] DataTypes

PostgreSQL does not have an unsigned integer data type, but it has a much richer data type support in several aspects: standards compliance, the logically fundamental data type BOOLEAN, user-defined data types mechanism, built-in and contributed data types.

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported. [6]

MySQL does not have network IP address data types that PostgreSQL has but does provide INET_ATON() and INET_NTOA() functions to convert IPv4 addresses to and from easily stored integers.

[edit] Subqueries

Both MySQL and PostgreSQL support subqueries, but in MySQL some forms can be a huge performance liability. This is corrected in version 6.0 [7]

[edit] Advanced Indexing

Advanced indexing methods allow database systems to optimize queries to achieve greater performance.

Index Type MySQL PostgreSQL
Full-Text Indexes MySQL comes with full-text search, but can only be run on the (not transaction safe) MyISAM storage engine. [8]

A 3rd party add-on to MySQL, Sphinx Fulltext Search Engine allows it to support full-text searches of InnoDB tables. The integrated text indexing can not index more than 255 characters. This means you can't have a guaranteed unique text column of more than 255 characters.

PostgreSQL 8.2 has full text search in the tsearch2 module.

PostgreSQL 8.3 integrates tsearch2 into the core: "TSearch2, our cutting-edge full text search tool, has been fully integrated into the core code, and also has a cleaner API." [9]

Partial Indexes MySQL does not support partial indexes. PostgreSQL supports partial indexes:

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.

One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases.

—PostgreSQL , PostgreSQL 8.2.6 Documentation: Chapter 11. Indexes
Prefix Indexes MySQL supports prefix indexes. Prefix indexes cover the first N characters of a string column, making the index much smaller than one that covers the entire width of the column, yet still provide good performance characteristics. With PostgreSQL, prefix indexes are a particular case of Expression Indexes (see below).
Multi-column Indexes MySQL is limited to 16 columns per index. [10] PostgreSQL is limited to 32 columns per index. [11]
Bitmap Indexes MySQL has no bitmap indexes but achieves similar functionality using its "index_merge" feature. Bitmap indexes will be introduced with the Falcon engine. PostgreSQL supports the ability to combine multiple indexes at query time using bitmap indexes.
Expression Indexes Expression Indexes can be emulated in MySQL by adding a precomputed column and using a trigger to maintain it. PostgreSQL allows you to create indexes based on expressions (which may include calls to immutable functions). This is very handy when you have a table with relatively stable data (not a lot of inserts / updates) and will often be running a query which involves an expensive calculation.
Non-blocking CREATE INDEX Dependent on the storage engine. Some engines (such as MySQL Cluster) support online add/drop index (no locks taken). If the engine doesn't support online add/drop index, a write exclusive lock is required and the table copied. PostgreSQL supports the ability to create indexes without locking the table for writes.

[edit] Data Storage Engines

Data Storage Engines take into account the medium it is using (for most purposes, databases are stored on disks to provide persistent data) to maximize read/write performance.

[edit] PostgreSQL

PostgreSQL supports one engine, its default Postgres Storage System.

There are a number of ways to increase PostgreSQL performance. For non-critical data, you can put its storage directory on a RAM disk. [12] This, of course, raises the question of why you'd want to put it in a database anyway. There are DSMS (Data Stream Management Systems) specifically designed to handle transitory data in a highly efficient fashion.

[edit] MySQL

MySQL 5.1 natively supports 9 storage engines [13]:

  • MyISAM
  • InnoDB
  • NDB Cluster
  • MERGE
  • MEMORY (HEAP)
  • FEDERATED
  • ARCHIVE
  • CSV
  • BLACKHOLE

However, the federated, and blackhole engines are not actually "storage" engines. (for example "blackhole" does not store anything). InnoDB is developed by the external company InnoBase, which has been acquired by Oracle. It retains its place in standard MySQL distributions as the main transactional engine.

MySQL is planning to introduce new engines named Maria and Falcon in an upcoming 6.x version. [14] [15]
They include new features such as recovery and more features. These are expected to replace and supplement the MyISAM and InnoDB engines respectively.

There are several externally-developed storage engines, some of the most popular are:

  • SolidDB
  • NitroEDB
  • BrightHouse

MySQL has several custom and community storage engines under development:

In some distributions, the default storage engine is MyISAM, which is not transaction safe. Setting the default engine to a transactional engine such as InnoDB is, however, trivial.

[edit] Licensing

PostgreSQL comes with a BSD-style license, which fits the Free Software Definition and Open Source Definition, and conforms to both the Debian Free Software Guidelines and the Copyfree Standard.

MySQL's source code is available under terms of the GNU General Public License, which also fits the Free Software and Open Source definitions and conforms to the Debian Free Software Guidelines (but not to the Copyfree Standard). It is also available under a proprietary license agreement, which is typically intended for use by those who wish to release software incorporating MySQL code without having to release the source code for the entire application. In practical terms, this means that MySQL can be distributed with or without source code, as can PostgreSQL, but to distribute without source code in the case of MySQL requires paying MySQL AB for a MySQL Commercial License.

[edit] Development

PostgreSQL is not controlled by any single company, but relies on a global community of developers and companies to develop it.

MySQL is owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, which holds the copyright to most of the codebase. On 16 January 2008 MySQL AB announced an agreement to be acquired by Sun Microsystems for approximately US $1 billion. The acquisition is expected to close in the third or fourth quarter of Sun's fiscal year which ends on 30 June 2008.

MySQL is an open-source PRODUCT.

Postgres is an open-source PROJECT.

—Greg Sabino Mullane , Postgres is not for sale

[edit] Culture

[edit] Community

MySQL's community is supported in part by the company's Community Relations Team. MySQL AB has sponsored an annual User's Conference and Expo since 2003. The fact that MySQL AB's business model is based on providing installation, configuration, migration, and special licensing support for the MySQL DBMS probably contributes to the lack of links to third-party and community resources on the official support page. Some believe this creates a contentious relationship between the company and the open source software user community, though the evidence seems to suggest that the relationship is almost nonexistent, rather than hostile.

With a higher ratio of developers to users, the PostgreSQL community tends to make up for a smaller number of users with a greater density of knowledge in community support resources. Lacking the corporate support of a company like MySQL AB (now a subsidiary of Sun), PostgreSQL does benefit from a number of independent companies around the world whose business models center around providing installation, configuration, and migration support for the open source database.

[edit] Name

When the ANSI SQL standard was written, its author explained that the official standards-compliant pronunciation of SQL is "ess queue ell". The names of both MySQL and PostgreSQL reflect the pronunciation specified by the SQL standard's author.

MySQL is pronouned "my ess queue ell".

Because MySQL is an owned software project, MySQL AB has complete control over the name of the project. As a result of this, and the desire for a consistent brand identity, the MySQL name is likely to remain static.

PostgreSQL is pronounced "post gress queue ell", formed by combining Postgres (the name of the original database management system from which PostgreSQL is descended) with SQL. PostgreSQL is a true portmanteau, in that it not only combines the spellings and pronunciations of two words, but also their meanings: it is the Postgres DBMS updated to use SQL.

There has been talk of going back to the original Postgres name, though how much traction the idea has had is debatable. The PostgreSQL wiki provides an overview of the debate, including pros and cons for such a name change and alternatives.

[edit] Popularity

MySQL is widely popular among various open-source web development packages. The MyISAM engine often is the only database engine included in webhosting providers. Many web developers use MySQL. Thus, MySQL became widely popular in web development and MySQL justifiably calls itself "The world's most popular open source database."

Thus, MySQL is stereotyped as "easier" to use than PostgreSQL because of its popularity.

[edit] Links

[edit] Pro PostgreSQL

[edit] Pro MySQL

Retrieved from "http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL"
Personal tools