MYSQL vs PostgreSQL vs ORACLE - ByteScout
  • Home
  • /
  • Blog
  • /
  • MYSQL vs PostgreSQL vs ORACLE

MYSQL vs PostgreSQL vs ORACLE

MySQL, PostgreSQL, and Oracle are relational database management systems. The major difference between these three databases is that MySQL is open source whereas PostgreSQL is an open-source database management system and Oracle database is developed by Oracle corporation. These three databases support various programming languages. For instance, MySQL supports Java, C++, TCL, Perl, and Haskel. Oracle and PostgreSQL to support various programming languages.

Summary

  1. Trigger
  2. Index
  3. Backup
  4. Package
  5. Restore
  6. Performance Tuning
  7. Security Features

Many database administrators think that the performance of MySQL is better than PostgreSQL. On the other hand, Oracle has more reliable recovery methods than MySQL. Oracle too supports various programming languages and it has robust recovery methods. The most significant thing is MySQL supports both UNIX and Linux. Oracle too supports Unix and Linux. The thing to remember is that all these three databases are active and robust and are widely used in the market. This post will explain the technical difference between these three databases.
MYSQL vs PostgreSQL vs ORACLE

Trigger

A trigger is a named block in any RDBMS. It is one of the most important database objects. For instance, Trigger is stored in the Database and fired implicitly when a triggering event occurs. In Oracle, MySQL, or PostgreSQL, the event can be DML, DDL, system event, or user event.

Oracle Trigger Example

In Oracle, Trigger can be used to implement multiple business practices that cannot be implemented using integrity constraints such as UNIQUE, NOT NULL, and CHECK. The following example is displaying the Trigger:

CREATE OR REPLACE TRIGGER clients_audit_tg
    AFTER 
    UPDATE OR DELETE 
    ON clients
    FOR EACH ROW    
DECLARE
   first_transaction VARCHAR2(20);
BEGIN
   -- find the transaction type
   first_transaction := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

The above Trigger will be fired after a row in the table client is updated or deleted.

MySQL Trigger Example

MySQL Trigger will be activated when a specified event is executed for the table. The trigger can be executed for INSERT, UPDATE, and DELETE and it can be called before or after the event. The following example is displaying the Trigger.

CREATE TRIGGER before_emp_update 
    BEFORE UPDATE ON emp
    FOR EACH ROW 
 INSERT INTO emp_audit
 SET action = 'update',
     empno = OLD.empno,
     firstname = OLD.firstname,
     changedat = NOW();

PostgreSQL Trigger

In PostgreSQL, the trigger is also one special function. This special function is called automatically whenever an insert, update, and delete events occur. These are user-defined triggers. PostgreSQL gives two main triggers: row and statement-level triggers. The following example is displaying the Trigger.

First, create a new function called log_emp_name_changes:

CREATE OR REPLACE FUNCTION log_emp_name_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL  
  AS
$$
BEGIN
	IF NEW.emp_name <> OLD.emp_name THEN
		 INSERT INTO emp_audits(emp_id,emp_name,changed_on)
		 VALUES(OLD.id,OLD.emp_name,now());
	END IF;

	RETURN NEW;
END;
$$

Now, we will create a Trigger:

CREATE TRIGGER emp_name_changes
  BEFORE UPDATE
  ON emp
  FOR EACH ROW
  EXECUTE PROCEDURE log_empt_name_changes();

Index

In databases, an Index is a schema object that includes a record for each substance that arrives in the indexed column(s) of the table or cluster and gives immediate, quick access to rows. Oracle Database maintains several kinds of indexes: Normal indexes. (By default, Oracle Database creates B-tree indexes.) The concepts of Index in Oracle, MySQL, and PostgreSQL are the same but the syntaxes are different.

Oracle Index Example

In Oracle, to create an index in another schema, it is necessary to have the CREATE ANY INDEX system privilege. The following example is displaying how to create a simple index:

CREATE INDEX ord_client_ix_sample 
   ON sales (client_id, sales_id)
   COMPRESS 1;

The above command is displaying that the index will compress duplicated incidents of client_id column values.

MySQL Index Example

MySQL utilizes indexes to promptly get rows with particular column values. Without an index, MySQL browses the entire table to find the appropriate rows. The more extensive table, the slower it explores. The following example is displaying the MySQL index:

CREATE INDEX jobindex ON emp(jobTitle);

PostgreSQL Index Example

In PostgreSQL, An index is an ordered data structure that expedites the data retrieval on storage to support it. The following example is displaying the PostgreSQL index:

CREATE INDEX idx_phonebook_phone 
ON phonebook(phoneno);

Backup

Simply addressing, a database backup is a representational image of data. When the primary data is suffered, one can employ the backup to restore lost data (the real files that establish the database). This image copy covers significant parts of the database, such as the control file, archive logs, and datafiles-structures. In the case of a media fiasco, the database backup is the solution to favorably retrieving the data.

Backup in Oracle

The backup is the most important utility of Oracle. It belongs to the database administrator. One can utilize the BACKUP command to back up the main or a copy of a database, tablespace, or control file. It can also be used to take a backup of the server parameter file, archived redo log file, or backup set.

BACKUP DATABASE
  FORMAT '/disk5/arch_bps/db_%U.bck' 
  TAG monthly
  KEEP UNTIL TIME 'SYSDATE + 365'
  RESTORE POINT Q3FY09;

Backup in MySQL

In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the mysqldump utility using a particular flag “:–single-transaction”. The following example is displaying the MySQL backup command.

# mysqldump -u root -ptecmint --all-databases > all-databases.sql

Backup in PostgreSQL

On the other hand, the PostgreSQL database allows users to take backup using a utility. It contains the pg_dump utility to analyze backing up a separate database. This command is fired as a user with reading permissions. The backup utility comes under the database administrator command. The following command is displaying the use of a backup command.

pg_dump testdb > testdb.bak

Package

Just like a procedure, a package is also a schema object that gathers rationally associated variables, constants, subprograms, cursors, and exceptions. A package is collected and saved in the database. This is the main difference between Oracle, MySQL, and PostgreSQL. The package utility is only available in the Oracle database while it is not available in the MySQL or PostgreSQL databases.

A package always has a stipulation, which represents the public objects that can be called from outside the package. The following example is displaying how to create a package in Oracle.

Package in Oracle Example

CREATE OR REPLACE PACKAGE emppkg AS
  -- get employee's name
  FUNCTION emp_ename(empno NUMBER)
    RETURN VARCHAR2;
  -- get employee's salary
  FUNCTION emp_sal(empno NUMBER)
    RETURN NUMBER;
END emppkg;

CREATE OR REPLACE PACKAGE BODY emppkg AS
  -- get employee's fullname
  FUNCTION emp_ename(empno NUMBER) RETURN VARCHAR2 IS
      e_name VARCHAR2(60);
  BEGIN
    SELECT ename
    INTO e_name
    FROM emp
    WHERE emp_id = empno;

    RETURN e_name;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  WHEN TOO_MANY_ROWS THEN
    RETURN NULL;
  END; 

  FUNCTION emp_sal(empno NUMBER) RETURN NUMBER IS
    emp_salary NUMBER(8,2);
  BEGIN
    SELECT sal
    INTO emp_salary
    FROM emp
    WHERE emp_id = empno;

    RETURN emp_salary;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
      WHEN TOO_MANY_ROWS THEN
        RETURN NULL;
  END;
END emppkg;

Restore

Just like backup, the restore is one of the most valuable commands of any relational database management system. If you are working as a database administrator then backup and restore are two commands that are widely used in the database environment. If you have taken a backup of the database and now if you don’t know how to restore it then it is not a sign of a good database administrator

Restore in Oracle

In Oracle, the RESTORE command is used to restore, verify, or test and preview RMAN backups. Usually, database administrators restore backups when a hardware/software malfunction has broken a prevailing datafile, control file, or archived redo log or before implementing a point-in-time restoration. This is not the proper time to be harsh, but recovery without backups is not recommended.

You understand that you should have examined your restoration plan and that you should regularly back up a damaged database before trying to restore it. In oracle, to restore a dynamic backup of a file is to build it and make it accessible to the Oracle database server. To redeem a reestablished datafile is to renew it by utilizing redo logs, that is, documents of modifications performed to the database after the backup was taken. The following example is displaying the use of a restore command.

RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;

The above command is the official example available in the Oracle documentation. The important thing here to note is that the RESTORE command restores complete backups with various parameters and database administrators can restore files to their default location or a separate location.

Restore in MySQL

In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the “mysqldump utility using a particular flag “:–single-transaction“.

Restore in PostgreSQL

In PostgreSQL, it is essential to terminate all connections to that particular database before restoring and making the backup file. The psq utility enables users to restore SQL script files created by the pg_dump, pg_dumpall utilities. These utilities are responsible for generating suitable backed up files. The following is the example of Restore in PostgreSQL.

>pg_restore--dbname=mytestdb_tpl--section=pre-data c:\mybackup\mytestdb.tar

Performance Tuning

Performance Tuning in Oracle

In Oracle, SQL statements should be written properly (e.g the keywords must be in CAPS) to support simplicity. Now, this is not a tuning tip actually. Still, it’s important. Also, never alter indexed columns with various functions like TO_CHAR, RTRIM, UPPER, TRUNC because this will stop the tool from recognizing the index. If feasible, complete the adjustment on the constant part of the condition. If the indexed column is used within a function then users can think of building a function-based index. In Oracle SQL, it is invariably more useful to write distinct SQL statements for various jobs, but if users want to utilize one SQL statement, then they can create a pretty complicated statement somewhat less complicated by utilizing the UNION ALL operator.

Performance Tuning in MySQL

The best approach to know how your server uses its time is to profile the server’s pressure. By profiling the workload, users can show the most costly queries for more tuning. Here, time is the most valuable parameter because when users fire a query on the server, they don’t think much about little about anything except how speedily it runs.

MySQL takes the queries the server completes and delivers a table of responsibilities ordered by lowering the amount of response time, immediately welling up the most valuable and time-consuming jobs to the top so that users can understand where to direct their applications.

Performance Tuning in PostgreSQL

The most important part of PostgreSQL is vacuuming. A vacuum is a scan that identifies tuples. Failure to achieve this indicates that users have expired tuples staying in the system. These expired tuples are usually termed as bloat. Bloat arises often from records that are being eliminated, updated, or inserted.

Normally, PostgreSQL comes with a collection of default settings. This default arrangement is a configuration for adaptability and is basically the best view that attempts to satisfy all potential use cases of PostgreSQL. Fortunately for users, that suggests there are some possible quick gains if they begin to pinch the default setting. In PostgreSQL, Checkpoints determine the steady nature of the database. They are important and checkpoints must happen frequently enough to secure data modifications are forever saved to disk and the database is a uniform environment all the time. Another important parameter to recognize from the I/O performance aspect is checkpoint_completion_target to 0.5. For example, checkpoint_completion_target = 0.5 x checkpoint_timeout = 2.5 minutes. A checkpoint is a position in the transaction log chain at which all data files have been renewed or updated.

Security Features

MYSQL Security Features

MySQL security can be comfortably categorized into three groups:

  • Control of access.
  • User safety.
  • The factors and plug-ins that support the safety of MySQL.
  • Aspects for safety and standard security recommendations.
  • Controls for security practices.

Setting up permissions accurately is one of the most crucial components of identity management. Simple GRANT and REVOKE insinuations can be utilized to give or withdraw special rights to and from users. The appropriate assignment of permissions is believed to be crucial for the safety of any facilities operated by MySQL; the greater the authorization technique, less the possibility the intruder has of flouting the defense systems. Privileges are divided into two categories managerial (which enables users to regulate what activities are conducted MySQL accomplishes and how it accomplishes them) and database-specific.

PostgreSQL Security Features

On the other hand, PostgreSQL provides a variety of security mechanisms that a DBMS can utilize to satisfy the security requirements of his or her company. The phrase “security” relates to an extremely broad concept that can relate to extremely separate methods and procedures to accomplish numerous PostgreSQL elements.

  • Server Listen Address
  • Host-Based Authentication
  • LDAP Server
  • PAM Authentication
  • Role-Based Access Control

The theory of duties in Postgre means allowing a dba to set up the authorization of incoming user interconnection as well as permission arrangement at the table, column, and user stages. PostgreSQL contains the pg hba.conf document, which manually configures basic authentication and endorses bigger and more powerful authentication mechanisms against distant authentication mechanisms.

Oracle Security Features

With data-driven security, Oracle Database 19c provides multi-layered confidentiality, such as control mechanisms to evaluate risks, inhibit unauthorized access reporting, identify and notify database operations, and enforce data security controls in the directory.

  • Advanced Security Encryption.
  • Key Vault 
  • Database Auditing.
  • Audit Vault and Firewall.
  • Database Security Assessment Tool.

Oracle Data Safe allows businesses to recognize data responsiveness, evaluate data threats, mask confidential material, implement and track security measures, evaluate user security, and supervise user activity—all from a cohesive dashboard. These functionalities assist in the planning of Oracle Databases’ day-to-day compliance and security prerequisites, both on-site and in the cloud and other implementations

   

About the Author

ByteScout Team ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.  
prev
next