1. GENERAL CONSIDERATION:

----------------------------------------------------------------------------------------------------
Category Problem Importance MySQL Oracle PostgreSQL
----------------------------------------------------------------------------------------------------
Elementary features Basic data types C B C A
SQL B B B B
Declarative constraints B C A A
Programming abstractions A C A C
Transactions Transactions A D A A
Locks A D A A
Programming in DB Multiuser access A C A C
Stored procedures and triggers B C A A
Administration Access control B A A B
Backup A C A C
Data migration C A B A
Portability and Portability B B A B
Scalability Scalability A B A C
Query optimization A B A B
Structures supporting optimization B D A B
Support for OLAP B D A D
Performance and VLDB Allocation of the disk space A C A C
(Very Large DB) Size limits A B A C
VLDB implementation A D A B
Access to multiple databases C C A C
Special data types Large objects B B A C
Post-relational extensions C D A B
Support for special data types C D A C
Application development Embedded SQL C D A B
and interfaces Standard interfaces B B A B
Additional interfaces A A A A
Web technology A B A B
XML B D A D
CASE B D A D
Reliability Recovery A C A C
Commercial issues Prices C A D A
Technical support A C B C
Position in the market A C A C
---------------------------------------------------------------------------------------------
Oracle8i and Oracle9i features are considered for the following comparisons,
and MySQL versions base 4 and higher, PostgreSQL versions 7.2.x and higher.

Binary and Character Large Objects

MySQL: Binary: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, differ only in the maximum
length of the values they can hold up to 2^32(4GB) bytes.
Character: The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond
to the four BLOB types and have the same maximum lengths and storage requirements.
Oracle: Binary:LONG RAW (2 GB), BLOB (4 GB) and BFILE (external storage, up to 4 GB) data types.
Character: LONG (2 GB) and CLOB (4 GB) data types.
PostgreSQL: Binary: Must be defined by user in CREATE TYPE; size is limited by max. row size.
BLOBs are saved in files outside tables, only OID of the BLOB is stored in PostgreSQL table.
Character: TEXT type (size limited by max. row size).

User-defined data types

MySQL: No.
Oracle: User can define new complex data types.
PostgreSQL: User may add new types to PostgreSQL using the CREATE TYPE command.

Subqueries in SQL query:

Primary and Unique keys:

MySQL: Yes.
Oracle: Yes.
PostgreSQL: Yes.

Foreign key:

MySQL: Yes for InnoDB table types where a relational or multi-table delete can be performed.
For MyISAM table type FOREIGN KEY clause is allowed for compatibility only and has no
effect on database operation
Oracle: Yes. ON DELETE CASCADE supported
PostgreSQL: Yes. ON DELETE CASCADE and ON UPDATE CASCADE supported.

Check:

MySQL: Yes for MyISAM tables and views. No for InoDB tables where the CHECK clause is
allowed for compatibility only and has no effect on database operation.
Oracle: Yes.
PostgreSQL: Yes.

Views:

MySQL: Yes in version 5 and higher.
Oracle: Yes.
PostgreSQL: Yes.

Updateable views:

MySQL: updateable VIEWs based on single table or other updateable VIEWs available
from MySql 5.0.1 release.
Oracle: Yes.
PostgreSQL: Yes - writes to the view are pushed through to the underlying base tables.

Synonyms (an alias for any table, view or other object in database):

MySQL: No.
Oracle: Yes.
PostgreSQL: No.

Autoincrement (counter) columns:

MySQL: Yes. There can be only one autoincrement column in a table, which must be indexed
Oracle: No.
PostgreSQL: Yes. SERIAL data type.

3. TRANSACTIONS

Support for transaction processing:

Partial rollback of transaction:

MySQL: Starting from 4.0.14, InnoDB supports SAVEPOINT and ROLLBACK TO SAVEPOINT.
Oracle: Rollback to savepoint. There is also a possibility to start a new,
independent transaction from current transaction.
PostgreSQL: Starting from 8.0.1 supports ROLLBACK, ROLLBACK TO SAVEPOINT.

Deadlock detection and resolving:

MySQL: Yes.
Oracle: Yes.
PostgreSQL: Yes.

4. PROGRAMMING IN DB

Languages for writing stored procedures:

MySQL: Yes as of version 5. Previously a UDF (written in C or C++) was used to
extend MySQL with a new function that works like native MySQL functions.
Oracle: PL/SQL and Java.
PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C, possibility of creating new language - CREATE LANGUAGE.

Triggers:

MySQL: Rudimentary support for triggers is included os of version 5.0.2.
Oracle: In PL/SQL. Possibility of writing triggers reacting on the events:
BEFORE/AFTER DELETE/UPDATE/INSERT. INSTEAD OF triggers can be used for updating data through views.
PostgreSQL: Declarative rules - extension to SQL. You can specify SELECT, INSERT, DELETE or UPDATE
as a rule event. INSTEAD OF rules can be used for updating data through views.
Procedural triggers in PL/PGSQL, PL/TCL, PL/Perl, C. CREATE CONSTRAINT TRIGGER creates
a trigger to support a constraint.
You can specify BEFORE or AFTER on INSERT, DELETE or UPDATE as a trigger event.

5. ADMINISTRATION

User authorization:

MySQL: User is identified on the base of the login, password, and hostname (from which you connect).
Authorization is made on server side, but there is possibility to use secure connections
between client and server using SSL.
Oracle: User is identified on the base of the login and password; there is also possibility to use
operating system level authorization.
PostgreSQL: Following per-database authentication options available in PostgreSQL:
Trust authentication;
Password authentication;
Kerberos authentication;
Ident-based authentication;
PAM - Pluggable Authentication Modules based authentication method.

Incremental and on-line backups:

Tools for data export:

MySQL: Set of special utilities - mysqldump, mysqlhotcopy, mysqlsnapshot, innodb2myisam.
Selected tables or the whole database dumped to SQL statements ready to insert to another database.
BLOB values are saved inside normal tables, so there is no any problem with dumping such a values.
Oracle: Only spooling of SQL query results.
PostgreSQL: pg_dump data dumping into a script file containing DML commands.
pg_dumpall is a utility for dumping out all PostgreSQL databases into one file.
COPY moves data between PostgreSQL tables and standard file-system files.

6. PORTABILITY AND SCALABILITY

Hardware and system platforms supported:

MySQL: Many Unix, Windows and OS/2 platforms. Packaged and available with most Linux distributions.
Oracle: Over 200 platforms, including Unix, Windows, mid-range and mainframe platforms.
PostgreSQL: Many Unix and Windows platforms, QNX - real time system for x86.

Portability of data and code (e.g. stored procedures)

MySQL: Copy mysql files (.frm, .MYI, .MYD) between platforms with different architecture but
using the same floatingpoint format. Data and index files in ISAM databases are independent
of hardware architecture and in some cases of systems. "mysqldump" program.
Oracle: Data and code can be ported between platforms without any changes using export/import utilities.
PostgreSQL: Yes.

Support for SMP systems (parallel query execution, etc.):

Parallel processing in single memory systems - also known as symmetric multiprocessing
(SMP) hardware, in which multiple processors use one memory resource.
MySQL: Multi-threaded server that can use many processors. A separate thread is created for each connection.
Oracle: Oracle can use multiprocessor SMP systems, e.g. for loadin/query paralleling (Parallel Query Option).
PostgreSQL: PostgreSQL is not threaded, but every connection gets it's own process.

"Manual" tuning of the allocation:

MySQL: Some parameters for InnoDB tables as tablespace, datafiles location, auto-extending, etc.
Oracle: Yes - Many parameters of the allocation can be tuned on system, tablespace or object level.
PostgreSQL: Datafile location and a tablespace implementation as of version 8.

Automatic partitioning of large tables/indexes and using partitions in query optimization:

Partitioning addresses the problem of supporting very large tables and indexes by allowing
users to decompose them into smaller and more manageable pieces called partitions.
MySQL: Yes for InnoDB tables.
Oracle: Yes, physical separation of tables or indices to improve performance and maintenance.
PostgreSQL: Tables and Indexes automatically partitioned, but no specific enhancements for query optimization.
PostgreSQL supports partitioning via table inheritance. Each partition
must be created as a child table of a single parent table. The parent table itself is normally empty;
it exists just to represent the entire data set. You should be familiar with inheritance
before attempting to set up partitioning.

Access to multiple databases in one session:

MySQL: Only switching between databases. In SELECT data from different database can be taken.
Oracle: Each session can use data from many instances, using transparent distributed SQL access.
PostgreSQL: Only switching between databases. No support for selecting data from different databases.

Replication:

Replication is the process of copying and maintaining database objects in multiple databases
that make up a distributed database system. Replication can improve the performance and protect
the availability of applications because alternate data access options exist.
MySQL: Replication works fine in the last stable version (3.23.32). It is master-slave replication using
binary log of operations on the server side. It is possible to build star or chain type structures.
Oracle: Generally 3 Oracle tools/products cover replication area. All of them are part of the
Oracle Server Enterprise Edition (not extra-cost options).
Oracle replication methods:

Oracle Data Guard - redo log based change data capture. Oracle Data Guard
uses archived redo logs to capture the data changes. Oracle Data Guard standby
databases can be either Physical standby or Logical standby. Physical replica/standby
can not be open during the replication. Logical replica can be open during
replication (useful for near real time reporting, replicated objects are in
read-only mode)

PostgreSQL: Several replications systems available (both commercial and non-commercial) for master-slave,
2 way asynchrons, and cascading slave solutions.

Gateways to other DBMSs:

8. APPLICATION DEVELOPMENT AND INTERFACES

Standard interfaces ODBC and JDBC:

MySQL: ODBC(myODBC code) and 2 types of JDBC drivers are supported.
Oracle: JDBC Thin Driver - for client-side applications (applets) in three layer architecture;
JDBC OCI Client-Side Driver - for client-server applications;
JDBC Server Driver - to create applications working on server side (servlets).
Oracle ODBC Driver for Rdb - enables Microsoft[R] Windows and Windows NT[TM] applications
that implement the Microsoft Open Database Connectivity (ODBC) API to read from and write
to Oracle Rdb databases.
PostgreSQL: PostgreSQL provides a type 4 JDBC Driver. Type 4 indicates that the driver is written
in Pure Java, and communicates in the database's own network protocol. Because of this,
the driver is platform independent. Once compiled, the driver can be used on any platform.

Support from CASE packages:

9. RELIABILITY

Automatic recovery from failures:

MySQL: Only InnoDB tables have automatic crash recovery. Hot backup tool available for InnoDB to make backups
of a running database in background, without setting any locks or disturbing database operation.
Oracle: Recovery from soft failures is automatic and transparent. Recovery from media failures requires
backup copy. With redo logs it is possible to recover all commited transactions or to recover to
specific time point.
PostgreSQL: Write Ahead Logging (WAL).

10. COMMERCIAL ISSUES

License type:

MySQL: GPL (Gnu General Public License) or Commercial.
Oracle: Negotiated with Oracle on a site by site basis.
PostgreSQL: BSD (Berkeley Software Distribution) open source.

Technical support:

MySQL: Mailing lists and Web-site. Also technical support services by phone available (paid).
Oracle: Technical support services available on several levels. Quality of these services is quite high.
PostgreSQL: Mailing lists and Web-site. Commercial support becoming available from several smaller
dedicated PostgreSQL companies (Command Prompt, PGSQL Inc.) and available in some geographic
areas from major companies like Fujitsu, SRA, and Pervasive.