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://severalnines.com/database-blog/basic-administration-comparison-between-oracle-mssql-mysql-postgresql

https://www.janbasktraining.com/blog/oracle-sql-server-mysql-and-postgresql/

http://www.postgresonline.com/journal/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html

https://stackshare.io/stackups/mysql-vs-oracle-vs-postgresql

https://www.educba.com/oracle-vs-postgresql/

https://www.altexsoft.com/blog/business/comparing-database-management-systems-mysql-postgresql-mssql-server-mongodb-elasticsearch-and-others/

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