GNU Info
(mysql.info)MySQL-PostgreSQL features
Featurewise Comparison of MySQL and PostgreSQL
..............................................
On the crash-me (http://www.mysql.com/information/crash-me.php) page
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 respective
database. The above web page is however extremely useful when you want
to ensure that your applications works with many different databases or
when you want to convert your application from one datbase to another.
MySQL offers the following advantages over PostgreSQL:
* `MySQL' is generally much faster than PostgreSQL. Note:
MySQL-PostgreSQL benchmarks.
* MySQL has a much larger user base than PostgreSQL, therefor the
code is more tested and has historically been more stable than
PostgreSQL. MySQL is the much more used in production
environments than PostgreSQL, mostly thanks to that MySQL AB,
formerly TCX DataKonsult AB, has provided top quality commercial
support for MySQL from the day it was released, whereas until
recently PostgreSQL was unsupported.
* MySQL works better on Windows than PostgreSQL does. MySQL runs as
a native Windows application (a service on NT/Win2000/WinXP), 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. Note: Contrib.
* MySQL works on 24/7 heavy duty systems. In most circumstances you
never have to run any cleanups on MySQL. 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 analyzes 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. The PostgreSQL team has
fixing this on their TODO, but we assume that this is not an easy
thing to fix permanently.
* A working, tested replication feature 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 repeatable bugs that have
come to our attention. We test MySQL with these on a lot of
platforms before every release. These tests are more
sophisticated than anything we have seen from PostgreSQL, and they
ensures that the MySQL is kept to a high standard.
* There are far more books in print about MySQL 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 on-line manual, because when a new feature
is implemented, the MySQL developers are required to document it
before it's included in the source.
* MySQL supports more of the standard ODBC functions than
`PostgreSQL'.
* MySQL has a much more sophisticated `ALTER TABLE'.
* MySQL has support for tables without transactions for applications
that need all speed they can get. The tables may be memory based,
`HEAP' tables or disk based `MyISAM'. Note: Table types.
* MySQL has support for two different table handlers that support
transactions, `BerkeleyDB' and `InnoDB'. 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. Note: 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. Note: MERGE.
* The option to compress read-only tables, but still have direct
access to the rows in the table, gives you better performance by
minimizing disk reads. This is very useful when you are archiving
things. Note: myisampack.
* MySQL has internal support for fulltext search. Note: Fulltext
Search.
* You can access many databases from the same connection (depending
of course on your privileges).
* MySQL 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 a big speed advantage in multi-user
applications and also makes it easier for MySQL to take full
advantage of symmetric multiprocessor (SMP) systems.
* MySQL 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
allows you to define a full set of different privileges on
database, table and column level. MySQL also allows you to
specify the privilege on host and user combinations. Note:
GRANT.
* MySQL supports a compressed client/server protocol which improves
performance over slow links.
* MySQL employs a "table handler" concept, and is the only relational
database we know of built around this concept. This allows
different low-level table types to be swapped into the SQL engine,
and each table type can be optimized for different performance
characteristics.
* All MySQL table types (except *InnoDB*) are implemented as files
(one table per file), which makes it really easy to backup, move,
delete and even symlink databases and tables, even when the server
is down.
* Tools to repair and optimize *MyISAM* tables (the most common
MySQL table type). A repair tool is only needed when a physical
corruption of a data file happens, usually from a hardware
failure. It allows a majority of the data to be recovered.
* Upgrading MySQL is painless. When you are upgrading MySQL, you
don't need to dump/restore your data, as you have to do with most
PostgreSQL upgrades.
Drawbacks with MySQL compared to PostgreSQL:
* The transaction support in MySQL is not yet as well tested as
PostgreSQL's system.
* Because MySQL uses threads, which are not yet flawless on many
OSes, one must either use binaries from
`http://www.mysql.com/downloads', or carefully follow our
instructions on
`http://www.mysql.com/doc/I/n/Installing_source.html' 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 usable be avoided when designing
the application. If not, one can always switch the trouble table
to use one of the transactional table types. Note: Table locking.
* With UDF (user defined functions) one can extend MySQL with both
normal SQL functions and aggregates, but this is not yet as easy
or as flexible as in PostgreSQL. Note: Adding functions.
* Updates and deletes that run over multiple tables is harder to do
in MySQL. This will, hoever, be fixed in MySQL 4.0 with multi-table
`DELETE' and multi-table `UPDATE' and in MySQL 4.1 with subselects.
PostgreSQL currently offers the following advantages over MySQL:
Note that because we know the MySQL road map, we have included in the
following table the version when MySQL should support this feature.
Unfortunately we couldn't do this for previous comparison, because we
don't know the PostgreSQL roadmap.
*Feature* *MySQL version*
Subselects 4.1
Foreign keys 4.0 and 4.1
Views 4.2
Stored procedures 4.1
Extensible type system Not planned
Unions 4.0
Full join 4.0 or 4.1
Triggers 4.1
Constrainst 4.1
Cursors 4.1 or 4.2
Extensible index types like R-trees R-trees are planned
for 4.2
Inherited tables Not planned
Other reasons to use PostgreSQL:
* Standard usage in PostgreSQL is closer to ANSI SQL in some cases.
* One can speed up PostgreSQL by coding things as stored procedures.
* PostgreSQL has a bigger team of developers that contribute to the
server.
Drawbacks with PostgreSQL compared to MySQL:
* `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.
MySQL-PostgreSQL benchmarks- Benchmarking MySQL and PostgreSQL
automatically generated by info2www version 1.2.2.9