1.10.2.2 Featurewise Comparison of MySQL and PostgreSQL

On the crash-me page (http://www.mysql.com/information/crash-me.php) you can find a list of those database constructs and limits that one can detect automatically with a program. Note, however, that a lot of the numerical limits may be changed with startup options for their respective databases. This web page is, however, extremely useful when you want to ensure that your applications work with many different databases or when you want to convert your application from one database to another.

MySQL Server offers the following advantages over PostgreSQL:

• MySQL Server is generally much faster than PostgreSQL. MySQL 4.0.1 also has a query cache that can boost up the query speed for mostly-read-only sites many times.
• MySQL has a much larger user base than PostgreSQL. Therefore, the code is tested more and has historically proven more stable than PostgreSQL. MySQL Server is used more in production environments than PostgreSQL, mostly thanks to the fact that MySQL AB, formerly TCX DataKonsult AB, has provided top-quality commercial support for MySQL Server from the day it was released, whereas until recently PostgreSQL was unsupported.
• MySQL Server works better on Windows than PostgreSQL does. MySQL Server runs as a native Windows application (a service on NT/2000/XP), while PostgreSQL is run under the Cygwin emulation. We have heard that PostgreSQL is not yet that stable on Windows but we haven't been able to verify this ourselves.
• MySQL has more APIs to other languages and is supported by more existing programs than PostgreSQL. See section B Contributed Programs.
• MySQL Server works on 24/7 heavy-duty systems. In most circumstances you never have to run any cleanups on MySQL Server. PostgreSQL doesn't yet support 24/7 systems because you have to run VACUUM once in a while to reclaim space from UPDATE and DELETE commands and to perform statistics analyses that are critical to get good performance with PostgreSQL. VACUUM is also needed after adding a lot of new rows to a table. On a busy system with lots of changes, VACUUM must be run very frequently, in the worst cases even many times a day. During the VACUUM run, which may take hours if the database is big, the database is, from a production standpoint, practically dead. Please note: in PostgreSQL version 7.2, basic vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new VACUUM FULL command does old-style vacuum by locking the table and shrinking the on-disk copy of the table.
• MySQL replication has been thoroughly tested, and is used by sites like:
  • Yahoo Finance (http://finance.yahoo.com/)
  • Mobile.de (http://www.mobile.de/)
  • Slashdot (http://www.slashdot.org/)
• Included in the MySQL distribution are two different testing suites, `mysql-test-run' and crash-me (http://www.mysql.com/information/crash-me.php), as well as a benchmark suite. The test system is actively updated with code to test each new feature and almost all reproducible bugs that have come to our attention. We test MySQL Server with these on a lot of platforms before every release. These tests are more sophisticated than anything we have seen from PostgreSQL, and they ensure that the MySQL Server is kept to a high standard.
• There are far more books in print about MySQL Server than about PostgreSQL. O'Reilly, SAMS, Que, and New Riders are all major publishers with books about MySQL. All MySQL features are also documented in the MySQL online manual because when a new feature is implemented, the MySQL developers are required to document it before it's included in the source.
• MySQL Server supports more of the standard ODBC functions than PostgreSQL.
• MySQL Server has a much more sophisticated ALTER TABLE.
• MySQL Server has support for tables without transactions for applications that need all the speed they can get. The tables may be memory-based, HEAP tables or disk based MyISAM. See section 7 MySQL Table Types.
• MySQL Server has support for two different storage engines that support transactions, InnoDB, and BerkeleyDB. Because every transaction engine performs differently under different conditions, this gives the application writer more options to find an optimal solution for his or her setup, if need be per individual table. See section 7 MySQL Table Types.
• MERGE tables gives you a unique way to instantly make a view over a set of identical tables and use these as one. This is perfect for systems where you have log files that you order, for example, by month. See section 7.2 MERGE Tables.
• The option to compress read-only tables, but still have direct access to the rows in the table, gives you better performance by minimising disk reads. This is very useful when you are archiving things. See section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator.
• MySQL Server has internal support for full-text search. See section 6.8 MySQL Full-text Search.
• You can access many databases from the same connection (depending, of course, on your privileges).
• MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes. Context switching and access to common storage areas is much faster between threads than between separate processes. This gives MySQL Server a big speed advantage in multi-user applications and also makes it easier for MySQL Server to take full advantage of symmetric multiprocessor (SMP) systems.
• MySQL Server has a much more sophisticated privilege system than PostgreSQL. While PostgreSQL only supports INSERT, SELECT, and UPDATE/DELETE grants per user on a database or a table, MySQL Server allows you to define a full set of different privileges on the database, table, and column level. MySQL Server also allows you to specify the privilege on host and user combinations. See section 4.3.1 GRANT and REVOKE Syntax.
• MySQL Server supports a compressed client/server protocol which improves performance over slow links.
• MySQL Server employs a ``storage engine'' concept, and is the only relational database we know of built around this concept. This allows different low-level table types to be called from the SQL engine, and each table type can be optimised for different performance characteristics.
• All MySQL table types (except InnoDB) are implemented as files (one table per file), which makes it really easy to back up, move, delete, and even symlink databases and tables, even when the server is down.
• Tools exist to repair and optimise MyISAM tables (the most common MySQL table type). A repair tool is only needed when a physical corruption of a datafile happens, usually from a hardware failure. It allows a majority of the data to be recovered.
• Upgrading MySQL Server is painless. When you are upgrading MySQL Server, you don't need to dump/restore your data, as you have to do with most PostgreSQL upgrades.

Drawbacks with MySQL Server compared to PostgreSQL:

• Because MySQL Server uses threads, which are not yet flawless on many OSs, one must either use binaries from http://www.mysql.com/downloads/, or carefully follow our instructions in section 2.3 Installing a MySQL Source Distribution to get an optimal binary that works in all cases.
• Table locking, as used by the non-transactional MyISAM tables, is in many cases faster than page locks, row locks, or versioning. The drawback, however, is that if one doesn't take into account how table locks work, a single long-running query can block a table for updates for a long time. This can usually be avoided when designing the application. If not, one can always switch the trouble table to use one of the transactional table types. See section 5.3.2 Table Locking Issues.
• With UDF (user-defined functions) one can extend MySQL Server with both normal SQL functions and aggregates, but this is not yet as easy or as flexible as in PostgreSQL. See section 12.2 Adding New Functions to MySQL.
• Updates that run over multiple tables used to be harder to do in MySQL Server. However, this has been fixed in MySQL Server 4.0.2 with multi-table UPDATE and in MySQL Server 4.1 with subqueries. In MySQL Server 4.0 one can use multi-table deletes to delete from many tables at the same time. See section 6.4.6 DELETE Syntax.

PostgreSQL currently offers the following advantages over MySQL Server:

Note that because we know the MySQL road map, we have included in the following table the version when MySQL Server should support this feature. Unfortunately we couldn't do this for previous comparisons, because we don't know the PostgreSQL roadmap.

Other reasons someone may consider using PostgreSQL:

• Standard usage in PostgreSQL is closer to standard SQL in some cases.
• One can speed up PostgreSQL by coding things as stored procedures.
• The PostgreSQL optimiser can do some optimisation that the current MySQL optimiser can't do. Most notable is doing joins when you don't have the proper keys in place and doing a join where you are using different keys combined with OR. The MySQL benchmark suite at http://www.mysql.com/information/benchmarks.html shows you what kind of constructs you should watch out for when using different databases.
• PostgreSQL has a bigger team of developers that contribute to the server.

Drawbacks with PostgreSQL compared to MySQL Server:

• VACUUM makes PostgreSQL hard to use in a 24/7 environment.
• Only transactional tables.
• Much slower INSERT, DELETE, and UPDATE.

For a complete list of drawbacks, you should also examine the first table in this section.

User Comments

Postgresql has 7.3 in beta testing, and we're
still comparing to 7.0.3? Come on, someone at
MySQL AB must have enough talent to get at least
7.2 up and running and do some comparisons to it,
and not a two year old code base. It makes us
look like cheerleaders, not engineers to compare
our database to a two year old version, especially
if our excuse is that we couldn't get vacuuming to
work reliably. I understand that all vacuum
issues were pretty much solved in the 7.2 release
from January 2002 or so.

Here are the points that I know are incorrect in
this page:

Vacuum no longer really affects performance, you
can run it during normal usage and it hardly
impacts performance. In some testing, I've run it
continuously while doing transactional work, and
the slow down was about 10%. That would hardly
qualify as "practically dead"

Postgresql comes with a complete regression test
package run by using "make test" which is quite
comprehensive.

What ODBC functions do we support that Postgresql
doesn't? I'm not really familiar with this part.

With 7.3 Postgresql's alter table will now have
drop column, so the argument about a more advanced
alter table may go away.

About table handlers, Postgresql does use one,
it's just that it's the only one it uses.

Postgresql autocompresses all text types (char,
varchar, text) so the argument for mysql's read
only compressed tables is weak, since with
postgresql you don't have to do anything to
compress text anyway.

The fact that Postgresql's full text search is
external actually reflects poorly on MySQL's lack
of easy extensibility. Postgresql now has three
different add on full text searches, two of which
are delivered as part of the contrib directory and
fairly easy to add.

Under Linux, threads and processes are pretty much
the same performance wise, so saying that being
multi-threaded makes a big difference on MySQL
should be limited to Solaris / Windows, where
threads are noticeably faster than processes.

Postgresql gets a fully functional priviledge
system for 7.3, which is in beta right now. So
that point may need more close examination.

Postgresql now has compression from client to
server in 7.3 as well.

All Postgresql tables are as well implemented as
files, but due to issues with >2 gig files on some
file systems auto splits them after they reach a
certain size. They still are capable of being
sym-linked elsewhere, just like with MySQL.

Postgresql has a similar program to mysql's isam
repair utility, it's just not very well known.

Most Postgresql upgrades can be accomplished in
place with the upgrade tool, but backups shold be
performed anyway just in case a bug corrupts your
data.

I am very keen to find out more about certain MySQL vs. PostgreSQL differences from an administration point of view.

I agree with Scott Marlowe, it appears as though this comparison is a bit out-of-date. For a user-editable MySQL vs. PostgreSQL comparison (which actually has bias toward PostgreSQL, since I'm the author) is at http://faemalia.org/wiki/view/Technical/PostgreSQLvsMySQL -- I would appreciate if MySQL gurus could give their opinion on that page, especially if you're an ADMINISTRATOR, not a DEVELOPER.

That page sets up the test in a certain fashion where MySQL will lose (ie: I want 24/7 uptime, and MySQL marketers conveniently forget to tell you there's no read-write backup for MySQL... the tables are in read-only mode whilst being backed up... which is a GIGANTIC HUGE GAPING HOLE that needs to be fixed).

There is another out-of-date comparison page (out-of-date like this page, but also pro-PostgreSQL) at the ACS "why not mysql" page: http://openacs.org/philosophy/why-not-mysql.

Add your own comment.