Comparison between MSSQL, Oracle, PostgreSQL, and MySQL
1. Overview
| Name | Microsoft SQL Server | Oracle | PostgreSQL | MySQL |
| Description | Microsofts relational DBMS | Widely used RDBMS | Widely used open-source RDBMS | Widely used open-source RDBMS |
| Primary database model | Relational DBMS | Relational DBMS | Relational DBMS | Relational DBMS |
| Secondary database models | Document store Graph DBMS |
Document store Graph DBMS RDF store |
Document store | Document store |
| DB-Engines Ranking |
Score1083.43 Rank #3 Overall #3 Relational DBMS |
Score1345.42 Rank #1 Overall #1 Relational DBMS |
Score509.86 Rank #4 Overall #4 Relational DBMS |
Score1268.35 Rank #2 Overall #2 Relational DBMS |
| Developer | Microsoft | Oracle | PostgreSQL Global Development Group | Oracle |
| Initial release | 1989 | 1980 | 1989 | 1995 |
| License | commercial | commercial | Open Source | Open Source |
| Cloud-based only | no | no | no | no |
| DBaaS offerings (sponsored links) |
ScaleGrid: Fully managed PostgreSQL hosting with high availability, dedicated servers, and superuser control on the #1 multi-cloud Amazon RDS alternative. Azure Database for PostgreSQL: A fully managed, scalable PostgreSQL relational database with high availability and security built-in at no extra cost |
Azure Database for MySQL: A fully managed, scalable MySQL relational database with high availability and security built in at no extra cost | ||
| Implementation language | C++ | C and C++ | C | C and C++ |
| Server operating systems | Linux Windows |
AIX HP-UX Linux OS X Solaris Windows z/OS |
FreeBSD HP-UX Linux NetBSD OpenBSD OS X Solaris Unix Windows |
FreeBSD Linux OS X Solaris Windows |
| Data scheme | yes | yes | yes | yes |
| Typing | yes | yes | yes | yes |
| XML support | yes | yes | yes | yes |
| Secondary indexes | yes | yes | yes | yes |
| SQL | yes | yes | yes | yes |
| APIs and other access methods | ADO.NET JDBC ODBC OLE DB Tabular Data Stream (TDS) |
JDBC ODBC ODP.NET Oracle Call Interface (OCI) |
ADO.NET JDBC native C library ODBC streaming API for large objects |
ADO.NET JDBC ODBC Proprietary native API |
| Supported programming languages | C# C++ Delphi Go Java JavaScript (Node.js) PHP Python R Ruby Visual Basic |
C C# C++ Clojure Cobol Delphi Eiffel Erlang Fortran Groovy Haskell Java JavaScript Lisp Objective C OCaml Perl PHP Python R Ruby Scala Tcl Visual Basic |
.Net C C++ Delphi Java JavaScript (Node.js) Perl PHP Python Tcl |
Ada C C# C++ D Delphi Eiffel Erlang Haskell Java JavaScript (Node.js) Objective-C OCaml Perl PHP Python Ruby Scheme Tcl |
| Server-side scripts | Transact SQL, .NET languages, R, Python and (with SQL Server 2019) Java | PL/SQL | user-defined functions | yes |
| Triggers | yes | yes | yes | yes |
| Partitioning methods | tables can be distributed across several files (horizontal partitioning); sharding through federation | horizontal partitioning | partitioning by range, list and (since PostgreSQL 11) by hash | horizontal partitioning, sharding with MySQL Cluster or MySQL Fabric |
| Replication methods | yes, but depending on the SQL-Server Edition | Master-master replication Master-slave replication |
Master-slave replication | Master-master replication Master-slave replication |
| MapReduce | no | no | no | no |
| Consistency concepts | Immediate Consistency | Immediate Consistency | Immediate Consistency | Immediate Consistency |
| Foreign keys | yes | yes | yes | yes |
| Transaction concepts | ACID | ACID | ACID | ACID |
| Default Port | 1433 | 1521 | 5432 | 3306 |
| Concurrency | yes | yes | yes | yes |
| Durability | yes | yes | yes | yes |
| In-memory capabilities | yes | yes | no | yes |
| User concepts | fine-grained access rights according to SQL-standard | fine-grained access rights according to SQL-standard | fine-grained access rights according to SQL-standard | Users with fine-grained authorization concept |
2. Comparison
2.1 Access to the database and DB creation
2.1.1 Oracle
Oracle separates the process of the binary and database creation. Unlike other popular database systems, database creation involves much more steps.
The Database Configuration Assistant (DBCA) is the preferred way to create a database because it can do it in a much more automated approach. DBCA can be launched by the Oracle Universal Installer (OUI), depending on the type of install that you select. You can also launch DBCA as a standalone tool at any time after Oracle Database Installation.
You can run DBCA in interactive mode or non-interactive/silent mode. Interactive mode provides a graphical interface and guided workflow for creating and configuring a database. Non-interactive/silent mode enables you to script the database creation. You can run DBCA in non-interactive/silent mode by specifying command-line arguments, a response file or both.
2.1.2 MSSQL
Management Studio is a preferred method to create a new database. To create a database in Microsoft SQL Server, connect to the computer where Microsoft SQL Server is installed using an administrator account.
Start Microsoft SQL Server Management Studio and choose to create a database option. The wizard process will walk you through the process. If you prefer command line this can be done with CREATE DATABASE syntax.
2.1.3 MySQL
In order to access your MySQL database use mysql client. The database creation is as simple as CREATE DATABASE <name>.
2.1.4 PostgreSQL
PostgreSQL database has the option for multiple ‘schemas’ which operate similarly to databases in MySQL.
Schemas contain the tables, indexes, etc, and can be accessed simultaneously by the same connection to the database that houses them. Access methods for PostgreSQL are defined in a file: pg_hba.conf. It can be located in various places. On Ubuntu 14.04 it is located in /etc/postgresql/9.3/main/pg_hba.conf, on Centos 7 on the other hand it’s located by default in /var/lib/pgsql/data/pg_hba.conf.
2.2 Backup process
2.2.1 Oracle
Oracle has the most complex, dedicated built-in backup tool of all four servers described here; it’s called Recovery Manager (RMAN).
Oracle has the most complex, dedicated built-in backup tool of all four servers described here; it’s called Recovery Manager (RMAN).
RMAN allows you to run sophisticated backup policies and selective restores. The same operations usually require a lot of manual steps in other RDBMS.
We can take backups in two ways:
- disabling the database and copying physical files (so-called cold backup)
- using RMAN and make a backup without disabling the database (hot backup)
To make a hot backup, set the base in ARCHIVELOG mode. This will tell Oracle to not keep the copy of redo log files as an archivelogs.
2.2.2 MSSQL
In the MS SQL world, you can use the built-in T-SQL commands to backup and restore databases. There is no need to use tools like mysqlhotcopy and mysqldump.
MS SQL Server offers three different online backup strategies:
- Simple Recovery Model (ALTER DATABASE dbname SET RECOVERY SIMPLE)
- Full Recovery Model (ALTER DATABASE dbname SET RECOVERY FULL)
- Bulk-Logged Recovery Model (ALTER DATABASE dbname SET RECOVERY BULK_LOGGED)
The recommended model is the full recovery if no data loss is acceptable. This mode is similar to the MySQL feature when the binary log is enabled. You can recover the database to any point of time, but you should regularly back up the transaction log as well as the database.
The bulk-logged model can be used for large bulk operations such as importing data or creating indexes on big tables. It’s rather less common method to run a database, especially production. It does not support point-in-time recovery so it is generally used as a temporary solution.
The Simple model is useful when the database is rarely updated or for testing and development purposes. In SIMPLE mode, the transaction log of the database is cut each time after the transaction is completed. In the other modes, the log is truncated via CHECKPOINT statement or after the transaction backup file. In case the database is damaged, only the most recent backup can be recovered and all changes since this backup are lost.
2.2.3 MySQL
Two most popular backup utilities are available for MySQL and MariaDB, namely mysqldump logical backup and binary backup Percona XtraBackup and MariaBackup (a fork of Percona XtraBackup). MySQL Enterprise version offers also mysqlbackup which is similar to XtraBackup and MariaBackup hot backup tools.
2.2.4 PostgreSQL
Most DBMS's provide some built-in backup tools. PostgreSQL has pg_dump and pg_dumpall out of the box. However, you may want to use some other tools for your production databases. More information can be found in the top backup tools for PostgreSQL article.
2.3 Controlling Query execution and concurrency support
2.3.1 Oracle
In Oracle, all the database objects are grouped by schemas. Schemas are collection of database objects and all the database objects are shared among all schemas and users. It can be translated to MySQL databases. Even though it is all shared, each user can be limited to certain schemas and tables via roles and permissions. This concept is quite similar to MySQL databases.Hi
2.3.2 MSSQL
MS SQL Server organizes all objects, such as tables, views, and procedures, by database names. Users are assigned to a login, which is granted access to the specific database and its objects. Also, in SQL Server each database has a private, unshared disk file on the server.
2.3.3 MySQL
MySQL only has MVCC support in InnoDB. It is a storage engine and by default is available in MySQL. It also provides ACID-complaint features like foreign key support and transaction handling. By default, each query is treated as a separate transaction, which is a different approach than in Oracle DB.
2.3.4 PostgreSQL
Postgres engine performs concurrency control by using a method called MVCC (Multiversion Concurrency Control). For every user connected to the database, the Postgres database gives a snapshot of the database at a particular instance. When the database must to update an item, it will add the newer version and point the old version as obsolete. It allows the database to save overhead but requires a regulated sweep to delete the old, outdated data.
2.4 Security
2.4.1 Oracle
Security features are great, the system provides multi-layered security including controls to evaluate risks, prevent unauthorized data disclosure, detect and report on database activities and enforce data access controls.
2.4.2 MSSQL
Security features are modest, the RDBMS offers fewer features than Oracle but still much more than Open Source database systems.
2.4.3 MySQL
MySQL implements security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers.
2.4.4 PostgreSQL
PostgreSQL has ROLES and inherited roles to set and maintain permissions. PostgreSQL has native SSL support for connections to encrypt client/server communications. It also has Row Level Security.
In addition to this, PostgreSQL comes with a built-in enhancement called SE-PostgreSQL which provides additional access controls based on SELinux security policy.
2.5 Replication options
2.5.1 Oracle
Oracle offers logical and physical replication through a built-in Oracle Data Guard. It is an enterprise feature.
Data Guard is a Ship Redo / Apply Redo technology, "redo" is the information needed to recover transactions.
2.5.2 MSSQL
Microsoft SQL Server provides the following types of replication for use in distributed applications:
- Transactional replication
- Merge replication
- Snapshot replication
It can be greatly extended with Microsoft Integration Services, giving you an option to customize the replication flow out of the box.
2.5.3 MySQL
MySQL Replication is probably the most popular high availability solution for MySQL,
and widely used by top web services.
It is easy to set up but ongoing maintenance like software upgrades, schema changes, topology changes, failover, and recovery have always been tricky.
MySQL replication does not require any third-party tools, both master-slave and multi-master can be done out of the box.
The recent versions of MySQL added multi-source replication and Global transaction id which make it even more reliable and easier to maintain.
2.5.4 PostgreSQL
PostgreSQL has several options available, each with its own pros and cons, depending on what is needed through replication. The build options are based on Write-Ahead Log. Files are shipped to a standby server where they are read and replayed, or Streaming Replication, where a read-only standby server fetches transaction logs over a database connection to replay them. In the case of a more sophisticated replication architecture, you would probably like to check Slony (master to multiple slaves) or Bucardo (multimaster).
3. Reference
https://db-engines.com/en/system/Microsoft+SQL+Server%3BOracle%3BPostgreSQL
https://db-engines.com/en/system/Microsoft+SQL+Server%3BMySQL%3BPostgreSQL
https://www.janbasktraining.com/blog/oracle-sql-server-mysql-and-postgresql/
https://stackshare.io/stackups/mysql-vs-oracle-vs-postgresql
https://www.educba.com/oracle-vs-postgresql/
https://www.janbasktraining.com/blog/oracle-sql-server-mysql-and-postgresql/
velog.io/@jisoo1170/Oracle-MySQL-PostgreSQL-%EC%B0%A8%EC%9D%B4%EC%A0%90%EC%9D%80