Comparison of Oracle, MySQL and Postgres DBMS

Table of contents

• Introduction
• Elementary features
• Transactions and multiuser access
• Programming in database
• Elements of database administration
• Portability and scalability
• Performance and VLDB (Very Large Databases)
• Distributed databases
• Special data types
• Application development and interfaces
• Reliability
• Commercial issues
• Summary
• Authors

Introduction

This document contains a comparison of Oracle, MySQL and PostgreSQL databases in the context of selection of a DBMS for ALICE experiment. One of the co-authors (W.Peryt), after his presentation on databases, was asked by the Technical Board at its meeting held at CERN on December 7-th 2000 to prepare such a document. This analysis was carried out by Warsaw database group from WUT, working for ALICE. We have taken the following approach: first of all we determined what features of DBMS are important from the point of view of such a large experiment. We chose the following features:

• Elementary features (basic data types, SQL language features, declarative integrity constraints, programming abstractions, automatic generation of identifiers, national characters support).
• Transactions and multi-user access (transactions, locks, multi-user access).
• Programming in database (stored procedures and triggers).
• Elements of database administration (access control, backup copies, data migration).
• Portability and scalability
• Performance and VLDB (Very Large Database) issues (query optimization, structures supporting query optimization, support for analytical processing, allocation of disk space, data size limits, known VLDB implementations)
• Distributed databases (access to multiple databases, heterogeneous systems support)
• Special data types (large objects in database, post-relational extensions, support for special data types)
• Application development and interfaces (embedded SQL, standard interfaces, additional interfaces, interoperability with Web technology, XML, CASE)
• Reliability (failure recovery)
• Commercial issues (technical support available, market position)

Having completed step one we carried out subsequent work in 3 subgroups; each of them dealt with only one DBMS. The members of particular subgroups had their own practical experience with using DBMS being subject to investigation by their subgroup. Such a procedure gave us the possibility of verifying information contained in manuals and other documentation available (for instance on Internet). As a result 3 extended documents devoted to Oracle, MySQL and PostgreSQL were created. Those were discussed by all people involved in this task and compilation entitled "Comparison of Oracle, MySQL and PostgreSQL DBMS" was made by Dr. Tomasz Traczyk, computer scientist specializing in the database domain. This compilation circulated within the whole group a few times to make sure we avoided some omissions or mistakes. This version of the document is accepted by all co-authors. We consider it a quite comprehensive and objective comparison.

The comparison contains also some kind of "weights" called by us "importance", with differentiation for Central database and Lab-participants. Central database will be a kind of data warehouse at CERN, containing all the data, also data transferred from Lab-participants periodically. The term "Lab-participants" denotes smaller databases in labs involved in ALICE experiment preparation.

A few explanations of terminology used in the database domain are also included to make this document easy to comprehend for non-specialists. Summary and list of authors are given at the end of the document.

Elementary features

Basic data types

Importance

Central database:		Less important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Good
Oracle8	Average
Postgres	Very good

Detailed questions

• Character, numeric and date/time data types

  MySQL:		Broad subset of SQL'92 types, including all SQL'92 numeric types.
  Oracle8:		Subset of SQL'92 types plus specific types. Some SQL'92 types are mapped into Oracle types. No boolean type nor equivalent.
  Postgres:		Broad set of native data types, including boolean, money, many date-time and numeric types. SQL'92 data types syntax are mapped directly into native Postgres types.

SQL language features

Importance

Central database:		Important
Lab-participants:		Important

Assessment

Product	Grade
MySQL	Average
Oracle8	Good
Postgres	Good

Detailed questions

• Conformance with SQL standards

  Conformance with SQL'89, SQL'92 and SQL3 standards.

  MySQL:		MySQL is compatible with SQL'92 entry level. MySQL Differences compared to ANSI SQL92 For VARCHAR columns, trailing spaces are removed when the value is stored. In some cases, CHAR columns are silently changed to VARCHAR columns. Privileges for a table are not automatically revoked when you delete a table. You must explicitly issue a REVOKE to revoke privileges for a table. NULL AND FALSE will evaluate to NULL and not to FALSE. Functionality missing from MySQL: Sub-selects (in FROM clause). SELECT INTO TABLE. MySQL doesn't yet support the Oracle SQL extension: SELECT... INTO TABLE... MySQL supports instead the ANSI SQL syntax INSERT INTO... SELECT..., which is basically the same thing. Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE... SELECT to solve your problem. Transactions; Stored procedures and triggers; Foreign key clauses (ON DELETE, ON UPDATE) exist only for compatibility; Views.
  Oracle8:		Good conformance with SQL'89, conformance with SQL'92 entry level declared.
  Postgres:		Postgres implements an extended subset of the SQL92 and SQL3 languages. Some language elements are not as restricted in this implementation as is called for in the language standards, in part due to the extensibility features of Postgres.

• Subqueries in SQL query

  Possibility of using subqueries (nested queries) anywhere in SQL query.

  MySQL:		No.
  Oracle8:		Uncorrelated and correlated subqueries allowed. Queries can be nested up to 255 levels.
  Postgres:		In the WHERE and HAVING clauses the use of subqueries (subselects) is allowed in every place where a value is expected. In this case the value must be derived by evaluating the subquery first.

• Subqueries in FROM clause

  Possibility of using subquery (nested query) in FROM clause of SQL query.

  MySQL:		No. Will be added in next versions.
  Oracle8:		Yes.
  Postgres:		No. The feature will be supported from version 7.1.

Declarative integrity constraints

Integrity constraints defined declaratively in SQL (e.g. in CREATE TABLE statement) and executed by DBMS.

Importance

Central database:		Important
Lab-participants:		Important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Very good

Detailed questions

• Primary key

  MySQL:		Yes.
  Oracle8:		Yes.
  Postgres:		Yes.

• Unique key

  MySQL:		Yes.
  Oracle8:		Yes.
  Postgres:		Yes.

• Foreign key

  MySQL:		No. FOREIGN KEY clause is allowed for compatibility only and has no effect on database operation.
  Oracle8:		Yes. ON DELETE CASCADE supported
  Postgres:		Yes. ON DELETE CASCADE and ON UPDATE CASCADE supported. SET NULL, SET DEFAULT and NO ACTION also supported.

• Check

  MySQL:		No. CHECK clause is allowed for compatibility only and has no effect on database operation.
  Oracle8:		Yes.
  Postgres:		The CHECK constraint specifies a restriction on allowed values within a column. The CHECK constraint is also allowed as a table constraint. The SQL'92 CHECK column constraints can only be defined on, and refer to, one column of the table. Postgres does not have this restriction.

Programming abstractions

Virtual SQL language structures as views and synonyms.

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Average

Detailed questions

• Views

  A view is a tailored presentation of the data contained in one or more tables (or other views). A view takes the output of a query and treats it as a table; therefore, a view can be thought of as a "stored query" or a "virtual table". It should be possible to use views in most places where a table can be used.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		Yes.

• Updateable views

  Updateable view is a view which can be used in DML (Data Manipulation Language) statements for modification of the data.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No. Similar functionality can be achieved using INSTEAD OF rules.

• Synonyms

  A synonym is an alias for any table, view or other object in database.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

Automatic generation of identifiers

Means of generating unique identifiers.

Importance

Central database:		Less important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Very good

Detailed questions

• Autoincrement (counter) columns

  Columns of such type are automatically filled by DBMS with unique numbers.

  MySQL:		MySQl supports autoincrement columns (only for numerical types). There can be only one autoincrement column in a table which have to be indexed. Default value is next value.
  Oracle8:		No.
  Postgres:		SERIAL data type.

• Sequence objects

  Sequences are objects generating series of unique numbers.

  MySQL:		No.
  Oracle8:		Yes. Many parameters allow precise control of sequence.
  Postgres:		CREATE SEQUENCE is a Postgres SQL language extension.

National characters support

Importance

Central database:		Important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Good

Detailed questions

• Support for West-European and Central-European Languages

  MySQL:		Yes.
  Oracle8:		Yes.
  Postgres:		Yes.

• Which code pages are supported (ISO 8859-1, ISO 8859-2)

  MySQL:		Many code pages, including ISO 8859-1 and ISO 8859-2.
  Oracle8:		Many code pages, including ISO 8859-1 and ISO 8859-2.
  Postgres:		ISO 8859-1, ISO 8859-2 and others.

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

  If code pages on server and on client differ, a conversion should be carried out.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

Transactions and multiuser access

Transactions

We consider transactions conforming ACID rules (see below).

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Very good

Detailed questions

• Support for transaction processing

  MySQL:		Transactions are not fully supported. There is semi-transaction processing in the newest version of MySQL, but it cannot be even compared with Oracle nor Postgres. In addition: using semi-transactions in MySQL requires special database design and slows down all queries. Better tools will be available in next versions.
  Oracle8:		Yes.
  Postgres:		Yes.

• ACID transactions (e.g. DEFERRED clause)

  ACID means: Atomicity, Consistency, Isolation and Durability of the transaction. In particular, the data should be consistent at the end of the transaction, but the integrity rules could be temporarily broken during the transaction.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		Yes.

• Method of transaction's isolation

  If some transactions are performed concurrently, each of them should view the data as if there is no concurrent transaction in the system.

  • With read commited isolation level each query executed by a transaction sees only data that was committed before the query (not the transaction) began. This isolation level prevents from reading dirty (uncommitted) data.
  • Serializable transactions see only those changes that were committed at the time the transaction began, plus changes made by the transaction itself. Serializable transactions do not experience nonrepeatable reads or phantoms.

  MySQL:		N/A
  Oracle8:		Oracle offers the read committed and serializable isolation levels. Versioning is used rather than locks - reads do not lock writes and vice versa.
  Postgres:		Postgres offers the read committed and serializable isolation levels.

• Partial rollback of transaction

  MySQL:		N/A
  Oracle8:		Yes - rollback to savepoint. There is also a possibility to start a new, independent transaction from current transaction.
  Postgres:		No.

Locks

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Very good

Detailed questions

• Locking level (table, page, row)

  MySQL:		Currently MySQL only supports table locking for ISAM/MyISAM and HEAP tables and page level locking for BDB tables.
  Oracle8:		Row level and table level.
  Postgres:		Row level and table level. In addition to locks, short-term share/exclusive latches are used to control read/write access to table pages in shared buffer pool. Latches are released immediately after a tuple is fetched or updated.

• Deadlock detection and resolving

  MySQL:		Yes.
  Oracle8:		Yes.
  Postgres:		Yes.

Multiuser access

Importance

Central database:		Critical
Lab-participants:		Not important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Average

Detailed questions

• Practically useful maximum number of concurrent users

  MySQL:		N/D
  Oracle8:		Over thousand. Ability to service a great amount of parallel connections to a database - use of multi-threaded server.
  Postgres:		N/D

• Practical maximum number of concurrent users writing to the database

  MySQL:		N/D
  Oracle8:		No limit.
  Postgres:		N/D

Programming in database

Stored procedures and triggers

Importance

Central database:		Important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Very good

Detailed questions

• Languages for writing stored procedures

  Stored procedures are procedures in procedural programming language, which are stored in database and can be executed on server side.

  MySQL:		No stored procedures supported in current version.CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER} SONAME shared_library_name, DROP FUNCTION function_name.A user-definable function (UDF) is a way to extend MySQL with a new function that works like native (built in) MySQL functions such as ABS() and CONCAT(). AGGREGATE is a new option for MySQL Version 3.23. An AGGREGATE function works exactly like a native MySQL GROUP function like SUM or COUNT(). CREATE FUNCTION saves the function's name, type, and shared library name in the mysql.func system table. For the UDF mechanism to work, functions must be written in C or C++, your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).
  Oracle8:		PL/SQL and Java.
  Postgres:		PL/PGSQL, PL/TCL, PL/Perl, SQL, C, possibility of creating new language - CREATE LANGUAGE. Functions written in C can be compiled into dynamically loadable objects, and used to implement user-defined SQL functions.

• Active rules, triggers

  Active rules are statements or procedures defined in the database (as an extension to table definition) that are automatically executed where given event (e.g. DML statement) occurs.

  Triggers are active rules defined as procedures in procedural language.

  MySQL:		No triggers supported now and won't be added in the future.
  Oracle8:		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.
  Postgres:		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.

Elements of database administration

Access control

Importance

Central database:		Important
Lab-participants:		Not important

Assessment

Product	Grade
MySQL	Very good
Oracle8	Very good
Postgres	Good

Detailed questions

• 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.
  Oracle8:		User is identified on the base of the login and password; there is also possibility to use operating system level authorization.
  Postgres:		Local entries control access by users logged into the same computer as the database server. Local connections use Unix domain sockets. The following per-database authentication options are available: trust - Trust users connecting to this database. password - Require a password of users connecting to this database. crypt - Like password, except send the password in an encrypted manner. This method is more secure than password. reject - Reject all connection requests for this database. Host and hostssl entries control TCP/IP network access. These entries support all of the local options, plus the following: Ident - Use a remote ident server for authentication. krb4 - Use Kerberos IV authentication. krb5 - Use Kerberos V authentication.

• Object access privileges (level)

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

• Access privileges grouping

  MySQL:		Not possible.
  Oracle8:		Privileges can be grouped into roles. Roles can be granted to users or to other roles.
  Postgres:		Groups of users can be created and privileges can can be granted to these groups.

Backup copies

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Average

Detailed questions

• Incremental backups

  An incremental backup is a backup that contains only the data that has been modified since a previous backup.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

• On-line backups

  On-line backups are backups that are performed when the database is on line.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

Data migration

Importance

Central database:		Less important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Very good
Oracle8	Good
Postgres	Very good

Detailed questions

• Tools for data export (in universal formats)

  MySQL:		All data can be exported (dumped) to SQL statements ready to insert to another database. There exists a special utility - mysqldump which can do that. It dumps only selected tables or the whole database. It has also many additional options (only structure, only data, with dropping old tables, etc.). BLOB values are saved inside normal tables, so there doesn't exist any problem with dumping such a values.
  Oracle8:		Only spooling of SQL query results..
  Postgres:		pg_dump is a utility for dumping out a Postgres database into a script file containing query commands. pg_dumpall is a utility for dumping out all Postgres databases into one file. It also dumps the pg_shadow table, which is global to all databases. pg_dumpall includes in this file the proper commands to automatically create each dumped database before loading. COPY moves data between Postgres tables and standard file-system files. COPY instructs the Postgres backend to directly read from or write to a file.

• Tools for data import

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

Portability and scalability

Portability of DBMS

Importance

Central database:		Important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Good

Detailed questions

• Hardware and system platforms supported

  MySQL:		Many Unix, Windows and OS/2 platforms.
  Oracle8:		Over 200 platforms, including Unix, Windows, mid-range and mainframe platforms.
  Postgres:		Many Unix and Windows platforms, QNX - real time system for x86.

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

  MySQL:		If you use MySQL version 3.23 then you can copy some 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. Generally if we want to move database to other platform we use "mysqldump" program.
  Oracle8:		Data and code can be ported between platforms without any changes. Oracle export/import utilities should be used.
  Postgres:		Yes.

Scalability

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Average

Detailed questions

• 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:		MySQL is multi-threaded server so it can use many processors. A separate thread is created for each connection.
  Oracle8:		Oracle can use multiprocessor SMP systems, e.g. for query paralleling (Parallel Query Option).
  Postgres:		Postgres is not threaded, but every connection gets it's own process. The OS will distribute the processes across the processors. Basically a single connection will not be any faster with SMP, but multiple connections will be.

• Support for MPP systems

  Parallel processing in clustered architectures and massively parallel processing (MPP) hardware, in which each node has its own memory.

  MySQL:		No.
  Oracle8:		MPP systems can be used by Parallel Server Option.
  Postgres:		No.

Performance and VLDB (Very Large Databases)

VLDB means Very Large DataBases - multiple gigabytes or single terabytes.

Query optimization

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Good

Detailed questions

• Types of query optimization (rule-based, cost-based, etc.)

  Query optimization is choosing the most effective plan of query execution (use of indexes, methods of joining tables, etc.).

  MySQL:		Query optimizer analyzes primarily a syntax of the query, but it can also use some statistics of indexes.
  Oracle8:		Rule-based optimizer (analyzes only a syntax of the query) and cost-based optimizer (uses statistics of data, can use histograms).
  Postgres:		Cost-based. The task of the planner/optimizer is to create an optimal execution plan. It first combines all possible ways of scanning and joining the tables that appear in a query. All the created paths lead to the same result and it's the task of the optimizer to estimate the cost of executing each path and find out which one is the cheapest. Every index access method must provide a cost estimation function for use by the planner/optimizer.Genetic Query Optimization (GEQO) - the GEQO module is intended for the solution of the query optimization problem similar to a traveling salesman problem (TSP). Possible query plans are encoded as integer strings. Each string represents the join order from one relation of the query to the next. The GEQO module gives the following benefits to the Postgres DBMS compared to the Postgres query optimizer implementation: handling of large join queries through non-exhaustive search; improved cost size approximation of query plans since no longer plan merging is needed (the GEQO module evaluates the cost for a query plan as an individual).

• Possibility of "manual" query optimization

  Possibility of giving "hints" to the query optimizer or tuning its parameters.

  MySQL:		No. EXPLAIN exists - like in Postgres.
  Oracle8:		Type of optimization can be choosen. For cont-based optimizer hints can be used to control query optimiser. A goal of optimization can be chosen. An execution plan for a query can be stored and used in subsequent queries.
  Postgres:		No, only some tricks can be used. Some optimizer parameters can be tuned for a whole session. EXPLAIN structure can also be used, which explains if indexes needed for effective execution of given query exist.

Structures supporting query optimization

Special structures - different from standard B-tree indexes - which support effective execution of queries.

Importance

Central database:		Important
Lab-participants:		Not important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Good

Detailed questions

• Bitmap indexes

  In a bitmap index, a bitmap for each key value is used instead of a list of row addresses. Each bit in the bitmap corresponds to a possible row, and if the bit is set, it means that the row contains the key value. If the number of different key values is small, bitmap indexes are very space efficient. Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all conditions are filtered out before the table itself is accessed. As a result, response time is improved, often dramatically.

  Bitmap indexing technology is used mainly in analytical processing (OLAP, data warehouses, etc.).

  MySQL:		No
  Oracle8:		Yes.
  Postgres:		No.

• Clusters

  Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

  MySQL:		No.
  Oracle8:		Yes. Clustering is dynamic - new or changed data in clustered tables is automatically reclustered.
  Postgres:		When a class is clustered, it is physically reordered based on the index information. The clustering is static. In other words, as the class is updated, the changes are not clustered. No attempt is made to keep new instances or updated tuples clustered. If one wishes, one can recluster manually by issuing the command again.

• Hashing

  Hashing is a way of accessing table data alternative to indexing. To use hashing, you create a hash table containing the results of a hash function for each key value.

  DBMS uses a hash function to generate a distribution of numeric values, called hash values, which are based on specific key values. To find or store a row in a hash cluster, DBMS applies the hash function to the row's key value; the resulting hash value corresponds to a data block in the database, which DBMS then reads or writes on behalf of the issued statement. To find or store a row in an indexed table or cluster, at least two I/Os must be performed (but often more): one or more I/Os to find or store the key value in the index, and another I/O to read or write the row in the table or cluster. In contrast, when DBMS uses a hash function to locate a row, no I/O is required.

  MySQL:		No.
  Oracle8:		Yes (hash clusters).
  Postgres:		Hash access methods for indices - an implementation of Litwin's linear hashing.

• Others

  MySQL:		None.
  Oracle8:		Function-based indexes (a function call can be used in index key). Index-organized tables (the data for the table is held in its associated index). Reverse key index - with reversed bytes of each column indexed while keeping the column order. Such an arrangement can help avoid performance degradation in indexes where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.
  Postgres:		Partial index - an index built over a subset of a table.

Support for analytical processing

Support for OLAP (On Line Analytical Processing), multidimensional analysis and similar analytical processing.

Importance

Central database:		Important
Lab-participants:		Not important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Poor

Detailed questions

• OLAP supporting functions in SQL

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

• Special optimization of star queries

  Star queries are queries against star data schemas, typical for data warehouses and OLAP. A star query is a join between a fact table and a number of lookup tables. Each lookup table is joined to the fact table using a primary-key to foreign-key join, but the lookup tables are not joined to each other.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

Allocation of the disk space

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Average

Detailed questions

• "Manual" tuning of the allocation

  MySQL:		No.
  Oracle8:		Yes - many parameters of the allocation can be tuned on system, tablespace or object level.
  Postgres:		No.

• Automatic partitioning of large tables/indexes

  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.

  The majority of application programs require partition transparency, that is the programs should be insensitive to whether the data they access is partitioned and how it is partitioned.

  MySQL:		No.
  Oracle8:		Yes. Automatic ("intelligent") partitioning is implemented as an option. Query optimizer is capable of using only necessary partitions to speed up query execution. Partition independence allows DBMS to perform some operations concurrently on different partitions. Some maintenance operations can be performed against separate partitions rather than full table. Manual partitioning using partition views is also possible.
  Postgres:		No.

• Use of partitions in query optimization

  Partitions can help solve performance problems. A query which only requires rows that correspond to a single partition (or range of partitions) can be executed using a partition scan rather than a table scan.

  MySQL:		N/A
  Oracle8:		Yes.
  Postgres:		N/A

Data size limits

Importance

Central database:		Critical
Lab-participants:		Important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Average

Detailed questions

• Max. number of rows and columns in a table

  MySQL:		Max. number of columns in one table - 3398
  Oracle8:		Max. number of columns in one table - 1000. Up to 32 columns in index key.
  Postgres:		Rows - unlimited, columns - 1600.

• Max. number of tables and indexes per table

  MySQL:		Number of tables - not limited, one table can have up to 16 indexes (256 bytes max. key length).
  Oracle8:		Unlimited.
  Postgres:		Unlimited.

• Max. size of a table row

  MySQL:		65534 (BLOB and TEXT not included).
  Oracle8:		Limited only by max. number of columns and max. size of columns of specific data types.
  Postgres:		8k, configurable to 32k. The limit will be removed in version 7.1.

VLDB implementations

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Good

Detailed questions

• Known VLDB implementations

  MySQL:		N/D
  Oracle8:		Terabyte databases exist.
  Postgres:		60GB databases exist.

Distributed databases

Access to multiple databases

Importance

Central database:		Less important
Lab-participants:		Not important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Average

Detailed questions

• Access to multiple databases in one session

  MySQL:		Only switching between databases. In each select data from different database can be taken.
  Oracle8:		Yes. Each session can use data from many instances, using transparent distributed SQL access.
  Postgres:		Only switching between 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.
  Oracle8:		Yes. Basic (read-only, snapshot) replication available in basic DBMS configuration. Advanced (symmetric, multimaster) replication is available as an option.
  Postgres:		No.

• Two phase commit

  Two-phase commit mechanism guarantees that all database servers participating in a distributed transaction either all commit or all roll back the statements in the transaction.

  MySQL:		No.
  Oracle8:		Yes.
  Postgres:		No.

Heterogeneous systems support

Heterogeneous system is a system that contains various types of database servers.

Importance

Central database:		Important
Lab-participants:		Not important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Good
Postgres	Poor

Detailed questions

• Gateways to other DBMSs

  MySQL:		None.
  Oracle8:		Yes. Gateways to other DBMSs are available. Heterogeneous services allow user to access non-Oracle data in transparent way.
  Postgres:		None.

Special data types

Large objects in database

Importance

Central database:		Important
Lab-participants:		Important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Average

Detailed questions

• Binary Large Objects

  MySQL:		There are four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB in MySQL. They differ only in the maximum length of the values they can hold. This types store up to 2^32(4GB) bytes of information.
  Oracle8:		Yes. LONG RAW (2 GB), BLOB (4 GB) and BFILE (external storage, up to 4 GB) data types.
  Postgres:		Must be defined by user (CREATE TYPE) - size is limited by max. row size. Postgres provides an interface supporting large binary objects. There are two built-in registered functions, lo_import and lo_export which are convenient for use in SQL queries. BLOBs are saved in files outside tables, only OID (object id) of the BLOB is stored in Postgres table. There is no size limit for such defined BLOBs (except file system limits).

• Character Large Objects

  MySQL:		The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements.
  Oracle8:		Yes. LONG (2 GB) and CLOB (4 GB) data types.
  Postgres:		Yes - TEXT type (size limited by max. row size).

Post-relational extensions

Importance

Central database:		Less important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Good

Detailed questions

• User-defined data types

  MySQL:		No.
  Oracle8:		Yes, user can define new complex data types.
  Postgres:		Users may add new types to Postgres using the CREATE TYPE command.

• Object-relational extensions

  The object-relational model allows users to define object types, specifying both the structure of the data and the methods of operating on the data, and to use these data types within the relational model.

  MySQL:		No.
  Oracle8:		Objects with persistent identity can be stored in database tables. They can contain complex data structures and user-defined methods. No inheritance is possible. Object views allow to use relational data as if they were stored as object types.
  Postgres:		Table structure can be inherited.

Support for special data types

Importance

Central database:		Less important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Average

Detailed questions

• Special data types (multimedia, etc.)

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

• Packages supporting special data types

  MySQL:		N/A
  Oracle8:		Many DBMS options supporting various special types of data, including full-text search, spatial data and time series (in future versions time series option functionality will be included directly into SQL analytic functions.
  Postgres:		Built-in functions supporting LOB types.

• Possibility of creating new special data types and programmatic support

  MySQL:		No.
  Oracle8:		It is possible to create user-defined complex data types, methods for object types, user-defined functions and SQL operators.
  Postgres:		CREATE OPERATOR defines a new user operator. CREATE AGGREGATE defines a new aggregate function.

Application development and interfaces

Embedded SQL

Embedded SQL is a method of use SQL statements in 3GL programs (e.g. in C/C++ or Cobol). The precompiler takes SQL statements embedded in 3GL program and converts them to standard language code.

Importance

Central database:		Less important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Good

Detailed questions

• Languages supported

  MySQL:		None.
  Oracle8:		Java (SQLJ), C/C++, COBOL, Pascal, FORTRAN, Ada.
  Postgres:		C (ecpg preprocessor).

Standard interfaces

Importance

Central database:		Important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Good

Detailed questions

• ODBC

  MySQL:		Yes (MyODBC code).
  Oracle8:		Yes.
  Postgres:		Yes.

• JDBC

  MySQL:		2 types of JDBC drivers are supported.
  Oracle8:		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).
  Postgres:		Postgres 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.

Additional interfaces

Importance

Central database:		Critical
Lab-participants:		Critical

Assessment

Product	Grade
MySQL	Very good
Oracle8	Very good
Postgres	Very good

Detailed questions

• Interfaces for PHP

  MySQL:		Yes.
  Oracle8:		Yes.
  Postgres:		Yes.

• Interfaces for root

  MySQL:		Yes.
  Oracle8:		Yes.
  Postgres:		Yes.

• Other non-standard interfaces

  MySQL:		PERL, C
  Oracle8:		Many. e.g. Oracle OCI - low level access to DBMS; OraPerl.
  Postgres:		C, C++, Libpgeasy, Perl, TCK/TK, Python.

Interoperability with Web technology

Importance

Central database:		Critical
Lab-participants:		Critical

Assessment

Product	Grade
MySQL	Good
Oracle8	Very good
Postgres	Good

Detailed questions

• Support from popular Web servers

  MySQL:		Yes (e.g. Apache http server).
  Oracle8:		Yes (e.g. Apache http server).
  Postgres:		Yes (e.g. Apache http server).

• Dedicated Web servers

  MySQL:		None.
  Oracle8:		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; for professional, scalable Web applications;
  Postgres:		None.

XML

Importance

Central database:		Important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Poor

Detailed questions

• XML support integrated in DBMS

  MySQL:		No.
  Oracle8:		Oracle delivers many XML tools integrated witch DBMS (parsers, processors XSLT etc.), e.g.: XDK - XML developer's Kit (XML parsers and XSLT processor with interfaces for Java, C, C++ and PL/SQL); XSU - XML SQL Utility; XSQL Servlet.
  Postgres:		No.

• Special solutions for storage of XML documents

  MySQL:		No.
  Oracle8:		XML documents can be stored in object data types in database tables. Oracle Internet File System can automatically parse XML files, store their contents in relational structures and re-create XML from relational data.
  Postgres:		No.

CASE

CASE stands for Computer Aided Software Engineering

Importance

Central database:		Important
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Poor

Detailed questions

• Support from CASE packages

  MySQL:		N/D
  Oracle8:		Oracle Designer is a powerful CASE tool highly integrated with Oracle DBMS and other Oracle products. Oracle DBMS is also supported by many leader CASE products.
  Postgres:		N/D

Reliability

Recovery from failures

Importance

Central database:		Critical
Lab-participants:		Important

Assessment

Product	Grade
MySQL	Average
Oracle8	Very good
Postgres	Average

Detailed questions

• Automatic recovery from failures

  MySQL:		No automatic recovery. The binary log can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash.
  Oracle8:		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.
  Postgres:		No. There is no transaction log, so the only recovery method is to restore database from backup.

Commercial issues

Prices

Importance

Central database:		Less important
Lab-participants:		Critical

Assessment

Product	Grade
MySQL	Very good
Oracle8	Poor
Postgres	Very good

Detailed questions

• Price of unlimited version

  MySQL:		Free
  Oracle8:		Number of processors * number of MHz * USD 15
  Postgres:		Free

• Price of the smallest multi-user installation

  MySQL:		Free
  Oracle8:		5 * USD 160
  Postgres:		Free

Technical support from manufacturer

Importance

Central database:		Critical
Lab-participants:		Important

Assessment

Product	Grade
MySQL	Average
Oracle8	Good
Postgres	Poor

Detailed questions

• Availability and quality of technical support

  MySQL:		Mailing lists and Web-site.
  Oracle8:		Technical support services available on several levels. Quality of these services is quite high.
  Postgres:		Mailing lists and Web-site.

• Price of technical support

  MySQL:		Paid technical support is available, prices - N/D.
  Oracle8:		22% of the prize annually.
  Postgres:		Free (for free version).

Position on the market

Importance

Central database:		Critical
Lab-participants:		Less important

Assessment

Product	Grade
MySQL	Poor
Oracle8	Very good
Postgres	Poor

Detailed questions

• Market share

  MySQL:		N/D
  Oracle8:		About 40% of commercial RDBMS market.
  Postgres:		N/D

• Specific market segments occupied

  MySQL:		Web servers (non-transactional services). Linux-based systems.
  Oracle8:		N/A
  Postgres:		Education. Small Web services.

Summary

Category	Problem	Importance		Assessment
		Central database	Lab-participants	MySQL	Oracle8	Postgres
Elementary features	Basic data types	C	C	B	C	A
	SQL	B	B	C	B	B
	Declarative constraints	B	B	C	A	A
	Programming abstractions	A	C	D	A	C
	Generation of ids	C	C	C	A	A
	National chars	B	C	B	A	B
Transactions	Transactions	A	C	D	A	A
	Locks	A	C	D	A	A
	Multiuser access	A	D	C	A	C
Programming in DB	Stored procedures and triggers	B	C	D	A	A
Administration	Access control	B	D	A	A	B
	Backup	A	C	C	A	C
	Data migration	C	C	A	B	A
Portability and scalability	Portability	B	C	B	A	B
	Scalability	A	C	B	A	C
Performance and VLDB	Query optimization	A	C	B	A	B
	Structures supporting optimization	B	D	D	A	B
	Support for OLAP	B	D	D	A	D
	Allocation of the disk space	A	C	C	A	C
	Size limits	A	B	B	A	C
	VLDB implementations	A	C	D	A	B
Distributed databases	Access to multiple databases	C	D	C	A	C
	Heterogeneous systems support	B	D	D	B	D
Special data types	Large objects	B	B	B	A	C
	Post-relational extensions	C	C	D	A	B
	Support for special data types	C	C	D	A	C
Application development and interfaces	Embedded SQL	C	C	D	A	B
	Standard interfaces	B	C	B	A	B
	Additional interfaces	A	A	A	A	A
	Web technology	A	A	B	A	B
	XML	B	C	D	A	D
	CASE	B	C	D	A	D
Reliability	Recovery	A	B	C	A	C
Commercial issues	Prices	C	A	A	D	A
	Technical support	A	B	C	B	D
	Position on the market	A	C	D	A	D

Authors

Konrad Bohuszewicz¹, Maciej Czy¿owicz², Micha³ Janik³, Dawid Jarosz¹, Piotr Mazan², Marcin Mierzejewski², Miko³aj Olszewski², Wiktor S. Peryt³, Sylwester Radomski³, Piotr Szarwas³, Tomasz Traczyk¹, Dominik Tukendorf², Jacek Wojcieszuk¹,

¹ Faculty of Electronics and Information Technology
² Faculty of Mathematics and Information Sciences
³ Faculty of Physics