Why Postgress ? - rajansinghal/prep GitHub Wiki

MySQL will be ideal for your project if you require a high-security RDBMS for web applications or custom solutions,

and PostGresql is fully SQL compliant RDBMS capable of performing complex tasks I.E complex procedures, integration, intricate designs and data integrity, and not around high-speed and ease of setting up.

https://hackr.io/blog/postgresql-vs-mysql

Performance

PostgreSQL is appropriate for use in large systems where data needs to be authenticated and read/write speeds are critical for success. Moreover, it also supports a number of performance enhancers which are only available in proprietary solutions, including concurrency without read locks, SQL server, and Geospatial data support among many others. In general, PostgreSQL is best suited for systems that require execution of complex queries, or data warehousing and data analysis.

MySQL is the first choice for those web-based projects which require a database merely for data transactions and not anything intricate. It works exceptionally well in Online Analytical Processing (OLAP) and Online Transaction Processing (OLTP) systems where only high read speeds are necessary. However, MySQL will start underperforming once it is stressed with heavy loads or complex queries.

While we discuss where these systems can be employed, the actual performance of an RDBMS can be measured only by evaluating the metrics of possible scenarios as it majorly depends on the requirements and the nature of the project it is being used for.

Security

The security of a DBMS profoundly impacts its reliability and emerges as one of the top characteristics to look for in a system. Database security points towards the collective use of processes and tools to secure the DBMS or database from threats and attacks from illegitimate sources. MySQL is highly secure and includes multiple security features, some rather advanced in its quiver. It executes security protocols based on the Access Control Lists (ACL) for user operations such as connections and queries. PostgreSQL offers native SLL support for connections for the encryption of client/server communications. Moreover, a built-in enhancement named SE-PostgreSQL which provides additional access controls based on SELinux policy.

Postgres Advantages over MySQL

Postgres is an object-relational database, while MySQL is a purely relational database. This means that Postgres includes features like table inheritance and function overloading, which can be important to certain applications. Postgres also adheres more closely to SQL standards.

Postgres handles concurrency better than MySQL for multiple reasons:

Postgres implements Multiversion Concurrency Control (MVCC) without read locks Postgres supports parallel query plans that can use multiple CPUs/cores Postgres can create indexes in a non-blocking way (through the CREATE INDEX CONCURRENTLY syntax), and it can create partial indexes (for example, if you have a model with soft deletes, you can create an index that ignores records marked as deleted) Postgres is known for protecting data integrity at the transaction level. This makes it less vulnerable to data corruption.