Postgres vs MySQL §

Key differences §

While PostgreSQL and MySQL are conceptually similar, there are many differences to consider before implementing them.

ACID compliance §

Atomicity, consistency, isolation, and durability (ACID) are database properties that ensure a database remains in a valid state even after unexpected errors. For example, if you update a large number of rows but the system fails midway, no row should be modified.

MySQL offers ACID compliance only when you use it with InnoDB and NDB Cluster storage engines or software modules. PostgreSQL is fully ACID compliant in all configurations.

Concurrency control §

Multiversion concurrency control (MVCC) is an advanced database feature that creates duplicate copies of records to safely read and update the same data in parallel. When you use MVCC, multiple users can read and modify the same data simultaneously without compromising data integrity.

MySQL databases do not offer MVCC, but PostgreSQL supports this feature.

Indexes §

Databases use indexes to retrieve data faster. You can index frequently accessed data by configuring the database management system to sort and store it differently from the other data.

MySQL supports B-tree and R-tree indexing that stores hierarchically indexed data. PostgreSQL index types include trees, expression indexes, partial indexes, and hash indexes. There are more options to fine-tune your database performance requirements as you scale.

Data types §

MySQL is a purely relational database. PostgreSQL, on the other hand, is an object-relational database. This means that in PostgreSQL, you can store data as objects with properties. Objects are a common data type in many programming languages like Java and .NET. Objects support paradigms like parent-child relationships and inheritance.

Working with PostgreSQL is more intuitive for database developers. PostgreSQL also supports other additional data types like arrays and XML.

Views §

A view is a data subset that the database system creates by pulling relevant data from multiple tables.

While MySQL supports views, PostgreSQL offers advanced view options. For example, you can precompute some values in advance (like the total value of all orders over a given period) to create materialized views. Materialized views improve database performance for complicated queries.

Stored procedures §

Stored procedures are structured query language (SQL) queries or code statements you can write and save in advance. You can reuse the same code repeatedly, which makes database management tasks more efficient.

While both MySQL and PostgreSQL support stored procedures, PostgreSQL allows you to call stored procedures written in languages other than SQL.

Triggers §

A trigger is a stored procedure that runs automatically when a related event occurs in the database management system.

In a MySQL database, you can only use AFTER and BEFORE triggers for SQL INSERT, UPDATE, and DELETE statements. That means the procedure will run automatically before or after the user modifies the data. In contrast, PostgreSQL supports the INSTEAD OF trigger, so you can run complex SQL statements using functions.

How to choose between PostgreSQL vs MySQL §

Both relational databases are suitable for most use cases. However, you may consider the following factors before making a final decision.

Application scope §

PostgreSQL is better suited for enterprise-level applications with frequent write operations and complex queries.

However, you can start a MySQL project if you want to prototype, create internal applications with fewer users, or create an information storage engine with more reads and infrequent data updates.

Database development experience §

MySQL is more suitable for beginners and has a shorter learning curve. It takes less time to build a new database project from scratch. It’s simple to set up MySQL as a standalone product or bundle it with other web development technologies like the LAMP stack.

PostgreSQL, on the other hand, can be much more challenging for newcomers. It typically requires complex infrastructure setup and troubleshooting experience.

Performance requirements §

If your application requires frequent data updates, PostgreSQL is a better choice. However, if you require frequent data reads, MySQL is preferred.

Write performance

MySQL uses write locks to achieve real concurrency. For example, if one user is editing the table, another user may have to wait until the operation finishes before changing the table.

However, PostgreSQL has built-in multiversion concurrency control (MVCC) support without read-write locks. This way, PostgreSQL databases perform better in the case of frequent and concurrent write operations.

Read performance

PostgreSQL creates a new system process with significant memory allocation (about 10 MB) for every user connected to the database. It requires memory-intensive resources to scale for multiple users.

On the other hand, MySQL uses a single process for multiple users. As a result, MySQL database outperforms PostgreSQL for applications that mainly read and display data to users.

Reference §

MySQL vs. PostgreSQL - Comparing Relational Database Management Systems (RDBMS) - AWS https://mp.weixin.qq.com/s/xf7qb4oAVHyi4_U32FSKPA