Comparison of relational database management systems
Last updated
The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.
Note (1): Currently only supports read uncommitted transaction isolation. Version 1.9 adds serializable isolation and version 2.0 will be fully ACID compliant.
Note (2): MariaDB and MySQL provide ACID compliance through the default InnoDB storage engine.[71][72]
Note (3): "For other than InnoDB storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines."[73]
Note (4): Support for Unicode is new in version 10.0.
Note (1): Firebird 2.x maximum database size is effectively unlimited with the largest known database size >980 GB.[79] Firebird 1.5.x maximum database size: 32 TB.
Note (6): Using VARCHAR (MAX) in SQL 2005 and later.[82]
Note (7): When using a page size of 32 KB, and when BLOB/CLOB data is stored in the database file.
Note (8): Java array size limit of 2,147,483,648 (231) objects per array applies. This limit applies to number of characters in names, rows per table, columns per table, and characters per CHAR/VARCHAR.
Note (9): Despite the lack of a date datatype, SQLite does include date and time functions,[83] which work for timestamps between 24 November 4714 B.C. and 1 November 5352.
Note (10): Informix DATETIME type has adjustable range from YEAR only through 1/10000th second. DATETIME date range is 0001-01-01 00:00:00.00000 through 9999-12-31 23:59:59.99999.
Note (11): Since version 12c. Earlier versions support up to 4000B.
Note (12): The 128PB limit refers to the storage limit of a single Informix server instance. Informix v12.10 and later versions support using sharding techniques to distribute a table across multiple server instances. A distributed Informix database has no upper limit on table or database size.
Note (13): Informix DECIMAL type supports up to 32 decimal digits of precision with a range of 10−130 to 10125. Fixed and variable precision are supported.
Tables and views
Information about what tables and views (other than basic ones) are supported natively.
Note (1): Server provides tempdb, which can be used for public and private (for the session) temp tables.[84]
Note (2): Materialized views are not supported in Informix; the term is used in IBM's documentation to refer to a temporary table created to run the view's query when it is too complex, but one cannot for example define the way it is refreshed or build an index on it. The term is defined in the Informix Performance Guide.[85]
Note (1): The users need to use a function from freeAdhocUDF library or similar.[121]
Note (2): Can be implemented for most data types using expression-based indexes.
Note (3): Can be emulated by indexing a computed column[122] (doesn't easily update) or by using an "Indexed View"[123] (proper name not just any view works[124]).
Note (4): Used for InMemory ColumnStore index, temporary hash index for hash join, Non/Cluster & fill factor.
Note (6): Can be implemented using Function-based Indexes in Oracle 8i and higher, but the function needs to be used in the sql for the index to be used.
Note (7): A PostgreSQL functional index can be used to reverse the order of a field.
Note (11): R-Tree indexing available in base edition with Locator but some functionality requires Personal Edition or Enterprise Edition with Spatial option.
Note (1): Both function and procedure refer to internal routines written in SQL and/or procedural language like PL/SQL. External routine refers to the one written in the host languages, such as C, Java, Cobol, etc. "Stored procedure" is a commonly used term for these routine types. However, its definition varies between different database vendors.
Note (2): In Derby, H2, LucidDB, and CUBRID, users code functions and procedures in Java.
Note (3): ENUM datatype exists. CHECK clause is parsed, but not enforced in runtime.
Note (5): Informix supports external functions written in Java, C, & C++.
Partitioning
Information about what partitioning methods are supported natively.
Note (1): Network traffic could be transmitted in a secure way (not clear-text, in general SSL encryption). Precise if option is default, included option or an extra modules to buy.
Note (2): Options are present to set a minimum size for password, respect complexity like presence of numbers or special characters.
Note (3): How do you get security updates? Is it free access, do you need a login or to pay? Is there easy access through a Web/FTP portal or RSS feed or only through offline access (mail CD-ROM, phone).
Note (4): Does database process run as root/administrator or unprivileged user? What is default configuration?
Note (5): Is there a separate user to manage special operation like backup (only dump/restore permissions), security officer (audit), administrator (add user/create database), etc.? Is it default or optional?
The SQL specification defines what an "SQL schema" is; however, databases implement it differently. To compound this confusion the functionality can overlap with that of a parent database. An SQL schema is simply a namespace within a database; things within this namespace are addressed using the member operator dot ".". This seems to be a universal among all of the implementations.
Both a schema and a database can be used to isolate one table, "foo", from another like-named table "foo". The following is pseudo code:
SELECT*FROMdatabase1.foo vs. SELECT*FROMdatabase2.foo (no explicit schema between database and table)
SELECT*FROM[database1.]default.foo vs. SELECT*FROM[database1.]alternate.foo (no explicit database prefix)
The problem that arises is that former MySQL users will create multiple databases for one project. In this context, MySQL databases are analogous in function to PostgreSQL-schemas, insomuch as PostgreSQL deliberately lacks off-the-shelf cross-database functionality (preferring multi-tenancy) that MySQL has. Conversely, PostgreSQL has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality.
MySQL aliases schema with database behind the scenes, such that CREATE SCHEMA and CREATE DATABASE are analogs. It can therefore be said that MySQL has implemented cross-database functionality, skipped schema functionality entirely, and provided similar functionality into their implementation of a database. In summary, PostgreSQL fully supports schemas and multi-tenancy by strictly separating databases from each other and thus lacks some functionality MySQL has with databases, while MySQL does not even attempt to support standard schemas.
Oracle has its own spin where creating a user is synonymous with creating a schema. Thus a database administrator can create a user called PROJECT and then create a table PROJECT.TABLE. Users can exist without schema objects, but an object is always associated with an owner (though that owner may not have privileges to connect to the database). With the 'shared-everything' Oracle RAC architecture, the same database can be opened by multiple servers concurrently. This is independent of replication, which can also be used, whereby the data is copied for use by different servers. In the Oracle implementation, a 'database' is a set of files which contains the data while the 'instance' is a set of processes (and memory) through which a database is accessed.
Informix supports multiple databases in a server instance like MySQL. It supports the CREATE SCHEMA syntax as a way to group DDL statements into a single unit creating all objects created as a part of the schema as a single owner. Informix supports a database mode called ANSI mode which supports creating objects with the same name but owned by different users.
PostgreSQL and some other databases have support for foreign schemas, which is the ability to import schemas from other servers as defined in ISO/IEC 9075-9 (published as part of SQL:2008). This appears like any other schema in the database according to the SQL specification while accessing data stored either in a different database or a different server instance. The import can be made either as an entire foreign schema or merely certain tables belonging to that foreign schema.[184] While support for ISO/IEC 9075-9 bridges the gap between the two competing philosophies surrounding schemas, MySQL and Informix maintain an implicit association between databases while ISO/IEC 9075-9 requires that any such linkages be explicit in nature.
PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transactions with atomicity, consistency, isolation, durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is supported on all major operating systems, including Linux, FreeBSD, OpenBSD, macOS, and Windows, and handles a range of workloads from single machines to data warehouses or web services with many concurrent users.
In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas. Common examples of DDL statements include CREATE, ALTER, and DROP. If you see a .ddl file, that means the file contains a statement to create a table. Oracle SQL Developer contains the ability to export from an ERD generated with Data Modeler to either a .sql file or a .ddl file.
A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural key.
An SQL INSERT statement adds one or more records to any single table in a relational database.
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.
ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. ADO.NET is a set of computer software components that programmers can use to access data and data services from a database. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational data sources. ADO.NET is sometimes considered an evolution of ActiveX Data Objects (ADO) technology, but was changed so extensively that it can be considered an entirely new product.
ADOdb is a database abstraction library for PHP, originally based on the same concept as Microsoft's ActiveX Data Objects. It allows developers to write applications in a consistent way regardless of the underlying database system storing the information. The advantage is that the database system can be changed without re-writing every call to it in the application.
Multi-master replication is a method of database replication which allows data to be stored by a group of computers, and updated by any member of the group. All members are responsive to client data queries. The multi-master replication system is responsible for propagating the data modifications made by each member to the rest of the group and resolving any conflicts that might arise between concurrent changes made by different members.
A relational database management system uses SQL MERGE statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.
A spatial database is a general-purpose database that has been enhanced to include spatial data that represents objects defined in a geometric space, along with tools for querying and analyzing such data.
In relational databases, the information schema is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information that some databases make available through non-standard commands, such as:
=> SELECT count(table_name) FROM information_schema.tables; count ------- 99 => SELECT column_name, data_type, column_default, is_nullable FROM information_schema.columns WHERE table_name='alpha'; column_name | data_type | column_default | is_nullable -------------+-----------+----------------+------------- foo | integer | | YES bar | character | | YES => SELECT * FROM information_schema.information_schema_catalog_name; catalog_name -------------- johnd
The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.
Navicat is a series of graphical database management and development software produced by CyberTech Ltd. for MySQL, MariaDB, Redis, MongoDB, Oracle, SQLite, PostgreSQL and Microsoft SQL Server. It has an Explorer-like graphical user interface and supports multiple database connections for local and remote databases. Its design is made to meet the needs of a variety of audiences, from database administrators and programmers to various businesses/companies that serve clients and share information with partners.
An embedded database system is a database management system (DBMS) which is tightly integrated with an application software; it is embedded in the application. It is a broad technology category that includes:
A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.
DatabaseSpy is a multi-database query, design, and database comparison tool from Altova, the creator of XMLSpy. DatabaseSpy connects to many major relational databases, facilitating SQL querying, database structure design, database content editing, and database comparison and conversion.
In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:
In relational databases a virtual column is a table column whose value(s) is automatically computed using other columns values, or another deterministic expression. Virtual columns are defined of SQL:2003 as Generated Column, and are only implemented by some DBMSs, like MariaDB, SQL Server, Oracle, PostgreSQL, SQLite and Firebird.
Database Workbench is a software application for development and administration of multiple relational databases using SQL, with interoperationality between different database systems, developed by Upscene Productions.
The following outline is provided as an overview of and topical guide to MySQL:
This page is based on this Wikipedia article Text is available under the CC BY-SA 4.0 license; additional terms may apply. Images, videos and audio are available under their respective licenses.
