MySQL General Information

Comparison of Oracle, MySQL and PostgreSQL DBMS

---------------------------------------------------------------------------------------------------- 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.

2. ELEMENTARY FEATURES:

Conformance with SQL standards:

MySQL: Entry-level SQL-92. Oracle: Conformance with SQL'89, subset of SQL'92 types plus specific types. Some SQL'92 types are mapped into Oracle types. No boolean type nor equivalent. PostgreSQL: Extended subset of the SQL92/99 and SQL3 languages. SQL'92 data types syntax are mapped directly into native PostgreSQL types.

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.

Object-relational extensions

MySQL: No. Oracle: Yes. PostgreSQL: Table structure can be inherited.

Special data types (multimedia, etc.)

MySQL: None. PostgreSQL: Geometric types that represent two-dimensional spatial objects.

Subqueries in SQL query:

MySQL: Yes, starting from 4.1 release. Oracle: Uncorrelated and correlated subqueries allowed up to 255 levels. PostgreSQL: Sql compliance with Oracle.

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.

Automatic conversion of code pages (e.g. between client and server):

Automatic conversion of code pages means if code pages on server and on client differ, a conversion should be carried out. MySQL: No. Oracle: Yes. PostgreSQL: No.

3. TRANSACTIONS

Support for transaction processing:

MySQL: Yes for InnoDB table types. Oracle: Yes. PostgreSQL: Yes. Offers the read committed and serializable isolation levels.

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.

Locking level (table, page, row):

MySQL: Table locking for ISAM/MyISAM and HEAP tables, page level locking for BDB tables. InnoDB tables provide locking individual rows. Oracle: Row level and table level. PostgreSQL: Row level and table level.

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.

Encrypted Client - Server connection:

MySql: SSL 4.0 Postgres: SSL or SSH. Kerberos authentication.

Object access privileges (level)

MySQL: Table level. UPDATE and INSERT rights can also be limited for selected columns. Oracle:Access rights on the table level. UPDATE, INSERT and REFERENCES rights on selected columns. PostgreSQL: Table level.

Access privileges grouping:

MySQL: No. Oracle: Privileges can be grouped into roles. Roles can be granted to users or to other roles. PostgreSQL: Groups of users can be created and privileges can can be granted to these groups.

Incremental and on-line backups:

MySQL: InnoDB on-line backup, also supports incremental backups. Oracle: Yes. PostgreSQL: Maybe added soon, expected after the release of 7.5.

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.

Tools for data import:

MySQL: Import from text files, html, dbf. Oracle: High-speed and very flexible data loader. PostgreSQL: COPY moves data between PostgreSQL tables and standard file-system files. COPY instructs the PostgreSQL backend to directly read from or write to a file.

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 query paralleling (Parallel Query Option).
PostgreSQL: PostgreSQL is not threaded, but every connection gets it's own process.

7. PERFORMANCE AND VLDB
Known VLDB implementations:
Very Large DataBases means multiple gigabytes or single terabytes.
MySQL: Handles large databases.
Oracle: Terabyte databases exist.
PostgreSQL: 60GB databases exist.

Maximum number of rows and columns in a table:
MySQL: Maximum number of columns in one table - 3398; size of a table row - 65534 (BLOB and TEXT not included).

Oracle: Maximum number of columns in one table - 1000. Up to 32 columns in index key.
PostgreSQL: Rows - unlimited, columns - 1600; size of a table row - 1.6TB.

Maximum number of tables and indexes:
MySQL: Number of tables - not limited, up to 32 indexes per table with 256 bytes maximum key length.
Oracle: Unlimited.
PostgreSQL: Unlimited.

"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.
PostgreSQL: Tables and Indexes automatically paritioned, but no specific enhancements for query optimization.

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: Yes. Basic (read-only, snapshot) replication available in basic DBMS configuration.
Advanced (symmetric, multimaster) replication is available as an option.
PostgreSQL: Several replications systems available (both commercial and non-commercial) for master-slave,
2 way asynchrons, and cascading slave solutions.

Gateways to other DBMSs:
MySQL: None.
Oracle: Yes, gateways to other DBMSs are available.
PostgreSQL: None.

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 cliens-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).
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.

Application support:
MySQL: C/C++, Java, Perl, Python, PHP, Ruby
Oracle: Many. e.g. Oracle OCI - low level access to DBMS; Perl, Python.
PostgreSQL: C/C++, JAVA/JDBC, PHP, Perl, Ruby, TCK/TK, Python, Object Pascal and
internal procedural languages like PL/pgSQL comparable to oracle PL/SQL.

Dedicated Web servers:
MySQL: None.
Oracle: Several specialized Oracle Web products exist, e.g.:
Oracle WebDB - for simple Web applications;
Oracle Application Server (OAS) - for professional, scalable Web applications;
Oracle Internet Server (IAS) - specialized version of Apache http server;
PostgreSQL: None.

XML support integrated in DBMS:
MySQL: No.
Oracle: Many XML tools integrated witch DBMS (parsers, processors XSLT etc.), e.g.
PostgreSQL: Provides an optional XML handling package distributed with core server.

Support from CASE packages:
MySQL: PLONE, Ruby on Rails.
Oracle: Oracle Designer, JDeveloper.
PostgreSQL: Unknown.

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.

Market share:

According to CD Times magazine dated July 1, 2004

Top Deployed Databases poll shows following databases in use:

SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.

Specific market segments occupied:

MySQL: Web servers. Common on Linux-based systems. Oracle: N/A PostgreSQL: Education, Telephony, DNS Services, Web services.

Mail comments to:[email protected] Last modified: March 14, 2005


Security, Privacy, Legal Fermilab Policy on Computing

MySQL General Information

Comparison of Oracle, MySQL and PostgreSQL DBMS

CONTENTS:

1. GENERAL CONSIDERATION: